# Average If and #DIV/0!

Discussion in 'Business Applications' started by laigUT, Oct 25, 2011.

Not open for further replies.

Joined:
Oct 25, 2011
Messages:
3
In my sheet I am using conditional formating to return 4-color traffic lights (using number 0-3). At the bottom of each colum I want the average of all number 1-3, excluding any 0s. I am using the formula =AVERAGEIFS(I3:I8,I3:I8,"<=3",I3:I8,">=1").

This is providing the correct answer unless it is a column of all 0s. How can I have the current formula I am using but also if the answer is #DIV/0! for it to return a zero instead of the error message?

An example of what I am doing is attached. Thanks for your help.

File size:
11.5 KB
Views:
49
2. ### Rollin_Again

Joined:
Sep 4, 2003
Messages:
4,912
Check out the ISERROR function. You can use it inside of another IF statement.

Rollin

Joined:
Oct 25, 2011
Messages:
3
Where would the ISERROR function be inserted into an AVERAGEIF function? Or do I need to use a normal IF function?

I am basically trying to say average I3:I8 if the values are <=3 and >=1. If those statements are not true then return a value of 0.

Joined:
Oct 25, 2011
Messages:
3
The other formula I tried was:
=IF(AND(E3:E8<=3,E3:E8>=1),AVERAGE(E3:E8),"0")

But this only returned the VALUE error?

Thank you so much for your help! I am trying to teach myself how to use Excel with only moderate success so far.

Joined:
Mar 27, 2008
Messages:
6,552
First Name:
Hans
=IF(ISERROR(AVERAGE(E3:E8)),0,AVERAGE(E3:E8))

This is the way ISERROR() works

As Seen On