Calculated field in pivot table

M

michaelmcandrew

New Member
Joined
Feb 23, 2009
Messages
5
Hi there,

My database exports transaction details with countries.

I want to create pivot table that splits these transactions (amongst other things) by whether the country is in the EU or not.

Here's a formula that would do that if it were just contained in a standard cell.
Code: 
=IF(OR(D2="Austria",D2="<wbr>Belgium",D2="Bulgaria",D2="<wbr>Cyprus",D2="Czech Republic",D2="Denmark",D2="<wbr>Estonia",D2="Finland",D2="<wbr>France",D2="Germany",D2="<wbr>Greece",D2="Hungary",D2="<wbr>Ireland",D2="Italy",D2="<wbr>Latvia",D2="Lithuania",D2="<wbr>Luxembourg",D2="Malta",D2="<wbr>Netherlands",D2="Poland",D2="<wbr>Portugal",D2="Romania",D2="<wbr>Slovakia",D2="Slovenia",D2="<wbr>Spain",D2="Sweden",D2="United Kingdom",),"EU", "Outside EU")
I thought I could create a calculated field using a slightly modified version of this code but my attempts so far have been fruitless.

I know I could just create another column and fill in with this formula before doing the pivot table but I am doing this repeatedly with different data and want to streamline the process as much as possible.

This is a tough one, right? Hope that someone can help,

Cheers,
Michael
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Sort by date Sort by votes
so for the calculated field in the pivot maybe put

Code: 
=IF(OR(D2="Austria",D2="Belgium",D2="Bulgaria",D2="Cyprus",D2="Czech Republic",D2="Denmark",D2="Estonia",D2="Finland",D2="France",D2="Germany",D2="Greece",D2="Hungary",D2="Ireland",D2="Italy",D2="Latvia",D2="Lithuania",D2="Luxembourg",D2="Malta",D2="Netherlands",D2="Poland",D2="Portugal",D2="Romania",D2="Slovakia",D2="Slovenia",D2="Spain",D2="Sweden",D2="United Kingdom",),1, 0)
 
Upvote 0
I want to turn this:



into this:

my.php

my.php
 
Upvote 0
Hi, Michael.

I don't see the images you posted just above.

If you can, it might be best to do this step in your database before the export. Such as, add a table in the database of EU_Countries. Then add the extra field before the export. So it could be like
Code: 
SELECT whatever.*, 'EU' AS [EU_OR_NOT]
FROM whatever, EU_Countries
WHERE whatever.Country = EU_Countries.Country
UNION ALL
SELECT whatever.*, 'Outside EU' AS [EU_OR_NOT]
FROM whatever, EU_Countries
WHERE NOT whatever.Country = EU_Countries.Country
Or similar with outer joins and Is Null.

If you can't do that in the database, the identical approach can be done in Excel. Just add a table of EU_Countries and use the identical SQL in Excel. You could even create the new table in a separate file and then pull from this one file for all your queries that require the data. Just include the full file path in the table reference in the SQL.

HTH, Fazza
 
Upvote 0
Thanks for these ideas. I thought the calculated field functionality of pivot tables should be able to handle this, but it seems not. Didn't fully understand the sql but presume that i'd have to do a lot of visual basic to get it automated - gave up and made a simple find and replace macro instead - thanks all anyway :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question
VBA to colour a cell based on data in 2 others cells
2
Replies
14
Views
805
honkin
H
T
  • Question
Sorting in pivot table
Replies
1
Views
640
Crystalyzer
Crystalyzer
I
  • Question
VBA Macro - Compile Error
Replies
4
Views
481
Isbenji
I
R
  • Question
Is there a quick and easy way to tabulate a list of values in a range?
Replies
0
Views
412
Rhothgar
R
M
  • Solved
Lookup Value and Replace Based on Table data
2
Replies
10
Views
836
maabadi
M

Forum statistics

Threads
1,215,772
Messages
6,126,806
Members
449,337
Latest member
BBV123

Share this page

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