How to average cells but exclude 0 values?

How do I average, for example, cell A1 across multiple sheets but exclude cells that contain 0 or no value? I keep getting the #DIV/0! error, thanks!

Using average() formula Excel won't take into account blank cells. However, if the cell Value is 0 but not blank Excel will count it.

If all cells are blank you get #DIV/0! error.

Difficult to say without seeing your formula!

=AVERAGE(Sheet1:Sheet3!A1)

works for me, although this will still include 0 values (but not blanks).

Richard

Here is one option.
It's an Array formula, meaning type the formula, then confirmed by holding down Ctrl+Shift and then hit Enter, Just hitting Enter will not work.

=AVERAGE(IF(ISNUMBER(A1:A4),IF(A1:A4<>0,A1:A4)))

Hope it helped
Ola