Need formula to calculate bonus or commision pay scale

**Altess** · 06-24-2013, 04:29 PM

I am trying to write a formula that will calculate the first 30K (0 to 30K) of a number, 127,000 for example, at 10 %, the 2nd 30K (30,001 to 60,000) at 20 % the 3rd 30K at 30 percent, the 4th 30K at 40% and everything over 50K at 50%. Is this possible? Thanks in advance.

**loginjmor** · 06-24-2013, 06:08 PM

Hi -

The best I got is a big hairy IF statement as follows:

=IF(J4>120000,120000*0.25+(J4-120000)*0.5,IF(J4>90000,90000*0.2+(J4-90000)*0.4,IF(J4>60000,60000*0.15+(J4-60000)*0.3,IF(J4>30000,30000*0.1+(J4-30000)*0.2,J4*0.1))))

Substitute cell J4 for whatever target cell you want. It's not very flexible, but it works. I assume in your problem statement you meant that anything over $120,000 is at 50%?

Hope this helps.

**Altess** · 06-24-2013, 06:17 PM

Hi,

Yes, anything over $120,000 is at 50%.

Wow, that was fast! Thanks, I will test this out and return with "solved" or more questions.

**Special-K** · 06-24-2013, 06:23 PM

Pls ignore, solution invalid

**Special-K** · 06-24-2013, 06:27 PM

Does this work (3rd attempt)?

=(J4-LOOKUP(J4,{0,30000,60000,90000,120000},{0,30000,60000,90000,120000}))*LOOKUP(J4,{0,30000,60000,90000,120000},{0.1,0.2,0.3,0.4,0.5})

**Altess** · 06-24-2013, 06:57 PM

Hello and thanks for your help.

This formula seems to just return last 50% for anything over $120,000 without adding the commissions on the first, second, third and fourth $30,000 to that amount for the total commission.

Thanks,