Sliding Retention calculation

[Chris2579]

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>

 

[Eric W]

Try:

	A	B	C	D	E	F	G	H	I	J	K
1		Invoice Amount	$ 900,000		Low Range	High Range	Percent	Delta percent
					$ -	$ 200,000	10%	10%		$ 20,000
3		Retention Value	$ 52,000		$ 200,000	$ 800,000	5%	-5%		$ 30,000
4					$ 800,000		2%	-3%		$ 2,000

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula 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.