Pivot table Average with [h]:mm format

  • Thread starter Thread starter Opa Horst
  • Start date Start date
O

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
 
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..
 
Then your calculation must be the problem. How are you calculating the
field?

Regards,
Fred

Opa Horst said:
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..

Opa Horst said:
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
Click to expand...
Click to expand...
 
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.


Fred Smith said:
Then your calculation must be the problem. How are you calculating the
field?

Regards,
Fred

Opa Horst said:
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..

Opa Horst said:
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
Click to expand...
Click to expand...

.
Click to expand...
 
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

Opa Horst said:
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.


Fred Smith said:
Then your calculation must be the problem. How are you calculating the
field?

Regards,
Fred

Opa Horst said:
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
Click to expand...

.
Click to expand...
Click to expand...
 
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.


Fred Smith said:
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

Opa Horst said:
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.


Fred Smith said:
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


.
Click to expand...
Click to expand...

.
Click to expand...
 
Back
Top