Incorrect total of calculated field in pivot table

G

Guest

I want to use a pivot table for a monthly timesheet. As I work on a task I
record the hours spent on that Task as "Time" in my source data. The pivot
table shows me the Time spent on each Task per Month. The pivot table has a
calculated field with the following formula:

=ROUNDUP(Time*Rate,-1)

The gives me a $ figure rounded up to $10 multiples.

However, the pivot table totals appear not to sum the rounded up values,
rather they appear to sum the source data and then round up. This results in
column totals that do not match the sum of the rows below them. Is there a
workaround for this?
 
R

Roger Govier

Hi Mark

That is the way that calculated fields work inside Pivot Tables.
Instead, add a column to your source data with the Time*Rate calculation,
give it a heading and extend your data range to include this new column.
Use this new field in the PT rather than the calculated field.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Pivot Table Calculated field error 1
Pivot Table Calculated Field - Sum/count 1
Pivot table, IF function, calculated item versus calculated field 1
Calculated field in Pivot Table 2
How can I get Pivot Tables to recognise time formatted data 1
Problem inserting calculated pivot fields into Pivot Table (2007) 1
how to show the total in a pivot chart 1
Pivot Table calculated item 4

Top