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

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 dont
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

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

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

Johnny wrote:
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 dont
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

--
Message posted via http://www.officekb.com

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

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
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 dont
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