# If Function with multiple variables

#### Bankerboy8

##### New Member
Hello, I am trying to calculate a different percentage for each part of an aggregate number. I think the formula is correct, but when I test it I get some wonky outcomes. Especially when I enter numbers just over the break points.

So for instance on the number \$5,100,000 the break down is

Column M Column N Column O
 12 First \$ 500,000 0.500% \$2,500 13 Next \$ 500,000 0.450% \$2,250 14 Next \$ 1,000,000 0.400% \$4,000 15 Next \$ 1,000,000 0.350% \$3,500 16 Next \$ 2,000,000 0.300% \$6,300 17 Next \$ 5,000,000 0.250% \$250 \$18,800

<tbody>
</tbody>

The formula being:
=IF(\$C\$7<=M12,ROUND(\$C\$7*N12,2),(M12*N12))

=IF(IF(\$C\$7-M12<=M12,ROUND((\$C\$7-M12)*N13,2),(M13*N13))<0,0,IF(\$C\$7-M12<=M12,ROUND((\$C\$7-M12)*N13,2),(M13*N13)))

=IF(IF(\$C\$7-(M13+M12)<=(M13+M12),ROUND((\$C\$7-(M13+M12))*N14,2),(M14*M14))<0,0,IF(\$C\$7-(M13+M12)<=(M13+M12),ROUND((\$C\$7-(M13+M12))*N14,2),(M14*N14)))

=IF(IF(\$C\$7-(M14+M13+M12)<=(M14+M13+M12),ROUND((\$C\$7-(M14+M13+M12))*N15,2),(M15*N15))<0,0,IF(\$C\$7-(M14+M13+M12)<=(M14+M13+M12),ROUND((\$C\$7-(M14+M13+M12))*N15,2),(M15*N15)))

=IF(IF(\$C\$7-(M15+M14+M13+M12)<=(M15+M14+M13),ROUND((\$C\$7-(M15+M14+M13+M12))*N16,2),(M16*N16))<0,0,IF(\$C\$7-(M15+M14+M13+M12)<=(M15+M14+M13+M12),ROUND((\$C\$7-(M15+M14+M13+M12))*N16,2),(M16*N16)))

=IF(ROUND((\$C\$7-(M15+M14+M13+M12+M16))*N17,2)<0,0,ROUND((\$C\$7-(M15+M14+M13+M12+M16))*N17,2))

Where \$5,100,000 = cell C7

Hope that makes sense and that you are able to reconstruct what it is I am trying to convey.
Thank you

### Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

#### Snakehips

##### Well-known Member
Bankerboy8,

Welcome to MrExcel.

Does this approach help...
Excel Workbook
LMNO
12First500,0000.50%2500
13Next500,0000.45%2250
14Next1,000,0000.40%4000
15Next1,000,0000.35%3500
16Next2,000,0000.30%6000
17Next5,000,0000.25%250
1818500
3

Drag O13 formula down thro O16

Last edited:

#### Bankerboy8

##### New Member
Bankerboy8,

Welcome to MrExcel.

Does this approach help...
3

 * L M N O 12 First 500,000 0.50% 2500 13 Next 500,000 0.45% 2250 14 Next 1,000,000 0.40% 4000 15 Next 1,000,000 0.35% 3500 16 Next 2,000,000 0.30% 6000 17 Next 5,000,000 0.25% 250 18 * * * 18500

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

 Cell Formula O12 =ROUND(MIN(C7,M12)*N12,2) O13 =ROUND(MAX(0,MIN(\$C\$7-SUM(M\$12:M12),M13)*N13),2)

<tbody>
</tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Drag O13 formula down thro O16

Snakehips,

The formula you supplied works beautifully! Thank you!! And mush smaller and easier than what I was trying to do. Thanks Again!

Replies
3
Views
191
Replies
0
Views
289
Replies
4
Views
462
Replies
12
Views
432
Replies
6
Views
329

1,191,683
Messages
5,987,991
Members
440,124
Latest member
dippy_egg

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