Complex problem - taking a weighted average of 8 of a series of 10 values

[energythief]

Hi all,

I am having a complex problem at work that I am having troubl
cracking... perhaps one of the local gurus here can help?

I need to take a weighted average of 8 values selected from a series o
10 values. The percentage appears next to a numerator and
demoninator. (Total of 3 cells for each value.) Here is an example:

Value 1: 100% 45 45
Value 2: 54% 54 100
Value 3: 91% 91 100
Value 4: 75% 75 100
Value 5: 100% 100 100
Value 6: 100% 20 20
Value 7: 54% 27 50
Value 8: 100% 75 75
Value 9: 100% 20 20
Value 10: 100% 20 20

What needs to happen is that the HIGHEST and LOWEST percentages need t
be excluded from the average, leaving 8 values of the ten to b
averaged. So far so good... nice and easy. Clearly, Value 2 or 7 woul
need to go, and so would any of the 100%s.

But wait!

If there is a tie for either HIGH or LOW percentage, then a distinctio
must be made. For LOW percentage, the one with the highest denominato
must be excluded (i.e., the most heavily weighted value.) For HIG
percentage, the one with the lowest denominator must be excluded (i.e.
the least heavily weighted value).

So, using the values above, there is a tie for LOW percentage:
Value 2: 54% 54 100
Value 7: 54% 27 50

In this case, since Value 2 is out of 100 points, and Value 7 is onl
out of 50, Value 2 needs to be excluded from the final average.

Still following? Okay, let's look at another twist: what if there is
tie?

In the case above, the HIGH percentage is 100%. The lowest denominato
(in this case, a value of 20) needs to be excluded. The problem is tha
ties may occur. In the above data there is a 3-way tie:
Value 6: 100% 20 20
Value 9: 100% 20 20
Value 10: 100% 20 20

ANY of these needs to be excluded, but the other two must remain a
part of the final calculation.

Ok, almost home...

Now we've identified our excluded values (hopefully). I need to hav
them indicated (i.e., with an "x" appearing somewhere next to those tw
fields) so I can trigger some conditional formatting.

The final calculation would be a fairly simple SUMIF taking the tota
of the numerators and dividing by the total of the denominator
(excluding the two values marked with an "x").

For our purposes, any number of additional columns or rows can be adde
(I can always hide them), but I am NOT able to use macros. (I'
thinking there is a solution somewhere in RANK, SUMIF and/or MIN/MAX
but I'm stumped...)

Bonus twist: The 10 values actually appear in 3 rows, not 3 columns
but it's easier to read if I show it as I did above.

Please help, and earn 100 bonus points!

Thanks,

Jame

 

[AlfD]

Energythief indeed!

Interesting problem. I'll stick my neck out.

First I would order the data.

Since your twist in the tail is that it runs across rather than dow
the sheet, may I suggest you copy the data and paste special wit
Transpose somewhere else - maybe another worksheet and we can star
from there.

Assume the data is in three columns: Percent : Num : Denom. (Excus
abbreviations..)

Sort the 3 columns by Percent ascending and by Denom descending.

This should ensure that at the top of the list is the lowes
percentage with the highest denominator of its class.

You'll want to drop the top one, according to your rules.

Now to the bottom of the list. Here you have the highest percentag
with the lowest denominator of its class.

You'll want to drop the bottom one, according to your rules.

Over to you to deal with the arithmetic on the other 8.

Al

 

[Frank Kabel]

Hi
if I understood you correctly:
- row 1 contains your percentage
- row 2 your nominator
- row 3 your denominator
- you use columns A-J

Enter the following in A4 as array formula (entered with
CTRL+SHIFT+ENTER):
=IF(OR(A3+COLUMN(A3)/10000=MIN(IF($A$1:$J$1=MAX($A$1:$J$1),$A$3:$J$3+CO
LUMN($A$3:$J$3)/10000)),A3+COLUMN(A3)/10000=MAX(IF($A$1:$J$1=MIN($A$1:$
J$1),$A$3:$J$3+COLUMN($A$3:$J$3)/10000))),"X","")

and copy this to the right for all columns