I have a base data field:

Presenting Complaint | Diagnosis
complaint a          | <blank>
complaint b          | diagnosis a
complaint c          | diagnosis b

I have a pivot table:

Presenting complaint | Count of diagnosis | Count of presenting complaint
complaint a          |                    | 1
complaint b          | 1                  | 1
complaint c          | 1                  | 1

I want to create a custom field which basically counts percentage diagnosis made, taking account of the two calculated pivot fields ('count of diagnosis') and ('count of presenting complaint'). Is this possible?

  • Pivot tables are funny in the way that you can't reference them. The best bet is to reference the data that your pivot table was generated from instead and do the calculations based on the raw data. – Eric F Mar 11 '16 at 18:13

You won't be able to calculate the percentages inside the pivot. Though, you can use the GetPivotData() function to calculate the percentages outside of the pivot table.

To calculate the percentage for a particular complaint you can use:

=GETPIVOTDATA("Count of  Diagnosis",$E$3,"Presenting Complaint ","complaint a")/GETPIVOTDATA("Count of Presenting Complaint ",$E$3,"Presenting Complaint ","complaint a")

For the Grand Total you can use:

=GETPIVOTDATA("Count of  Diagnosis",$E$3)/GETPIVOTDATA("Count of Presenting Complaint ",$E$3)

$E$3 is the position of the pivot table.

While this looks cumbersome, the advantage is that when the structure of the pivot changes by adding data or adding columns, you won't have to change your formula. It will always select the items you specify in the formula.

It's also easy to use. When you enter an equal sign, = , and click on a value in the pivot, excel will automatically enter the formula for you.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.