|
Rate Calculation
I am a relative beginner with excel and would appreciate in assistance with
the following problem. The following data from personnel timesheets is contained in the cells A B C D E F G H 1 $15 per hour Mon Tue W ed Thu Fri Sat Sun 2 Total Hours / Day 15 3 18 1 5 9 3 3 Normal Hours 4 Hours x 1.5 5 Hours x 2 6 Rate per day Monday to Friday For the first 7.5 hours the rate per hour applies For the next 4 hours the rate per hour is multiplied by 1.5 For the next 12 hours the rate per hour is multiplied by 2 Saturday For all hours the rate per hour is multiplied by 1.5 (cells G3:G6) Sunday For all hours the rate per hour is multiplied by 2 (cells H3:H6) The number of hours worked at each rate may vary from 0.25 hour upwards. The total hours per day will be inputted in cells B2:H2. From the total hours per day Require the spreadsheet to calculate the number of hours of 1) normal time and put in cell B3. 2) normal time x 1.5 and put in cell B4. 3) normal time x 2 and put in cell B5. 4) The rate per day to be calculated in cell B6. Many thanks for any assistance that you can provide. |
Rate Calculation
The following data from personnel timesheets is contained in the cells A B C D E F G H 1 $15 per hour Mon Tue W ed Thu Fri Sat Sun 2 Total Hours / Day 15 3 18 1 5 9 3 3 Normal Hours f1 4 Hours x 1.5 f2 5 Hours x 2 f3 6 Rate per day f4 f5 f6 f1: =MIN(B2,7.5) fill to right until Sun f2: =MIN(B2-B3,4) fill to right until Sun f3: =B2-B3-B4 fill to right until Sun f4: =$A$1*B3+$A$1*B4*1.5+$A$1*B5*2 fill to right until Fri f5: =$A$1*G2*1.5 f6: =$A$1*H2*2 Regards, Stefi |
Rate Calculation
Hi
B3 =MIN(7.5,B2) B4 =MIN(4,MAX(0,B2-7.5))*1.5 B5 =MAX(0,B2-11.5)*2 B6 =SUM(B3:B5)*15 or if A1 is Numeric, then =SUM(B3:B5)*$A$1 copy B3:B6 across through C3:F3 In cell G6 =B2*1.5*15 or =B2*1.5*$A$1 in cell H6 =B2*2*15 or=B2*2*$A$1 -- Regards Roger Govier "zephyr" wrote in message ... I am a relative beginner with excel and would appreciate in assistance with the following problem. The following data from personnel timesheets is contained in the cells A B C D E F G H 1 $15 per hour Mon Tue W ed Thu Fri Sat Sun 2 Total Hours / Day 15 3 18 1 5 9 3 3 Normal Hours 4 Hours x 1.5 5 Hours x 2 6 Rate per day Monday to Friday For the first 7.5 hours the rate per hour applies For the next 4 hours the rate per hour is multiplied by 1.5 For the next 12 hours the rate per hour is multiplied by 2 Saturday For all hours the rate per hour is multiplied by 1.5 (cells G3:G6) Sunday For all hours the rate per hour is multiplied by 2 (cells H3:H6) The number of hours worked at each rate may vary from 0.25 hour upwards. The total hours per day will be inputted in cells B2:H2. From the total hours per day Require the spreadsheet to calculate the number of hours of 1) normal time and put in cell B3. 2) normal time x 1.5 and put in cell B4. 3) normal time x 2 and put in cell B5. 4) The rate per day to be calculated in cell B6. Many thanks for any assistance that you can provide. |
Rate Calculation
alternative for f4
=SUMPRODUCT(B3:B5,{1;1.5;2}) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Stefi" wrote in message ... The following data from personnel timesheets is contained in the cells A B C D E F G H 1 $15 per hour Mon Tue W ed Thu Fri Sat Sun 2 Total Hours / Day 15 3 18 1 5 9 3 3 Normal Hours f1 4 Hours x 1.5 f2 5 Hours x 2 f3 6 Rate per day f4 f5 f6 f1: =MIN(B2,7.5) fill to right until Sun f2: =MIN(B2-B3,4) fill to right until Sun f3: =B2-B3-B4 fill to right until Sun f4: =$A$1*B3+$A$1*B4*1.5+$A$1*B5*2 fill to right until Fri f5: =$A$1*G2*1.5 f6: =$A$1*H2*2 Regards, Stefi |
Rate Calculation
Another solution:
Make a helper table of multipliers in Sheet2: A B C D E F G H 1 Mon Tue Wed Thu Fri Sat Sun 2 3 Normal Hours 1,0 1,0 1,0 1,0 1,0 1,5 2,0 4 Hours x 1.5 1,5 1,5 1,5 1,5 1,5 1,5 2,0 5 Hours x 2 2,0 2,0 2,0 2,0 2,0 1,5 2,0 You need only a single formula: =$A$1*B3*INDIRECT("Sheet2!"&ADDRESS(ROW()-3,COLUMN(),4,1,))+$A$1*B4*INDIRECT("Sheet2!"&ADDRE SS(ROW()-2,COLUMN(),4,1,))+$A$1*B5*INDIRECT("Sheet2!"&ADDRE SS(ROW()-1,COLUMN(),4,1,)) Regards, Stefi |
Rate Calculation
Stefi
Many thanks to you and the other guys that responded. With the alternative solution I have put the multipliers in cells B3 to H5 and the formula in cell B6 to H6. The cells have different totals than the first solution. i.e. Cell B6 =$307.5 with the first solution Cell B6 =$108.75 with the single formula I may have the formula in the wrong cell, I have checked the cell references and they appear ok. You're help is appreciated Many thanks "Stefi" wrote: Another solution: Make a helper table of multipliers in Sheet2: A B C D E F G H 1 Mon Tue Wed Thu Fri Sat Sun 2 3 Normal Hours 1,0 1,0 1,0 1,0 1,0 1,5 2,0 4 Hours x 1.5 1,5 1,5 1,5 1,5 1,5 1,5 2,0 5 Hours x 2 2,0 2,0 2,0 2,0 2,0 1,5 2,0 You need only a single formula: =$A$1*B3*INDIRECT("Sheet2!"&ADDRESS(ROW()-3,COLUMN(),4,1,))+$A$1*B4*INDIRECT("Sheet2!"&ADDRE SS(ROW()-2,COLUMN(),4,1,))+$A$1*B5*INDIRECT("Sheet2!"&ADDRE SS(ROW()-1,COLUMN(),4,1,)) Regards, Stefi |
Rate Calculation
$307.5 is the correct result, I get the same with the alternative solution!
Please copy here your exact multiplier table with cell references to check it! Stefi zephyr ezt *rta: Stefi Many thanks to you and the other guys that responded. With the alternative solution I have put the multipliers in cells B3 to H5 and the formula in cell B6 to H6. The cells have different totals than the first solution. i.e. Cell B6 =$307.5 with the first solution Cell B6 =$108.75 with the single formula I may have the formula in the wrong cell, I have checked the cell references and they appear ok. You're help is appreciated Many thanks "Stefi" wrote: Another solution: Make a helper table of multipliers in Sheet2: A B C D E F G H 1 Mon Tue Wed Thu Fri Sat Sun 2 3 Normal Hours 1,0 1,0 1,0 1,0 1,0 1,5 2,0 4 Hours x 1.5 1,5 1,5 1,5 1,5 1,5 1,5 2,0 5 Hours x 2 2,0 2,0 2,0 2,0 2,0 1,5 2,0 You need only a single formula: =$A$1*B3*INDIRECT("Sheet2!"&ADDRESS(ROW()-3,COLUMN(),4,1,))+$A$1*B4*INDIRECT("Sheet2!"&ADDRE SS(ROW()-2,COLUMN(),4,1,))+$A$1*B5*INDIRECT("Sheet2!"&ADDRE SS(ROW()-1,COLUMN(),4,1,)) Regards, Stefi |
Rate Calculation
Stefi
The table is below. The figures may be out of line when posted. A B C D E F G H 1 $15.00 M T W T F S S 2 Total Hours / Day 15 3 18 1 5 9 3 3 Normal Hours 1 1 1 1 1 1.5 2 4 Hours x 1.5 1.5 1.5 1.5 1.5 1.5 1.5 2 5 Hours x 2 2 2 2 2 2 1.5 2 6 Rate per day $108.75 $108.75 $108.75 $108.75 $108.75 $101.25 $180.00 Regards "Stefi" wrote: $307.5 is the correct result, I get the same with the alternative solution! Please copy here your exact multiplier table with cell references to check it! Stefi zephyr ezt *rta: Stefi Many thanks to you and the other guys that responded. With the alternative solution I have put the multipliers in cells B3 to H5 and the formula in cell B6 to H6. The cells have different totals than the first solution. i.e. Cell B6 =$307.5 with the first solution Cell B6 =$108.75 with the single formula I may have the formula in the wrong cell, I have checked the cell references and they appear ok. You're help is appreciated Many thanks "Stefi" wrote: Another solution: Make a helper table of multipliers in Sheet2: A B C D E F G H 1 Mon Tue Wed Thu Fri Sat Sun 2 3 Normal Hours 1,0 1,0 1,0 1,0 1,0 1,5 2,0 4 Hours x 1.5 1,5 1,5 1,5 1,5 1,5 1,5 2,0 5 Hours x 2 2,0 2,0 2,0 2,0 2,0 1,5 2,0 You need only a single formula: =$A$1*B3*INDIRECT("Sheet2!"&ADDRESS(ROW()-3,COLUMN(),4,1,))+$A$1*B4*INDIRECT("Sheet2!"&ADDRE SS(ROW()-2,COLUMN(),4,1,))+$A$1*B5*INDIRECT("Sheet2!"&ADDRE SS(ROW()-1,COLUMN(),4,1,)) Regards, Stefi |
Rate Calculation
It seems, that you placed the formula in sheet2 containing the multiplier
table. Place it in sheet1, cells B6:H6 where hours are stored! Yes, its sure, I could recunstruct the erroneous number 108.75. Regards, Stefi zephyr ezt *rta: Stefi The table is below. The figures may be out of line when posted. A B C D E F G H 1 $15.00 M T W T F S S 2 Total Hours / Day 15 3 18 1 5 9 3 3 Normal Hours 1 1 1 1 1 1.5 2 4 Hours x 1.5 1.5 1.5 1.5 1.5 1.5 1.5 2 5 Hours x 2 2 2 2 2 2 1.5 2 6 Rate per day $108.75 $108.75 $108.75 $108.75 $108.75 $101.25 $180.00 Regards "Stefi" wrote: $307.5 is the correct result, I get the same with the alternative solution! Please copy here your exact multiplier table with cell references to check it! Stefi zephyr ezt *rta: Stefi Many thanks to you and the other guys that responded. With the alternative solution I have put the multipliers in cells B3 to H5 and the formula in cell B6 to H6. The cells have different totals than the first solution. i.e. Cell B6 =$307.5 with the first solution Cell B6 =$108.75 with the single formula I may have the formula in the wrong cell, I have checked the cell references and they appear ok. You're help is appreciated Many thanks "Stefi" wrote: Another solution: Make a helper table of multipliers in Sheet2: A B C D E F G H 1 Mon Tue Wed Thu Fri Sat Sun 2 3 Normal Hours 1,0 1,0 1,0 1,0 1,0 1,5 2,0 4 Hours x 1.5 1,5 1,5 1,5 1,5 1,5 1,5 2,0 5 Hours x 2 2,0 2,0 2,0 2,0 2,0 1,5 2,0 You need only a single formula: =$A$1*B3*INDIRECT("Sheet2!"&ADDRESS(ROW()-3,COLUMN(),4,1,))+$A$1*B4*INDIRECT("Sheet2!"&ADDRE SS(ROW()-2,COLUMN(),4,1,))+$A$1*B5*INDIRECT("Sheet2!"&ADDRE SS(ROW()-1,COLUMN(),4,1,)) Regards, Stefi |
Rate Calculation
Stefi
Many thanks I had the single formula on the same worksheet. I have the sheet working with the original formula that you provided. Once again many thanks for your help. Regards Zephyr "Stefi" wrote: $307.5 is the correct result, I get the same with the alternative solution! Please copy here your exact multiplier table with cell references to check it! Stefi zephyr ezt *rta: Stefi Many thanks to you and the other guys that responded. With the alternative solution I have put the multipliers in cells B3 to H5 and the formula in cell B6 to H6. The cells have different totals than the first solution. i.e. Cell B6 =$307.5 with the first solution Cell B6 =$108.75 with the single formula I may have the formula in the wrong cell, I have checked the cell references and they appear ok. You're help is appreciated Many thanks "Stefi" wrote: Another solution: Make a helper table of multipliers in Sheet2: A B C D E F G H 1 Mon Tue Wed Thu Fri Sat Sun 2 3 Normal Hours 1,0 1,0 1,0 1,0 1,0 1,5 2,0 4 Hours x 1.5 1,5 1,5 1,5 1,5 1,5 1,5 2,0 5 Hours x 2 2,0 2,0 2,0 2,0 2,0 1,5 2,0 You need only a single formula: =$A$1*B3*INDIRECT("Sheet2!"&ADDRESS(ROW()-3,COLUMN(),4,1,))+$A$1*B4*INDIRECT("Sheet2!"&ADDRE SS(ROW()-2,COLUMN(),4,1,))+$A$1*B5*INDIRECT("Sheet2!"&ADDRE SS(ROW()-1,COLUMN(),4,1,)) Regards, Stefi |
| All times are GMT +1. The time now is 10:19 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com