Find a Target Average (Excel 2003 SP2)

Topic

New Reply

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…

Reply | Quote

Replies

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

Reply | Quote

Hi Hans,

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

Thanks…

Reply | Quote

So what do you want?

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Correct, I was showing the method of creating the average by a changing range…I wasn’t going to do everything

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

Reply | Quote

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.

Reply | Quote

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

As always, thank you very much…

Reply | Quote

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

Reply | Quote

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

Reply | Quote