Help with a formula

[tonywatsonhelp]

Hi Everyone,
I'm having a bit of trouble getting this formula to work

I have
Column I = Total Amount in local Currency
Column L = Total Amount in GBP

Column J = Amount Still Owed in Local Currency

Column AH = Amount already paid in GBP (this is the sum i want.)

Now because exchange rates change I need to somehow work out the amount from the figurse i have

I'll do a little chart to show what i'm on about!

row	Column I	Column J		Column L		Column AH
10	Total In Euros (For example)	Amount Still Owed in Euros		Total in GBP		Amount Already Paid GBP		so to make this nice and clear
11	1000	500		900		this is what i need??		The Amount Already Paid in Local Currency is I - J
12	2000	200		1800				But I need it in GBP?
13	3000	2500		2700
14

 

[Special-K99]

As exchange rates change on a daily basis you'll need a history of the exchange rates not just the amount in euros and equivalent in GBP

 

[tonywatsonhelp]

Hi Special K,
No please dont think about it like its exchange rates,
Just different figures,

I can almoost see how to do it in my head but cant quite get it to work out

For example Total amount total 1000 = total amount 500 then
amount paid 500 = amount paid 250

So the fact that we have the two Totals, means we have a ratio we can apply to the amount paid, giving me the amount remaining?
im just not sure how to get the ratio?

 

[Peter_SSs]

Like this?

21 08 05.xlsm
	I	J	K	L																					AG	AH
10	Total In Euros (For example)	Amount Still Owed in Euros		Total in GBP																						Amount Already Paid GBP
11	1000	500		900																						450
12	2000	200		1800																						1620
13	3000	2500		2700																						450
Amount Paid

Cell Formulas
Range	Formula
AH11:AH13	AH11	=(I11-J11)/I11*L11

 

[tonywatsonhelp]

Thank you peter
This looks perfect
Thanks
Tony

 

[Peter_SSs]

Cheers