Formula Help

[scowan]

Hi any help on the below problem would be appreciated, alternatively if it's been answered elsewhere please direct me.....
[TABLE="width: 500"]
<tbody>[TR]
[TD]Month[/TD]
[TD]Revenue LC[/TD]
[TD]Revenue GBP[/TD]
[TD]Invoice LC[/TD]
[TD]Invoice GBP[/TD]
[TD]FX Rate[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]2,000[/TD]
[TD]1,333.33[/TD]
[TD]1.5[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1.75[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD]300[/TD]
[TD]150[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2,100[/TD]
[TD]????[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1.25[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]300[/TD]
[TD]????[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1.75[/TD]
[/TR]
</tbody>[/TABLE]

I need a formula in the Revenue GBP column that, if there is Revenue, is able to to calculate the GBP value relating to outstanding invoices to date GBP amounts and then if there is any revenue remaining calculate this at the GBP rate for the current month.

In the above table Revenue GBP month 4 should be £1,383.33 (2,000 / 1.5 and 100 / 2) and then month 5 should be 157.14 (200 / 2 and 100 / 1.75). I can give it a go with a lot of nested if formulas but was hoping there is a cleaner way as this will be for 1,000s of contracts over several months.

 

[Johnny Thunder]

This detail is hard to follow since you reference numbers that don't correspond to your data...

"In the above table Revenue GBP month 4 should be £1,383.33 (2,000 / 1.5 and 100 / 2) and then month 5 should be 157.14 (200 / 2 and 100 / 1.75). I can give it a go with a lot of nested if formulas but was hoping there is a cleaner way as this will be for 1,000s of contracts over several months.[/QUOTE]
"

Hi any help on the below problem would be appreciated, alternatively if it's been answered elsewhere please direct me.....
[TABLE="width: 500"]
<tbody>[TR]
[TD]Month[/TD]
[TD]Revenue LC[/TD]
[TD]Revenue GBP[/TD]
[TD]Invoice LC[/TD]
[TD]Invoice GBP[/TD]
[TD]FX Rate[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]2,000[/TD]
[TD]1,333.33[/TD]
[TD]1.5[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1.75[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD]300[/TD]
[TD]150[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2,100[/TD]
[TD]????[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1.25[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]300[/TD]
[TD]????[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1.75[/TD]
[/TR]
</tbody>[/TABLE]

I need a formula in the Revenue GBP column that, if there is Revenue, is able to to calculate the GBP value relating to outstanding invoices to date GBP amounts and then if there is any revenue remaining calculate this at the GBP rate for the current month.

In the above table Revenue GBP month 4 should be £1,383.33 (2,000 / 1.5 and 100 / 2) and then month 5 should be 157.14 (200 / 2 and 100 / 1.75). I can give it a go with a lot of nested if formulas but was hoping there is a cleaner way as this will be for 1,000s of contracts over several months.