# Problems with my nested if formula

#### CROWAN

##### New Member
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

### Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

#### 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):
Code:
``=[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]``

Last edited:

#### CROWAN

##### New Member
That is what I doing wrong. Thank you so much.

You are welcome!

Replies
7
Views
887

1,191,093
Messages
5,984,635
Members
439,897
Latest member
osman53

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back