IF formula against currency signs

[outtahere]

I would like to use an IF formula against cells with different currencies. For example column A has no of units, column b has price and column c has units multiplied by price. The prices are formatted using currency format and have either $ or £ signs. I would like to apply different formula based on these signs. I am using excel 2007.

 

[Randombard]

Hi there,

the first thing you will need to do is open VBA and add this function to a module

Function GetFormat(Cell As Range) As String
   GetFormat = Cell.NumberFormat
End Function

once you have this you can use this formula

=IF(LEFT(getformat(D14),1)="$","DollarFormula","PoundFormula")

Edit as you need

That should sort that out for you.

 

[Randombard]

Actualy having tested that it does not seem to work.

Looks like it always turns up the "$" value.

I will have a play see if there is a better way.

 

[Randombard]

Ok so the original one does work but with a minor change

The Formula should be

=IF(LEFT(getformat(D14),1)="$","PoundFormula","DollarFormula")

Function GetFormat(Cell As Range) As String
Application.Volatile
   GetFormat = Cell.NumberFormat
End Function

As the changes you are making are farmat based they dont triger calculatation so you will need to do that manualy "F9" will do that.

the other way is

Function TestForDolar(Cell As Range) As String
Application.Volatile
If Cell.NumberFormat = "[$$-409]#,##0.00" Then
TestForDolar = "TRUE"
Else
TestForDolar = "FALSE"
End If
End Function

This gives you a result that can be used in a IF statment.

 

[outtahere]

Hi there, thanks for the quick reply, I have never used VBA before. I guess this means there is no standard Excel function for this is there? I may, for the time being do this in a less elegant way by adding a currency column next to the value. This is a quick fix. Then I will start to look at VBA. Appreciate your help.

 

[Randombard]

Hi OUttahere,

It looks and sounds more impresive then it is and once you start you will never stop

Before doing any of this save a copy of the spreadsheet as a back up!

press

Alt and F11


this brings up the VBA editor.

on the top left you should see projects find the one with the name of your workbook and click the little cross next to it to expand.

Now right click and select insert -> module

in the new module paste the code I send you (not the formulas just the bits in the indent)

And your away.

 

[outtahere]

OK you have convinced me. thanks I will try it.

 

[Peter_SSs]

I would like to use an IF formula against cells with different currencies. For example column A has no of units, column b has price and column c has units multiplied by price. The prices are formatted using currency format and have either $ or £ signs. I would like to apply different formula based on these signs. I am using excel 2007.

If you only have those 2 currency symbols, you may be able to use something like this. I'm not working on Excel 2007 at the moment so my instructions may not be exact. Also, I haven't tested this in Excel 2007.

1. Select cell B2

2. Formulas ribbon|Name Manager|New|Name: nf | Refers to: =GET.CELL(7,!A1)|OK

3. Use a formula similar to this, copied down, to do something different for the 2 currencies. ($ are multiplied by 3, £ are multiplied by 5)

4. After copying the formula down, use the Format Painter to apply the formats from col B to col C so the currency formats match.

Excel Workbook
	A	B	C
1	Units	Price	Total
2	1	$2.00	$6.00
3	2	3.00	30.00
4	3	$5.00	$45.00
Identify Currency

 

[outtahere]

Thank you Peter and sorry it took me a year to reply. It worked.

 

[Peter_SSs]

... sorry it took me a year to reply.

Better late than never I guess!