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.
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
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.
 
Upvote 0
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?
 
Upvote 0
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%."
 
Upvote 0
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
 
Upvote 0
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>
 
Upvote 0
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
 
Upvote 0
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????
 
Upvote 0

Forum statistics

Threads
1,213,486
Messages
6,113,932
Members
448,533
Latest member
thietbibeboiwasaco

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