salary defined by slab table

[XKruodo]

Hi,
I have a sheet with a table which has number of hours in a column A. Salary
per hour is in column B as under.

50 3.5
100 5.7
150 7.7
300 8.8

This is not just simple multiplication function. If you notice, first 3 rows
in A total up to 300 hours and 4th row has 300. It means that if someone has
worked for say 425 hours, I need to pay for first 50 hours 3.5 per hour, next
100 hours 5.7 per hour, next 150 , 7.7 hour and rest ( above 300 ) 125 hours,
8.8 per hour..

I have a cell that displays hours. How do i get values from this table?

 

[XKruodo]

There are different tables. Different for say supervisors , managers etc..
And table has more than 10 slabs. "IF" function wont work as i cannot use
"IF" more than 7 times in single formula..

 

[T. Valko]

Based on your sample data with 425 hrs, what result do you expect?

 

[XKruodo]

With 425 hours, i should get values in column C as under. I can then simply
multiply B and C to get the salary..
A B C

50 3.5 50 =C1*B1 ( 175)
100 5.7 100 =C2*B2 ( 570 ) AND SO ON...
150 7.7 150
300 8.8 125

If someone has worked for 175 hours i should get values in column C as under.

A B C

50 3.5 50 =C1*B1 ( 175)
100 5.7 100 =C2*B2 ( 570 ) AND SO ON...
150 7.7 25
300 8.8 0

For 60 hours

A B C

50 3.5 50 =C1*B1 ( 175)
100 5.7 10 =C2*B2 ( 570 ) AND SO ON...
150 7.7 0
300 8.8 0

For 30 hours

A B C

 

[T. Valko]

Try this...

A1 = hours worked = 425

Create this table...

......F.......G.......H
1...0.......3.5.....=G1
2...50.....5.7.....=G2-G1
3...150...7.7.....=G3-G2
4...300...8.8.....=G4-G3

Then, to get the total pay:

=SUMPRODUCT(--(A1>F1:F4),(A1-F1:F4),H1:H4)

Result = 3000

 

[Ashish Mathur]

Hi,

I had authored a similar article on computing income tax payable based on
slabs. You may refer to the solution (with explanation) here -
http://ashishmathur.com/articles.aspx. Click on "Computing personal income
tax" under B > 1