ExcelBanter - Average Formula

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Average Formula (https://www.excelbanter.com/excel-discussion-misc-queries/258876-average-formula.html)

How can I write the average formula so it does not count the cells with zeros
in it...
A1= 12
A2= 0
A3 = 12
A4 = 0
A5= 12
A6 =0
if I write the formula as =average(a1:a6) the value is 6, but I want it to
read 12.

{=AVERAGE(IF(A1:A6<0,A1:A6))}
*** This is an array formula, and is to be confirmed with CTRL+SHIFT+ENTER
rather than with simply ENTER.
The curly brackets {} are not to be typed manually, those are entered by the
€œExcel€, when the formula is entered as an Array formula. ***
Micky

"Peter" wrote:

How can I write the average formula so it does not count the cells with zeros
in it...
A1= 12
A2= 0
A3 = 12
A4 = 0
A5= 12
A6 =0
if I write the formula as =average(a1:a6) the value is 6, but I want it to
read 12.