# Thread: Find a Target Average (Excel 2003 SP2)

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

2. ## Re: Find a Target Average (Excel 2003 SP2)

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

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

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

6. ## Re: Find a Target Average (Excel 2003 SP2)

Hi Hans,

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

Thanks...

7. ## Re: Find a Target Average (Excel 2003 SP2)

So what do you want?

8. ## 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. ## 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. ## 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. ## 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
•