Calculating Number Needed to Hit Percentage

[raytehawsome]

Hi There,

I am wondering if I could get some help. At work we calculate customer satisfaction with NPS surveys, which is percentage of promoters (rating of 9 or 10) minus percentage of detractors (rating of 6 or below). Our NPS score is calculated as ((Promoters/Total Surveys)-(Detractors/Total Surveys). With that, I currently have this set up in Excel:

[TABLE="width: 156"]
<colgroup><col width="144" style="width: 108pt; mso-width-source: userset; mso-width-alt: 5266;"> <col width="64" style="width: 48pt;"> <tbody>[TR]
[TD="width: 208, bgcolor: transparent, colspan: 2"]Quarter NPS[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]NPS[/TD]
[TD="bgcolor: transparent, align: right"]79%[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF99]#FFFF99[/URL] "]Promoters[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF99]#FFFF99[/URL] , align: right"]30[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF99]#FFFF99[/URL] "]Passives[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF99]#FFFF99[/URL] , align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF99]#FFFF99[/URL] "]Detractors[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF99]#FFFF99[/URL] , align: right"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Total # of Surveys[/TD]
[TD="bgcolor: transparent, align: right"]34[/TD]
[/TR]
</tbody>[/TABLE]

Yellow is what I change, NPS is set up as =(B3/B6)-(B5/B6), and Total # of Surveys is just =C4+C5+C6.

I would like to create an additional an additional line below, # of Promoters to 90%. I have used Goal Seek to find the number, but I have not figured out a way to display it. I am wondering if I could get some help from someone to figure out a formula in order to figure out how many more Promoters I need to hit a certain percentage. I appreciate the help, and thank you in advance!

 

[palaeontology]

if you entered ... # of promoters needed ... into B4, and move the other three items down one cell each (to B5, B6 and B7), AND if you changed the formula for NPS to ... (c4/c7)-(c6/c7) ... AND ... if you added 'Goal' to E2 and whatever percentage was your goa(you mentioned 90% for example) into F2, then wouldn't you simply add the formula ... (f2*c7)+c6 ... into c4 ??

Or have I misunderstood what you were asking ?

Kind regards,

Chris

 

[raytehawsome]

Thank you so much for your response! And I'm so sorry, but it was late last night when I posted this and I totally mistyped the formula. Here, I'll try to make it more clear:

[TABLE="width: 172"]
<colgroup><col width="21" style="width: 16pt; mso-width-source: userset; mso-width-alt: 768;"> <col width="144" style="width: 108pt; mso-width-source: userset; mso-width-alt: 5266;"> <col width="64" style="width: 48pt;"> <tbody>[TR]
[TD="width: 21, bgcolor: transparent"][/TD]
[TD="width: 144, bgcolor: transparent"]A[/TD]
[TD="width: 64, bgcolor: transparent"]B[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, colspan: 2"]Quarter NPS[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"]NPS[/TD]
[TD="bgcolor: transparent, align: right"]71%[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent"]Promoters[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF99]#FFFF99[/URL] , align: right"]20[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent"]Passives[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF99]#FFFF99[/URL] , align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent"]Detractors[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF99]#FFFF99[/URL] , align: right"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent"]Total # of Surveys[/TD]
[TD="bgcolor: transparent, align: right"]24[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]7[/TD]
[TD="bgcolor: transparent"]# of Promoters to 90%
[/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]

So here is my table. NPS (B2) is calculated as =(B3/B6)-(B5/B6). Total # of Surveys (B6) is calculated as =B3+B4+B5 (not C like I said last night). How do I create a formula for B7, which is # of Promoters to 90%?

 

[Joe4]

I have merged your two accounts together.
Per forum rules, users are only allowed to have one account (rule 7 here: https://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html).

 

[raytehawsome]

Thank you! Sorry, for some reason I couldn’t remember my password but got it all figured out. I shouldn’t be trying to post so late, too many errors!

 

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

1​

[/td][td]

Promoters​

[/td][td]

30​

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

2​

[/td][td]

Passives​

[/td][td]

1​

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

3​

[/td][td]

Detractors​

[/td][td]

3​

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

4​

[/td][td]

NPS​

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

79%​

[/td][td]B4: =(B1 - B3)/SUM(B1:B3)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

Goal​

[/td][td]

90%​

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

6​

[/td][td]

Promoters needed​

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

36​

[/td][td]B6: =SUM(B1:B3) * (B5 - B4) / (1 - B5)[/td][/tr]
[/table]

 

[Joe4]

for some reason I couldn’t remember my password

Just use the password reset functionality. If that doesn't work, use the "Contact Us" option.

 

[raytehawsome]

Thank you sooooo much! I can't even begin to tell you how much this helps me. I appreciate it!

 

[shg]

You're welcome.