If function nested maybe XL97

[Tigercub]

I need to have a function that works out retention from each progress claim when the retention is deducted at rate of 10% of contract value until 5% of the contract value is reached with the relevant progress claim showing any balance retention figure up to the Max claimable 5%. The formula I have been trying to sort out is listed where

F29 - the total of all progress claim invoiced at that time
G9 - 10%
L10 - total contract value to work out max 5%
G8 - 5%
F14 - current progress claim amount to have the formula work on

=IF($F$29*$G$9<=$L$10*$G$8,F14*$G$9,IF($F$29*$G$9>$L$10*$G$8,F14*$G$8))

Any help is greatly appreciated.

 

[Tigercub]

Can anybody help with this request please!

 

[konew1]

I need to have a function that works out retention from each progress claim when the retention is deducted at rate of 10% of contract value until 5% of the contract value is reached with the relevant progress claim showing any balance retention figure up to the Max claimable 5%. The formula I have been trying to sort out is listed where

F29 - the total of all progress claim invoiced at that time
G9 - 10%
L10 - total contract value to work out max 5%
G8 - 5%
F14 - current progress claim amount to have the formula work on

=IF($F$29*$G$9<=$L$10*$G$8,F14*$G$9,IF($F$29*$G$9>$L$10*$G$8,F14*$G$8))

Any help is greatly appreciated.

Why do you have the embedded IF? You can removing it leaving only the result if true. What is wrong with the results you generate, what does it give and what is the correct result?

 

[Tigercub]

That formula,which is mostly worked out against multiple invoices, has to reduce the amount at the rate of 10% until 5% of the contract is reached. Currently the formula only works out the 10% but does not reduce the relevant invoice when the 5% amount is reached. It continues to work out the 10% reduction. That's the bit I cannot get to work.