Multipule IF Statements

[ignitebenhaigh]

Hi, Please help.

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

 

[konew1]

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)

 

[ignitebenhaigh]

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

 

[konew1]

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

 

[ignitebenhaigh]

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?

 

[konew1]

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

 

[Dryver14]

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>

Spreadsheet Formulas

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

 

[ignitebenhaigh]

thanks Dryver14, the table works perfectly. Cheers

 

[Dryver14]

Your welcome.

Kev