convert to GBP - formula to identify 1st character and Acc type

J

Johnny

I have data in 16 columns with the header in the first row in Excel. The
information is ODBC from Access. Currently there are about 45,000 rows of
information.

Col A has the account no. - for UK accounts it consist of 5 digits (e.g.
02008, 18001 etc.), for Euro A/C there is the letter E (e.g. E02008, E23005
etc.) and for Dollar A/C there is the letter D (e.g. D02008, D25005 etc.)
Col G shows the Qty Ordered, Column H the selling price according to the
account type (£, Euro or $)

Is there any way that I can write a formula to give me the Conversion price
in Col 17 as £ Sterling whether it is a (£, Euro or $) account type? I don’t
mind putting in a fix conversion rate for Euro and dollar

The formula should be able to identify the account type and convert the
selling price into £ Sterling and multiply the qty to give a total price in £
sterling
Sample of Data:
Acc No Qty Ord Price Total Price Conv Price
02008 150 0.58 87.00 87.00
E42006 60 0.44 26.40 20.96 (1 Euro = GBP 0.7940)
D01023 70 0.30 21.00 10.58 (1USD = GBP 0.5039)
Any help will be appreciated
Thank you
 
S

Stefi

Create a column, say G for change rates:
G
Rates
1 GBP = GBP 1
1 Euro = GBP 0.7940
1USD = GBP 0.5039

and enter this formula in E2 and copy down as necessary!
=D2*CHOOSE(IF(LEFT(A2)="E",2,IF(LEFT(A2)="D",3,1)),$G$2,$G$3,$G$4)

Regards,
Stefi


„Johnny†ezt írta:
 
Top