salary defined by slab table
in [Functions]
|
From: XKruodo on 18 Mar 2010 11:33 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?
From: XKruodo on 18 Mar 2010 11:46 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? >
From: T. Valko on 18 Mar 2010 12:03 Based on your sample data with 425 hrs, what result do you expect? -- Biff Microsoft Excel MVP "XKruodo" <XKruodo(a)discussions.microsoft.com> wrote in message news:0837A31B-E72F-4D8D-B1BB-D909F31F0E6B(a)microsoft.com... > 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? >
From: XKruodo on 18 Mar 2010 23:30 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? >
From: T. Valko on 19 Mar 2010 00:25 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 -- Biff Microsoft Excel MVP "XKruodo" <XKruodo(a)discussions.microsoft.com> wrote in message news:186EB33D-1862-4CAB-ADC0-293BE21BC0F8(a)microsoft.com... > 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? >>
|
Next
|
Last
Pages: 1 2 Prev: How to enable auto height with merged cells? Next: retrieving current data |