AVERAGE EXCLUDING SOME CELLS

[Q45]

How do I write a formula to average about 10 specific cells but I need to exclude any 0's from the average?
Example: Average cells A1, C1, D3, F4, G5 but if any one of those is a zero I don't want it to be included because it will skew the average.
Thanks!

[Von Pookie]

In the cell where you want the average to be, type =AVERAGE(

Then, hold down control and click on all of the cells you wish to average. You can leave out any cells you want.

When you have all of the cells selected, close your parenthesis (add the ")" to the end) and press enter.

[Q45]

Thanks. It's a varying sheet though, meaning some days there will be a zero in the cell and other days the same cells will have a value. I need something that can dynamiclly change to exclude any zeros that may be in the cells. Any ideas on how to exclude zeros? Again, thanks.

[Mark W.]

On 2002-03-15 08:30, Q45 wrote:
How do I write a formula to average about 10 specific cells but I need to exclude any 0's from the average?
Example: Average cells A1, C1, D3, F4, G5 but if any one of those is a zero I don't want it to be included because it will skew the average.
Thanks!

In an unused column (e.g., H) enter a reference to each of these cells (e.g., =A1 in H1, =C1 in H2, =D3 in H3, etc.), hide the column (if desired), and use the following array formula...

{=AVERAGE(IF(H1:H5,H1:H5))}

Note: Array formulas must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula.


[ This Message was edited by: Mark W. on 2002-03-15 09:40 ]

[Q45]

I have completed the formula exactly as you stated and excel enclosed the formula with the {} to recognize the properly entered array formula. However, the value of the cell gives me the #VALUE! message. Any suggestions? Thank you!

[Chris Davison]

Q,

is the range size and location static, just some could be 0 and some could have numbers in them ?

[Aladin Akyurek]

On 2002-03-15 15:29, Chris Davison wrote:
Q,

is the range size and location static, just some could be 0 and some could have numbers in them ?

Chris: I think he has formula returned blanks in some of the cells of interest.

After creating a range of consecutive cells, would

=SUM(H1:H5)/MAX(1,COUNTIF(H1:H5,">0")+COUNTIF(H1:H5,"<0"))

do the job.

Aladin

[Chris Davison]

On 2002-03-15 15:37, Aladin Akyurek wrote:
Chris: I think he has formula returned blanks in some of the cells of interest.

yeah, trying to use a named range defeated me (!) Question thusly : with a named range of non-contiguous cells (say "table") why does =sum(table) give a number, say 60, but =countif(table,">0") gives #VALUE error ?

Forever curious.....



[ This Message was edited by: Chris Davison on 2002-03-15 15:54 ]

[Aladin Akyurek]

On 2002-03-15 15:53, Chris Davison wrote:

yeah, trying to use a named range defeated me (!) Question thusly : with a named range of non-contiguous cells (say "table") why does =sum(table) give a number, say 60, but =countif(table,">0") gives #VALUE error ?

Forever curious.....

[img]/board/images/smiles/icon_smile.gif[/img]

[ This Message was edited by: Chris Davison on 2002-03-15 15:54 ]

I suppose the syntax difference explains the trouble:

SUM([multi-cell-range]*,[single-cell-range]*)

COUNTIF(a-multi-or-single-cell-range,condition)

A named range of non-contiguous cells (say "table") used in =sum(table) will give a number, because the underlying syntax supports it, while that of countif does not. Names thus does not add an enrichment to the syntax the functions require. I'd say that's just right.

=countif(table,">0") should give indeed a #VALUE error, indicating that the function is fed with a range arg which it does not support.

Aladin

[Chris Davison]

thanks,

a subtle, albeit unfortunate syntax difference !