averages without including zero values

  • Thread starter Thread starter lighting
  • Start date Start date
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
 
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?
 
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
 
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)
 
Back
Top