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.


--
SkyBluesFan
------------------------------------------------------------------------
SkyBluesFan's Profile: http://www.excelforum.com/member.php...o&userid=28215
View this thread: http://www.excelforum.com/showthread...hreadid=477639

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)


--
bill k


------------------------------------------------------------------------
bill k's Profile: http://www.excelforum.com/member.php...nfo&userid=821
View this thread: http://www.excelforum.com/showthread...hreadid=477639

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?


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=477639

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


--
SkyBluesFan
------------------------------------------------------------------------
SkyBluesFan's Profile: http://www.excelforum.com/member.php...o&userid=28215
View this thread: http://www.excelforum.com/showthread...hreadid=477639

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


--
bill k


------------------------------------------------------------------------
bill k's Profile: http://www.excelforum.com/member.php...nfo&userid=821
View this thread: http://www.excelforum.com/showthread...hreadid=477639

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

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


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=477639

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

Live and learn!!

Good luck.


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=477639

Good on you Bruce
Confirmation again. Excel is excellent.

Bill
Too old to die young.


--
bill k


------------------------------------------------------------------------
bill k's Profile: http://www.excelforum.com/member.php...nfo&userid=821
View this thread: http://www.excelforum.com/showthread...hreadid=477639

[quote=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)


--
bill k


Hi,

I have a similar problem. I have a list of companies and the states are listed. I have to convert the currencies into dollars for the states of british columbia and canada - using an IF statement. This is for an assignment at uni and any suggestions would greatly be appreciated.

Martin