I have a large spreadsheet where B46 =AVERAGE(G42,L42,Q42). But if any of the values are zero, I want the average to ignore the cell (divide by 2 or 1 instead of 3). There's an Excel help topic on this situation that averages the range A2:A7 with the array formula {=AVERAGE(IF(A2:A7<>0,A2:A7,""))}. How do I write the formula for my non-contiguous cells?
Average non-contiguous cells, ignoring zeros (1 Viewer)
- Thread starter bobfin
- Start date
Howdy. You could use an IF statement combined with COUNTIF and then use the average based on that.
________
Stobart motorsport
________
Stobart motorsport
Last edited:
Brianwarnock
Retired
- Local time
- Today, 15:25
- Joined
- Jun 2, 2003
- Messages
- 12,701
I'm not sure how that would work, but you could use Sum and If
average =Sum(g42,l42,q42)/sum(if(g42=0,0,1),if(l42-0,0,1),if(q42=0,0,1))
wouldn't like to write it for many cells.
Brian
average =Sum(g42,l42,q42)/sum(if(g42=0,0,1),if(l42-0,0,1),if(q42=0,0,1))
wouldn't like to write it for many cells.
Brian
Brianwarnock
Retired
- Local time
- Today, 15:25
- Joined
- Jun 2, 2003
- Messages
- 12,701
Rich
I think, well feel pretty sure, that average ignores blanks, but think I will check.
Brian
Edit yep it does.
I think, well feel pretty sure, that average ignores blanks, but think I will check.
Brian
Edit yep it does.
ok, i think there is a problem - when you show blanks instead of zeros (through Excel Options), the ZEROS ARE STILL THERE, they are just hidden. like their font is white.
my apologies. i projected this feature from pivot tables, i guess, where it works just fine..
this is for Excel 2007, btw, you may want to double check how it behaves in the previous version.
l
my apologies. i projected this feature from pivot tables, i guess, where it works just fine..
this is for Excel 2007, btw, you may want to double check how it behaves in the previous version.
l
Brianwarnock
Retired
- Local time
- Today, 15:25
- Joined
- Jun 2, 2003
- Messages
- 12,701
It still counts the cell in 2002.
Brian
Brian
