Forum Discussion
How to exclude specific cells from an AutoSum?
Is it possible to do something like...
=AVERAGE(B2:B318) -- except for B7, B100, B103, and B258
How does one write that?
Thanks in advance!
18 Replies
ElJarrod =Averageif(B3:B318,"<>#DIV/0!") , can change range as you need will exclude those regardless of how many and wherever they occur.
OR
in your autosum cell you can edit sum formula like this =SUMIF(D6:D14,"<>#DIV/0!") this will eliminate them in first place hence you don't need to do in average.
Thanks.
u want to get rid of DIV 0 errors =if(ISERROR(A1/A2),"",A1/A2)
You're right that this formula gets rid of the error message, but not of the underlying condition that is causing the DIV/0 error. There are times when (for data integrity reasons) you'd be better off seeking to eliminate the cause of the error rather than just masking over it. Not always; when that isn't needed your formula (or the one Wyn suggested) is worth knowing.
=AVERAGE(B2:B6,B8:B99,B101,B104:B257,B259:B318) -- excludes B7, B100, B103, and B258
- take a look at the AVERAGEIFS function. You'll need to add an additional column that somehow sets apart those cells you don't want), but it should do what you ask.
Now, let me ask: how is the data arranged in the first place that would cause you to need to exclude some records? There may be an entirely legitimate reason, but it sounds like there are many things that do belong together, but others that don't. So why are they there in the first place? As I said, maybe a totally valid reason....I'm just curious whether there's an underlying design issue here.- The data here are various elements of video content consumption. I have a column for "Content Completion Rate" (Content Completes ÷ Content Starts) and another for "Avg. Content Time Spent. (Content Time Spent ÷ Content Starts)."
For the former, the issue occurs when zero "Content Starts" actually occurred. Same thing for the latter. In both cases, the result is a div/0, which is problematic because I'm trying to get an average and quartiles for all the data in these columns.
In the grand scheme, I'd love to never have an instance of zero content starts! Hopefully this whole exercise helps me get to that point.So in those rows where there's a zero divisor,ElJarrod , you could use the formula (edited, obviously, for real references) =IF(DivisorCell=0,"",NumeratorCell/DIvisorCell)
Then the cell that would otherwise have an error will be left totally blank, and not figured in with the average of the whole column.
That would be preventive action rather than contingent, always preferable.
And, of course, longer term (is this a streaming service of some kind?)...you want there never to be zero starts, etc. 🙂🙂
mathetes Thanks for recommending AVERAGEIFS, that will come in handy.
To answer your question, the reason I was trying exclude specific cells is because I had some div/0 things happening that were making it so my AutoSum Average, Median, and Quartiles weren't working. I realized there were very few instances of div/0 though, so I just manually turned them into zeros and the functions calculated out correctly.
ElJarrod Wyn's suggestion is a better one than turning those error messages into zero--doing that will distort (perhaps only slightly) the averages that result.
If possible though (and maybe it's just not that important), it would be better overall to deal with the underlying problem and eliminate those DIV/0 errors to begin with.
