Converting Currency

[SkyBluesFan]

I have a spreadsheet with the following columns:

- Customer Name
- Amount Owed (Can be either $ or £)
- Amount Owed (£)

Unfortunately I am unable to make an IF statement recognise the $ or £
sign. Any quick tips on how this can be done without having to have a
separate column with the signs in. The IF statement would divide by the
rate (£1 = $1.7326) if it is dollars and would state the amount if it
was in pounds.

Any help would be appreciated.

 

[bill k]

Assuming that the $ sign or the pounds sign is a format issue.

=IF(CELL("format",(A2))="c2",A2/1.7326,A2)


this will check for the dollar sign

obviously you could enter the conversion rate in say cell D1,
and use the cell reference in the formula

=IF(CELL("format",(A2))="c2",A2/D1,A2)

 

[swatsp0p]

Bill has a good idea, however 'C2' simply means Currency, 2 decimals.
It does not look at WHAT currency symbol is displayed. This method
will not meet your needs.

When you Format a cell to display either one or the other currency
symbols, technically, the cell is numeric and doesn't really contain
either sign (check the formula bar for the exact contents of the
cell).

My best solution would be the use of a helper column (named "Currency
Type") to hold a value to signify the type, e.g. 'D' or 'P').

You can then use an IF statement to test the helper cell for either D
or P and achieve the desired outcome.

=IF(C1="D", B1/1.7326,B1)

Format this cell as Currency/pound

NOTE: as the exchange rate fluctuates, you may also want to put that
rate in another cell (E1) and change your formula to:

=IF(C1="D", B1/E1,B1) then just update E1 when the rate changes and
all your formulas will show the current value.

Does this work for you?

 

[SkyBluesFan]

Cheers for the help guys!

I set it up originally with a column for the amount and a column where
you could select the currency from a list (a message states that an
entry must be made and it provides the two options).

I have then used that as the basis for my IF statement. I was just
hoping that you could avoid the 'Currency Column' and enter the values
in the following format £xx.xx or $xx.xx

It doesn't appear to be possible to simply find the £ or $ sign and run
an IF statement off that though.

Thank you anyway for your suggestions

 

[bill k]

I did try out my cell function and it worked for me.

I.e. "c2" picked up any amount formatted in dollars and ",2" recognised
the pounds.

So there.............( :>)

Now, it could well be that the c2 will pick up the default currency
format, and the ,2 picks up a different one.

You may have to check on your system.
Just format some numbers and use the function =CELL("format",A1)
to see what's what.

feed back please
always willing to learn

 

[swatsp0p]

I, too, am willing to learn. In my test, I got the same value returne
regardless of the currency format chosen, be it dollars, pounds, rand
yen, etc.

Maybe I'm missing something... sorry! :

 

[swatsp0p]

OOOH.. I have a DUH!!! here. I didn't recalculate after making th
change in the currency cell. The =CELL() function will not chang
until the cell is recalculated! In my system $ returns "C2", al
others return ",2".

Live and learn!!

Good luck

 

[bill k]

Good on you Bruce
Confirmation again. Excel is excellent.

Bill
Too old to die young.