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:

Jan '18

Feb '18

Mar '18

Apr '18

May '18

Jun '18

Jul '18

Aug '18

Sept '18

Oct '18

Nov '18

Dec '18

<tbody>
</tbody>

201,575	201,575	201,575	195,689	204,584	204,584	195,689	192,131	201,575	201,575	201,575	201,575
144,344	144,344	144,344	140,129	146,498	146,498	140,129	137,581	144,344	144,344	144,344	144,344
108,847	108,847	108,847	105,669	110,472	110,472	105,669	103,748	108,847	108,847	108,847	108,847
163,741	163,741	163,741	158,960	166,185	166,185	158,960	156,070	163,741	163,741	163,741	163,741
348,300	348,300	348,300	338,129	353,499	353,499	338,129	331,981	348,300	348,300	348,300	348,300
133,833	133,833	133,833	129,925	135,831	135,831	129,925	127,563	133,833	133,833	133,833	133,833

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

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)

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

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

 

[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