Multiple IF statements, MATCH, INDEX and OR

[Capitalmind]

Hi all, I'm trying to build a table to calculate compounding of interest however the input amount is something I need to edit and the interest changes at different levels.


I tried stacking multiple IF statements so =IF(A1<=300,0.01,IF(A1>300 OR <1000... my formula breaks.


I don't know how to set banding in an IF statement or nesting AND/OR statements. It's probably better to use an index match but I don't know how to do that either.


Anyway the bands are:


$300 - $1000 0.25%
$1000 - $5000 0.35%
$5000 - $10000 0.4%


I want to look at the left hand value, determine with logic what interest band it is in then show that interest in the second column. I'll sum in the third column.


Any tips appreciated. Stacking multiple IF's is a regular problem for me, probably need INDEX/MATCH only I don't know how to use it.

 

[AhoyNC]

See if this works for you.

Excel Workbook
	A	B
1	325	81.25
2	3660	1281
3	8655	3462
Sheet

 

[Capitalmind]

See if this works for you.

	A	B

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: right"]325[/TD]
[TD="align: right"]81.25[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]3660[/TD]
[TD="align: right"]1281[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]8655[/TD]
[TD="align: right"]3462[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula B1 =A1*IF(A1<1000,0.25,IF(A1<5000,0.35,0.4)) B2 =A2*IF(A2<1000,0.25,IF(A2<5000,0.35,0.4)) B3 =A3*IF(A3<1000,0.25,IF(A3<5000,0.35,0.4)) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Thanks for looking at this. I threw the formula into a spreadsheet though the interest seems to work in reverse meaning as the deposit amount grows the return is less? This was the issue I had with banding, as you can see here (Dropbox link).