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.