AVERAGE problems...

[S3NTYN3L]

I've got cell C7 displaying the average for C8:C20 ($2.409)

In cell E8 I have the following formula:
=AVERAGE(IF($C$8:$C$20>$C$7,$C$8:$C$20,""))

This gives me the average of all data in C8:C20 that is GREATER tha
C7's result. ($2.419)


In cell E10 I've got basically the same formula.
=AVERAGE(IF($C$8:$C$20<$C$7,$C$8:$C$20,""))

This SHOULD return an average of all the data that's LOWER than C7'
result.
The correct answer should be $2.399.

For some reason the formula in E10 won't ignore empty cells or thos
containing a zero.

Can someone please provide me with a fix?
I've been racking my brain for almost a week...

Attached is an example of what I've described above.
If I can get this to work I'm going to be using the formulas, in a Fue
Log that I've created, as conditional formatting formulas..

+-------------------------------------------------------------------
|Filename: Forum Help.zip
|Download: http://www.excelforum.com/attachment.php?postid=4267
+-------------------------------------------------------------------

 

[daddylonglegs]

Average doesn't ignore zeroes, amend formula to make it do so

=AVERAGE(IF($C$8:$C$20<$C$7,IF($C$8:$C$20<>0,$C$8:$C$20,"")))

 

[Morrigan]

Since all the blank cells are consider to be zero which is less than the
value in C7, ie. your formula is averaging (3 x 2.399 + 7 x 0)/10. To
fix it, you can simply put another argument:

=AVERAGE(IF($C$8:$C$20<$C$7,IF($C$8:$C$20<>"",$C$8:$C$20,"")))


Hope it helps.

 

[Domenic]

Try...

=AVERAGE(IF($C$8:$C$20>0,IF($C$8:$C$20<$C$7,$C$8:$C$20)))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[S3NTYN3L]

That fixed it.

I could have sworn I tried that variation of the formula before.
Like I've said, I've been at this one for about a week...

Thanks again all!