Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Find a Target Average (Excel 2003 SP2)

    Good day to all you Excel gurus. Here's our problem:

    We have a large range of values which returns an average (let's say the average is 50). What we'd like to do is to create a formula that will return an average of 40, instead of 50, by dropping the largest values in order. In other words, the range now returns 50. If we don't include the top six values, the average drops to 46. If we don't include the top 22 values, the average drops to 40.

    We are hoping for a non-VBA solution, non-UDF solution, if at all possible.

    Thanks in advance...

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find a Target Average (Excel 2003 SP2)

    There's no guarantee that you'll reach an average of exactly 40, is there?

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Find a Target Average (Excel 2003 SP2)

    Hi George

    Further to what Hans states, you could use the following and adapt to your needs...I think

    Say you have thhe list of numbers in range A1:A10, type the following formula in B1


    =AVERAGE(A1:$A$10)

    and copy down to cell B10.

    This will create averages of the cells in the range directly to the left of the formula and down to the bottom of column A. See attached
    Jerry

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find a Target Average (Excel 2003 SP2)

    But that doesn't omit the highest values, unless the values are sorted in descending order.

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Find a Target Average (Excel 2003 SP2)

    Correct, I was showing the method of creating the average by a changing range...I wasn't going to do everything <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

    Seriously though, you are correct, the range will have to be sorted in descending order for my method to work....
    Jerry

  6. #6
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find a Target Average (Excel 2003 SP2)

    Hi Hans,

    No, it doesn't have to be exactly 40.

    Thanks...

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find a Target Average (Excel 2003 SP2)

    So what do you want?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find a Target Average (Excel 2003 SP2)

    You could use this array formula (confirm with Ctrl+Shift+Enter) in B1:

    =AVERAGE(SMALL($A$1:$A$10,ROW($1:1)))

    and fill down.

  9. #9
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find a Target Average (Excel 2003 SP2)

    Actually, I think your solution will work. We should be able to sort the values in descending order...

    As always, thank you very much...

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find a Target Average (Excel 2003 SP2)

    Here is an example that does not require sorting the original data, using Jezza's sample.

  11. #11
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find a Target Average (Excel 2003 SP2)

    Very nice, very clever solution. Thank you very much...

Posting Permissions

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