Calculating NPS

[mattevans]

Hi,

I need some help!

I have made a sheet for my work on Excel which calculates NPS. It will predict NPS response rate based on the amount of guests we serve. In the sheet, the user has to input how many NPS responses we've had for promoters, passives and detractors and will then calculate the weeks NPS score based on this.

The sheet will then collate this information to give the use the years NPS score. But, I want it to tell me how many more promoters we need to hit a certain score i.e. current year score is 80, how many more promoters do we need to get the score to 82?

Hope that all makes sense.

Thanks,

Matt

 

[shg]

For example, ...?

 

[mattevans]

Hi MrExcel MVP,

So,

In my spreadsheet, I have various weeks and I record in it each weeks number of promoters, passives and detractors. The sheet then calculates the weeks NPS score and compares it against the target NPS score for that week. But, I want the sheet to tell me how many more promoters we need to hit the target score.

I have managed to achieve the result with goal seek, but my colleagues in work have no idea how to use excel really so fear that will be too complicated to run each time.

An example,

PROMOTERS 1
PASSIVES 1
DETRACTORS 0
TOTAL 2
NPS SCORE 50
TARGET SCORE 75

To get to the target score from 50 to 75 based on 1 promoters and 1 passive, I would need an additional 2 promoters to achieve this. But I want the spreadsheet to tell me that.

Hope this is what you needed?

Thanks,

P.S - my excel knowledge is ok, nothing expert so any help in

 

[shg]

Maybe ...

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

1​

[/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]

0​

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

4​

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

5​

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

50%​

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

6​

[/td][td]Target[/td][td]

82%​

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

7​

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

3.56​

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

 

[mattevans]

That doesn't work

 

[mattevans]

It works! I just hadn't set the cell values to a percentage.

Thank you MrExcel MVP for your help

 

[shg]

You're welcome.

 

[kamiljaku]

Hey, your solution i just great. I was struggling a while with this and then I found this post.

Is it possible that you explain how this works?

Regards
Kamil