How to stop a calculation from a cell if the max is 400

[RubenC]

Hi guys,

Im working on some sort of a system for my brothers company and i am a bit stuck on something.
This is on the payrole system.

So..

Ive got a cell that calculates the salary but my brothers company is doing something called retention. What this means is that 4% of the wages gows to a "pot" with the maximum of £400. When it gets to £400 the driver can ask for that money any time in case of emergency.
Now..

Ive done the calculations with the percenteges. but im struggling with the if condition that i need to make on the calculation bit.

Example

[TABLE="width: 743"]
<tbody>[TR]
[TD="width: 175"]Retention up to date @ 4%[/TD]
[TD="width: 64"][/TD]
[TD="width: 145"]pay without retention[/TD]
[TD="width: 64"][/TD]
[TD="width: 107"]Retention value[/TD]
[TD="width: 64"][/TD]
[TD="width: 124"]Pay with retention[/TD]
[/TR]
[TR]
[TD="align: right"]£306.72[/TD]
[TD][/TD]
[TD="align: right"]£600.00[/TD]
[TD][/TD]
[TD="align: right"]£24.00[/TD]
[TD][/TD]
[TD="align: right"]£576.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]£952.00[/TD]
[TD][/TD]
[TD="align: right"]£38.08[/TD]
[TD][/TD]
[TD="align: right"]£913.92[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]£877.00[/TD]
[TD][/TD]
[TD="align: right"]£35.08[/TD]
[TD][/TD]
[TD="align: right"]£841.92[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]£688.00[/TD]
[TD][/TD]
[TD="align: right"]£27.52[/TD]
[TD][/TD]
[TD="align: right"]£660.48[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]£999.00[/TD]
[TD][/TD]
[TD="align: right"]£39.96[/TD]
[TD][/TD]
[TD="align: right"]£959.04[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]£999.00[/TD]
[TD][/TD]
[TD="align: right"]£39.96[/TD]
[TD][/TD]
[TD="align: right"]£959.04[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]£999.00[/TD]
[TD][/TD]
[TD="align: right"]£39.96[/TD]
[TD][/TD]
[TD="align: right"]£959.04[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]£999.00[/TD]
[TD][/TD]
[TD="align: right"]£39.96[/TD]
[TD][/TD]
[TD="align: right"]£959.04[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]£555.00[/TD]
[TD][/TD]
[TD="align: right"]£22.20[/TD]
[TD][/TD]
[TD="align: right"]£532.80[/TD]
[/TR]
</tbody>[/TABLE]

The idea is to get a formula that allows me to stop any deductions from the pay when the retention up to date gets to £400.

I really appreciate your time for this.

Thanks guys.

 

[asjmoron]

This may not be the most elegant of solutions but...

I added a helper column that takes the first retention payment and from the second entry down, adds it to the previous amount to give a running total.

[TABLE="width: 944"]
<colgroup><col span="7"><col></colgroup><tbody>[TR]
[TD]Retention up to date @ 4%[/TD]
[TD] [/TD]
[TD]pay without retention[/TD]
[TD] [/TD]
[TD]Retention value[/TD]
[TD] [/TD]
[TD]Pay with retention[/TD]
[TD]Running Retention[/TD]
[/TR]
[TR]
[TD]£400[/TD]
[TD] [/TD]
[TD]£600.00[/TD]
[TD] [/TD]
[TD]£24.00[/TD]
[TD] [/TD]
[TD]£576.00[/TD]
[TD]£24.00[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]£952.00[/TD]
[TD] [/TD]
[TD]£38.08[/TD]
[TD] [/TD]
[TD]£913.92[/TD]
[TD]£62.08[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]£877.00[/TD]
[TD] [/TD]
[TD]£35.08[/TD]
[TD] [/TD]
[TD]£841.92[/TD]
[TD]£97.16[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]£688.00[/TD]
[TD] [/TD]
[TD]£27.52[/TD]
[TD] [/TD]
[TD]£660.48[/TD]
[TD]£124.68[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]£999.00[/TD]
[TD] [/TD]
[TD]£39.96[/TD]
[TD] [/TD]
[TD]£959.04[/TD]
[TD]£164.64[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]£999.00[/TD]
[TD] [/TD]
[TD]£39.96[/TD]
[TD] [/TD]
[TD]£959.04[/TD]
[TD]£204.60[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]£999.00[/TD]
[TD] [/TD]
[TD]£39.96[/TD]
[TD] [/TD]
[TD]£959.04[/TD]
[TD]£244.56[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]£999.00[/TD]
[TD] [/TD]
[TD]£39.96[/TD]
[TD] [/TD]
[TD]£959.04[/TD]
[TD]£284.52[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]£555.00[/TD]
[TD] [/TD]
[TD]£22.20[/TD]
[TD] [/TD]
[TD]£532.80[/TD]
[TD]£306.72[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]£555.00[/TD]
[TD] [/TD]
[TD]£22.20[/TD]
[TD] [/TD]
[TD]£532.80[/TD]
[TD]£328.92[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]£555.00[/TD]
[TD] [/TD]
[TD]£22.20[/TD]
[TD] [/TD]
[TD]£532.80[/TD]
[TD]£351.12[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]£555.00[/TD]
[TD] [/TD]
[TD]£22.20[/TD]
[TD] [/TD]
[TD]£532.80[/TD]
[TD]£373.32[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]£555.00[/TD]
[TD] [/TD]
[TD]£22.20[/TD]
[TD] [/TD]
[TD]£532.80[/TD]
[TD]£395.52[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]£555.00[/TD]
[TD] [/TD]
[TD]£22.20[/TD]
[TD] [/TD]
[TD]£555.00[/TD]
[TD]£417.72[/TD]
[/TR]
</tbody>[/TABLE]


After this I added an if to the PAY WITH RETENTION.

so in the helper column (I started the whole thing at a1 btw)

[TABLE="width: 117"]
<colgroup><col width="117" style="width:88pt"> </colgroup><tbody>[TR]
[TD="class: xl66, width: 117"]=E2[/TD]
[/TR]
[TR]
[TD="class: xl66"]=E3+H2[/TD]
[/TR]
[TR]
[TD="class: xl66"]=E4+H3

and so on
[/TD]
[/TR]
</tbody>[/TABLE]

Then in the Pay with Retention column I used =IF(H2<=400,C2-E2,C2)

hope this helps (and makes sense)