PivotTable day-average from data that's sub-day

[Kevin Lucas]

I have a list with one row per model rocket flight. It has columns for time
of day (AM, PM, Evening), engine type (A, B, C, D etc), how many times that
rocket flew at that time of day and more. When a rocket did not fly at a
particular time of day a row exists with a zero count.

I would like to show, via a PivotChart (and hence a PivotTable) the average
number of flights on each day, regardless of engine type and regardless of
time of day.

If I use the Average sub-total on the number of flights I get an average
that is 3 times too small because the number of data points (rows) for each
day is 3 times too big since there are entries for the morning (AM),
afternoon and evening. I don't want my average to be calculated by dividing
by the number of flights but by the number of days for which I have data.

Can I do this in a PivotTable (and thence into a PivotChart) or should I be
going about this some other way?

I've looked through many postings on averages but can't quite spot what I'm
looking for but then I'm maybe getting confused - PTs do a lot but some of
the calculation nuances need careful thought and I'm probably becoming
fuddled - apologies if I've somehow missed a relevant previous posting.

And many thanks for any pointers you can give.

Kevin Lucas

 

[Debra Dalgleish]

You may have to add columns to the source table, to calculate unique
values for days, then use those fields in the pivot table calculations.
There's an example here:

http://www.contextures.com/xlPivot07.html#Unique

 

[Kevin Lucas]

Ah, that helps explain why I was finding this tricky within a PivotTable
itself. Many thanks for the pointer and I'll take a good look.
Kevin