Ask Your Question

how do I rid myself of #DIV/0!

asked 2015-06-01 20:24:05 +0200

muppet7 gravatar image

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

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2015-06-01 20:53:38 +0200

erAck gravatar image

updated 2015-06-01 20:54:19 +0200

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.

edit flag offensive delete link more


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

muppet7 gravatar imagemuppet7 ( 2015-06-02 10:44:59 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2015-06-01 20:24:05 +0200

Seen: 4,611 times

Last updated: Jun 01 '15