If Statement Calculations

[DShack]

Hello All,

I am trying to build a If formula that will calculate fees based off percentages. Below is monthly revenue generated by different lines of business. If the sum of the months are less than a 1 million dollars than the monthly revenue gets calculated at 7%. Once the revenue goes above 1 million dollars than it gets calculated at 5%. As you can see below my numbers go negative. If you take line 5 (348300) for instance. The first month gets calced at 7%, the same with Feb however in March 44900 of the 348300 is over 1 Million so the 44900 gets calced at 5% and the 303400 gets calced at 7%. My current formula works for the first couple months but fails me towards the end of the year.

Here is what i have:

[TABLE="width: 907"]
<tbody>[TR]
[TD="class: xl67, width: 93"]Jan '18[/TD]
[TD="class: xl67, width: 74"]Feb '18[/TD]
[TD="class: xl67, width: 74"]Mar '18[/TD]
[TD="class: xl67, width: 74"]Apr '18[/TD]
[TD="class: xl67, width: 74"]May '18[/TD]
[TD="class: xl67, width: 74"]Jun '18[/TD]
[TD="class: xl67, width: 74"]Jul '18 [/TD]
[TD="class: xl67, width: 74"]Aug '18[/TD]
[TD="class: xl67, width: 74"]Sept '18 [/TD]
[TD="class: xl67, width: 74"]Oct '18[/TD]
[TD="class: xl67, width: 74"]Nov '18[/TD]
[TD="class: xl68, width: 74"]Dec '18[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 907"]
<colgroup><col><col span="11"></colgroup><tbody>[TR]
[TD] 201,575[/TD]
[TD] 201,575[/TD]
[TD] 201,575[/TD]
[TD] 195,689[/TD]
[TD] 204,584[/TD]
[TD] 204,584[/TD]
[TD] 195,689[/TD]
[TD] 192,131[/TD]
[TD] 201,575[/TD]
[TD] 201,575[/TD]
[TD] 201,575[/TD]
[TD] 201,575[/TD]
[/TR]
[TR]
[TD] 144,344[/TD]
[TD] 144,344[/TD]
[TD] 144,344[/TD]
[TD] 140,129[/TD]
[TD] 146,498[/TD]
[TD] 146,498[/TD]
[TD] 140,129[/TD]
[TD] 137,581[/TD]
[TD] 144,344[/TD]
[TD] 144,344[/TD]
[TD] 144,344[/TD]
[TD] 144,344[/TD]
[/TR]
[TR]
[TD] 108,847[/TD]
[TD] 108,847[/TD]
[TD] 108,847[/TD]
[TD] 105,669[/TD]
[TD] 110,472[/TD]
[TD] 110,472[/TD]
[TD] 105,669[/TD]
[TD] 103,748[/TD]
[TD] 108,847[/TD]
[TD] 108,847[/TD]
[TD] 108,847[/TD]
[TD] 108,847[/TD]
[/TR]
[TR]
[TD] 163,741[/TD]
[TD] 163,741[/TD]
[TD] 163,741[/TD]
[TD] 158,960[/TD]
[TD] 166,185[/TD]
[TD] 166,185[/TD]
[TD] 158,960[/TD]
[TD] 156,070[/TD]
[TD] 163,741[/TD]
[TD] 163,741[/TD]
[TD] 163,741[/TD]
[TD] 163,741[/TD]
[/TR]
[TR]
[TD] 348,300[/TD]
[TD] 348,300[/TD]
[TD] 348,300[/TD]
[TD] 338,129[/TD]
[TD] 353,499[/TD]
[TD] 353,499[/TD]
[TD] 338,129[/TD]
[TD] 331,981[/TD]
[TD] 348,300[/TD]
[TD] 348,300[/TD]
[TD] 348,300[/TD]
[TD] 348,300[/TD]
[/TR]
[TR]
[TD] 133,833[/TD]
[TD] 133,833[/TD]
[TD] 133,833[/TD]
[TD] 129,925[/TD]
[TD] 135,831[/TD]
[TD] 135,831[/TD]
[TD] 129,925[/TD]
[TD] 127,563[/TD]
[TD] 133,833[/TD]
[TD] 133,833[/TD]
[TD] 133,833[/TD]
[TD] 133,833[/TD]
[/TR]
</tbody>[/TABLE]


