Forum Discussion
How to exclude specific cells from an AutoSum?
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.
I would use iferror to remove any #div/0 or #value etc.
so formula would work along the line for iferror(average...
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.
