Pivot Table Problem

zomcnam

New Member
Joined
Oct 7, 2002
Messages
4
Hi there. I have a table with 5 columns: Store, Store Size (in Sq. Meters), Service Provided, Cost, and Cost per Square Meter (Column 4 divided by column 2). I made a Pivot Table to see how much each store gives out for each service. My problem is that I need to know how much each service costs per square meter preferrably as part of the Pivot Table. Any tips?
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

paliman

Active Member
Joined
Jul 7, 2002
Messages
254
Sorry, I don't understand what you want. How do you calculate how much each service costs per m2? Why not inserting an extra column and adding it to your pivot table?
If you can give me some more details i'll try to help you.
 

Mark W.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
On 2002-10-08 14:01, zomcnam wrote:
Hi there. I have a table with 5 columns: Store, Store Size (in Sq. Meters), Service Provided, Cost, and Cost per Square Meter (Column 4 divided by column 2). I made a Pivot Table to see how much each store gives out for each service. My problem is that I need to know how much each service costs per square meter preferrably as part of the Pivot Table. Any tips?

Create a use a Calculated Field in your PivotTable with the formula...

='Cost'/'Store Size'
 

zomcnam

New Member
Joined
Oct 7, 2002
Messages
4
First of all, thanks. I tried the calculated field but there is a problem. For example:

'Store' 'Size' 'Service' 'Cost' Cost/m2

'Store A' '500 m2' 'Cleaning' '$500' '$1/m2'

'Store A' '500 m2' 'Repairs' '$500' '$1/m2'

'Store B' '200 m2' 'Cleaning' '$20' '$.1/m2'

'Store C' '100 m2' 'Cleaning' '$10' '$.1/m2'

If I added the total costs together for all stores and divided by the total Square Meters I would have $1030 divided by 1300 m2 which would give me $.79 / m2. The problem is that there are only 800 m2 and not 1300 m2. The pivot table adds all entries even if they are duplicates. Is there any way to avoid this? Thanks in advance.
 

Mark W.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654

ADVERTISEMENT

Ahh, you're right! A Calculated Field won't satisfy your needs, but why doesn't the summation of your prorated costs, 'Cost per Square Meter', do the trick?

Store A $2.00
Store B $0.10
Store C $0.10
 

zomcnam

New Member
Joined
Oct 7, 2002
Messages
4
Thanks again for your help. The problem with adding the total 'Cost / m2' is that each number is correct for each store individually but I also need the result for all stores grouped together. The Pivot Table adds Store A ($2), Store B ($.10), and Store C ($.10) together which leaves me with $2.20/m2 across all three stores. However, the total cost ($1030) divided by the total area (800 m2) should be $1.29/m2. Any solution?
 

Mark W.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
Any solution? Not really. The problem you are confronting is rooted in relational database theory. You're experiencing reporting anomalies attributable to the fact that your single Excel data list is only in First Normal Form (1NF), and the only way to resolve the problem is to divide your list into separate lists (tables) which would make it unusable for a PivotTable summary. Your data violates the rules for Second Normal Form (2NF) because your "Store Cost" table's organization is based on a composite key ('Store', 'Service Provided'). An individual 'Cost' value can only be determined by "knowing" both of these values). 'Store Size' (which is used to calculate 'Cost per Square Meter') is functionally dependant on only a part of this composite key -- 'Store' -- and as such should be stored in a separate table to alleviate such reporting anomalies.

Welcome to the exciting world of relational database design... and it's implications for Excel PivotTables... Catch-22.
This message was edited by Mark W. on 2002-10-10 10:36
 

zomcnam

New Member
Joined
Oct 7, 2002
Messages
4
Thanks for the explanation. Accepting this limitation I tried something else. I copied the results of the pivot table to a new table and put those results in a new pivot table. I know that much of the pivot utility is lost but by making the numbers less 'multidimensional' I seem to get what I am looking for. Thanks alot for your help.
 

Forum statistics

Threads
1,143,702
Messages
5,720,375
Members
422,281
Latest member
jantoooh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top