Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Linda
 
Posts: n/a
Default 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   Report Post  
Debra Dalgleish
 
Posts: n/a
Default

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   Report Post  
Linda
 
Posts: n/a
Default

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   Report Post  
Debra Dalgleish
 
Posts: n/a
Default

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   Report Post  
Linda
 
Posts: n/a
Default

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   Report Post  
Debra Dalgleish
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Using a Pivot Table Calculated Field to get a Unique Count Mike Struckman Excel Worksheet Functions 1 November 22nd 05 06:32 PM
Using a MIN, MAX formula on a calculated field in a pivot table fhaberland Excel Discussion (Misc queries) 0 August 2nd 05 07:47 PM
Calculated field in pivot table Dan Excel Discussion (Misc queries) 1 April 19th 05 11:06 PM
pivot table - hide details but show subtotal for calculated field tenneym Excel Discussion (Misc queries) 1 February 9th 05 04:07 AM
How to create a calculated field formula based on Pivot Table resu dha17 Excel Discussion (Misc queries) 1 December 15th 04 06:39 AM


All times are GMT +1. The time now is 12:47 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"