Calculate number required to achieve target percentage wiuth multiple inputs

[Thanhpi]

Hello,

Im trying to calculate a way for me and my colleagues to know how many remaining 5s we need to reach our target %. Since the target is 88% the only input that brings it up is the input 5 since input 4 is valued at "75%". Ive tried solving this for a few hours but i just cant wrap my finger around it


A = Categories
B = Input 1 worth 0
C = Input 2 worth 0,25
D = Input 3 worth 0,5
E = Input 4 worth 0,75
F = Input 5 worth 1
G = Current % (C-F Row 3/I)
H = Goal %
I = Total Input (C-F Row 2)
J = How many of Input 5 i need to reach Goal %

Row 3 is the Value of the inputs
Row 4 is the calculated value (Row2*Row3)

I Hope what im trying to make is possible and it kind of made sense. Thanks for the help!

 

[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][/tr][tr][td="bgcolor:#C0C0C0"]

1​

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

Inpt​

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

Wgt​

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

Count​

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

​

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

2​

[/td][td]

1​

[/td][td]

0.00​

[/td][td]

3​

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

3​

[/td][td]

2​

[/td][td]

0.25​

[/td][td]

2​

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

4​

[/td][td]

3​

[/td][td]

0.50​

[/td][td]

8​

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

5​

[/td][td]

4​

[/td][td]

0.75​

[/td][td]

10​

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

6​

[/td][td]

5​

[/td][td]

1.00​

[/td][td]

56​

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

7​

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

79​

[/td][td]C7: =SUM(C2:C6)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

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

86.08%​

[/td][td]C8: =SUMPRODUCT($B$2:$B$6, C2:C6) / C7[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

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

Target​

[/td][td]

88.00%​

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

10​

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

scores needed​

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

12.6667​

[/td][td]C10: =C7 * (C9 - C8) / (1 - C9)[/td][/tr]
[/table]

 

[Thanhpi]

Thanks alot! Me and a friend have been trying to figure this out for quite some time now during the night. (Im in EU) Thanks for the help truly. Have a good one!

 

[shg]

You're welcome.

A more robust formula for C10:

[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][/tr][tr][td="bgcolor:#C0C0C0"]

1​

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

Input​

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

Value​

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

Count​

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

​

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

2​

[/td][td]

1​

[/td][td]

0%​

[/td][td]

3​

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

3​

[/td][td]

2​

[/td][td]

25%​

[/td][td]

2​

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

4​

[/td][td]

3​

[/td][td]

50%​

[/td][td]

8​

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

5​

[/td][td]

4​

[/td][td]

75%​

[/td][td]

10​

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

6​

[/td][td]

5​

[/td][td]

100%​

[/td][td]

56​

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

7​

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

79​

[/td][td]C7: =SUM(C2:C6)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

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

86.08%​

[/td][td]C8: =SUMPRODUCT($B$2:$B$6, C2:C6) / C7[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

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

Target​

[/td][td]

88.00%​

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

10​

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

scores needed​

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

12.6667​

[/td][td]C10: =C7 * (C9 - C8) / (MAX(B2:B6) - C9)[/td][/tr]
[/table]