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
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

venkat1926

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
use "calculated field"
make google serch "pivot table calculated field excel______"
 

Watch MrExcel Video

Forum statistics

Threads
1,122,805
Messages
5,598,173
Members
414,217
Latest member
Mystix

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