+ Reply to Thread
Results 1 to 8 of 8

Pivot Table Currency Conversion

  1. #1
    Registered User
    Join Date
    05-29-2013
    Location
    Bangalore
    MS-Off Ver
    Excel 2003
    Posts
    4

    Pivot Table Currency Conversion

    Dear all,

    I have pivot table which tracks all billing transactions. I work with USD, EUR, INR etc..

    In the pivot chart, I would like to have all currency converted to USD. I struggled with Pivot item and Pivot field. But reference to conversion factor outside the pivot table was not allowed.

    Attached workbook has three worksheet. In the resource sheet, currency conversion factor is listed. Custom formula in the pivot report has to check which currency it is and to do the conversion based on the reference table.



    Could you please guide me to solve this issue.

    Thanks,
    Suresh
    Last edited by pssureshbabu; 06-03-2013 at 01:04 AM.

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Pivot Table Currency Conversion

    Please attach a sample workbook with expected output for giving exact solution


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Registered User
    Join Date
    05-29-2013
    Location
    Bangalore
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Pivot Table Currency Conversion

    Quote Originally Posted by :) Sixthsense :) View Post
    Please attach a sample workbook with expected output for giving exact solution
    Workbook attached.
    Attached Files Attached Files

  4. #4
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Pivot Table Currency Conversion

    What I suggest is use a separate column to convert Other Currencies EUR and INR to USD and based on that you can populate all the data in pivot with USD value.

    In Resources sheet-D5 to D7 I have used for Currency values for $

    In AO Column of Database sheet I used the total in $ and avoided the calculated filed.

    Please refer the attached file for details
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    05-29-2013
    Location
    Bangalore
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Pivot Table Currency Conversion

    Thanks!

    I though there should be some way to do this via custom formula which easy!

  6. #6
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Pivot Table Currency Conversion

    Not sure what do you mean by custom formula?

  7. #7
    Registered User
    Join Date
    05-29-2013
    Location
    Bangalore
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Pivot Table Currency Conversion

    I mean, can I refer the "currency table" value inside pivot table calculated field and to do the conversion automatically?

  8. #8
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Pivot Table Currency Conversion

    No you can't because in the source the (4 column) amounts are belongs to different currencies. So when you apply this in PT then all the currency values are added together and we can' able to use the formula to do the process of converting the currencies to USD.

    So it is better to keep a helper column for doing this

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1