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]

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][td="bgcolor:#C0C0C0"]

F​

[/td][td="bgcolor:#C0C0C0"]

G​

[/td][td="bgcolor:#C0C0C0"]

H​

[/td][td="bgcolor:#C0C0C0"]

I​

[/td][td="bgcolor:#C0C0C0"]

J​

[/td][td="bgcolor:#C0C0C0"]

K​

[/td][td="bgcolor:#C0C0C0"]

L​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

delta​

[/td][td]

20%​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td="bgcolor:#F3F3F3"]

numProjects​

[/td][td]

1​

[/td][td]

2​

[/td][td]

3​

[/td][td]

4​

[/td][td]

5​

[/td][td]

6​

[/td][td]

7​

[/td][td]

8​

[/td][td="bgcolor:#F3F3F3"]

Total​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

1​

[/td][td="bgcolor:#CCFFCC"]

100.00%​

[/td][td="bgcolor:#CCFFFF"][/td][td="bgcolor:#CCFFFF"][/td][td="bgcolor:#CCFFFF"][/td][td="bgcolor:#CCFFFF"][/td][td="bgcolor:#CCFFFF"][/td][td="bgcolor:#CCFFFF"][/td][td="bgcolor:#CCFFFF"][/td][td="bgcolor:#E5E5E5"]

100.00%​

[/td][td][/td][td="bgcolor:#CCFFCC"]B3: =2 / (A3 * (2 + (A3-1) * delta))[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

2​

[/td][td="bgcolor:#CCFFCC"]

45.45%​

[/td][td="bgcolor:#CCFFFF"]

54.55%​

[/td][td="bgcolor:#CCFFFF"][/td][td="bgcolor:#CCFFFF"][/td][td="bgcolor:#CCFFFF"][/td][td="bgcolor:#CCFFFF"][/td][td="bgcolor:#CCFFFF"][/td][td="bgcolor:#CCFFFF"][/td][td="bgcolor:#E5E5E5"]

100.00%​

[/td][td][/td][td="bgcolor:#CCFFFF"]C3: =IF(C$2 > $A3, "", (1 + (C$2 - 1) * delta) * $B3)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

3​

[/td][td="bgcolor:#CCFFCC"]

27.78%​

[/td][td="bgcolor:#CCFFFF"]

33.33%​

[/td][td="bgcolor:#CCFFFF"]

38.89%​

[/td][td="bgcolor:#CCFFFF"][/td][td="bgcolor:#CCFFFF"][/td][td="bgcolor:#CCFFFF"][/td][td="bgcolor:#CCFFFF"][/td][td="bgcolor:#CCFFFF"][/td][td="bgcolor:#E5E5E5"]

100.00%​

[/td][td][/td][td="bgcolor:#E5E5E5"]J3: =SUM(B3:I3)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]

4​

[/td][td="bgcolor:#CCFFCC"]

19.23%​

[/td][td="bgcolor:#CCFFFF"]

23.08%​

[/td][td="bgcolor:#CCFFFF"]

26.92%​

[/td][td="bgcolor:#CCFFFF"]

30.77%​

[/td][td="bgcolor:#CCFFFF"][/td][td="bgcolor:#CCFFFF"][/td][td="bgcolor:#CCFFFF"][/td][td="bgcolor:#CCFFFF"][/td][td="bgcolor:#E5E5E5"]

100.00%​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]

5​

[/td][td="bgcolor:#CCFFCC"]

14.29%​

[/td][td="bgcolor:#CCFFFF"]

17.14%​

[/td][td="bgcolor:#CCFFFF"]

20.00%​

[/td][td="bgcolor:#CCFFFF"]

22.86%​

[/td][td="bgcolor:#CCFFFF"]

25.71%​

[/td][td="bgcolor:#CCFFFF"][/td][td="bgcolor:#CCFFFF"][/td][td="bgcolor:#CCFFFF"][/td][td="bgcolor:#E5E5E5"]

100.00%​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]

6​

[/td][td="bgcolor:#CCFFCC"]

11.11%​

[/td][td="bgcolor:#CCFFFF"]

13.33%​

[/td][td="bgcolor:#CCFFFF"]

15.56%​

[/td][td="bgcolor:#CCFFFF"]

17.78%​

[/td][td="bgcolor:#CCFFFF"]

20.00%​

[/td][td="bgcolor:#CCFFFF"]

22.22%​

[/td][td="bgcolor:#CCFFFF"][/td][td="bgcolor:#CCFFFF"][/td][td="bgcolor:#E5E5E5"]

100.00%​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]

7​

[/td][td="bgcolor:#CCFFCC"]

8.93%​

[/td][td="bgcolor:#CCFFFF"]

10.71%​

[/td][td="bgcolor:#CCFFFF"]

12.50%​

[/td][td="bgcolor:#CCFFFF"]

14.29%​

[/td][td="bgcolor:#CCFFFF"]

16.07%​

[/td][td="bgcolor:#CCFFFF"]

17.86%​

[/td][td="bgcolor:#CCFFFF"]

19.64%​

[/td][td="bgcolor:#CCFFFF"][/td][td="bgcolor:#E5E5E5"]

100.00%​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]

8​

[/td][td="bgcolor:#CCFFCC"]

7.35%​

[/td][td="bgcolor:#CCFFFF"]

8.82%​

[/td][td="bgcolor:#CCFFFF"]

10.29%​

[/td][td="bgcolor:#CCFFFF"]

11.76%​

[/td][td="bgcolor:#CCFFFF"]

13.24%​

[/td][td="bgcolor:#CCFFFF"]

14.71%​

[/td][td="bgcolor:#CCFFFF"]

16.18%​

[/td][td="bgcolor:#CCFFFF"]

17.65%​

[/td][td="bgcolor:#E5E5E5"]

100.00%​

[/td][td][/td][td][/td][/tr]
[/table]

 

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