# Sliding Retention calculation

#### Chris2579

##### New Member
Hi

I'm trying to create the following formula for a sliding retention calculation based on an invoice amount as follows:-

1. If the current amount invoiced is less than \$200,000 retention value is 10%
2. If the current amount invoiced is between \$200,000 and \$800,000 the retention value is \$200,000 @ 10% and the balance at 5%
3. If the current amount invoiced is over \$800,000 the retention value is \$200,000 @ 10% and \$600,000 5% and the balance at 2%

For example

 A B C D 2 Invoice Amount \$900,000 3 4 <​ \$200,000 10% \$20,000 5 >\$200,000 but <\$800,000​ \$800,000 5% \$30,000 6 >​ \$800,000 2% \$2,000 7 Retention Total \$52,000 8 Formula Answer \$57,000

<tbody>
</tbody>

IF((C2)<=B4,C2*C4,IF(AND((C2)>B4,(C2)<=B5),(B4*C4)+(C2-B4)*C5,IF((C2)>B6,((B4*C4)+((C2-B4)*C5)+(C2-B6)*C6))))

Formula above is sort of there works for anything under \$800,000 but over \$800,000 doesn't give the right answer

Any Help much appreciated

Thanks

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

### Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

#### Eric W

##### MrExcel MVP
Try:

ABCDEFGHIJK
1Invoice Amount \$ 900,000 Low Range High RangePercentDelta percent
\$ -\$ 200,00010%10%\$ 20,000
3Retention Value \$ 52,000 \$ 200,000 \$ 800,0005%-5% \$ 30,000
4 \$ 800,0002%-3% \$ 2,000

<tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
C3=SUMPRODUCT(C1-E2:E4,--(C1>E2:E4),H2:H4)
H2=G2-N(G1)
H3=G3-N(G2)
H4=G4-N(G3)
J2=MIN(C1,F2)*G2
J3=MEDIAN(C1-F2,F3-F2,0)*G3
J4=MAX(0,C1-F3)*G4

<tbody>
</tbody>

<tbody>
</tbody>

Build your rate table in E1:H4. Columns E:G should be obvious, add column H, and put the H2 formula in and copy down. Then the C3 formula should give you the answer you want. The J2:J4 formulas I only put in as a check, but you can just sum them up, or combine them in a single formula if you want, and you won't need the H column.

Let us know if this works for you.