Need Help w/ Weighted Averaging

T

Tom

I now want to utilize Weighted Averaging. Please find some example data
below:

&&&&&&&&&&&&&&&&&&&
1 0.067
2 0.133
3 0.200
4 0.267
5 0.333
-----------
15 1.000
&&&&&&&&&&&&&&&&&&&

The result of A6 = Sum of A1:A5 (or 15)
The results of B1 to B5 are: B1 = A1/A6 (or 0.067); B2 = A2/A6 (or 0.133);
B3 = A3/A6 (or 0.200), etc.
The result of B6 = Sum of B1:B6 (or 1.000)



Now, here's what I'm trying to achieve:
- Although the weights (e.g. 0.067, 0.133, 0.200, 0.267, 0.333) are
calculated by "Rank / OverAllSum", I need to assign a different order.

In essence, I want the data to look like this (calculated result is in
reverse order):

1 0.333
2 0.267
3 0.200
4 0.133
5 0.067
-----------
15 1.000

Basically, the number "1 to 5" are priorities that must get a value in
column B. And here, "1" has a greater weight than "5", so I must give "1"
the highest value and "5" the lowest value.


Lastly, the calculation must be dynamic. If I were to add a "6th" record
(priority) the numbers must be recalculatd within making a great deal of
manual changes.

So, with a 6th value, the results in column B should look like this:


1 0.286
2 0.238
3 0.190
4 0.143
5 0.095
6 0.048
21 1.000



Thanks in advance,
Tom
 
J

Jerry W. Lewis

Assuming that the first row is always 1, and there are no blank rows.
=SUMPRODUCT(ranks,LARGE(ranks,ROW(ranks)))/SUM(ranks)
would do what I think you are asking for (you never gave a desired
result) without needing to enter the second column. Here ranks is the
range of rows for the ranks (e.g. A1:A5 or A1:A6).

If your ranks are always 1:n (i.e. no ties) then you could simplify to
=SUMPRODUCT(ranks,RANK(ranks,ranks))/SUM(ranks)
which need not start in row 1.

If you want the flexibility of specifying your own weights in B instead
of automatically calculating per the specified formula, then you could use
=SUMPRODUCT(ranks,LARGE(weights,ROW(weights)))/SUM(weights)
which again assumes (because of the ROW() function) that the data starts
in row 1. The final /SUM(weights) is superfluous if (as in your
example) you have already normalized the weights to sum to one.

Jerry
 
T

Tom

Jerry:

Thanks for the reply...

I named the values in A1:A5 as "ranks", then applied the formula... it
didn't work... not sure why?

The calculated results in Column B (A1/A$6 = B1... then apply to other range
B2:B5) provide the data below:

1 0.067
2 0.133
3 0.200
4 0.267
5 0.333
-----------
15 1.000

However, I want the data to be calculated as above (or maybe not) and
displayed in reverse order, such as:

1 0.333
2 0.267
3 0.200
4 0.133
5 0.067
-----------
15 1.000


Any additional pointers?

Tom








Hmh, I thought I listed the expected results.
 
J

Jerry W. Lewis

With the numbers 1:5 (equivalent to =ROW()) in A1:A5, I get the value
2.33... from either

=SUMPRODUCT(A1:A5,RANK(A1:A5,A1:A5))/SUM(A1:A5)
or if the range A1:A5 is named "ranks" from
=SUMPRODUCT(ranks,RANK(ranks,ranks))/SUM(ranks)

I get this in Excel 4.0, Excel 2000 SP-3, Excel 2002(XP) SP-2, and Excel
2003 SP-1. What do you get, and in what environment?

Jerry
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top