+ Reply to Thread
Results 1 to 8 of 8

IF() statement with Currency Symbol

  1. #1
    Forum Contributor meyero90's Avatar
    Join Date
    03-24-2010
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    202

    Arrow IF() statement with Currency Symbol

    Hello,
    I have a row of data starting in A2 with an amount in a given currency (e.g $100, €100,…) and in B2 the corresponding Currency code (e.g, “GBP”, “USD”, “EUR”,…) and in C2, I’m trying to do the following:
    =IF(B2="GBP","£"&ABS(A2),IF(B2="USD","£"&ABS(A2)/$I$3,IF(B2="EUR","£"&ABS(A2)/$I$4,"")))
    Now the whole purpose of this is to NEVER CARE what’s the given currency showed (by default or not) in A2, since the formula relies on cell B2 to convert A2 in the desired currency. So, here’s my PROBLEM:
    - I needed to add “manually” the GBP symbol “£”& in each argument of the formula and IT WORKS!!,….BUT
    - Is there a “smarter way to do this” instead of inserting the symbols all the time…and MOST IMPORTANTLY (as you’ll see in my xls attachment)….
    - YOU CAN’T SUM THE OBTAINED CURRENCY CONVERTED VALUES.
    Thank you all for your help and let me know if you need more info to solve my problem.
    Attached Files Attached Files
    Last edited by meyero90; 05-03-2010 at 08:55 AM.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: IF() statement with Currency Symbol

    Perhaps you could use:

    C2: =ABS($A2)/VLOOKUP("GBP"&REPT($B2,$B2<>"GBP"),$H$2:$I$4,2,0)
    copied down

    Use the Currency Format to add the £ symbol - at which point your SUM will work (it doesn't presently given the values are text strings by virtue of the concatenation).

  3. #3
    Forum Contributor meyero90's Avatar
    Join Date
    03-24-2010
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    202

    Re: IF() statement with Currency Symbol

    Great thx for your help, it works fine, but I need to some tutorial on the REPT() function. I understand very well vlookup, but how does the function understand which rate to apply if in the argument there was only "GBP".

    Thanks again

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: IF() statement with Currency Symbol

    All currency rates in your lookup table have a GBP prefix.

    Where the currency is anything other than GBP the lookup string will be GBP??? where ??? represents the currency code.

    The REPT is used to simply append the GBP prefix with the appropriate currency code if it is anything other than GBP.

    The 2nd parameter of REPT expects an integer.
    If this 2nd parameter value is a Boolean (as is the case here - either TRUE/FALSE depending on whether currency is other than GBP) then the Boolean will automatically be coerced to it's integer equivalent
    (in native XL is: TRUE -> 1, FALSE -> 0)

    So, in terms of the evaluation process, using simple example:

    REPT("Apple",1=1) -> REPT("Apple",TRUE) -> REPT("Apple",1) -> "Apple"

    REPT("Apple",1=2) -> REPT("Apple",FALSE) -> REPT("Apple",0) -> ""

    I hope that makes sense.

  5. #5
    Forum Contributor meyero90's Avatar
    Join Date
    03-24-2010
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    202

    Re: IF() statement with Currency Symbol

    Also isn't there a simpler formula??
    Thx again
    Your help is much appreciated

  6. #6
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: IF() statement with Currency Symbol

    Quote Originally Posted by meyer.ohayon
    Also isn't there a simpler formula??
    I'm not sure how much simpler you need in truth - an ABS/VLOOKUP isn't really that complex.

    If you want to remove the REPT I would suggest you maintain some consistency in your rate table - ie use GBPGBP ... at which point:

    =ABS(A2)/VLOOKUP("GBP"&B2,$H$2:$I$4,2,0)

    Inconsistencies arising from naming conventions invariably make things more complex than they need be.

  7. #7
    Forum Contributor meyero90's Avatar
    Join Date
    03-24-2010
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    202

    Re: IF() statement with Currency Symbol

    Excellent, I got it now!
    Thanks again

  8. #8
    Registered User
    Join Date
    07-19-2017
    Location
    London
    MS-Off Ver
    2016
    Posts
    2

    Re: IF() statement with Currency Symbol

    Hi everyone! I need help with a formula and currency signs.
    I have a drop down list in cell G5 of EUR, GBP, USD - after the sign is chosen I want cell G21 which is total multiply of cells E21*F21 and comeback with a correct currency sign £, $ or € and total.
    Thank you in advance for your help!

+ 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