Calculate the number of survey to pass the target

[markhasnomana]

I have these columns in excel:

0-6 scores
top 4
top 2
total
nps
number of ep to pass

on column 0-6 scores, on cell b5, I have the value of 2.
on column top 4, on cell c5, I have the value of 0.
on column top 2, on cell d5, I have the value of 1.
on column total, on cell e5, I have the value of 3.
on column nps, on cell f5, i have the value of -33.33%. the formula on cell f5 is =((d5-b5)/e5)*100/100. the passing rate is 52%

What formula can I use to calculate the needed number of ep to pass? in my manual calculation, the answer should be 6. because top 2 will be adjusted to 7. the total will be updated to 9 which result to 55.56%. Thanks.

 

[Special-K99]

Your formula

=((d5-b5)/e5)*100/100

*100/100 results in the original value, you don't need it.
e.g.
5 * 100 / 100 is 5
7 * 100 / 100 is 7

This should simply be

=(d5-b5)/e5

More importantly, which of these

0-6 scores
top 4
top 2
total
nps

represents an "ep", (as you haven't speciifed what an ep is)???

 

[markhasnomana]

Your formula

=((d5-b5)/e5)*100/100

*100/100 results in the original value, you don't need it.
e.g.
5 * 100 / 100 is 5
7 * 100 / 100 is 7

This should simply be

=(d5-b5)/e5

More importantly, which of these

0-6 scores
top 4
top 2
total
nps

represents an "ep", (as you haven't speciifed what an ep is)???

Hi @Special-K99. I appreciate your response.

columns "0-6 scores", "top 4", "top 2" are number of my eps.

in my above sample, I have 2 ep under "0-6 scores".
I do not have any for "top 4".
I have 2 ep under "top 2".

My goal is to display how many ep do I need, in order for me to pass the 52% target.

My manual calculation says I need 6.

Essentially, this will update my data to
0-6 scores - 2
top 4 - 0
top 2 - 7
total - 9
nps - 55.56% right? Thank you in advance.

 

[markhasnomana]

anyone who can help?

 

[awoohaw]

Try::

=  B5 * (1.52)/ (0.48)

Book1
	A	B	C	D	E	F	G
1		0-6 scores	top 4	top 2	total	nps	number of ep to pass
2
3
4
5		45	0	143	188	0.521277	142.5
6
7		.52=((d5-B5)/E5)*100/100
8		((d5-B5)/(D5+B5))=.52
9		.52=((d5-B5)/(D5+B5))
10		.52*(D5+B5)=(d5-B5)
11		.52(D5)+.52(B5) = D5 - B5
12		.52*(B5)+B5 = D5 - .52*(D5)
13		D5 - .52*(D5)=.52*(B5)+B5
14		D5 * (1 - .52)=B5 * (.52 +1)
15		D5 * (.48)=B5 * (1.52)
16		D5 = B5 * (1.52)/ (.48)
Sheet3

Cell Formulas
Range	Formula
E5	E5	=SUM(B5:D5)
F5	F5	=(D5-B5)/E5
G5	G5	= B5 * (1.52)/ (0.48)

 

[markhasnomana]

Hi @awoohaw, your response is so helpful. May you please confirm what does 1.52 and 0.48 stands for? seems we only use the cell b5 on this formula. Thanks.

 

[awoohaw]

Here is another a version of it, with a new cell, and whole number rounding. The new cell calculates the number needed.
Please look at the algebra below in the mini worksheet for the explanation.

Book1
	A	B	C	D	E	F	G	H
1		0-6 scores	top 4	top 2	total	nps	number of ep to pass	number additional needed
2
3
4
5		2	0	1	3	-0.33333	7	6
6
7		.52=((d5-B5)/E5)*100/100
8		((d5-B5)/(D5+B5))=.52
9		.52=((d5-B5)/(D5+B5))
10		.52*(D5+B5)=(d5-B5)
11		.52(D5)+.52(B5) = D5 - B5
12		.52*(B5)+B5 = D5 - .52*(D5)
13		D5 - .52*(D5)=.52*(B5)+B5
14		D5 * (1 - .52)=B5 * (.52 +1)
15		D5 * (.48)=B5 * (1.52)
16		D5 = B5 * (1.52)/ (.48)
17
Sheet3

Cell Formulas
Range	Formula
E5	E5	=SUM(B5:D5)
F5	F5	=(D5-B5)/E5
G5	G5	= ROUNDUP(B5 * (1.52)/ (0.48),0)
H5	H5	=ROUNDUP((B5*(1.52)/(0.48))-D5,0)

 

[markhasnomana]

Hi @awoohaw your explanation is clear. Thank you kind sir!

 

[awoohaw]

Happy to help.

Best wishes!