If statement based on currency format

[Guest]

I have a spreadsheet that has a column (column B) of numbers. Some of the
numbers are formatted in Euro currency and some are formatted in USD currency.
I would like to put a formula in column C to automatically calculate the
currency conversion of the Euro to USD.

If cell B1 currency = Euro, then cell C1*1.45, else cell B1
Is there any way to do this?

 

[Guest]

first put this in a cell:

=CELL("format",B2) where B2 exchange to a cell where Euro is formattet
the result from this u can use in :

put in C2 and copy down:
=IF(CELL("format",B2)=" put result from above in here ",B2*1.45,B2)

 

[Guest]

first put this in a cell:

=CELL("format",B2) where B2 exchange to a cell where Euro is formattet
the result from this u can use in :

put in C2 and copy down:
=IF(CELL("format",B2)=" put result from above in here ",B2*1.45,B2)

 

[Richard Buttrey]

I have a spreadsheet that has a column (column B) of numbers. Some of the
numbers are formatted in Euro currency and some are formatted in USD currency.
I would like to put a formula in column C to automatically calculate the
currency conversion of the Euro to USD.

If cell B1 currency = Euro, then cell C1*1.45, else cell B1
Is there any way to do this?

How will any formula know whether the number is in euros of dollars,
unless you have a third column which defines the currency of col B
amounts?

Rgds
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[Richard Buttrey]

I have a spreadsheet that has a column (column B) of numbers. Some of the
numbers are formatted in Euro currency and some are formatted in USD currency.
I would like to put a formula in column C to automatically calculate the
currency conversion of the Euro to USD.

If cell B1 currency = Euro, then cell C1*1.45, else cell B1
Is there any way to do this?

How will any formula know whether the number is in euros of dollars,
unless you have a third column which defines the currency of col B
amounts?

Rgds
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[Per Erik Midtrød]

Hello.

I dont' think there is any built-in function to do this, but this one
might just work:
Public Function CheckEuro(rng As Range) As Boolean
Application.Volatile
CheckEuro = False
If rng.NumberFormatLocal = "[$€-2] # ##0,00" Then ' You need to
'excatly check what format you are using
CheckEuro = True
End If
End Function

Press Alt+F11 and choose insert Procedure and choose funcion.
It returns true if then number format in the cell you are testing is
excatly: "[$€-2] # ##0,00"

Then you could use the following if-formula
IF(checkEuro(B1)=TRUE;B1*1,5;A1)

You must make sure that the number format you are using for Euro is
excatly as shwon in the formula. If you are using different formats
you might manage to do something with a clever use of the left
function in vba.

Best regards
Per Erik

 

[Per Erik Midtrød]

Hello.

I dont' think there is any built-in function to do this, but this one
might just work:
Public Function CheckEuro(rng As Range) As Boolean
Application.Volatile
CheckEuro = False
If rng.NumberFormatLocal = "[$€-2] # ##0,00" Then ' You need to
'excatly check what format you are using
CheckEuro = True
End If
End Function

Press Alt+F11 and choose insert Procedure and choose funcion.
It returns true if then number format in the cell you are testing is
excatly: "[$€-2] # ##0,00"

Then you could use the following if-formula
IF(checkEuro(B1)=TRUE;B1*1,5;A1)

You must make sure that the number format you are using for Euro is
excatly as shwon in the formula. If you are using different formats
you might manage to do something with a clever use of the left
function in vba.

Best regards
Per Erik

 

[Guest]

Thank you all for your help! Very useful, I got what I needed

 

[Guest]

Thank you all for your help! Very useful, I got what I needed