Insert formula in pivot table

[Ecel Dumbo]

Hi Friends,
I have a pivot table that has "POST CODES". I will need to insert a column in the pivot table to show "STATES".
For this I will need do a calculated filed for "STATES"
All post codes are stored in text format (for eg-08000). I don't want to convert the text to number, keep it as it is
All postcodes beginning with 0 is NT state, 2 is NSW state- list below
0= NT
2=NSW
3=VIC
4=QLD
5=SA
6=WA
7=TAS
Can you please suggest a formula

Thanks

 

[Ecel Dumbo]

I have attached a file for easier understanding. Any help 'll be greatly appreciated

 

[Ecel Dumbo]

File now uploaded

 

[Khalid NGO]

Hi,

I think this will work, try adding this in your pivot:

=LOOKUP(LEFT(A2,1),{"0","2","3","4","5","6","7"},{"NT","NSW","VIC","QLD","SA","WA","TAS"})

you can use helper columns to avoid hardcodings.

Regards,

 

[Ecel Dumbo]

But Khalid, that formula will work if i use it outside the pivot table. I need a formula to be entered in the "Calculated Field" area of pivot table . A2 should be referencing column head "Post Code"

 

[Khalid NGO]

Ops...
Sorry i didn't seen the complete question.
Wait for someone for the confirmation whether it is possible or not.

Regards,

 

[Ecel Dumbo]

Any help please

 

[Nebu]

Hi:

It will not be possible to include this formula as a calculated field as you need the information in the row labels. You can either use the formula given by Khalid on a separate column adjacent to your post code column or else you can double click on the grand total of the pivot, this will give you the source data of the pivot in a separate tab you can add a new column to this data tab with the help of the formula from Khalid and can create a pivot from this new data set the way you want it.

Thanks