Avoiding 0 or #Div/0

[Stu.martin01]

I need to avoid including empty cells or ones containing #Div/0 to calculate an average of a column that will not always be full. i.e

A1-8
A2-7
A3-1
A4-1
A5-0 or Div 0
A6-0 or Div 0
A7-0 or Div 0
A8-0 or Div 0

In any scenario i only want to Average the values of the cells which containing inputs (A1-A4 in this case) but cells A5-A8 must remain just not be counted. i presume it is an IF Formula but im being unsuccessful in producing one so, if anyone could write me the correct formula for what cell A9 should contain to produce an average without DIV 0 appearing

Thanks

Stu

[Pepe Le Mokko]

Try

Please Login or Register  to view this content.

[kbkumar]

Hi - If the values in cells A1:A8 are coming from a formula you can easily modify the formula to display "NA" instead of 0 or Div / 0 errors. By doing so, the average wont get impacted in cell A9 as typically the Average function does not take "NA"'s into consideration.

Thxs.

I need to avoid including empty cells or ones containing #Div/0 to calculate an average of a column that will not always be full. i.e

A1-8
A2-7
A3-1
A4-1
A5-0 or Div 0
A6-0 or Div 0
A7-0 or Div 0
A8-0 or Div 0

In any scenario i only want to Average the values of the cells which containing inputs (A1-A4 in this case) but cells A5-A8 must remain just not be counted. i presume it is an IF Formula but im being unsuccessful in producing one so, if anyone could write me the correct formula for what cell A9 should contain to produce an average without DIV 0 appearing

Thanks

Stu

[Stu.martin01]

How do i modify the formula to display NA instead of DIV 0?

Thanks
Stu

[kbkumar]

Change the existing formula in cell A1 to:

=if(iserror(YOUR EXISTING FORMULA),"NA",YOUR EXISTING FORMULA)

Repeat this for all cells down to A8. If you do this right, the average in A9 would be what you were looking for.

Thanks,

[Fotis1991]

IFERROR, as you use Eexcel 2007. =iferror(YOUR EXISTING FORMULA),"NA")

But why not AVERAGEIG, as suggested Or AVERAGEIFS?

[kbkumar]

Not sure that we will be able to eliminate "#Div/0!" errors appearing in any of the cells between A1:A8 by using AverageIF function.

Though i see that 0's could be eliminated from the ranges by using that...

Thanks,
K B

[Pepe Le Mokko]

Not sure that we will be able to eliminate "#Div/0!" errors appearing in any of the cells between A1:A8 by using AverageIF function.

Though i see that 0's could be eliminated from the ranges by using that...

Thanks,
K B

Perhaps you could do the effort to try it out?

Also read post #6 to learn XL 2010 / 2007 possibilities?