Formula HELP!!?

[ChrisK1994]

[TABLE="width: 500"]
<tbody>[TR]
[TD]remaining Old Contract Credits[/TD]
[TD]10[/TD]
[TD]Price[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]New Contract Credits[/TD]
[TD]50[/TD]
[TD]Price[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]Out of contract (no credits)[/TD]
[TD][/TD]
[TD]Price[/TD]
[TD]35[/TD]
[/TR]
[TR]
[TD]Remaining Credits[/TD]
[TD]60[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Order[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]order 1 number of books[/TD]
[TD]order 2 number of books[/TD]
[TD]order 3 number of books[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Book 1[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]book 2[/TD]
[TD]3[/TD]
[TD]40[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]book 3[/TD]
[TD]4[/TD]
[TD]10[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]invoice[/TD]
[TD]Formula needed[/TD]
[TD]Formula needed[/TD]
[TD]Formula needed[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

so for example here we have 3 books we sell customers agree an annual amount of books they will buy (credits) upfront with contracts and get invoiced instantly £10 per credit ordered in that contract. the remaining cost is invoiced per order.

in this example a customer has 10 credits left on his old contract. his old contract was £30 per book. he has now signed a new contract for 50 more credits at £25. (any orders with no credits left is £35)

now i have a table showing 3 separate orders what formula can i use to invoice each order correctly using the price for the remaining credits first and then using the new contract price after that and if no credits left charging the out of contract price.

i tried an IF formula so if Remaining credits is greater than new contract credits then charge old price - 10 (-10 as they pay £10 up front when signing contract) if remaining credits is less than or equal to the new contract credits then charge the new contract price and if remaining credits is less than or equal to 0 charge out of contract price.

but if one order contained both pricing it couldn't work it out so in the example order 2 should take into account all 3 pricing

 

[ChrisK1994]

???