Problems with my nested if formula

[CROWAN]

I must be missing something here. I need to calculate fees based on average net assets

First $25 bil is 20 bps (0.00002)
Next $25 bil is 15 bps (0.000015)
Next $25 bil is 10 bps (0.00001)
> $75 bil is 5 bps (0.000005)

I was able to get to the second tier with the formula working:
=IF(B14<25000000000,B14*0.00002/366*C14,IF(AND(B14>25000000001,B14<50000000000),25000000000*0.00002/366*C14+(B14-25000000000)*0.000015/366*C14))

But I get an error at the third tier
=IF(B14<25000000000,B14*0.00002/366*C14,IF(AND(B14>25000000001,B14<50000000000),25000000000*0.00002/366*C14+(B14-25000000000)*0.000015/366*C14)),IF(AND(B14>50000000001,B14<75000000000),25000000000*0.00002/366*C14+25000000000*.00002/366*c14+(B14-50000000000)*0.00001/366*C14))

I'm missing something

 

[Joe4]

A few things:
- You do not need the ANDs. If you work your way up from smallest to largest, you only need to check to see if is less than the value. This is because once it reaches a true condition, it will not go on to the other ones, so there is no need to check to the lower bound on each level.
- It looks like you may not have added the last level (what to do if over $75). You can just add that in the FALSE argument of your last IF. I will leave that to you to add it.

Here is what the structure of your formula should look like (I coded each part in a different color to make it easier to see):

=[COLOR=#0000ff]IF(B14<25000000000,B14*0.00002/366*C14,[/COLOR][COLOR=#ff0000]IF(B14<50000000000,25000000000*0.00002/366*C14+(B14-25000000000)*0.000015/366*C14,[/COLOR][COLOR=#008000]IF(B14<75000000000,25000000000*0.00002/366*C14+25000000000*0.00002/366*C14+(B14-50000000000)*0.00001/366*C14,[/COLOR][COLOR=#ffa500]"what to do if over $75")))[/COLOR]

 

[CROWAN]

That is what I doing wrong. Thank you so much.

 

[Joe4]

You are welcome!