Results 1 to 7 of 7
Thread: Excluding cells (Office 97/SR1)

20020327, 21:37 #1
 Join Date
 Mar 2002
 Location
 Georgia
 Posts
 4
 Thanks
 0
 Thanked 0 Times in 0 Posts
Excluding cells (Office 97/SR1)
I am trying to average the values in a column, excluding certain cells which contain data out of the norm. I know how to hide the cells, then copy only the visible cells in a range (EditGo ToSpecialVisible Cells Only). But that trick doesn't work in a formula. The hard way would be to edit the range [=AVERAGE(D3,D513,D1620)] But the table is going to get pretty big.
I would appreciate any help.

20020327, 21:56 #2
 Join Date
 Jul 2001
 Location
 Long Beach, California, USA
 Posts
 233
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Excluding cells (Office 97/SR1)
Try something like this:
=AVERAGE(COUNTIF(A1:A10,">1"))

20020327, 22:35 #3
 Join Date
 Nov 2001
 Location
 Vienna, Wien, Austria
 Posts
 5,009
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Excluding cells (Office 97/SR1)
Without your being more specific about the criteria, what I suggest is that you step back from the AVERAGE formula and break it down into its components: SUM/COUNT. There is no AVERAGEIF formula, but if you take SUMIF()/COUNTIF() you should be able to exclude any abnormal data. HTH
Grüße

20020327, 23:34 #4
 Join Date
 Feb 2001
 Location
 Dublin, Ireland, Republic of
 Posts
 2,697
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Excluding cells (Office 97/SR1)
If you can AutoFilter the cells you do not want to include (set up a column with a value you can select and hide through the autofilter) and use the SUBTOTAL function. Say all the data you want to average run from A2:A200, use =SUBTOTAL(1,A2:A200). When you apply the autofilter to hide the cells to be excluded, the function should recalculate and include only the visible cells.
Andrew C.

20020328, 20:59 #5
 Join Date
 Mar 2002
 Posts
 1
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Excluding cells (Office 97/SR1)
As a followup to the reply from Unkamunka, you could also use the SMALL() and LARGE() functions to eliminate outliers. For example, the following removes the 2 smallest values from the average for A1:A10:
=(SUM(A1:A10)MIN(A1:A10)SMALL(A1:A10,2))/(COUNT(A1:A10)2)

20020328, 21:27 #6
 Join Date
 Mar 2002
 Location
 Georgia
 Posts
 4
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Excluding cells (Office 97/SR1)
Thank you to all who replied.
=AVERAGE(COUNTIF(A1:A10,"<1") would just return the same as COUNTIF.
Since the data I want to exclude may or may not be greater/less than any number, I added a column for "Exclusion" to be filled with "Yes" or "No". Then used unkamunka's suggestion to do SUMIF(A1:A10,"No",B1:B10)/COUNTIF(A1:A10,"No").
I might try the AutoFilter suggestion from Andrew if I get the time. Sounds like it would do exactly what I had wanted to do.
I think damonerules' suggestion is similar to TRIMMEAN. However TRIMMEAN is symmetrical: removes equal numbers of "small" and "large" values.
Thanks again for all your help.

20020329, 04:54 #7
 Join Date
 Jan 2001
 Location
 West Long Branch, New Jersey, USA
 Posts
 1,931
 Thanks
 6
 Thanked 9 Times in 7 Posts
Re: Excluding cells (Office 97/SR1)
One more solution:
{=AVERAGE(IF((B1:B7>=5)*(B1:B7=<17)=1,B1:B7,"xx")) }
with the above:
 the formula is an "array" formula. You don't type the { }. After entering the formula as AVERAGE(..."xx")), you press CTRL+SHIFT+enter to tell excel this is an array formula
 I'm assuming you'll have some lower value below which you want data excluded (5 arbitrarily chosen by me0 and a higher value above which you want data excluded (17 here)
 I entered my data in B1:B7; this would be adjusted to fit your data
 the (B1:B7>=5) admits only data >=5 (ie, data below 5 excluded) and returns 1 or 0 depending on whether the data >=5; similarly for B1:B7=<17. This is done element by element (ie, B1, then B2,...,then B7) to actually get 7 1's or 0's. By multiplying the 2 together for B1, you will get multiply B1's 1 or 0 depending on whether it is >=5 by B1's 1 or 0 depending on whether it is <=17. The only way the product = 1 is if B1 is both >=5 and <=17 (ie, an AND test). Same holds for B2,B3,...B7. If the product is 1, then the IF returns the corresponding value; if not, it returns xx
 Now you have a set of values resulting from the IF. For example, 7, 9, xx, 13, 16, xx, 8. AVERAGE will work only over numeric values so it will only take the values 7, 9, 13, 16, 8 and ignore the 2 xx's (both for summing and counting the number of values).
 obviously you can choose any nonnumeric in place of xx
HTH
Fred