# Formula Help

#### quickhappy

##### Board Regular
Hi Need a formula to calculate this. See below.

 Row Labels Promoters Detractors Total CSAT Score NPS Score ALAMOGORDO 47 3 53 96.66 83.02% JODY WELLS 9 9 99.45 100.00% ADAM WALOWITZ 17 2 21 91.64 71.43% NOE VALDEZ 15 16 95.55 93.75% ADAN GUZMAN 6 1 7 100.00 71.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

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

#### AlanY

##### Well-known Member
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.

#### Special-K99

##### Well-known Member
Or simply reverse the calculation

Promoters = 0.9 * Total + Detractors

#### quickhappy

##### Board Regular
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

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
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

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 Total CSAT Score NPS Score ALAMOGORDO 216 12 237 95.54 86.08% DYNAMIC INSTALLATIONS INC 49 3 55 96.56 83.64% SYNERGIES3 TEC SERVICES LLC 167 9 182 95.29 86.81% ALBUQUERQUE 201 5 213 97.62 92.02% EMPIRE SATELLITE 93 1 99 96.50 92.93% SUNRISE TECH SVCS 108 4 114 98.59 91.23% AMARILLO 230 6 247 97.90 90.69%

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

#### shobolos

##### Active Member
Hi Need a formula to calculate this. See below.

 Row Labels Promoters Detractors Total CSAT Score NPS Score ALAMOGORDO 47 3 53 96.66 83.02% JODY WELLS 9 9 99.45 100.00% ADAM WALOWITZ 17 2 21 91.64 71.43% NOE VALDEZ 15 16 95.55 93.75% ADAN GUZMAN 6 1 7 100.00 71.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
Promoters = 90 % / 100 * Total + Detractors

Thanks but this does not work. Any other takers on figuring this out???????? Has to be some guru out there. Help.

#### quickhappy

##### Board Regular
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 Total CSAT Score NPS Score ALAMOGORDO 216 12 237 95.54 86.08% DYNAMIC INSTALLATIONS INC 49 3 55 96.56 83.64% SYNERGIES3 TEC SERVICES LLC 167 9 182 95.29 86.81% ALBUQUERQUE 201 5 213 97.62 92.02% EMPIRE SATELLITE 93 1 99 96.50 92.93% SUNRISE TECH SVCS 108 4 114 98.59 91.23% AMARILLO 230 6 247 97.90 90.69%

<tbody>
</tbody>

Any other takers????

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Threads
1,164,148
Messages
5,835,680
Members
430,375
Latest member
datdog22

### 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

### 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