Index, match, lookup?

[excelbetter]

I am trying to build a spreadsheet whereby I am able to figure out the total effective fees charged to a brokerage account based on the account balance. I know the fees charged for each $X in the account, but I can figure out how to do a "lookup" of sorts, or IF statements, to determine the fees.

I have a sample concept in the image attached. Are you able to help? Thank you.

 

[etaf]

this may help
assuming the fees start at a5
E5
=IF($E$2 > C5,C5*A5,$E$2*A5)
E6
=IF($E$2 > (C5+C6),C6*A6,IF(($E$2-C5) > 0,($E$2-C5)*A6,0))
E7
=IF($E$2 > (C5+C6+C7),C7*A7,IF(($E$2-C5-C6) > 0,($E$2-C5-C6)*A7,0))
E8
=IF($E$2 > (C5+C6+C7+C8),C8*A8,IF(($E$2-C5-C6-C7) > 0,($E$2-C5-C6-C7)*A8,0))
E9
=IF($E$2 > (C5+C6+C7+C8+C9),(C5+C6+C7+C8+C9)*A9,0)

 

[etaf]

Excel Workbook
	A	B	C	D	E
1
2					7000000
3
4	fee				fee owed
5	1.25%		2000000		25000
6	0.90%		3000000		27000
7	0.60%		5000000		12000
8	0.50%		15000000		0
9	0.45%				0
10
11					64000
12					0.91%
Sheet1

 

[excelbetter]

EXCELLENT. Thank you for the help!!

Sheet1

	A	B	C	D	E
fee				fee owed

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="align: right"]7000000[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD="align: right"]1.25%[/TD]

[TD="align: right"]2000000[/TD]

[TD="align: right"]25000[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]
[TD="align: right"]0.90%[/TD]

[TD="align: right"]3000000[/TD]

[TD="align: right"]27000[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]
[TD="align: right"]0.60%[/TD]

[TD="align: right"]5000000[/TD]

[TD="align: right"]12000[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]
[TD="align: right"]0.50%[/TD]

[TD="align: right"]15000000[/TD]

[TD="align: right"]0[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]
[TD="align: right"]0.45%[/TD]

[TD="align: right"]0[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]11[/TD]

[TD="align: right"]64000[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]12[/TD]

[TD="align: right"]0.91%[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula E5 =IF($E$2>C5,C5*A5,$E$2*A5) E6 =IF($E$2>(C5+C6),C6*A6,IF(($E$2-C5)>0,($E$2-C5)*A6,0)) E7 =IF($E$2>(C5+C6+C7),C7*A7,IF(($E$2-C5-C6)>0,($E$2-C5-C6)*A7,0)) E8 =IF($E$2>(C5+C6+C7+C8),C8*A8,IF(($E$2-C5-C6-C7)>0,($E$2-C5-C6-C7)*A8,0)) E9 =IF($E$2>(C5+C6+C7+C8+C9),(C5+C6+C7+C8+C9)*A9,0) E11 =SUM(E5:E9) E12 =E11/E2 <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[etaf]

you are welcome