# AVERAGEIF Question

#### dzilla

##### New Member
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!

### Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Try...

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

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

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!.

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)

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)

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?

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?

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?

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

Yes, here they are:

B9 = 10
C9 = 20
D9 = 30

Replies
13
Views
483
Replies
2
Views
294
Replies
2
Views
96
Replies
5
Views
240
Replies
5
Views
473

1,196,306
Messages
6,014,566
Members
441,828
Latest member
cofracr

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back