Pivot Tables: Calculated Field. Can I do this?

[Chris The Rock]

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.

Thanks in advance.

 

[Jerry Sullivan]

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

 

[Chris The Rock]

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