I have a series of cells of which I want a average,if one or two cells are blank the rest with values then no problem, but if all cells in the series are blank then I get #DIV/0! which I don’t want.
Here is my formula:
=SUM(T6:T18)/COUNTIF(T6:T18,">0")
Many thanks
Derek
First, your formula does not calculate an average, or it does it only if all cell values are >0.
Second, by definition of average you divide by the number of elements, if there are none then of course you get a div/0 error.
Third, if you really wanted an average, why not use AVERAGE(T6:T18) instead?
If you do not want an error to be displayed you could use something like =IF(COUNT(T6:T18),AVERAGE(T6:T18),"nil"), or =IFERROR(AVERAGE(T6:T18),"nil") but that suppresses any error.
thanks very very much, you have cured it completely, I could have gone on forever try to figure it out.
My old brain has spent too much time in noisy excavators to be sharp anymore.
Thanks again
Derek