# Multipule IF Statements

#### ignitebenhaigh

##### New Member

I need a (IF or similar) formula that's capable of doing the following.

If "value" = or is > "tier 1 value" calculate the difference between "value" & "tier 1 value" and multiply by "tier 1 %" but if "value" = or is > "tier 2 value" override & calculate the difference between "value" & "tier 1 value" and multiply by "tier 2 %" but if "value" = or is > "tier 3 value" calculate the difference between "value" & "tier 1 value" and multiply by "tier 3 %" bit if "value" = or is > "tier 4 value" calculate the difference between "value" & "tier 1 value" and multiply by "tier 4 %".

Problem i'm having is that i've tried to use:

=IFERROR(IF(C16<\$B\$6,"0",IF(C16>=\$B\$6,(C16-\$B\$6)*\$C\$6+\$D\$6,IF(C16>=\$B\$7,(C16-\$B\$6)*\$C\$7+\$D\$6,IF(C16>=\$B\$8,(C16-\$B\$6)*\$C\$8+\$D\$6,IF(C16>=\$B\$9,(C16-\$B\$6)*\$C\$9+\$D\$6,""))))),0)

but it only ever calculates the difference at the tier 1 %

Hopefully someone out their has a solution. Thanks in advance

### Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Is this what you mean. You really have made it difficult by not describing where data is on your sheet. I'm guessing that the tier percentages are the sum of 2 numbers, but I don't have a crystal ball and have only guessed

=iferror(if(c16<\$b\$6,"0",if(c16>=\$b\$6,(c16-\$b\$6)* (\$c\$6+\$d\$6) ,if(c16>=\$b\$7,(c16-\$b\$6)* (\$c\$7+\$d\$6) ,if(c16>=\$b\$8,(c16-\$b\$6)* (\$c\$8+\$d\$6) ,if(c16>=\$b\$9,(c16-\$b\$6)* (\$c\$9+\$d\$6) ,""))))),0)

Last edited:
Is this what you mean. You really have made it difficult by not describing where data is on your sheet. I'm guessing that the tier percentages are the sum of 2 numbers, but I don't have a crystal ball and have only guessed

=iferror(if(c16<\$b\$6,"0",if(c16>=\$b\$6,(c16-\$b\$6)* (\$c\$6+\$d\$6) ,if(c16>=\$b\$7,(c16-\$b\$6)* (\$c\$7+\$d\$6) ,if(c16>=\$b\$8,(c16-\$b\$6)* (\$c\$8+\$d\$6) ,if(c16>=\$b\$9,(c16-\$b\$6)* (\$c\$9+\$d\$6) ,""))))),0)

Sorry about that, ok so i'm trying to calculate different levels of bonuses

C16 is the value that they achieved for the month.

B6 (tier 1), B7 (tier 2), B8 (tier 3), B9 (tier 4) are the benchmarks and if achieved they correspond to a %.

So if they achieve tier 1 they get 5% of the difference between C16 & B6 (tier 1 total)

i hope that makes sense? let me know if not

Is tier 4 bonus better than tier 1 bonus? If so the entire formula is in wrong sequence. You must calculate the highest sales/bonus first, then work down to the smallest

yes tier 2 6%, tier 3 10%, tier 15%, so what i had originally will work as long as i start with the 15% and work down?

Only you can say if a formula returns what you expect. Try it. If you get wrong results then come back with detail information of the values in the sheet, the expected result and the actual result. Then we can look at the formula and help

Probably best using a table for the tiers like this and then referencing it.

Sheet1

 A B C 1 Amount addition 2 13000 150 3 4 5 Tier 1 10000 0.05 6 Tier 2 20000 0.06 7 Tier 3 30000 0.07 8 Tier 4 40000 0.08

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

 Cell Formula C2 =SUM(\$A\$2-\$B\$5)*VLOOKUP(\$A\$2,B5:C8,2,1)

<tbody>
</tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

thanks Dryver14, the table works perfectly. Cheers

Kev

Replies
9
Views
259
Replies
9
Views
165
Replies
4
Views
300
Replies
0
Views
155
Replies
1
Views
480

### Forum statistics

1,196,328
Messages
6,014,676
Members
441,835
Latest member
rthomas268 ### 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