The problem of multi interval and multi condition value when excel calculates employee commission?

Paste the following formula in the first square of yellow:

if(if(b3=0,1,if(b3<10000,2,if(b3<11000,3,if(b3<12000,4,if(b3<13000,5,if(b3<14000,6,if(b3<15000,7,if(b3<16000,8,if(b3<17000,9,10)))=if(c3<10,1,if(c3<25,2,if(c3<30,3,if(c3<35,4,if(c3<40,5,if(c3<45,6,if(c3<50,7,if(c3<55,8,if(c3<60,9,10)))index($h$3:$h$12,if(c3<10,1,if(c3<25,2,if(c3<30,3,if(c3<35,4,if(c3<40,5,if(c3<45,6,if(c3<50,7,if(c3<55,8,if(c3<60,9,10)))1),index($h$3:$h$12,if(b3=0,1,if(b3<10000,2,if(b3<11000,3,if(b3<12000,4,if(b3<13000,5,if(b3<14000,6,if(b3<15000,7,if(b3<16000,8,if(b3<17000,9,10)))1))

Then pull down.

As shown in the following figure, d2=min(lookup(b2,$f$2:$f$11,$h$2:$h$11),lookup(c2,$g$2:

g$11,$h$2:$h$11)), the formula drop-down autofills. <>

Let's start with the idea:

1.The commission amount of each level is fixed.

2.Just determine which gear each technician belongs to (take the lower value).

3.Conditional area plus auxiliary columns: gear, 1st gear, 2nd gear. That's it.

4.According to the if function, the gear is judged by the upper clock and the flowing water. Thus the results are demanded.

Assuming that A1 is the sales performance of the employee, the formula for calculating the employee's sales commission in B1 should be =if(a1<=150000,a1*,if(a1<=300000,(a1-150000)*,a1-300000)*

The meaning is as follows: if the performance is less than 150,000, then use the formula"Performance*"

If the performance is greater than 150,000 and less than 300,000, then use the formula"(Performance-150000)*

If the performance is greater than 300,000, then use the formula "(performance - 300,000)*

After counting and summarizing it, it won't seem very difficult, and I believe you can do it well, so you don't need to say more.

To improve the level of payroll management, use Blue Ice Payroll Software!

It would be faster to screen out 150,000 yuan and then calculate.

1. Where is AB in your snapshot?

If the waiter is counted as A, and the supervisor type sells as B, what is the new arrival?

You haven't been chasing up on the content, ignoring the "new" ones first.

d6 input. lookup(c6,if(b6="The waiter is redundant",,if(b6="Supervisor",,0)))

Enter and fill down.