Pivot Chart - unusual average conumdrum

Hi

I have a series of Spreadsheets for Speedway results. (Motor bikes)

On any particular sheet, a riders average is calculated as ((Points+Bonus)/Rides)X4 - this gives a consistent average for all team members based on a usual set of 4 rides per meeting.

The Chart to show this would have columns
Name/Rides/Points/Bonus/Average/wins/seconds/thirds/fourths/Exclusions

However, when I create a further spreadsheet, to collate multi seasons with a pivot chart, the column for Average doesn't work, as it doesn't fit the 'Summarize field by' criteria, IE it isn't a Sum of averages, or a count of averages, as it will always be as per the calculation above.
I would prefer to have the Average column in the same place, and the only way I have thought to do this is a regular formula outside the Pivot.
This produces several 'problems'
a) It isn't in the place I want it
b) I want to sort the table by Highest to Lowest on these averages, which obviously works automatically on the Pivot data if I tell it which to sort.
c) As seasons progress, and riders are added, I have several changes to make, not only in Source data, but then also adding the extra manual formulas.

I hope all this makes sense, and wondered if anyone can think of another way around this?

Cheers
Neil

Hi Neil,

What if you copy the data and Paste using Values Only to another sheet. If you were to take many races and Paste Values Only and then do a Pivot of these numbers (without the formulas) you may get what you desire. If that is wrong, then I'd need to see why your formula interferes with the Pivot. You can attach a sample workbook showing what is wrong and we can look at the problem.

To attach a sample workbook, click on "Go Advanced" and then on the Paper Clip Icon above the advanced message area.

Hi neil,

It would have been easier if you gave me a workbook to use, so I wouldn't have to build my own. See if the attached is what you want.

You've done exactly what I did MarvinP - I have built a Pivot like that, with a manual formula
Sorry for not attaching a sheet.
The problems are:
1 - I need to display all the columns in my Pivot, and the traditional way of displaying stats within the sport, is exactly in the order as shown, so that the riders average is shown after Points and Bonus Points.
2 - Each time I add a year/rider, the manual formula will continually need to be added, and takes out the automated nature of the pivot, where I only need to change the data source traditionally.
3 - I need to sort the pivot by highest to lowest average.

---------- Post added at 05:00 PM ---------- Previous post was at 04:08 PM ----------

I solved it MarvinP

I have added a Calculated Field with this formula:

Please Login or Register  to view this content.

I can then put that in the Pivot anywhere I need it.
*EDIT*
In fact, I altered this to account for zero's thus:

Please Login or Register  to view this content.

Hey Neil40,

Could you attach your pivot table so we could all see how you did a Calculated Field. I'd like to check the calculated field answer agains the one I gave.