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

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,AB 2,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

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

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



--

Regards,

Peo Sjoblom

"M Enfroy" wrote in message
...
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,AB 2,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

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

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" wrote:

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



--

Regards,

Peo Sjoblom

"M Enfroy" wrote in message
...
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,AB 2,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

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

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?

--

Regards,

Peo Sjoblom

"M Enfroy" wrote in message
...
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" wrote:

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



--

Regards,

Peo Sjoblom

"M Enfroy" wrote in message
...
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,AB 2,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

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

That is what Peo's formula does.....averages the value from every second
column from A2 through AP2

=AVERAGE(IF((MOD(B2:AP2,2)=0)*(B2:AP2<""),B2: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


On Mon, 31 Oct 2005 13:24:02 -0800, "M Enfroy"
wrote:

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" wrote:

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



--

Regards,

Peo Sjoblom

"M Enfroy" wrote in message
...
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,AB 2,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

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

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


M Enfroy wrote:

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" wrote:

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



--

Regards,

Peo Sjoblom

"M Enfroy" wrote in message
...
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,AB 2,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




--

Dave Peterson

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

That did it Dave. Thanks!

"Dave Peterson" wrote:

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


M Enfroy wrote:

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" wrote:

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



--

Regards,

Peo Sjoblom

"M Enfroy" wrote in message
...
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,AB 2,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




--

Dave Peterson