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]

Yes, here they are:

B9 = 10
C9 = 20
D9 = 30

Thanks Aladin!

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

yields 10. If we delete the contents of B9 for example, we would get 0 as it should.

 

[dzilla]

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

yields 10. If we delete the contents of B9 for example, we would get 0 as it should.

Ok, so I suppose my real question is this: Is there any logic or arguments to add to that formula which can tell if the amount entered into D9 is greater than C9, and then force a value of 0 into E9 (the formula cell)?

 

[barry houdini]

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 average of B9 and D9........

From your original description it seems to me that you want just the average of B9 and D9.....unless that's >= 20 (which is defined in C9) in which case show zero. In which case try

=IF((B9+D9)/2 >= C9,0,(B9+D9)/2)<c9,(b9+d9) 2,0)<="" html=""></c9,(b9+d9)>

 

[Aladin Akyurek]

Ok, so I suppose my real question is this: Is there any logic or arguments to add to that formula which can tell if the amount entered into D9 is greater than C9, and then force a value of 0 into E9 (the formula cell)?

Not sure what you are after. Entertaining a guess...

=IF(D9>C9,0,AVERAGEIF(B9:D9,"<20"))

Is this in the ballpark?

 

[magicmark]

Try this

=IF(AVERAGE(B9:D9)<20,AVERAGE(B9:D9),0)

 

[dzilla]

I can confirm that both Barry's and Aladin's formulas work and indeed display 0 in the formula cell of E9 . I couldn't get magicmark's to work...

Thank you all very much - you guys are genius with these formulas!

 

[vogel997]

The result will be 10, 10 is the only number smaller than 20, so the average will be 10 and not 0

 

[FDibbins]

If you are testing to see if any of the values are >20, and then waht to return 0 if any are, then try this...
=IF(MAX(B10:D10)>20,0,AVERAGEIF(B9:D9,"<20"))