Formula Help

quickhappy

Board Regular
Joined
Dec 21, 2013
Messages
65
Hi Need a formula to calculate this. See below.

Row Labels Promoters Detractors TotalCSAT Score NPS Score
ALAMOGORDO4735396.6683.02%
JODY WELLS9999.45100.00%
ADAM WALOWITZ1722191.6471.43%
NOE VALDEZ151695.5593.75%
ADAN GUZMAN617100.0071.43%

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>


Ok, the above data has "NPS Score" column. That data is calculated by the number in "promoters" - "Detractors" / by "total". What I want is a formula that will calculate how many promoters I need to reach a 90% score in "NPS Score".

Example 1. Row "alamogordo" has 47 promoters with 3 detractors with a total of 53. So 47-3=44 and 44/53 = 83.02........ So how many more promoters do I need to bring NPS Score to 90%.


Thanks for your help.
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,238
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
you can use the built-in goal seek function.

for your example,

Excel 2012
ABCDEF
1Row LabelsPromotersDetractorsTotalCSAT ScoreNPS Score
2ALAMOGORDO4735396.6683.02%
3JODY WELLS9999.45100.00%
4ADAM WALOWITZ1722191.6471.43%
5NOE VALDEZ151695.5593.75%
6ADAN GUZMAN61710071.43%

<tbody>
</tbody>
Sheet1
put =(B2-C2)/D2 in Cell F2.

for the goal seek function (in data ribbon > what if analysis > Goal seek;

set cell to F2,
Value to 0.9
by changing Cell B2,

that should give you an answer of 50.7, ie 51-47= 4 more.
 

quickhappy

Board Regular
Joined
Dec 21, 2013
Messages
65
you can use the built-in goal seek function.

for your example,

Excel 2012
ABCDEF
1Row LabelsPromotersDetractorsTotalCSAT ScoreNPS Score
2ALAMOGORDO4735396.6683.02%
3JODY WELLS9999.45100.00%
4ADAM WALOWITZ1722191.6471.43%
5NOE VALDEZ151695.5593.75%
6ADAN GUZMAN61710071.43%

<tbody>
</tbody>
Sheet1
put =(B2-C2)/D2 in Cell F2.

for the goal seek function (in data ribbon > what if analysis > Goal seek;

set cell to F2,
Value to 0.9
by changing Cell B2,

that should give you an answer of 50.7, ie 51-47= 4 more.


Thanks for the help. I guess I should've mention the above data is in a pivot table..Any suggestion to make this work in a pivot table?
 

quickhappy

Board Regular
Joined
Dec 21, 2013
Messages
65

ADVERTISEMENT

Thanks for the help. I guess I should've mention the above data is in a pivot table..Any suggestion to make this work in a pivot table?


The goal seek formula works outside of pivot table but it changes the value in cell "b2". I want the goal to show in a different cell. Like adding a column g and have that labeled "Promoters Needed to 90%."
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,238
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
ok,

revise Cell F2 to =(B2-C2+H2)/D2
add =B2+H2 to Cell G2

for the goal seeking,
set cell to F2,
Value to 0.9
by changing Cell H2

Cell G2 should show 50.7
 

quickhappy

Board Regular
Joined
Dec 21, 2013
Messages
65

ADVERTISEMENT

The goal seek formula works outside of pivot table but it changes the value in cell "b2". I want the goal to show in a different cell. Like adding a column g and have that labeled "Promoters Needed to 90%."

Can anyone help. I couldn't the above recommendations to work and my formula just got a little more complex. The below table is a pivot table so I can't put a formula inside of it and I don't get raw data that makes up the pivot.

If you take promotors-detractors/total you get the "NPS Score". I need a formula that can produce how many more promoters I need to reach a goal of 90% NPS without recieving anymore detractors.

So here it goes..Promoters +(X),-detractors/Total+(x) would =90% NPS Score. We don't know what (X) is. This is what I need. And if we add to the number of promoters the "Total" also goes up by the same number. My end result needs to be a number. Say 1st line would result in 100 meaning I need 100 promoters with no detractors to reach 90%.

Please help.

Row Labels Promoters Detractors TotalCSAT Score NPS Score
ALAMOGORDO2161223795.5486.08%
DYNAMIC INSTALLATIONS INC4935596.5683.64%
SYNERGIES3 TEC SERVICES LLC167918295.2986.81%
ALBUQUERQUE201521397.6292.02%
EMPIRE SATELLITE9319996.5092.93%
SUNRISE TECH SVCS108411498.5991.23%
AMARILLO230624797.9090.69%

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>
 

shobolos

Active Member
Joined
Sep 7, 2010
Messages
274
Hi Need a formula to calculate this. See below.

Row Labels Promoters Detractors TotalCSAT Score NPS Score
ALAMOGORDO4735396.6683.02%
JODY WELLS9999.45100.00%
ADAM WALOWITZ1722191.6471.43%
NOE VALDEZ151695.5593.75%
ADAN GUZMAN617100.0071.43%

<tbody>
</tbody>


Ok, the above data has "NPS Score" column. That data is calculated by the number in "promoters" - "Detractors" / by "total". What I want is a formula that will calculate how many promoters I need to reach a 90% score in "NPS Score".

Example 1. Row "alamogordo" has 47 promoters with 3 detractors with a total of 53. So 47-3=44 and 44/53 = 83.02........ So how many more promoters do I need to bring NPS Score to 90%.


Thanks for your help.


Promoters = 90 % / 100 * Total + Detractors
 

quickhappy

Board Regular
Joined
Dec 21, 2013
Messages
65
Can anyone help. I couldn't the above recommendations to work and my formula just got a little more complex. The below table is a pivot table so I can't put a formula inside of it and I don't get raw data that makes up the pivot.

If you take promotors-detractors/total you get the "NPS Score". I need a formula that can produce how many more promoters I need to reach a goal of 90% NPS without recieving anymore detractors.

So here it goes..Promoters +(X),-detractors/Total+(x) would =90% NPS Score. We don't know what (X) is. This is what I need. And if we add to the number of promoters the "Total" also goes up by the same number. My end result needs to be a number. Say 1st line would result in 100 meaning I need 100 promoters with no detractors to reach 90%.

Please help.

Row Labels Promoters Detractors TotalCSAT Score NPS Score
ALAMOGORDO2161223795.5486.08%
DYNAMIC INSTALLATIONS INC4935596.5683.64%
SYNERGIES3 TEC SERVICES LLC167918295.2986.81%
ALBUQUERQUE201521397.6292.02%
EMPIRE SATELLITE9319996.5092.93%
SUNRISE TECH SVCS108411498.5991.23%
AMARILLO230624797.9090.69%

<tbody>
</tbody>



Any other takers????
 

Watch MrExcel Video

Forum statistics

Threads
1,122,538
Messages
5,596,750
Members
414,097
Latest member
FaeFen

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
Top