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]

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

This works for me.

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

 

[mordrid]

What about?

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

Thanks this picks up all values whether dollar or euro and calculates using the dollar exchange rate so assumes there are no false ones

 

[JoeMo]

What about?

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

Doesn't work if C2 is negative.

 

[JoeMo]

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

To install the UDF:
1. With your workbook active press Alt and F11 keys. This will open the VBE window.
2. In the project tree on the left of the VBE window, find your project and click on it.
3. On the VBE menu: Insert>Module
4. Copy the UDF from your browser window and paste it into the white space in the VBE window.
5. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
6. Use the UDF as you would any native Excel function.
7. Make sure you have enabled macros whenever you open the file or the code will not run.

 

[Fluff]

This works for me.

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

That will always be True as you are specifying a $ format in the Text function.

 

[mordrid]

To install the UDF:
1. With your workbook active press Alt and F11 keys. This will open the VBE window.
2. In the project tree on the left of the VBE window, find your project and click on it.
3. On the VBE menu: Insert>Module
4. Copy the UDF from your browser window and paste it into the white space in the VBE window.
5. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
6. Use the UDF as you would any native Excel function.
7. Make sure you have enabled macros whenever you open the file or the code will not run.

Thank you it appears to work so thats great

 

[JoeMo]

Thank you it appears to work so thats great

You are welcome- thanks for the reply.