Results 1 to 11 of 11

20070625, 18:38 #1
 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 nonVBA solution, nonUDF solution, if at all possible.
Thanks in advance...

20070625, 19:13 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Find a Target Average (Excel 2003 SP2)
There's no guarantee that you'll reach an average of exactly 40, is there?

20070625, 19:39 #3
 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 attachedJerry

20070625, 19:46 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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.

20070625, 19:50 #5
 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

20070625, 20:13 #6
 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...

20070625, 20:15 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Find a Target Average (Excel 2003 SP2)
So what do you want?

20070625, 20:17 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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.

20070625, 20:20 #9
 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...

20070625, 21:03 #10
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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.

20070626, 13:32 #11
 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...