Averaging cells not connected
 

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: Thanks.

Averaging cells not connected
 

Try:

=AVERAGE(IF(B5:D5<0,B5:D5),IF(B10:D10<0,B10:D10) )

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


Replace @mailinator.com with @tiscali.co.uk


"Gary" wrote in message
...
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: Thanks.

Averaging cells not connected
 

Hi,

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

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

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

If this helps, please click the Yes button.
--
Thanks,
Shane Devenshire


"Gary" wrote:

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: Thanks.

Averaging cells not connected
 

First point - blank cells are treated are ignored by the
average function, as are all non-numeric cells.
=AVERAGE((B5:D5<0)*B5:D5,(B10:D10<0)*B10:D10)

That will include both numeric 0 and empty cells.


--
Biff
Microsoft Excel MVP


"ShaneDevenshire" wrote in
message ...
Hi,

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

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

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

If this helps, please click the Yes button.
--
Thanks,
Shane Devenshire


"Gary" wrote:

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: Thanks.