Looking for formula for survey percentage

[psdwit]

Morning Everyone I am hoping to get a little help. I'm stuck on my formula.
Using standard Net Promoter Score surveys I am looking to calculate how many promoters are needed to attain goal.
the formula we use is the following
P=Promoter
N=Neutral
D=Detractor

(P-D)/(P+N+D)
What I have so far is this
[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]Promoters[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]Neutral[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Detractors[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]GOAL[/TD]
[TD]85%[/TD]
[/TR]
[TR]
[TD]Current NPS[/TD]
[TD]=(B1-B3)/(B1+B2+B3)[/TD]
[/TR]
[TR]
[TD]# of Promoters Needed[/TD]
[TD]=IF(B5>=B4,"Goal Met")[/TD]
[/TR]
</tbody>[/TABLE]
I'm lost after the first part of the IF then statement in trying to calculate how many more of "B2" are needed if goal isn't met. I hope all this makes sense.
thank you for the help.

 

[hotabae]

I used this:

=IF(B5>=B4,"Goal Met",ROUNDUP((B1+B2+B3)*B4,0)-B1)

I think it does what you want. It just assumes that your "additional promoters" will come from one of the neutrals or detractors. (rather than adding people to the pool)

In your case it does this:

B1 + B2 + B3 = 18

18 * (85%) = 15.3

Roundup(15.3) = 16

You need 16 out of 18 people to be Promoters.

Since you have 15 right now, (16 - 15) = 1

You need 1 more.

Edit: soo... it appears, it kinda works, but its not perfect. I'll keep looking at it

 

[psdwit]

In your case it does this:

B1 + B2 + B3 = 18

18 * (85%) = 15.3

Roundup(15.3) = 16

You need 16 out of 18 people to be Promoters.

Since you have 15 right now, (16 - 15) = 1

You need 1 more.

Thank you for the reply. Unfortunately the way the formula is calculated the number work a little different rather than
b1+b2+b3=18
it is
b1-b3/(b1+b2+b3)= a percentage
(15-2)/(15+1+2)
13/18 =72%
this is why I'm stuck since the promoters added must then be plugged in to that equation so if goal is 85% and I am currently at 72% I will need 16 additional promoters ( B1) to reach 85%

 

[DRSteele]

This ought to help.

Book1
	A	B
1	Promoters	15
2	Neutral	1
3	Detractors	2
4	GOAL	85%
5	Current NPS	72%
6	Promoters Needed	31
7	Additional Promoters needed	16
Sheet71

Cell Formulas
Range	Formula
B5	=(B1-B3)/(B1+B2+B3)
B6	=ROUNDUP((B2+B3+B3/B4)/(1/B4-1),0)
B7	=IF(B5>=B4,"Goal Met",B6-B1)

 

[psdwit]

This ought to help.

	A	B
Promoters
Neutral
Detractors
GOAL
Current NPS
Promoters Needed
Additional Promoters needed

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]15[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]85%[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]72%[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]31[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]16[/TD]

</tbody>

Sheet71​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]B5[/TH]
[TD="align: left"]=(B1-B3)/(B1+B2+B3)[/TD]
[/TR]
[TR]
[TH]B6[/TH]
[TD="align: left"]=ROUNDUP((B2+B3+B3/B4)/(1/B4-1),0)[/TD]
[/TR]
[TR]
[TH]B7[/TH]
[TD="align: left"]=IF(B5>=B4,"Goal Met",B6-B1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Thank you DrSteele
i want the formula though to tell me how many promoters I need. Sure I can do the math and just keep changing the value to cell B1 but I would like to just input the values into cells B1,2&3 and have the spreadsheet tell me my current percentage (which does now) as well as how many additional promoters I may need to reach goal or am I misreading the formula you wrote. It looks like I have to manually input the promoters needed #

 

[hotabae]

Oh, I'm seeing it now. I thought it was based on a fixed pool, but you're talking about adding people. That starts to get much more complicated. I'll take another look after lunch, but I imagine this is beyond me too.

 

[DRSteele]

It's simple maths. If you know the parameters Neutrals, Detractors and Goal, then you can use simple algebra to re-arrange the equation to calculate the parameter Promoters to meet the Goal.

 

[psdwit]

I know the math equation of (x-2)/(x+1+2)=.85 whereas x=1/17 (20 x-91) and xdoes not equal 2
however....as I stated above I do not know how to put this in a excel formula.
thanks again for the help.

 

[DRSteele]

Look, you're trying to solve for P in the equation

(P-D)/(P+N+D)=G

Re-arrange that and you get

P=(D+GN+GD)/(1-G)

or P=(2+.85*1+.85*2)/(1-.85), so P=30.333333

That's how many Promoters you need if you have 1 Neutral, 2 Distractors and a goal of 85%.

 

[psdwit]

Look, you're trying to solve for P in the equation

(P-D)/(P+N+D)=G

Re-arrange that and you get

P=(D+GN+GD)/(1-G)

or P=(2+.85*1+.85*2)/(1-.85), so P=30.333333

That's how many Promoters you need if you have 1 Neutral, 2 Distractors and a goal of 85%.

Ok...look...thank you the response and the cheeky attitude to go with it. However your math is still wrong. I can happily concede I may not have explained it fully as well.
the formula/math you outlined requires N&D to remain constant, they do not always. The result will give me total of P needed to attain G not the additional number of P needed.

As I posted at the top, I have created a spreadsheet that the user will plug in P, N and D into as a result
p-d/p+n+d will determine a percentage.
if that percentage is less that G than a formula is needed to calculate how many additional P are needed based off of current N&D numbers to attain goal.
it is this formula I am needing. As I stated I can easily just keep manipulating the value in the P cell to get to the desired value for G and subtract the difference to come up with the number however I am looking for a formula to calculate it