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
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