Pivot table Average with [h]:mm format

[Opa Horst]

Is there a way to get the correct average in the Total Row or Column in a
Pivot table when the value field is formatted with [h]:mm?
The average works find in normal number format, but gives the Total rather
then the average when formatted with [h]:mm.
Using Excel 2007.
Thanks

 

[Opa Horst]

Found the issue
I am using a calculated field for the values, when I change this to the
actual values, the average is calculated correctly. Still weird..

 

[Fred Smith]

Then your calculation must be the problem. How are you calculating the
field?

Regards,
Fred

Found the issue
I am using a calculated field for the values, when I change this to the
actual values, the average is calculated correctly. Still weird..

Is there a way to get the correct average in the Total Row or Column in a
Pivot table when the value field is formatted with [h]:mm?
The average works find in normal number format, but gives the Total
rather
then the average when formatted with [h]:mm.
Using Excel 2007.
Thanks

 

[Opa Horst]

Thanks for your reply.
The data field is Usage (in seconds), the calculated field is
(Usage+30)/(24*3600), to get the usage data in [h]:mm format.

Then your calculation must be the problem. How are you calculating the
field?

Regards,
Fred

Found the issue
I am using a calculated field for the values, when I change this to the
actual values, the average is calculated correctly. Still weird..

Is there a way to get the correct average in the Total Row or Column in a
Pivot table when the value field is formatted with [h]:mm?
The average works find in normal number format, but gives the Total
rather
then the average when formatted with [h]:mm.
Using Excel 2007.
Thanks

.

 

[Fred Smith]

That's the correct way of converting seconds to Excel times, so you'll need
to elaborate on your process.

You said "when I change this to actual values, the average is calculated
correctly". What do you mean by "actual values"? Are you totalling the Usage
field?

Also, how do you know it's giving you a total, rather than an average, when
you use [h]:mm format?

Regards
Fred

Thanks for your reply.
The data field is Usage (in seconds), the calculated field is
(Usage+30)/(24*3600), to get the usage data in [h]:mm format.

Then your calculation must be the problem. How are you calculating the
field?

Regards,
Fred

Found the issue
I am using a calculated field for the values, when I change this to the
actual values, the average is calculated correctly. Still weird..

:

Is there a way to get the correct average in the Total Row or Column
in a
Pivot table when the value field is formatted with [h]:mm?
The average works find in normal number format, but gives the Total
rather
then the average when formatted with [h]:mm.
Using Excel 2007.
Thanks

.

 

[Opa Horst]

Firstly, when I do the calculation on the data, so outside the Pivot Table,
and then use this data (which has now the correct values for the [h]:mm
format) as the data source for the Pivot Table the Average calculation is
correct. This is what I mend with using the ‘actual values’
Secondly, I checked the Average/Total value manually with the Average/Sum
formula. With the above method the two average values are the same, with a
calculated field in [h]:mm format in the Pivot table they are not.
This all feels like a bug with custom format in Excel!
The reason why I think that this is a bug with the custom [h]:mm format is
that the average values are being calculated correctly with the calculated
field with a standard numeric format. As soon as you change to the custom
format the system defaults to the Total values.

That's the correct way of converting seconds to Excel times, so you'll need
to elaborate on your process.

You said "when I change this to actual values, the average is calculated
correctly". What do you mean by "actual values"? Are you totalling the Usage
field?

Also, how do you know it's giving you a total, rather than an average, when
you use [h]:mm format?

Regards
Fred

Thanks for your reply.
The data field is Usage (in seconds), the calculated field is
(Usage+30)/(24*3600), to get the usage data in [h]:mm format.

Then your calculation must be the problem. How are you calculating the
field?

Regards,
Fred

Found the issue
I am using a calculated field for the values, when I change this to the
actual values, the average is calculated correctly. Still weird..

:

Is there a way to get the correct average in the Total Row or Column
in a
Pivot table when the value field is formatted with [h]:mm?
The average works find in normal number format, but gives the Total
rather
then the average when formatted with [h]:mm.
Using Excel 2007.
Thanks


.

.