Help creating a specific formula

[irishshopper]

I have an excel sales report listing in a single column sales in one of 5 currencies (US$, CA$, AUS$, GBP, Euro). I would like a formula to calculate the GBP equivalent of each sales total and put than in the same row but next column.

In the spreadsheet on the day go to run the report would input the current exchange rate for the 4 non GBP exchange rates against the GBP.

Can a formula or macro be used to do all this ?

Will attach a sample of this but reproduce it below as well.

Thank you.

A B C D
[TABLE="width: 703"]
<colgroup><col><col><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD]Sales record number[/TD]
[TD]Total price[/TD]
[TD]Price in GBP[/TD]
[TD][/TD]
[TD][/TD]
[TD]Conversion Rates on date of report[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6844[/TD]
[TD]£17.50[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD]AU $ to GBP[/TD]
[TD="align: right"]0.60[/TD]
[/TR]
[TR]
[TD="align: right"]6845[/TD]
[TD]£5.99[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD]US $ to GBP[/TD]
[TD="align: right"]0.80[/TD]
[/TR]
[TR]
[TD="align: right"]6846[/TD]
[TD]AU $16.37[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD]CA $ to GBP[/TD]
[TD="align: right"]0.61[/TD]
[/TR]
[TR]
[TD="align: right"]6847[/TD]
[TD]EUR 87.00[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD]EUR to GBP[/TD]
[TD="align: right"]0.85[/TD]
[/TR]
[TR]
[TD="align: right"]6848[/TD]
[TD]£10.99[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6849[/TD]
[TD]£7.99[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6850[/TD]
[TD]£35.50[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6851[/TD]
[TD]£10.99[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6852[/TD]
[TD]US $31.29[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6853[/TD]
[TD]EUR 45.47[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6854[/TD]
[TD]AU $33.91[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6855[/TD]
[TD]AU $214.11[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6856[/TD]
[TD]CA $34.70[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]GB TOTAL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]See spreadsheet above with actual data.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 6"]Need formula to calculate the GBP value of each order and put this in Column C.[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 7"]Each time I run this Report will input in column G the current exchange rates for the 4 foreign currencies.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Hope above is clear.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[oldbrewer]

[TABLE="width: 1214"]
<colgroup><col><col><col><col span="2"><col><col span="10"></colgroup><tbody>[TR]
[TD]Sales record number[/TD]
[TD]Total price[/TD]
[TD]Price in GBP[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]Conversion Rates on date of report[/TD]
[TD][/TD]
[TD="colspan: 2"]CURRTABLE[/TD]
[TD][/TD]
[TD]COL M[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6844[/TD]
[TD="align: right"]£17.50[/TD]
[TD="align: right"]£17.50[/TD]
[TD][/TD]
[TD][/TD]
[TD]AU $ to GBP[/TD]
[TD="align: right"]0.6[/TD]
[TD][/TD]
[TD][/TD]
[TD]£[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD="colspan: 2"]col M is the length[/TD]
[/TR]
[TR]
[TD="align: right"]6845[/TD]
[TD="align: right"]£5.99[/TD]
[TD="align: right"]5.99[/TD]
[TD][/TD]
[TD][/TD]
[TD]US $ to GBP[/TD]
[TD="align: right"]0.8[/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD="align: right"]0.6[/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD="colspan: 2"]of cells in B column[/TD]
[/TR]
[TR]
[TD="align: right"]6846[/TD]
[TD]AU $16.37[/TD]
[TD="align: right"]9.822[/TD]
[TD][/TD]
[TD][/TD]
[TD]CA $ to GBP[/TD]
[TD="align: right"]0.61[/TD]
[TD][/TD]
[TD][/TD]
[TD]C[/TD]
[TD="align: right"]0.61[/TD]
[TD][/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6847[/TD]
[TD]EUR 87.00[/TD]
[TD="align: right"]73.95[/TD]
[TD][/TD]
[TD][/TD]
[TD]EUR to GBP[/TD]
[TD="align: right"]0.85[/TD]
[TD][/TD]
[TD][/TD]
[TD]E[/TD]
[TD="align: right"]0.85[/TD]
[TD][/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6848[/TD]
[TD="align: right"]£10.99[/TD]
[TD="align: right"]10.99[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]U[/TD]
[TD="align: right"]0.8[/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6849[/TD]
[TD="align: right"]£7.99[/TD]
[TD="align: right"]7.99[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6850[/TD]
[TD="align: right"]£35.50[/TD]
[TD="align: right"]35.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6851[/TD]
[TD="align: right"]£10.99[/TD]
[TD="align: right"]10.99[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6852[/TD]
[TD]US $31.29[/TD]
[TD="align: right"]25.032[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6853[/TD]
[TD]EUR 45.47[/TD]
[TD="align: right"]38.6495[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6854[/TD]
[TD]AU $33.91[/TD]
[TD="align: right"]20.346[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6855[/TD]
[TD]AU $214.11[/TD]
[TD="align: right"]8.466[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6856[/TD]
[TD]CA $34.70[/TD]
[TD="align: right"]21.167[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]FORMULA IN C1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 13"]=(IF(ISERROR(VLOOKUP(LEFT(B2,1),CURRTABLE,2)*B2),MID(B2,M2-4,99)*1,VLOOKUP(LEFT(B2,1),CURRTABLE,2)*B2))*VLOOKUP(LEFT(B2,1),CURRTABLE,2)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

Welcome to the MrExcel board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules. Be sure to follow & read the link at the end of the rule too!

Cross posted at: Help creating a specific formula

If you do cross-post in the future and also provide a link, then there shouldn’t be a problem.

 

[irishshopper]

Very many thanks for the prompt answer with this formula to my problem. Much appreciated.

Obviously the formula works but I am keen to learn what the different parts of the formula do. Can you talk me through the steps of the formul so I can learn from this for the future.

Thank you.

 

[oldbrewer]

currtable is a lookup table - I took the first character of the currency, arranged them in alphabetical order and then the lookup formula finds the matching conversion factor and returns column 2
VLOOKUP(LEFT(B2,1),CURRTABLE,2) (left(b2,1) is the first character)

*B2 is multiply by original currency amount

sometimes excel returns errors if(iserror(a formula here) makes excel return what you want

as the currency can be 1,2 or 3 characters long and the amount could be 9.99 or even 999999.99 I used LEN to count all the characters
and MID(B2,M2-4,99) merely subtracts 4 from the length and 99 is any number to force all characters from that point to the end

in fact in this form it would fail if amount were 999.99 so a bit more work is needed...

[TABLE="class: cms_table, width: 1214"]
<tbody>[TR]
[/TR]
[TR]
[TD="colspan: 13"]further questions please come back on it[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[irishshopper]

Thank you for this.

Can anyone out there make the necessary alterations to this to make it work correctly for all invoice amounts bearing in mind the comment from oldbrewer below

in fact in this form it would fail if amount were 999.99 so a bit more work is needed...

 

[oldbrewer]

[TABLE="width: 1177"]
<colgroup><col><col><col span="2"><col><col><col span="10"></colgroup><tbody>[TR]
[TD="align: right"]6844[/TD]
[TD]UK £17.50[/TD]
[TD]UK[/TD]
[TD="align: right"]1[/TD]
[TD]17.50[/TD]
[TD="align: right"]17.50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]AU[/TD]
[TD="align: right"]0.6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6845[/TD]
[TD]UK £5.99[/TD]
[TD]UK[/TD]
[TD="align: right"]1[/TD]
[TD]5.99[/TD]
[TD="align: right"]5.99[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]CA[/TD]
[TD="align: right"]0.61[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6846[/TD]
[TD]AU $16.37[/TD]
[TD]AU[/TD]
[TD="align: right"]0.6[/TD]
[TD]16.37[/TD]
[TD="align: right"]9.82[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]mytable > > > > > > > > >[/TD]
[TD]EU[/TD]
[TD="align: right"]0.85[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6847[/TD]
[TD]EUR 87.00[/TD]
[TD]EU[/TD]
[TD="align: right"]0.85[/TD]
[TD]87.00[/TD]
[TD="align: right"]73.95[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]UK[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6848[/TD]
[TD]UK £10.99[/TD]
[TD]UK[/TD]
[TD="align: right"]1[/TD]
[TD]10.99[/TD]
[TD="align: right"]10.99[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]US[/TD]
[TD="align: right"]0.8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6849[/TD]
[TD]UK £7.99[/TD]
[TD]UK[/TD]
[TD="align: right"]1[/TD]
[TD]7.99[/TD]
[TD="align: right"]7.99[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6850[/TD]
[TD]UK £35.5[/TD]
[TD]UK[/TD]
[TD="align: right"]1[/TD]
[TD]35.5[/TD]
[TD="align: right"]35.50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6851[/TD]
[TD]UK £10.99[/TD]
[TD]UK[/TD]
[TD="align: right"]1[/TD]
[TD]10.99[/TD]
[TD="align: right"]10.99[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6852[/TD]
[TD]US $831.29[/TD]
[TD]US[/TD]
[TD="align: right"]0.8[/TD]
[TD]831.29[/TD]
[TD="align: right"]665.03[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6853[/TD]
[TD]EUR 45.47[/TD]
[TD]EU[/TD]
[TD="align: right"]0.85[/TD]
[TD]45.47[/TD]
[TD="align: right"]38.65[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6854[/TD]
[TD]AU $33.91[/TD]
[TD]AU[/TD]
[TD="align: right"]0.6[/TD]
[TD]33.91[/TD]
[TD="align: right"]20.35[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6855[/TD]
[TD]AU $9214.11[/TD]
[TD]AU[/TD]
[TD="align: right"]0.6[/TD]
[TD]9214.11[/TD]
[TD="align: right"]5528.47[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6856[/TD]
[TD]CA $98734.70[/TD]
[TD]CA[/TD]
[TD="align: right"]0.61[/TD]
[TD]98734.70[/TD]
[TD="align: right"]60228.17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]col B[/TD]
[TD]col C[/TD]
[TD]col D[/TD]
[TD]col E[/TD]
[TD]col F[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]all the other currencies were text[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]so I changed £ to UK £[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]making it text[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 4"]now the first 2 characters are referred to the look up table[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]and the conversion factor is returned in col D[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 4"]col E is characters 5 onwards from column B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]col F is col D multiplied by col E[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 4"]note it now deals with any size original amounts[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]