Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenTue, 02 Jun 2015 10:44:59 +0200how do I rid myself of #DIV/0!https://ask.libreoffice.org/en/question/51439/how-do-i-rid-myself-of-div0/ 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
DerekMon, 01 Jun 2015 20:24:05 +0200https://ask.libreoffice.org/en/question/51439/how-do-i-rid-myself-of-div0/Answer by erAck for <p>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</p>
https://ask.libreoffice.org/en/question/51439/how-do-i-rid-myself-of-div0/?answer=51440#post-id-51440First, 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.
Mon, 01 Jun 2015 20:53:38 +0200https://ask.libreoffice.org/en/question/51439/how-do-i-rid-myself-of-div0/?answer=51440#post-id-51440Comment by muppet7 for <p>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 <code>AVERAGE(T6:T18)</code> instead?
If you do not want an error to be displayed you could use something like <code>=IF(COUNT(T6:T18),AVERAGE(T6:T18),"nil")</code>, or <code>=IFERROR(AVERAGE(T6:T18),"nil")</code> but that suppresses any error.</p>
https://ask.libreoffice.org/en/question/51439/how-do-i-rid-myself-of-div0/?comment=51453#post-id-51453thanks 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
DerekTue, 02 Jun 2015 10:44:59 +0200https://ask.libreoffice.org/en/question/51439/how-do-i-rid-myself-of-div0/?comment=51453#post-id-51453