Averaging cells not connected

[Gary]

Iam somewhat new to Excell 2003. I've just learned how to average cells (&
ignore zeros) that are connected, either row to row, or coloumb to coloumb.
I need to know how to average cells (& ignore zeros or blank cells) that is a
mix of connected cell and others that are not connected.....like;
B5 C5 D5
B10 C10 D10
this format continues for several more rows.

Not sure if I'll find this help screen again. If you don't mined, would
you also email the results to: (e-mail address removed) Thanks.

 

[Sandy Mann]

Try:

=AVERAGE(IF(B55<>0,B55),IF(B1010<>0,B1010))

This is an array formula so must be enterered by pressing and holding the
Crtl & Shift keys while you press the Enter key. If you do this correctly
then Excel will place curly braces { } around the formula.

If you do not array enter it you will get a #Value! error.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[ShaneDevenshire]

Hi,

First point - blank cells are treated are ignored by the average function,
as are all non-numeric cells.

Here is one formula:
=AVERAGE((B55<>0)*B55,(B1010<>0)*B1010)
It needs to be array entered - Press Shift+Ctrl+Enter rather than Enter.

Now if the rows between B55 and B1010 are blank, or contain non-numeric
data you can use:
=AVERAGE(B510)
No array entry needed.

If this helps, please click the Yes button.

 

[T. Valko]

First point - blank cells are treated are ignored by the

average function, as are all non-numeric cells.
=AVERAGE((B55<>0)*B55,(B1010<>0)*B1010)

That will include both numeric 0 and empty cells.