Help please! Need formula for bonus based on value ranges & percentages

[glodc]

Hi- I'm sure someone has asked this before and I've been looking through the forums, but haven't found a fix for my problem.

I have 6 bonus hurdles

If cell value is 75,000 - 114,999, then 1% = bonus
If 115,000 - 154,999 , then 2% = bonus
If 155,000 - 194,999 , then 4% = bonus
If 195,000 - 234,999 , then 5% = bonus
If 235,000 - 279,999 , then 7% = bonus
If 280,000 - 9,999,999, then 9% = bonus

 

[MisterBates]

how about, with A1 containing your cell value:

=CHOOSE(MATCH(A1,{9999999,279999,234999,194999,154999,114999,74999},-1),0.09,0.07,0.05,0.04,0.02,0.01,0)

 

[glodc]

Thanks for the response, MisterBates! It didn't work for me, though. Not sure if it matters, but the cell that contains the number, C13, has this formula : =SUM(C4:C12)

Does that impede the ability to use that cell's value? The answer should be 18,298 but when I enter the formula you gave it gives me the answer "0" :/

 

[glodc]

Can anyone help? Please? I'd really appreciate it- I'm at my wit's end :/

 

[Rameses]

not sure how helpful this is, but for starters formula is below, just build on that with the rest of your if statements (if data is in A1). if the result were to be in B1 what do you want it to show '1%' or '= bonus' or '1% = bonus'
=IF(A1>=75000,IF(A1<=114999,1,0))

 

[shg]

One way:

       ----A---- --B---
   1     Value   Bonus%
   2    100,000      1%
   3    130,000      2%
   4    160,000      4%
   5    190,000      4%
   6    220,000      5%
   7    250,000      8%
   8    280,000      9%
   9    310,000      9%
  10    340,000      9%

In B2 and copied down,

=LOOKUP(A2, {0,75,115,155,195,235,280}*1000, {0,1,2,4,5,7,9}%)

If you want the amount rather than the percentage,

=A2 * LOOKUP(A2, {0,75,115,155,195,235,280}*1000, {0,1,2,4,5,7,9}%)

The formula assumes the bonus is paid on the total amount, not in tiers.

 

[Jim885]

This will work for you.
In any cell you want the result to appear, place this formula.

=IF(C13>=280000,0.9,IF(C13>=235000,0.7,IF(C13>=195000,0.5,IF(C13>=155000,0.4,IF(C13>=115000,0.2,IF(C13>=75000,0.1,0))))))