Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Pivot Table Calculated Field (Grand total question)
Hi,
I'm using Excel 2000. My pivot table contains calculated fields, the problem I'm experiencing is that the grand total for the field is almost 2 times higher than it should be. However, if I look at the individuals rows that calculation is correct, if I sum the calculated field column (with exception to the grand total) the sum is correct but the highlighted column doesn't match it's grand total....not sure why. I'm not user if the problem is with my source data worksheet, the only thing I'm doing which maybe an issue is- for the rate column, I have it equal a cell that's resides on another work sheet. -- Linda |
#2
|
|||
|
|||
What's the calculated field formula?
What result are you getting, and what result do you want? Linda wrote: Hi, I'm using Excel 2000. My pivot table contains calculated fields, the problem I'm experiencing is that the grand total for the field is almost 2 times higher than it should be. However, if I look at the individuals rows that calculation is correct, if I sum the calculated field column (with exception to the grand total) the sum is correct but the highlighted column doesn't match it's grand total....not sure why. I'm not user if the problem is with my source data worksheet, the only thing I'm doing which maybe an issue is- for the rate column, I have it equal a cell that's resides on another work sheet. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#3
|
|||
|
|||
answers to you questions:
1) the calculated field formula for 3q05 Labor $ is "=3q05 labor Hrs*labor rate" 2) the results I'm looking for is to be able to enter any labor rate and model the dollars impact. __________________________________________________ _________________ I've worked on the file since my last e-mal and have abit more information. It would appear that the calculation method used between "Sum of 3q05 Labor Hrs" and "Sum of 3q05 labor $" are different (see below). The 3q05 labor dolars should total 15 (hrs) * 75 (labor rate) = $1125 and not $1500. It seem to be getting confused (or me) because item #1 has multiple records (item 1 has multiple tasks being performed with different task durations but the same rate, thus has different hrs between one another). example of pivot table item Sum of 3q05 Labor Hrs Sum of 3q05 labor $ 1 10 $750 2 5 $375 ____ _____ Grand Total 15 $1500 Example of source data item 3q05 Labor Hrs labor rate 1 5 $75 1 5 $75 2 5 $75 -- Linda "Debra Dalgleish" wrote: What's the calculated field formula? What result are you getting, and what result do you want? Linda wrote: Hi, I'm using Excel 2000. My pivot table contains calculated fields, the problem I'm experiencing is that the grand total for the field is almost 2 times higher than it should be. However, if I look at the individuals rows that calculation is correct, if I sum the calculated field column (with exception to the grand total) the sum is correct but the highlighted column doesn't match it's grand total....not sure why. I'm not user if the problem is with my source data worksheet, the only thing I'm doing which maybe an issue is- for the rate column, I have it equal a cell that's resides on another work sheet. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#4
|
|||
|
|||
Either calculate the dollars in each line of the source data, and add
that field to the pivot table, or change your formula to ='3q05 Labor Hrs'*75 Linda wrote: answers to you questions: 1) the calculated field formula for 3q05 Labor $ is "=3q05 labor Hrs*labor rate" 2) the results I'm looking for is to be able to enter any labor rate and model the dollars impact. __________________________________________________ _________________ I've worked on the file since my last e-mal and have abit more information. It would appear that the calculation method used between "Sum of 3q05 Labor Hrs" and "Sum of 3q05 labor $" are different (see below). The 3q05 labor dolars should total 15 (hrs) * 75 (labor rate) = $1125 and not $1500. It seem to be getting confused (or me) because item #1 has multiple records (item 1 has multiple tasks being performed with different task durations but the same rate, thus has different hrs between one another). example of pivot table item Sum of 3q05 Labor Hrs Sum of 3q05 labor $ 1 10 $750 2 5 $375 ____ _____ Grand Total 15 $1500 Example of source data item 3q05 Labor Hrs labor rate 1 5 $75 1 5 $75 2 5 $75 -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#5
|
|||
|
|||
Hi Debra,
Thank you for your response, I originally had the $75 hard coded in the formula but the request was to make it dynamic so anyone could enter any dollar value & refresh the pivot to see the results. I could place the calculation in the source data worksheet but the file & existing pivot are pretty large. I would have to add (to the pivot table) 8 additional fields (8 quarter forecast) to an existing 16 calculated fields. I'm concerned that the table will be too big and will not calculate. So I tried to change the formular to ="=3q05 labor Hrs*(average(labor rate))" but I had no luck with it. Do you think some kind of "IF" statement might work... -- Linda "Debra Dalgleish" wrote: Either calculate the dollars in each line of the source data, and add that field to the pivot table, or change your formula to ='3q05 Labor Hrs'*75 Linda wrote: answers to you questions: 1) the calculated field formula for 3q05 Labor $ is "=3q05 labor Hrs*labor rate" 2) the results I'm looking for is to be able to enter any labor rate and model the dollars impact. __________________________________________________ _________________ I've worked on the file since my last e-mal and have abit more information. It would appear that the calculation method used between "Sum of 3q05 Labor Hrs" and "Sum of 3q05 labor $" are different (see below). The 3q05 labor dolars should total 15 (hrs) * 75 (labor rate) = $1125 and not $1500. It seem to be getting confused (or me) because item #1 has multiple records (item 1 has multiple tasks being performed with different task durations but the same rate, thus has different hrs between one another). example of pivot table item Sum of 3q05 Labor Hrs Sum of 3q05 labor $ 1 10 $750 2 5 $375 ____ _____ Grand Total 15 $1500 Example of source data item 3q05 Labor Hrs labor rate 1 5 $75 1 5 $75 2 5 $75 -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#6
|
|||
|
|||
The number of fields should be the same, whether you use a calculated
field, or fields from the source data. If you can use programming in the workbook, you could add a cell named LaborRate, and allow users to type a rate there. Then add the following code to the pivot table's sheet module: '======================= Private Sub Worksheet_Change(ByVal Target As Range) Dim ws As Worksheet Set ws = ActiveSheet If Target.Address = ws.Range("LaborRate").Address Then ws.PivotTables(1).CalculatedFields("3q05 labor $"). _ StandardFormula = "='3q05 Labor Hrs'*" _ & ws.Range("LaborRate").Value End If End Sub '====================== Linda wrote: Hi Debra, Thank you for your response, I originally had the $75 hard coded in the formula but the request was to make it dynamic so anyone could enter any dollar value & refresh the pivot to see the results. I could place the calculation in the source data worksheet but the file & existing pivot are pretty large. I would have to add (to the pivot table) 8 additional fields (8 quarter forecast) to an existing 16 calculated fields. I'm concerned that the table will be too big and will not calculate. So I tried to change the formular to ="=3q05 labor Hrs*(average(labor rate))" but I had no luck with it. Do you think some kind of "IF" statement might work... -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using a Pivot Table Calculated Field to get a Unique Count | Excel Worksheet Functions | |||
Using a MIN, MAX formula on a calculated field in a pivot table | Excel Discussion (Misc queries) | |||
Calculated field in pivot table | Excel Discussion (Misc queries) | |||
pivot table - hide details but show subtotal for calculated field | Excel Discussion (Misc queries) | |||
How to create a calculated field formula based on Pivot Table resu | Excel Discussion (Misc queries) |