# Thread: Excluding cells (Office 97/SR1)

1. ## 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 (Edit|Go To|Special|Visible 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.

2. ## Re: Excluding cells (Office 97/SR1)

Try something like this:
=AVERAGE(COUNTIF(A1:A10,">1"))

3. ## 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

4. ## 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.

5. ## Re: Excluding cells (Office 97/SR1)

As a follow-up 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)

6. ## 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.

7. ## 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 non-numeric in place of xx

HTH

Fred

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•