Forum Discussion
How to exclude specific cells from an AutoSum?
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. 🙂🙂
That formula is exactly the kind of thing I was looking for - thank you,
But what would the proper format be if I'm combining that =IF() with something like the following:
=QUARTILE(J2:J296, 3)
And long-term, you're 100 percent correct -- we never want zero starts. I've obviously discovered something that's either broken and/or ineffective. Which, frankly, is the purpose of this whole endeavour.ElJarrod I've never had occasion to use the QUARTILE function, but from what I've read of it, it usually will be operating on a column (or array) of numbers that have already been calculated. My formula above would be what you use to produce that array....and QUARTILE would pass over the few blank cells that result, not include them in the statistical analysis. In short, if my understanding is correct, there's no need to "nest" the function.
QUARTILE, along with other stats functions will ignore text, blanks, Booleans but will propagate errors such as #DIV/0! Zeros are taken into account as legal values. I have used the internally nested IF formula to replace the #DIV/0! error by FALSE.
p.s. My formulas always look strange to spreadsheet users because I treat any direct cell reference as an error, preferring to use defined Names to identify arrays of data instead.
= QUARTILE( IF( starts>0, completes / starts ), {0;1;2;3;4} )
would return the minimum, 1st quartile, median, 3rd quartile and maximum as an array or the values could be entered within individual formulae. The ">0" could be omitted because Excel treats non-zero numbers as TRUE. You might also wish to calculate
= COUNTIFS( starts, 0 ) / COUNT( starts )
