averages without including zero values

L

lighting

={AVERAGE(IF(J7:J21<>0, J7:J21,""))}

Trying to use a array formula similar to the above to calculate an
average of numbers from a column without including zero value fields in
the average. but I am not successful.

Any ideas
 
N

Norman Jones

Hi Lightning,

The Average function ignores empty cells, so try:

= Average(J7:J21)
 
L

lighting

Norman -

Thank you for the reply. Yes I realize this. But for instance in a
large worksheet/workbook where some cells that are to be averaged may
not be populated yet (due to awaiting a calculation from another field)
then until there is at least 1 value ready the cell we are trying to
create for this example displays "#VALUE!". We would like to not have
numerous cells display this while they await other data so we had hoped
to use a calculation that would allow "" to be displayed if all the
cells to be averaged were aslo displaying ""

Any further thoughts?
 
B

Biff

Hi!
={AVERAGE(IF(J7:J21<>0, J7:J21,""))}
Click to expand...

So, are you saying that some cell(s) in J7:J21 contain #VALUE! ?

If so, you need to "fix" those formulas.

Try posting one of those fomulas.

Biff
 
B

Bob Phillips

As Biff says, you should get rid of the errors, but if that is not feasible,
you could try

=AVERAGE(IF(NOT(ISERROR(J7:J21)),IF(J7:J21<>"",J7:J21)))

which is an array formula, so commit with Ctrl-Shift-Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
Top