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]

	A​	B​	C​	D​
1​	Inpt​	Wgt​	Count​	​
2​	1​	0.00​	3​
3​	2​	0.25​	2​
4​	3​	0.50​	8​
5​	4​	0.75​	10​
6​	5​	1.00​	56​
7​			79​	C7: =SUM(C2:C6)
8​			86.08%​	C8: =SUMPRODUCT($B$2:$B$6, C2:C6) / C7
9​		Target​	88.00%​	C9: Input
10​		scores needed​	12.6667​	C10: =C7 * (C9 - C8) / (1 - C9)

 

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

	A​	B​	C​	D​
1​	Input​	Value​	Count​	​
2​	1​	0%​	3​
3​	2​	25%​	2​
4​	3​	50%​	8​
5​	4​	75%​	10​
6​	5​	100%​	56​
7​			79​	C7: =SUM(C2:C6)
8​			86.08%​	C8: =SUMPRODUCT($B$2:$B$6, C2:C6) / C7
9​		Target​	88.00%​	C9: Input
10​		scores needed​	12.6667​	C10: =C7 * (C9 - C8) / (MAX(B2:B6) - C9)