Averaging cells which contain #DIV/0!

[jlabsher]

OK, I've racked my brain and googled myself to death over this one.
have a column with a formula which gets data from anothe
column/formula. If all the rows of the spreadsheet aren't completed
the formula for the blank ones comes back #DIV/0!. Now, I know how t
remove that using iserror but, the formula already has an if statemen
and is pretty complicated. I managed to hide the #DIV/0! usin
conditional formatting, but now have been told I need to average th
numbers in the column.

If I use the average function, I get #DIV/0! as an answer and hav
tried several permutations, but cannot get an average.

How can I get an average of the completed cells to display

 

[Domenic]

Try...

=AVERAGE(IF(ISNUMBER(A1:A10),A1:A10))

...entered using CONTROL+SHIFT+ENTER.

Hope this helps

 

[Paul Corrado]

Not sure if this is the best way, but the following Array Formula should
work

=SUM(IF(ISERROR(D19:F19)=FALSE,D19:F19))/COUNT(IF(ISERROR(D19:F19)=FALSE,D19
:F19))

Use CTRL+SHIFT+ENTER when you enter the formula and it will appear in {}'s

 

[Guest]

This sounds like what I need, also. Except in my situation, the cells I need
to find the average of are not contiguous.

 

[Domenic]

This sounds like what I need, also. Except in my situation, the cells
need
to find the average of are not contiguous.

Which cells do you want to average

 

[Guest]

E10,E13,E16,E19,E22. These values were all derived from a sumif.

 

[Domenic]

Try the following array formulas, entered using CONTROL+SHIFT+ENTER...

=AVERAGE(IF((MOD(ROW(E10:E22),3)=1)*(ISNUMBER(E10:E22)),E10:E22))

OR

=IF(COUNT(E10,E13,E16,E19,E22),AVERAGE(IF((MOD(ROW(E10:E22),3)=1)*(ISNUMBER(E10:E22)),E10:E22)),"")

Hope this helps!

E10,E13,E16,E19,E22. These values were all derived from a sumif.

 

[Guest]

That did it! Thanks!

Try the following array formulas, entered using CONTROL+SHIFT+ENTER...

=AVERAGE(IF((MOD(ROW(E10:E22),3)=1)*(ISNUMBER(E10:E22)),E10:E22))

OR

=IF(COUNT(E10,E13,E16,E19,E22),AVERAGE(IF((MOD(ROW(E10:E22),3)=1)*(ISNUMBER(E10:E22)),E10:E22)),"")

Hope this helps!