salary defined by slab table

X

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?
 
X

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..
 
X

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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top