Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Incorrect total of calculated field in pivot table
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Incorrect total of calculated field in pivot table
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. -- Regards Roger Govier "Mark1406" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot table formula (calculated field) result is incorrect | Excel Worksheet Functions | |||
Using '% of Total' in calculated field in Pivot Table | Excel Discussion (Misc queries) | |||
Missing Grand Total for a Calculated Field in a Pivot Table | Excel Worksheet Functions | |||
Totals of calculated field in pivot table give incorrect results | Excel Worksheet Functions | |||
Pivot Table Calculated Field (Grand total question) | Excel Discussion (Misc queries) |