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

[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

 

[Peo Sjoblom]

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

 

[Guest]

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

 

[Peo Sjoblom]

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?

 

[Gord Dibben]

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

 

[Dave Peterson]

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))

 

[Guest]

That did it Dave. Thanks!

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))