AVERAGEIF Question

[dzilla]

Hi all,

I am sure this is pretty basic for most of you, but I am truly a novice in Excel formulas so here goes. I would like to display the results of the average of 2 different cells but only if the average of the two cells is less than a specified amount. The problem I am having is that if the average of the 2 cells is greater than the specified amount, the formula cell automatically displays the value of cell #1, when I wish it would display 0 (zero). So for example:

=AVERAGEIF(B9:D9,"<20")

Is there an additional argument that I can add to have the formula display a zero in the event that the average of B9 and D9 is greater than 20?

Thank you all!

 

[Aladin Akyurek]

Try...

=IFERROR(AVERAGEIF(B9:D9,"<20"),0)

 

[FDibbins]

Im confused? If your average formula is only taking values less than 20 into account, then the average cannot possibly be greater than 20??

 

[Aladin Akyurek]

Im confused? If your average formula is only taking values less than 20 into account, then the average cannot possibly be greater than 20??

When there is no value < 20 in B9:D9 or B9:D9 contains no numbers, the formula will end up in #DIV/0!.

 

[FDibbins]

Aladin, my question was in response to the OP's comment...

Is there an additional argument that I can add to have the formula display a zero in the event that the average of B9 and D9 is greater than 20?

I understand that >20 will produce an error, and maybe that is what they meant (on thinking about it again)

 

[dzilla]

Indeed, just a mistype there. Any idea how to have that value be zero?

Aladin, my question was in response to the OP's comment...


I understand that >20 will produce an error, and maybe that is what they meant (on thinking about it again)

 

[Aladin Akyurek]

Indeed, just a mistype there. Any idea how to have that value be zero?

Are you saying that

=IFERROR(AVERAGEIF(B9:D9,"<20"),0

doesn't satisfy?

 

[dzilla]

Are you saying that

=IFERROR(AVERAGEIF(B9:D9,"<20"),0

doesn't satisfy?

No it does not work. So for the above formula, it still shows the result as whatever occupies cell B9 rather than the numeral 0. Any ideas?

 

[Aladin Akyurek]

No it does not work. So for the above formula, it still shows the result as whatever occupies cell B9 rather than the numeral 0. Any ideas?

Would you post what you have in B9, C9, and D9?

 

[dzilla]

Would you post what you have in B9, C9, and D9?

Yes, here they are:

B9 = 10
C9 = 20
D9 = 30

Thanks Aladin!