This is my current formula with its out put.
These amounts are in data cells for reference: 7%, 5%, 1,000,000
=IF(SUM(Frachisee!$D4:J4)>Revenue!$F$167,((SUM(Frachisee!$D4:J4)-1000000)*$E$167)+((Frachisee!J4-((SUM(Frachisee!$D4:J4))-1000000))*Revenue!$D$167),Frachisee!J4*Revenue!$D$167)


[TABLE="width: 941"]
<colgroup><col><col><col span="10"></colgroup><tbody>[TR]
[TD] 14,110[/TD]
[TD] 14,110[/TD]
[TD] 14,110[/TD]
[TD] 13,698[/TD]
[TD] 14,221[/TD]
[TD] 10,129[/TD]
[TD] 5,593[/TD]
[TD] 1,501[/TD]
[TD] (1,869)[/TD]
[TD] (5,901)[/TD]
[TD] (9,932)[/TD]
[TD] (13,964)[/TD]
[/TR]
[TR]
[TD] 10,104[/TD]
[TD] 10,104[/TD]
[TD] 10,104[/TD]
[TD] 9,809[/TD]
[TD] 10,255[/TD]
[TD] 10,255[/TD]
[TD] 9,683[/TD]
[TD] 6,753[/TD]
[TD] 4,340[/TD]
[TD] 1,453[/TD]
[TD] (1,434)[/TD]
[TD] (4,321)[/TD]
[/TR]
[TR]
[TD] 7,619[/TD]
[TD] 7,619[/TD]
[TD] 7,619[/TD]
[TD] 7,397[/TD]
[TD] 7,733[/TD]
[TD] 7,733[/TD]
[TD] 7,397[/TD]
[TD] 7,262[/TD]
[TD] 7,619[/TD]
[TD] 6,014[/TD]
[TD] 3,837[/TD]
[TD] 1,660[/TD]
[/TR]
[TR]
[TD] 11,462[/TD]
[TD] 11,462[/TD]
[TD] 11,462[/TD]
[TD] 11,127[/TD]
[TD] 11,633[/TD]
[TD] 11,633[/TD]
[TD] 8,297[/TD]
[TD] 4,973[/TD]
[TD] 2,235[/TD]
[TD] (1,039)[/TD]
[TD] (4,314)[/TD]
[TD] (7,589)[/TD]
[/TR]
[TR]
[TD] 24,381[/TD]
[TD] 24,381[/TD]
[TD] 23,483[/TD]
[TD] 16,008[/TD]
[TD] 10,014[/TD]
[TD] 2,944[/TD]
[TD] (4,894)[/TD]
[TD] (11,964)[/TD]
[TD] (17,788)[/TD]
[TD] (24,754)[/TD]
[TD] (31,720)[/TD]
[TD] (38,686)[/TD]
[/TR]
[TR]
[TD] 9,368[/TD]
[TD] 9,368[/TD]
[TD] 9,368[/TD]
[TD] 9,095[/TD]
[TD] 9,508[/TD]
[TD] 9,508[/TD]
[TD] 9,095[/TD]
[TD] 7,718[/TD]
[TD] 5,480[/TD]
[TD] 2,803[/TD]
[TD] 127[/TD]
[TD] (2,550)[/TD]
[/TR]
</tbody>[/TABLE]

 

[StephenCrump]

A8: =Rate1*A6+(Rate2-Rate1)*MEDIAN(A6,0,SUM($A6:A6)-Threshold)

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L
1	Rate1	7%
2	Rate2	5%
3	Threshold	1,000,000
4
5	Jan '18	Feb '18	Mar '18	Apr '18	May '18	Jun '18	Jul '18	Aug '18	Sept '18	Oct '18	Nov '18	Dec '18
6	201,575	201,575	201,575	195,689	204,584	204,584	195,689	192,131	201,575	201,575	201,575	201,575
7
8	14,110	14,110	14,110	13,698	14,221	10,229	9,784	9,607	10,079	10,079	10,079	10,079
Sheet1