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

Retention up to date @ 4%		pay without retention		Retention value		Pay with retention
£306.72		£600.00		£24.00		£576.00
		£952.00		£38.08		£913.92
		£877.00		£35.08		£841.92
		£688.00		£27.52		£660.48
		£999.00		£39.96		£959.04
		£999.00		£39.96		£959.04
		£999.00		£39.96		£959.04
		£999.00		£39.96		£959.04
		£555.00		£22.20		£532.80

<tbody>
</tbody>

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.

Retention up to date @ 4%		pay without retention		Retention value		Pay with retention	Running Retention
£400		£600.00		£24.00		£576.00	£24.00
		£952.00		£38.08		£913.92	£62.08
		£877.00		£35.08		£841.92	£97.16
		£688.00		£27.52		£660.48	£124.68
		£999.00		£39.96		£959.04	£164.64
		£999.00		£39.96		£959.04	£204.60
		£999.00		£39.96		£959.04	£244.56
		£999.00		£39.96		£959.04	£284.52
		£555.00		£22.20		£532.80	£306.72
		£555.00		£22.20		£532.80	£328.92
		£555.00		£22.20		£532.80	£351.12
		£555.00		£22.20		£532.80	£373.32
		£555.00		£22.20		£532.80	£395.52
		£555.00		£22.20		£555.00	£417.72

<colgroup><col span="7"><col></colgroup><tbody>
</tbody>

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

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

=E2

=E3+H2

=E4+H3 and so on

<colgroup><col width="117" style="width:88pt"> </colgroup><tbody>
</tbody>

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

hope this helps (and makes sense)