Rate Calculation

[zephyr]

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.

 

[Stefi]

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

 

[Roger Govier]

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

 

[Bob Phillips]

alternative for f4

=SUMPRODUCT(B3:B5,{1;1.5;2})

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Stefi]

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!"&ADDRESS(ROW()-2,COLUMN(),4,1,))+$A$1*B5*INDIRECT("Sheet2!"&ADDRESS(ROW()-1,COLUMN(),4,1,))

Regards,
Stefi

 

[zephyr]

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]

$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:

 

[zephyr]

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]

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:

 

[zephyr]

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