Results 1 to 9 of 9

Thread: Removing Currency Sign in the Equation

  1. #1
    Acolyte Francis Gerard Tupaz's Avatar
    Join Date
    Jun 2012
    Location
    Philippines
    Posts
    30
    Articles
    0
    Excel Version
    2011

    Removing Currency Sign in the Equation



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Dear Excel Guru's,

    May I seek your assistance please in the attached excel file that am working on?

    This is suppose to compute for potential earnings "region or country specific" currency rate.

    Example: If you put "Japan" in under REGION (C6) the Local Currency Rate will change to "JPY" (G6), and automatically the columns D1014 will likewise adopt currency rate. And it is suppose to change as you change the regions i.e Europe 1, Europe 2, etc. Source data is located in product code sheet. I am getting an error if I multiply D10*E10 right now.

    Is there a way I can delete the currency sign under B21 so it will just consider the number? Or is there a way that it will multiply even with the currency?

    Thanks very much, hoping to hear from you my friends.

    Regards,

    Francis
    Attached Files Attached Files

  2. #2
    Here is one option . I just added a hidden column (E) and entered formula to trim off the currency symbol. then just multiplied by e10 instead of d10 in Cell b21.

    Code:
    =RIGHT(D10,LEN(D10)-1)
    i attached a file if you want to see it work firsthand.
    Attached Files Attached Files

  3. #3
    It is treating your currency numbers in your Product Code sheet as text. did you happen to copy and paste these into those cells.

    you can see what i am talking about by doing the following.

    Example: use the japanese currency cells that your demo file is referring to.

    1. delete all data on the Product Code sheet in cells D71- D75
    2. set cell format to general
    3. re-enter your values 650,1150,1500,3400,650
    4. highlite D71-D75
    5. set cell format to currency and select japanese
    6. make sure you have it set to 2 places ..it will mess up your trim(index) formula on Purchase Request sheet

    Now go to your Purchase Request sheet and set the cell formats like you want them in d10 and b21

    it should now multiply correctly , at least it did for me.

    hope it helps
    Last edited by tommyt61; 2012-06-15 at 10:32 PM.

  4. #4
    Acolyte Francis Gerard Tupaz's Avatar
    Join Date
    Jun 2012
    Location
    Philippines
    Posts
    30
    Articles
    0
    Excel Version
    2011
    Dear Tommy and Excel Guru,

    Thank you for the help, sadly I still am getting an error after doing all the suggestions whenever I change the regions it no longer computes.

    Hope someone can help me.

    Regards,

    Francis

    Quote Originally Posted by tommyt61 View Post
    It is treating your currency numbers in your Product Code sheet as text. did you happen to copy and paste these into those cells.

    you can see what i am talking about by doing the following.

    Example: use the japanese currency cells that your demo file is referring to.

    1. delete all data on the Product Code sheet in cells D71- D75
    2. set cell format to general
    3. re-enter your values 650,1150,1500,3400,650
    4. highlite D71-D75
    5. set cell format to currency and select japanese
    6. make sure you have it set to 2 places ..it will mess up your trim(index) formula on Purchase Request sheet

    Now go to your Purchase Request sheet and set the cell formats like you want them in d10 and b21

    it should now multiply correctly , at least it did for me.

    hope it helps

  5. #5
    Can you be more specific , you mention after making changes it no longer computes. I attached a copy of your demo file that i made the exact changes i listed . See if it works correctly for you .
    Attached Files Attached Files
    Last edited by tommyt61; 2012-06-16 at 03:53 PM.

  6. #6
    Or in your file you can put this formula in B21 and copy down to B25. This will just trim the currency symbol off of the number in D10-D14 before you multiply by 10. set your cell format on B21-B25 to number no spaces .

    Code:
    =RIGHT(D10,LEN(D10)-1)*10

  7. #7
    Hey Francis,

    I went back and looked at your sheet and realized that you have a bunch of different currency formats. so the options i gave you was me assuming that all amounts would only have 1 symbol in front of the number so the options i provided will not work . sorry i didn't pick up on that. i would not have wasted your time messing with my suggestions.

    Here is where your problem lies . a Vlookup function will not bring the data format into cell D10 as a number. Vlookup displays it's data as text only. You can't multiply text *10 without getting an error, you have to multiply number * number only. to test this pick a blank cell in Purchase Request sheet and enter this formula =ISTEXT(D10) If you get a True than it sees the data in D10 as text. If you get a False it sees the data as a number.

    you have to get the data in Cells D10-D14 in number format. currently using Vlookup is not gonna work. If you can deal with not having the currency symbol in cells D10-D25 it might not be to hard to find a workaround. hope this helps answer some questions for you .

  8. #8
    Take a look at this . i went through each currency and everything seemed to calculate correctly... but i have said that before haven't i
    Attached Files Attached Files

  9. #9
    Acolyte Francis Gerard Tupaz's Avatar
    Join Date
    Jun 2012
    Location
    Philippines
    Posts
    30
    Articles
    0
    Excel Version
    2011

    SOLVED: Removing Currency Sign in the Equation

    Hi Tommy,

    Again I owe you a big big thanks! I was able to use the formula you sent tweaked it a bit so it will just be under one cell all together but your guidance has helped me SO MUCH!

    May you continue to prosper!

    Regards,

    Francis
    Quote Originally Posted by tommyt61 View Post
    Take a look at this . i went through each currency and everything seemed to calculate correctly... but i have said that before haven't i

Posting Permissions

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