Pivot Tables: Calculated Field. Can I do this?

Chris The Rock

Active Member
I want to create a calculation representing Customer Satisfaction. I have customer survey data that contains a column called "Q1". This column contains 5 possible values, 1 through 5.

The calculation for Customer Satisfaction (CSAT) is the total number of Q1 responses that are 4 or 5, divided by the total number of Q1 responses.

I can do this easily by creating a pivot table and doing some calculations outside of the pivot table. I can also add on to the data and create new fields to help me calculate this also.

I'm just wondering if it's possible to write a formula for a pivot table calculated field that will do it. I just have trouble with the syntax, since formulas for calculated fields don't work the same way that worksheet functions do.

None of the educational material I've seen deals with this kind of situation. I use calculated fields all the time, but not in the type of situation where I need to use IF/AND functions.

I am using Excel 2010.

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Hi Chris,

The problem with trying to do this with a Calculated Field directly from your data source is that Calculated Fields operate on the Sum of all matching records.
As far as I know, there isn't a way to distinguish the records that are 4 or 5 and then use that result in a Calculated Field without the use of some helper columns or an intermediate table or query.

One approach would be to use MS Query to add those additional fields without the need to modify your source data.

Here is a link to some instructions on how to use MS Query in Excel 2010.

http://www.mrexcel.com/forum/excel-...table-multiple-consolidation.html#post3412102

I think you'll only need two modifications to adapt those instructions for your scenario.
1. Just make one Named Range called "MyData" for your source data.
2. Replace the Query Text shown in Green Font in the example with this Query:

SELECT *, IIF(D.Q1>3,1,0) AS [HighMarksCount], IIF(D.Q1>0,1,0) AS [AllMarksCount]
FROM MyData as D

The resulting PivotTable Field List will have two added fields:
HighMarksCount - a count of records with marks of 4 or 5
AllMarksCount - a count of all marks greater than 0 (this assumes blanks are not to be counted in the CSAT denominator- it can be modified if they are).

Now you can define a calculated field CSAT using the formula:
= HighMarksCount/ AllMarksCount

Last edited:
This is great. I've learned two things:
1) It's OK that I was unable to find a solution, given the direction I was pursuing. I was "right" to think that this isn't possible.
2) This really cool method of using MSQuery. Thanks!

In interest of getting the project done, I did go back to my fallback solution of adding on to my source data, essentially adding the EXACT fields you've prescribed here.

But this is cool. Thanks for sharing.

Hi Chris,

The problem with trying to do this with a Calculated Field directly from your data source is that Calculated Fields operate on the Sum of all matching records.
As far as I know, there isn't a way to distinguish the records that are 4 or 5 and then use that result in a Calculated Field without the use of some helper columns or an intermediate table or query.

One approach would be to use MS Query to add those additional fields without the need to modify your source data.

Here is a link to some instructions on how to use MS Query in Excel 2010.

http://www.mrexcel.com/forum/excel-...table-multiple-consolidation.html#post3412102

I think you'll only need two modifications to adapt those instructions for your scenario.
1. Just make one Named Range called "MyData" for your source data.
2. Replace the Query Text shown in Green Font in the example with this Query:

SELECT *, IIF(D.Q1>3,1,0) AS [HighMarksCount], IIF(D.Q1>0,1,0) AS [AllMarksCount]
FROM MyData as D

The resulting PivotTable Field List will have two added fields:
HighMarksCount - a count of records with marks of 4 or 5
AllMarksCount - a count of all marks greater than 0 (this assumes blanks are not to be counted in the CSAT denominator- it can be modified if they are).

Now you can define a calculated field CSAT using the formula:
= HighMarksCount/ AllMarksCount

Replies
4
Views
362
Replies
0
Views
159
Replies
1
Views
630
Replies
1
Views
230
Replies
1
Views
256

1,196,512
Messages
6,015,629
Members
441,912
Latest member
Rayna_rahman00

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.

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