Multiple nested if and statement

[blankmc]

I am using the below formula to try and match values with a given fee percentage.

=IF(C4<b8,c4*c8),if(c4>=B10,C4*C11),IF(AND(C4<b9,c4>C8),C4*C9),IF(AND(C4<b10,c4>C9),C4*C10)

The result is #value !

Thank you for your help.</b10,c4></b9,c4></b8,c4*c8),if(c4>

 

[Max1616]

What are you trying to accomplish with the two And statements? You can't have two cell references in a statement without some sort of operator to separate the two (C4C8 and C4C9).

 

[blankmc]

Here is the corrected formula I used: =IF(C4<=B8,C4*C8),IF(C4>=B10,C4*C11),IF(AND(C4<=B9,C4>B8),C4*C9),IF(AND(C4<=B10,C4>B9),C4*C10)

I am trying to link an associated percentage (cell c8 thru c11) with a base amount (c4) based on where it falls relative a certain size.

So, if the current base amount (c4) is less than or equal to 100 (b8) the fee is c4 x c8 (the fee .25)
if the base amount is equal to or greater than the last bracket amount (b10) then it is the base amount times c11 (.10)

Then there are two inside amounts

if the base amount is less than or equal to 200 (b9) and greater than 100 (b8) then c4 times c9 (.2)
if the base amount is less than or equal to 500 (b10) and greater than 100 (b9) then c4 times c10 (.15)

Hope the above makes sense...

Truly appreciate your help.

 

[blankmc]

Hope the below may help. the letters a the top are the columns and the numbers on the left are the rows.

[TABLE="width: 446"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]a[/TD]
[TD]b [/TD]
[TD]c[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]Alpha aum[/TD]
[TD] 100,000,000[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]Bravo aum[/TD]
[TD] 100,000,000[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]Technology License Fee[/TD]
[TD]Rate[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD] 100,000,000[/TD]
[TD="align: right"]0.25%[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD] 200,000,000[/TD]
[TD="align: right"]0.20%[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD] 500,000,000[/TD]
[TD="align: right"]0.15%[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD] 5,000,000,000[/TD]
[TD="align: right"]0.10%[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD]Alpha rev[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD]Index License Fee[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
</tbody>[/TABLE]

 

[blankmc]

After multiple tries I finally found the error. below is the correct formula that looks to be working.

=IF(C4<=B8,C4*C8,IF(C4>=B10,C4*C11,IF(AND(C4<=B9,C4>C8),C4*C9,IF(AND(C4<=B10,C4>C9),C4*C10,0))))