# Currency conversion

#### dunlopoil

##### Board Regular
Hi,
I am working on a sheet that logs sales enquiries and quotations. The quotations may be made in either Dollars, Euros or Pounds. However, I want the 'reporting' column to be Pounds. I would appreciate any advice on the simplest way of achieving this.

Imagine column A is dollars, column B is Euros and column C is pounds. Most of the time the quotations are done in pounds and the other two columns will be blank. Also, there will only ever be one figure quoted per row. Would it be easier to create a fourth column (D) to consolidate the three separate figures together? We are using fixed exchange rates for the year, so to convert to pounds we will multiply any dollar quotations by 0.5 and any euro quotations will be multiplied by 0.69.

Many thanks,
Doug.

Last edited:

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

#### Richard Schollar

##### MrExcel MVP
Hi Doug

You could use a standard IF statement to do this or if you may extend the different currencies you use a more scaleable option could be:

=LOOKUP(2,1/(\$A2:\$C2<>""),{0.5,0.69,1})*SUM(\$A2:\$C2)

#### dunlopoil

##### Board Regular
Hi Doug

You could use a standard IF statement to do this or if you may extend the different currencies you use a more scaleable option could be:

=LOOKUP(2,1/(\$A2:\$C2<>""),{0.5,0.69,1})*SUM(\$A2:\$C2)

Thanks Richard- that option is ideal.
Cheers, Doug.

#### dunlopoil

##### Board Regular
The only additional thing that might be good is to be able to ensure there is only one currency quoted. Is it possible to prevent entry into cells B2 and C2 if there is an entry in A2, for example?

#### Richard Schollar

##### MrExcel MVP
Sure - select your range (A2:C10) say and go Data>Validation>Settings tab and under the Allow dropdown choose Custom. Then write in the following formula:

=COUNT(\$A2:\$C2)=1

and make sure "Ignore blanks" is checked.

This will limit you to 1 numeric value per row (columns A:C).

Great, thanks.

#### dunlopoil

##### Board Regular
Richard,
As I said, the solution you recommended works well. However, I am struggling a bit with how the formula is constructed. If you can spare a bit of time to briefly explain this I would be very grateful.
Many thanks,
Doug.

#### Richard Schollar

##### MrExcel MVP
Sure.

=LOOKUP(2,1/(\$A2:\$C2<>""),{0.5,0.69,1})*SUM(\$A2:\$C2)

The SUM bit on the end is the easy part - we know there's only going to be one value in the 3 columns, so we can sum them all together (the 2 blank cells will be ignored and won't affect the Sum).

The middle part of the Lookup is where the work is done:

1/(\$A2:\$C2<>"")

The Denominator part is returning an array (or list) of True and Falses dependent on whether the 3 cells (A2:C2) are not empty (returns True) or are empty (returns False). The Numerator is 1 so that you get a resultant list of 1/True or False for each value in the A2:C2 range. The clever bit is that 1/True = 1 but 1/False = #DIV/0! (ie an error).

So assuming that the currency is in B2 we get a list returned that looks like:

{#DIV/0!,1,#DIV/0!}

The Lookup value 2 is then looked up against this list. Lookup assumes a sorted ascending list and, crucially, ignores error values. Thus lookup will lookup the last value in the list that is less than or equal to the lookup value. Since our list only contains a 1 and error values it always returns the position (column 1, 2 or 3) where our currency value is. This position (in our case position 2 representing column B) is then used to return the exchange rate we have in:

{0.5,0.69,1}

position 2 being 0.69.

This then gets multiplied gainst the sum of A:C hence you get your sterling value.

Make sense?

#### dunlopoil

##### Board Regular
Richard,
Just one thing- could you please explain what the 2 in the part of the formula =LOOKUP(2,1/(\$A2:\$C2<>"") refers to?
Thanks,
Doug.

Replies
2
Views
441
Replies
2
Views
471
Replies
2
Views
393
Replies
1
Views
531
Replies
8
Views
2K

1,191,007
Messages
5,984,129
Members
439,872
Latest member
ExcelRM

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back