If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
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? |
#4
|
|||
|
|||
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? |
#5
|
|||
|
|||
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? |
#6
|
|||
|
|||
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? |
Thread Tools | |
Display Modes | |
|
|