Results 1 to 6 of 6

Thread: Help With AVERAGIF

  1. #1

    Unhappy Help With AVERAGIF



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello everyone,

    I created a tracking spreadsheet for a facility to determine opportunities in space utilization. I created weekly, monthly, and daily averages (for specific time frames). This tracking method relies on staff doing rounds and head counts which, unfortunately, doesn't happen as consistently as I would prefer leaving zeros or empty spaces in the input sheets. I would like to change the formula from a regular =AVERAGE to an =AVERAGEIF that only considers values greater than 0. I tried multiple variations based on other blog posts but nothing seems to work. Here is the formula I am trying to fix:

    =AVERAGEIF((Mon!B5:D7,Tue!B5:D7,Wed!B5:D7,Thu!B5:D7,Fri!B5:D7,Sat!B5:D7,Sun!B5:D7),">0")

    As you can see, I'd like to take the average of a range (B5:D7) from multiple sheets (Mon!, Tue!, Wed!, etc) of all values greater than zero. I have also tried "<>" as the condition for to exclude empty cells but this didn't work either.

    What should I try to make this work?

  2. #2
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    794
    Articles
    0
    Excel Version
    2010
    With data values to average in A1:A4 cells on Sheet1, 2 and 3:
    Put the below formula somewhere in Sheet1. Adjust the formula accordingly if you want the result in a different sheet.

    =(SUM(A1:A4)+SUM(Sheet2!A1:A4)+SUM(Sheet3!A1:A4))/(COUNTIF(A1:A4, "<>"&0)+COUNTIF(Sheet2!A1:A4,"<>"&0)+COUNTIF(Sheet3!A1:A4, "<>"&0))

  3. #3
    Worked! Thank you!

  4. #4
    Actually, after I started entering some data I realized it was simply adding everything and not taking the average. It's as if its dividing by 1. Do you know why this is?

  5. #5
    Never mind. I figured it out. I was just missing some brackets.

  6. #6
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    794
    Articles
    0
    Excel Version
    2010
    Glad you solved it, and sorry Ive only just picked up your query. I used to get an email telling me about replies, but its not working any more.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •