Looking for formula for survey percentage

psdwit

New Member
Joined
May 13, 2013
Messages
12
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.
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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
 
Last edited:
Upvote 0
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%
 
Upvote 0
This ought to help.


Book1
AB
1Promoters15
2Neutral1
3Detractors2
4GOAL85%
5Current NPS72%
6Promoters Needed31
7Additional Promoters needed16
Sheet71
Cell Formulas
RangeFormula
B5=(B1-B3)/(B1+B2+B3)
B6=ROUNDUP((B2+B3+B3/B4)/(1/B4-1),0)
B7=IF(B5>=B4,"Goal Met",B6-B1)
 
Upvote 0
This ought to help.

AB
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 #
 
Last edited:
Upvote 0
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.
 
Upvote 0
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.
 
Last edited:
Upvote 0
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.
 
Upvote 0
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%.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,224,222
Messages
6,177,230
Members
452,765
Latest member
Erka Gizli

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top