Need a macro to create a special percentage calculation

[theletterh]

Hi all,
Looking to use a macro to calculate Net Promoter Score. NPS is the percentage of Promoters minus the percentage of Detractors.
Sample data attached.

Promoter 20379
Neutral 5816
Detractor 2797

The basic calculation:
Calculate the percent of people who are promoters: 20379/28992 (70.3%)
Calculate the percent of people who are detractors: 2797/28992 (9.6%)
Subtract detractors % from promoters %: (70.3-9.6=60.7)

Keeping all calculations to one decimal point.

Still new to creating macros. I can create the equation in the cell, referring to the data in the three cells above it, but it'd be great if I could just have a macro assigned to a shortcut, which refers to data in the three cells immediately above where the result will wind up.

Thanks so much for your help!

[Andrew-R]

How's this:

Please Login or Register  to view this content.

P.S. Do you work in Market Research as well, then?

[AlvaroSiza]

Slightly different than Andrew's above, the following creates a UDF that can be used anywhere in the workbook. To use, enter =NPS in a cell and select the promoters, neutral, and detractors (in that order) seperated by commas. In your example, it would look like =NPS(B1, B2, B3) and the result is 60.6%.

Hope this helps.

Please Login or Register  to view this content.

[theletterh]

This is great, thanks Andrew-R! I do indeed work in market research, though (like vba) new to that as well!

[theletterh]

AlvaroSiza, this is great, because the data isn't always arranged vertically for me. Thanks for your help!

[shg]

It seems like a gratuitous use of a macro or UDF. How about just =(B1-B3)/SUM(B1:B3)