Excel 2003 PivotTable Calculated Item

[BellKev]

Hello ...

I create a pivot table from a data spreadsheet no problem. When I add a calculated item I start running into problems. My Data basically looks like this ...

PLAN LOCATION DESCRIPTION QTY
401 A1-A2 Dig 5
402 A2-A3 Place 7

And so on ... when I create my pivot table, with Plan, Location on my left side, Description Across the top, and the QTY in the middle Everything shows up right. For each plan I only get the locations that show up on that plan. When I add a calculated item it seems that the total "Locations" gets added to each plan. So I have a false summary (ie. A2-A3 would show up beside plan 401, but there would be nothing there ...) Anyone know why this is?

Kevin

 

[AnotherHiggins]

What is the formula used in the calculated field?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[BellKev]

=Description 1 QTY / Description 2 QTY

 

[SkipVought]

Why not just
[tt]
=D2/D3
[/tt]
Is this ALL the data, or just a sample?

What do you mean by Description 1 QTY & Description 2 QTY, if there are more than these two rows?

Skip,
[sub]
[red][/red]
[/sub]

 

[BellKev]

Building on my description above ...

PLAN LOCATION DESCRIPTION QTY
401 A1-A2 Dig 5
401 A1-A2 Dig Additional 25
402 A2-A3 Place 7
402 A2-A3 Place Additional 49

The idea is that there is a rate paid for the first operation done, then a different rate paid for any additional items ... for example... if I'm placing conduit into the ground ... there is a rate to pay to trench and place the first conduit in the ground ... then to place additional conduits I pay a reduced rate. But the way it works is that it's by trench foot ... so in my example above I would be trenching and placing (A1-A2) 1 conduit for 5ft. Then in the same section I'm placing 25 feet of additional, so 25/5 = 5 5 additional conduits .. which give me 6 in total ...

 

[SkipVought]

I think that it's best to do in the source table not in a pivot table.
[tt]
=if(upper(right(c2,10))="ADDITIONAL",d1/d2,d2)
[/tt]


Skip,
[sub]
[red][/red]
[/sub]