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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Johnny - 22 May 2008 14:45 GMT

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

Reply to this Message

edvwvw - 22 May 2008 15:17 GMT

This is one way that you could try:

=IF(LEFT(A2,1)="E",0.794,IF(LEFT(A2,1)="D",0.5039))*1

Where the account number is in A2. This will give you the required conversion
rate for Euros and Dollars, the *1 returns instead of FALSE so you can
"convert" for Sterling.

Drag the formula down.

If the conversion rate changes frequently then you could reference a cell for
the rate rather than update the figure in the formula

edvwvw

>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
[quoted text clipped - 20 lines]
>Any help will be appreciated
>Thank you

Reply to this Message

Stefi - 22 May 2008 15:27 GMT

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:

> 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
[quoted text clipped - 20 lines]
> Any help will be appreciated
> Thank you

Reply to this Message