How to retain the three letter currency information when doing math formulas?

**Chrysalis** · 02-27-2015, 09:04 AM

Hi,

I have an excel sheet with a few problems.

I have yearly maintenance fees that are paid in the local currency. This is marked with the international signs on the right.

In some cases we also charge a once a month fee, which is accrued over the course of the year. Others are just paid at the beginning of the year.

The thing is, for monthly payments I need to keep on the right hand side the three letter currency information.

For example in this case: 2,250 USD over the course of six months is 13,500.00. At the moment I use this kind of formula:
=IF(ISNUMBER(J3),J3,VALUE(LEFT(J3,SEARCH(" ",J3)-1))) * 6

Now I need to retain the USD marker because not all currencies convert from USD to EUR the same way, so I cannot use $ (AT ALL).

Maintenance fees, per annum (Orig)	Monthly installments	Yearly maintenance fees	Maintenance fees, per annum (EUR)
	2,250 USD	13,500.00 USD	11,880.00
14,000 AUD	NA		9,660.00
14,000 AUD	NA		9,660.00

**windy58** · 02-27-2015, 09:43 AM

Is this any help?

=IF(ISNUMBER(J3),J3,VALUE(LEFT(J3,SEARCH(" ",J3)-1))) * 6&" "&RIGHT(J3,3)

Windy

**AlKey** · 02-27-2015, 10:17 AM

Or maybe a short one

There is no need for VALUE function since multiplication will do it anyway. There is also no use for ISNUMBER either.

=LEFT(J3,FIND(" ",J3))*6&" "&RIGHT(J3,3)

You may also would like to add an error trapping to your formula:

If you use Excel verision 2007 and up you can use IFERROR function

=IFERROR(LEFT(I3,FIND(" ",I3))*6&" "&RIGHT(I3,3),"NA")

and if you still using Excel 2003 or lover

=IF(ISERROR(LEFT(J3,FIND(" ",J3))*6&" "&RIGHT(J3,3)),"NA",LEFT(J3,FIND(" ",J3))*6&" "&RIGHT(J3,3))