Dec 06 2019 03:41 PM
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!
Dec 06 2019 04:01 PM
Dec 06 2019 04:36 PM
@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.
Dec 07 2019 03:07 AM
You can also use AGGREGATE to ignore errors = AGGREGATE(1,6, A1:A10) will Average and Ignore errors
Cheers
Wyn
Dec 07 2019 05:43 AM
@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.
Dec 07 2019 06:18 AM
=AVERAGE(B2:B6,B8:B99,B101,B104:B257,B259:B318) -- excludes B7, B100, B103, and B258
Dec 07 2019 06:31 AM - edited Dec 07 2019 06:34 AM
u want to get rid of DIV 0 errors =if(ISERROR(A1/A2),"",A1/A2) @ElJarrod
Dec 07 2019 03:10 PM
Dec 07 2019 07:20 PM
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.
Dec 08 2019 01:42 AM
I agree. There needs to be a reason for discounting A2=0 beyond the fact that it messes up the calculation. If A2 were equal to 0.0000000001 rather than 0 it would have a major impact on the average but no error would show.
My normal formula for eliminating 0s, if that is indeed the solution to the problem, is
= IF( denominator, numerator / denominator )
leaving the result of FALSE for zero or blanks, which is ignored by most aggregation functions.
Dec 08 2019 01:47 AM
data integrity though is another question all together @mathetes I agree
Dec 08 2019 09:19 PM
I would use iferror to remove any #div/0 or #value etc.
so formula would work along the line for iferror(average...
Dec 09 2019 02:48 PM
Dec 09 2019 02:59 PM - edited Dec 09 2019 02:59 PM
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. :):)
Dec 09 2019 05:19 PM - edited Dec 09 2019 05:20 PM
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.
Dec 10 2019 12:54 AM
= 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 )
Dec 10 2019 05:58 AM
@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.
Dec 10 2019 07:05 AM
@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.
Dec 10 2019 07:52 AM - edited Dec 10 2019 07:53 AM
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.