Another Average or AverageIf Issue

**scarames** · 08-18-2009, 09:58 AM

I've got a spreadsheet that shows the averages of riders per revenue hour. First, I don't like averaging a row of averages, but you gotta do what you gotta do. But there are some routes that we've eliminated or changed and so won't have any more entries. Because of history, I've got to leave them on the sheet. When I average the column, it counts the blanks or zeros. Apparently, I'm doing something wrong, because I can't get the averageif to work here. Can you help? For example, row 44 should be the average of rows 6-42. But of course, without the blank cells. Thanks Excel 2007

Scarames

**NBVC** · 08-18-2009, 10:05 AM

Does this work for you?

=AVERAGEIF(B6:B42,">0")

**daddylonglegs** · 08-18-2009, 10:08 AM

Your existing formula seems OK to me. AVERAGE function ignores blank cells anyway, you'd only need AVERAGEIF if you have zeroes in there, which you don't

**NBVC** · 08-18-2009, 10:12 AM

I assumed based on this:

it counts the blanks or zeros

that there may be zeroes... so safeway...

Also, the grey cells in column K are hidden 0's..

**scarames** · 08-18-2009, 10:49 AM

DaddyLongLegsUnfortunately it didn't work tho, the average of 18.06 was incorrect. When I manually took out the empty cells I would get a different answer. That's why I wanted the correct formula.

**scarames** · 08-18-2009, 10:52 AM

Originally Posted by NBVC

Does this work for you?

=AVERAGEIF(B6:B42,">0")

This works with the same answer I'd get if I manually took out the blanks or zeros. Thanks so much, I must have just been missing an argument somewhere.

**NBVC** · 08-18-2009, 11:09 AM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save