salary defined by slab table
 

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?

salary defined by slab table
 

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

"XKruodo" wrote:

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?

salary defined by slab table
 

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

--
Biff
Microsoft Excel MVP


"XKruodo" wrote in message
...
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?

salary defined by slab table
 

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
50 3.5 30 =C1*B1 ( 175)
100 5.7 0 =C2*B2 ( 570 ) AND SO ON...
150 7.7 0
300 8.8 0

"XKruodo" wrote:

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?

salary defined by slab table
 

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(--(A1F1:F4),(A1-F1:F4),H1:H4)

Result = 3000

--
Biff
Microsoft Excel MVP


"XKruodo" wrote in message
...
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
50 3.5 30 =C1*B1 ( 175)
100 5.7 0 =C2*B2 ( 570 ) AND SO ON...
150 7.7 0
300 8.8 0

"XKruodo" wrote:

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?

salary defined by slab table
 

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

--
Regards,

Ashish Mathur
Microsoft Excel MVP

"XKruodo" wrote in message
...
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?