Set algorithm

[username_rand]

Hello,

I need to make a sort of weights calculator that i could just put in the number of projects i have, rate them from most to least important, and the percentage of increase and it would automatically give me the required weights.

For example, right now I have four projects:

Pro. A
Pro. B
Pro. C
Pro. D

rated from most to least important with project A being most important. With 20% increase between each project, I figured out the math on paper as follows:

x (pro. D) + 1.2x (pro. C) + 1.4x (pro. B) + 1.6x (pro. A) = 100%

i solved for x and figured out the weight of each project depending on its value.

Is there a formula that would help in figuring out the weights as i did on paper for other projects? (even if i changed the number of projects and/or the percentage of increase)

 

[shg]

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

1​

delta​

20%​

2​

numProjects​

1​

2​

3​

4​

5​

6​

7​

8​

Total​

3​

1​

100.00%​

100.00%​

B3: =2 / (A3 * (2 + (A3-1) * delta))

4​

2​

45.45%​

54.55%​

100.00%​

C3: =IF(C$2 > $A3, "", (1 + (C$2 - 1) * delta) * $B3)

5​

3​

27.78%​

33.33%​

38.89%​

100.00%​

J3: =SUM(B3:I3)

6​

4​

19.23%​

23.08%​

26.92%​

30.77%​

100.00%​

7​

5​

14.29%​

17.14%​

20.00%​

22.86%​

25.71%​

100.00%​

8​

6​

11.11%​

13.33%​

15.56%​

17.78%​

20.00%​

22.22%​

100.00%​

9​

7​

8.93%​

10.71%​

12.50%​

14.29%​

16.07%​

17.86%​

19.64%​

100.00%​

10​

8​

7.35%​

8.82%​

10.29%​

11.76%​

13.24%​

14.71%​

16.18%​

17.65%​

100.00%​

 

[username_rand]

Thank you so much, this works out perfectly.

 

[username_rand]

Could you tell me the logic behind the equations you used so i can get a clear picture of how it works?

 

[shg]

S = a1 + (1 + d)a1 + (1 + 2d)a1 + ... + (1 + (n - 1)d)a1 == 1, and d = 20%.

Solve for a1.

 

[username_rand]

S = a1 + (1 + d)a1 + (1 + 2d)a1 + ... + (1 + (n - 1)d)a1 == 1, and d = 20%.

Solve for a1.

Thank you very much!

 

[shg]

You're welcome.