• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Insert formula in pivot table

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
 
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,
 
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"
 
Ops...
Sorry i didn't seen the complete question.
Wait for someone for the confirmation whether it is possible or not.

Regards,
 
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
 
Back
Top