# IF() statement with Currency Symbol

1. ## 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.

2. ## 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. ## 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. ## 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. ## Re: IF() statement with Currency Symbol

Also isn't there a simpler formula??
Thx again

6. ## Re: IF() statement with Currency Symbol

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. ## Re: IF() statement with Currency Symbol

Excellent, I got it now!
Thanks again

8. ## 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.

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

#### 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