AVERAGE EXCLUDING SOME CELLS

Q45

New Member
Joined
Feb 17, 2002
Messages
32
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!
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
On 2002-03-15 15:53, Chris Davison wrote:
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.....

/board/images/smiles/icon_smile.gif
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,205
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top