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

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

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

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

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

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