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
|