Re: averages without including zero values

Hi Lightning,

The Average function ignores empty cells, so try:

= Average(J7:J21)

---
Regards,
Norman



"lighting" <lighting.20242a_1134608701.0233@excelforum-nospam.com> wrote in
message news:lighting.20242a_1134608701.0233@excelforum-nospam.com...
>
> ={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
>
>
> --
> lighting
> ------------------------------------------------------------------------
> lighting's Profile:
> http://www.excelforum.com/member.php...o&userid=29656
> View this thread: http://www.excelforum.com/showthread...hreadid=493664
>

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,""))}

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

"lighting" <lighting.20257y_1134610200.6609@excelforum-nospam.com> wrote in
message news:lighting.20257y_1134610200.6609@excelforum-nospam.com...
>
> 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?
>
>
> --
> lighting
> ------------------------------------------------------------------------
> lighting's Profile:
> http://www.excelforum.com/member.php...o&userid=29656
> View this thread: http://www.excelforum.com/showthread...hreadid=493667
>

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)

"lighting" <lighting.20257y_1134610200.6609@excelforum-nospam.com> wrote in
message news:lighting.20257y_1134610200.6609@excelforum-nospam.com...
>
> 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?
>
>
> --
> lighting
> ------------------------------------------------------------------------
> lighting's Profile:
http://www.excelforum.com/member.php...o&userid=29656
> View this thread: http://www.excelforum.com/showthread...hreadid=493667
>