Calculated field

**pinjisi** · 04-16-2017, 04:16 AM

I have a pivot table for class marks on 6 subjects
For each student I want to an average of the students best top three marks
Do I use a calculated field for this or can I loop through using VBA code ?

I noticed excel has a LARGE keyword but that works in the spreadsheet formulas, I want this on the pivot table

**JeteMc** · 04-16-2017, 10:37 PM

Hello pinjisi and Welcome to Excel Forum.
According to the people who maintain a web site on Excel functions, the only function that will work in a calculated field is SUM (although they go on to show the IF function being used). Therefore I don't think that you'll be able to resolve this issue with a calculated field.
I am going to offer two formula based solutions.
1) Append you pivot table with a non-pivot table column populated with the formula:

Formula:

Please Login or Register  to view this content.

This option is utilized on sheet 2 of the attached file. Notice that the formula is copied down to row 20 so that if the pivot table is expanded the formula will appear to work as you would expect of a calculated field.
2) Append the actual data table (sheet 1) with a column populated by the formula:

Formula:

Please Login or Register  to view this content.

The column, Average (Top 3), is then included in the values field of the pivot table on sheet 3. One advantage of this approach is that as the table is expanded the formula in the Average (Top 3) column will expand also.
Let us know if you have any questions.