How do I ignore cells with errors when calculating an average?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to average a bunch of non-adjacent cells as follows:

=AVERAGE(B2,D2,F2,H2,J2,L2,N2,P2,R2,T2,V2,X2,Z2,AB2,AD2,AF2,AH2,AJ2,AL2,AN2,AP2)

My problem is that a couple of the cells display #DIV/0! As such, my
average calcuation also compes up with #DIV/0!

I would like to calculate the average but ignore any cells with the error.

Suggestions?

thx
 
Fix the root source, assuming they retrun error because cell in those
formulas are blan, use

=IF(B2="","",A2/B2)

since average ignores blank it will now work

you can shorten your formula to

=AVERAGE(IF((MOD(B2:AP2,2)=0)*(B2:AP2<>""),B2:AP2))

assuming you average every other column just like in your example

it needs to be entered with ctrl + shift & enter


--

Regards,

Peo Sjoblom
 
What If I cannot shorten the formula as you suggested since I do not want to
include every column between B2 and AP2 in the average. I am only averaging
every other column. thx
 
The whole point was that it averages every other column as I
said " assuming you average every other column just like in your example"
I guess you didn't read that far?
 
That is what Peo's formula does.....averages the value from every second
column from A2 through AP2

Type it in one cell then hit CTRL + SHIFT + ENTER to finish.

It will look like {=AVERAGE(IF((MOD(B2:AP2,2)=0)*(B2:AP2<>""),B2:AP2))} after
you hit CTRL + SHIFT + ENTER

Excel adds the curly brackets.


Gord Dibben Excel MVP
 
I think Peo dropped Column() from his formula.

And I read your problem slightly differently. I read it that any of B2, D2,
...., AP2 could be a div/0 error.

If you have at least one number in that range, then I think that this array
formula will work:

=AVERAGE(IF((MOD(COLUMN(B2:AP2),2)=0)*(ISNUMBER(B2:AP2)),B2:AP2))
 
That did it Dave. Thanks!

Dave Peterson said:
I think Peo dropped Column() from his formula.

And I read your problem slightly differently. I read it that any of B2, D2,
...., AP2 could be a div/0 error.

If you have at least one number in that range, then I think that this array
formula will work:

=AVERAGE(IF((MOD(COLUMN(B2:AP2),2)=0)*(ISNUMBER(B2:AP2)),B2:AP2))
Click to expand...
 
Back
Top