Currency conversion

dunlopoil

Board Regular
Joined
May 29, 2008
Messages
92
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

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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)
 
Upvote 0
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?
 
Upvote 0
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).
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,144
Members
448,552
Latest member
WORKINGWITHNOLEADER

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top