Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If statement based on currency format
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If statement based on currency format
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) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If statement based on currency format
On Mon, 18 Sep 2006 11:35:02 -0700, juliejg1
wrote: 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 __________________________ |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If statement based on currency format
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 On Mon, 18 Sep 2006 11:35:02 -0700, juliejg1 wrote: 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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If statement based on currency format
Thank you all for your help! Very useful, I got what I needed
"juliejg1" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Currency format in sort | Excel Discussion (Misc queries) | |||
Put an if statement according to cell format | Excel Worksheet Functions | |||
New column based on row cell format | Excel Discussion (Misc queries) | |||
format cell based on results of vlookup function | Excel Worksheet Functions | |||
convert a numerical currency into word format | Excel Worksheet Functions |