# Calculated field - count values in columns and divide by grand total

#### igneous

##### New Member
I'm working NPS (Net Promoter Score) reporting for my company. The Pivot table looks like this:

 Sum of WEIGHT Column Labels Row Labels 1 2 3 4 5 6 7 8 9 10 Grand Total oktober 14 1 0 1 10 1 7 5 2 11 38 november 14 0 1 10 4 3 4 1 9 33 desember 14 1 1 1 4 0 7 14 januar 15 1 4 2 4 15 3 8 38 februar 15 1 0 0 0 7 1 2 14 4 7 39 mars 15 1 0 10 3 3 7 1 7 33 april 15 0 0 1 1 5 1 2 5 2 9 26 mai 15 0 0 15 3 4 4 2 6 34 Grand Total 4 2 2 4 61 16 25 59 15 66 255

<colgroup><col><col><col span="3"><col span="6"><col></colgroup><tbody>
</tbody>

I need to calculate four fields within the pivot table itself.

1. Detractors: sum of 1-6 scores from column labels, divided by row grand total
2. Neutrals: sum of 7-8 scores from column lables, divided by row grand total
3. Promoters: sum of 9-10 scores from column lables, divided by row grand total
4. NPS: Promoters - Detractors.

I can obviously do this outside the pivot table, but the calculations get messed up if the pivot table changes. So I am looking for a way to do this with calculated fields. An example Excel file can be found here: https://www.dropbox.com/s/xew1i5ltrh3ui8j/NPS.xlsx?dl=0

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Anyone?

Replies
1
Views
65
Replies
4
Views
174
Replies
6
Views
109
Replies
5
Views
89
Replies
1
Views
153

1,203,502
Messages
6,055,772
Members
444,822
Latest member
Hombre

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

### Which adblocker are you using?

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

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