If text & value formulas

[Sophster]

Hello,

I have 1 column (column C) displaying values and text. When it is displaying values, depending on the currency in column B, I have used a IF formula to translate the value in Column E in the Euro currency. The below formula works fine for values.

=IF($B6="GBP",C6*1.2,IF($B6="USD",C6*0.7,IF($B6="EUR",C6*1,0)))

However, column C also sometimes display text ("N/A" or "Inclusive"). If the cell in column C displays value, then the above formula should apply, but if it's text, then the same text should be displayed in E.

I don't know what formula to use as the text is changing.
I have attached a sample spreadsheet.

Many thanks

[ChemistB]

An an initial IF statement to check to see if col C is text like this

=IF(ISTEXT(C5),C5,IF($B5="GBP",C5*1.2,IF($B5="USD",C5*0.7,IF($B5="EUR",C5*1,0))))

Does that work for you?

[edvwvw]

We crossed ChemistB

Try this in E5 and drag down

=IF(ISTEXT(C5), C5, IF($B5="GBP",C5*1.2,IF($B5="USD",C5*0.7,IF($B5="EUR",C5*1,0))))

works in Excel 2007

edvwvw

[Sophster]

This is working perfectly!!

Thank you both very much!!