Sliding Retention calculation

Chris2579

New Member
Joined
Apr 18, 2019
Messages
1
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

ABCD
2Invoice Amount$900,000
3
4
<​
$200,00010%$20,000
5
>$200,000 but <$800,000​
$800,0005%$30,000
6
>​
$800,0002%$2,000
7RetentionTotal$52,000
8Formula 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>
 

Some videos you may like

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
Joined
Aug 18, 2015
Messages
9,774
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,540
Messages
5,469,295
Members
406,647
Latest member
ssinovec

This Week's Hot Topics

Top