Results 1 to 7 of 7
  1. #1
    New Lounger
    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 (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. #2
    3 Star Lounger
    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"))
    Attached Files Attached Files

  3. #3
    Platinum Lounger
    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
    Gre

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

  5. #5
    New Lounger
    Join Date
    Mar 2002
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

  7. #7
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    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 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
  •