Pivot table - creating a calculated field

ihorwitz

New Member
Joined
Oct 16, 2003
Messages
28
I am trying to create a calculated field without success.

The underlying pivot table data is as follows:

FISC SKU COM VOL UNIT_COST
2008 151 173 155 8.92
2008 200 20 145 0.59
2008 200 90 145 0.49
2008 200 100 145 0.20
2009 151 173 139 8.46
2009 200 20 130 0.56
2009 200 90 130 0.46
2009 200 100 130 0.19

The pivot table is designed as follows:

Data FISC
VOL UNIT_COST
SKU COM 2008 2009 2008 2009
151 173 155 139 8.92 8.46
200 20 145 130 0.59 0.56
90 145 130 0.49 0.46
100 145 130 0.20 0.19

I would like to create a calculated field that does the following calculation:
=(UNIT_COST for 2009 - UNIT_COST for 2008) x VOL for 2009

Can anyone help?

Thanks
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Hi,

If you have data only for 2008 & 2009,
Code:
Iif(FISC=2008,-1,1)*VOL AS [NewField]

Though a better approach might be
Code:
Iif(FISC=2008,-1,Iif(FISC=2009,1,0)*VOL AS [NewField]

Use these in the SQL between the source data and the pivot table. For example when creating the pivot table take the 'external data source' at the first step of the wizard, or, via menu data, import external data, new database query. If you don't have a non-dynamic named range for the data & get the message about no visible cells, OK to it and then go 'options' and 'system tables'. OK to this and see the worksheets listed. The actual change is done in MS Query - hit the SQL button and edit the SQL there. (In the finished pivot table, place the new field as a data field and sum on it.) HTH, Fazza
 
Upvote 0

Forum statistics

Threads
1,223,920
Messages
6,175,377
Members
452,638
Latest member
Oluwabukunmi

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