Calculate cell value based on another cells format

[mordrid]

Hi, thanks for the help earlier the response worked, however, I misunderstood the ask of me and am struggling with the following issue

In cell A1 I have the value of the dollar exchange rate I want to use
In cell A2 I have the value of Euro exchange rate I want to use

In Col C I have a mix of Euro and Dollar values (cells are formatted as Accountancy dollar or Euro accordingly)

In Col D I would like to auto calculate the Pound value of the corresponding value in C but to do that I need to multiply the C value by the right exchange rate.

So something along the lines IF C2 is formatted as dollars then D2 would = C2*A1 but if C2 is formatted in Euros then D2 would = C2*A2

Any help or pointers gratefully accepted

 

[ChrisFoster]

You can use the following formula in cell D2 and copy it down to the rest of the cells in column D:

=IF(LEFT(C2,1)="$",C2*A1,C2*A2)

 

[mordrid]

You can use the following formula in cell D2 and copy it down to the rest of the cells in column D:

=IF(LEFT(C2,1)="$",C2*A1,C2*A2)

Thanks, however when it looks for first character to be $ it returns false and therefore multiplies by the wrong exchange rate

 

[ChrisFoster]

Thanks, however when it looks for first character to be $ it returns false and therefore multiplies by the wrong exchange rate

Ah yes, sorry the left function wont work here. Does this one work?

=IF(CELL("format",C2)="$0.00",C2*A1,C2*A2)

 

[mordrid]

Ah yes, sorry the left function wont work here. Does this one work?

=IF(CELL("format",C2)="$0.00",C2*A1,C2*A2)

Thanks for the quick reply but unfortunately it doesnt work either

 

[JoeMo]

Think you will need to resort to a UDF to determine the format. Here's a simple UDF that returns a number greater than 0 if cell A1 is formatted as USD, 0 otherwise.

Function ConvertIt(R As Range)
ConvertIt = InStr(1, R.Text, "$")
End Function

An example:

Book1
	A	B
1	$ 100.36	2
2	100.29 €	0
Sheet1

Cell Formulas
Range	Formula
B1:B2	B1	=ConvertIt(A1)

With the UDF installed you could then use this worksheet formula:

=IF(ConvertIt(A1)> 0,C2*A1,C2*A2)

 

[Fluff]

As far as I know, there is no-way to determine if a cell is formatted as Dollars, or Euros with a formula

 

[JoeMo]

As far as I know, there is no-way to determine if a cell is formatted as Dollars, or Euros with a formula

True for a native worksheet formula. See Post #6 for a simple UDF solution.

 

[ChrisFoster]

What about?

=IFERROR(IF(LEFT(TEXT(C2,"$#,##0.00"),1)="$",C2*A1,C2*A2),C2)

 

[mordrid]

Think you will need to resort to a UDF to determine the format. Here's a simple UDF that returns a number greater than 0 if cell A1 is formatted as USD, 0 otherwise.

Function ConvertIt(R As Range)
ConvertIt = InStr(1, R.Text, "$")
End Function

An example:

Book1
	A	B
1	$ 100.36	2
2	100.29 €	0
Sheet1

Cell Formulas
Range	Formula
B1:B2	B1	=ConvertIt(A1)

With the UDF installed you could then use this worksheet formula:

=IF(ConvertIt(A1)> 0,C2*A1,C2*A2)

Thanks, however, I am not sure how to set this UDF up