Pivot table column operations?

Abel1

New Member
Joined
Sep 19, 2014
Messages
1
Hi there,

I'm currently investigating the energy usage of a large drinking water company. I have monthly data on the amount of raw water used, drinking water delivered and energy used per process step. My input data looks something like this:
Date
Location
Category
Subcategory
Unit
Value
1/1/2013
Location1
Production
UV
kWh
8754
1/1/2013
Location1
Production
Sand Filters
kWh
4000
1/1/2013
Location1
Clean Water
High Pressure Pump 1
m3
100000
1/1/2013
Location1
Clean Water
High Pressure Pump 2
m3
40000
1/1/2013
Location1
Raw Water
Low Pressure Pump
m3
150000
1/1/2013
Location1
Delivery
High Pressure Pumping Station
kWh
40000
1/1/2013
Location1
Production
Low Pressure Pumping Station
kWh
30000
1/1/2013Location1OtherHead OfficekWh6000

<tbody>
</tbody>

Using a Pivot Table to create sums of energy use per category and location, or total clean and raw water production works brilliantly.
But what I'm most interested in is the amount of energy used per cubic meters of raw water produced and clean water delivered. For example the energy used for UV divided by the amount of Raw Water, or the energy use of the Production + Delivery per amount of clean water.

I'm at a loss on how to let Excel calculate those values for me. I tried using the Calculated Field option, but that does not seem to be able to do what I want. Manually adding columns with formulas is also not possible.

Can someone help me solve this puzzle?

Thanks!

Abel
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Forum statistics

Threads
1,217,382
Messages
6,136,238
Members
450,000
Latest member
jgp19

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
Back
Top