If Function with multiple variables

[Bankerboy8]

Hello, I am trying to calculate a different percentage for each part of an aggregate number. I think the formula is correct, but when I test it I get some wonky outcomes. Especially when I enter numbers just over the break points.

So for instance on the number $5,100,000 the break down is

Column M Column N Column O

12 First	$ 500,000	0.500%	$2,500
13 Next	$ 500,000	0.450%	$2,250
14 Next	$ 1,000,000	0.400%	$4,000
15 Next	$ 1,000,000	0.350%	$3,500
16 Next	$ 2,000,000	0.300%	$6,300
17 Next	$ 5,000,000	0.250%	$250
			$18,800

<tbody>
</tbody>

The formula being:
=IF($C$7<=M12,ROUND($C$7*N12,2),(M12*N12))

=IF(IF($C$7-M12<=M12,ROUND(($C$7-M12)*N13,2),(M13*N13))<0,0,IF($C$7-M12<=M12,ROUND(($C$7-M12)*N13,2),(M13*N13)))

=IF(IF($C$7-(M13+M12)<=(M13+M12),ROUND(($C$7-(M13+M12))*N14,2),(M14*M14))<0,0,IF($C$7-(M13+M12)<=(M13+M12),ROUND(($C$7-(M13+M12))*N14,2),(M14*N14)))

=IF(IF($C$7-(M14+M13+M12)<=(M14+M13+M12),ROUND(($C$7-(M14+M13+M12))*N15,2),(M15*N15))<0,0,IF($C$7-(M14+M13+M12)<=(M14+M13+M12),ROUND(($C$7-(M14+M13+M12))*N15,2),(M15*N15)))

=IF(IF($C$7-(M15+M14+M13+M12)<=(M15+M14+M13),ROUND(($C$7-(M15+M14+M13+M12))*N16,2),(M16*N16))<0,0,IF($C$7-(M15+M14+M13+M12)<=(M15+M14+M13+M12),ROUND(($C$7-(M15+M14+M13+M12))*N16,2),(M16*N16)))

=IF(ROUND(($C$7-(M15+M14+M13+M12+M16))*N17,2)<0,0,ROUND(($C$7-(M15+M14+M13+M12+M16))*N17,2))

Where $5,100,000 = cell C7

Hope that makes sense and that you are able to reconstruct what it is I am trying to convey.
Thank you

 

[Snakehips]

Bankerboy8,

Welcome to MrExcel.

Does this approach help...

Excel Workbook
	L	M	N	O
12	First	500,000	0.50%	2500
13	Next	500,000	0.45%	2250
14	Next	1,000,000	0.40%	4000
15	Next	1,000,000	0.35%	3500
16	Next	2,000,000	0.30%	6000
17	Next	5,000,000	0.25%	250
18				18500
3

Drag O13 formula down thro O16

 

[Bankerboy8]

Bankerboy8,

Welcome to MrExcel.

Does this approach help...
3

*	L	M	N	O
12	First	500,000	0.50%	2500
13	Next	500,000	0.45%	2250
14	Next	1,000,000	0.40%	4000
15	Next	1,000,000	0.35%	3500
16	Next	2,000,000	0.30%	6000
17	Next	5,000,000	0.25%	250
18	*	*	*	18500

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula O12 =ROUND(MIN(C7,M12)*N12,2) O13 =ROUND(MAX(0,MIN($C$7-SUM(M$12:M12),M13)*N13),2) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4


Drag O13 formula down thro O16

Snakehips,

The formula you supplied works beautifully! Thank you!! And mush smaller and easier than what I was trying to do. Thanks Again!