Pivot table column operations?

[Abel1]

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/2013	Location1	Other	Head Office	kWh	6000

<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

 

[venkat1926]

use "calculated field"
make google serch "pivot table calculated field excel______"