| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
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 |
|
#2
|
|||
|
|||
|
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 |
|
#3
|
|||
|
|||
|
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 |
|
#4
|
|||
|
|||
|
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 |
|
#5
|
|||
|
|||
|
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 |
|
#6
|
|||
|
|||
|
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 |
|
#7
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Average Non-Continuous Cells Without Zero's | Excel Worksheet Functions | |||
| Limit or Exclude cells in Average and Sum formula | Excel Worksheet Functions | |||
| How do I use an average function, not counting cells containing a | Excel Worksheet Functions | |||
| average cells omitting nulls | Excel Worksheet Functions | |||
| how do you ignore blank cells | Excel Discussion (Misc queries) | |||
Linear Mode
