Max value of percentage of figure formulas

**chrisi** · 08-24-2015, 02:51 AM

Hi,

I need help with a formula for a claim sheet for contractors. As with my industry, we have to retain a percentage of a contract value over each claim to a maximum value. For example, if a contract is for $10,000, we would retain 5% of this for 12 months to allow for defective works over this time. The contractor would make several claims over this time against the contract value, for example:

Claim 01 - 10% of Contract Value = $1,000
Claim 02 - 40% of Contract value = $4,000-Claim 01 = $3,000
Claim 03 - 70% of Contract value = $7,000-Claim 01+Claim 02 = $3,000
Claim 04 - 10% of Contract value = $10,000-Claim 01+Claim 02+Claim 03 = $3,000

The total amount to be retained (retention) is 5% of the contract value = $500. This is the max to retain.

The problem comes as we have to hold 10% of each claim until the 5% of the contract value is met.

So:
Claim 01 = $100
Claim 02 = $300
Claim 03 = $100 (As the 5% has been met)
Claim 04 = $0

My problem is generating a formula to achieve this.

**Glenn Kennedy** · 08-24-2015, 03:05 AM

Why, at claim 1, is 10% of the contract value $1000 and at claim 4, 10% is $10,000?

**zbor** · 08-24-2015, 03:42 AM

I belive it's typo and should be 100%

**Glenn Kennedy** · 08-24-2015, 04:11 AM

hi there try this... There's a hidden row at line 3 (I need a blank cell above the first amount retained).

**chrisi** · 08-24-2015, 08:30 PM

Formula worked a treat! Thanks so much Glenn.

**Glenn Kennedy** · 08-25-2015, 02:01 AM

Glad to have helped! If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. It'd also be appreciated if you were to click the add Reputation button at the foot of any of the posts of those who helped you reach a solution.