confusing sums

[amreeves87]

Hi all.

not sure how to explain this properly

Name	Rate	Weekday Overtime Rate	Weekend Overtime Rate	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday	Total Cost
J Smith	£40​	£60​	£80​	10​	10​	10​	10​	10​	10​	10​

I am trying to figure out the total cost of an employee per week based on hours worked per day.
if the first 7.24 hours of a weekday are standard.
above that is x1.5, weekend is all x2.

I do not even know where to start.

sumif(the first 7.24 of mondays 10 x £40, anything above the 7.24 x £60) + (the same on tues, weds,thurs,fri) + (sat hours x £80) + (sun x £80)

needs to be able to work if the hours are more of less.

is this possible?

many thanks

 

[Scott Huish]

Perhaps:

Book2
	A	B	C	D	E	F	G	H	I	J	K	L
1	Name	Rate	Weekday Overtime Rate	Weekend Overtime Rate	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday	Total Cost
2	J Smith	£ 40.00	£ 60.00	£ 80.00	10	10	10	10	10	10	10	£3,876.00
Sheet1

Cell Formulas
Range	Formula
L2	L2	=(SUM(IF(E2:I2>7.24,7.24,E2:I2))*B2)+SUM(IF(E2:I2>7.24,(E2:I2-7.24)*C2,0))+(SUM(J2:K2)*D2)
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[snjpverma]

In your example how much would be for Monday?

sumif(the first 7.24 of mondays 10 x £40, anything above the 7.24 x £60) + (the same on tues, weds,thurs,fri) + (sat hours x £80) + (sun x £80)

I am a bit confused.
In the above quote you have written Mondays 10 * 40, however, as per the remaining explanation only 7.24 would be multiplied by 40.

Could you tell us what would be the correct output for Monday in figures? and please show us how you derived at the results.

 

[kennypete]

I agree, the spec is ambiguous, though here is my initial reading's take on what the answer is presuming the rates are hourly:

Book2
	A	B	C	D	E	F	G	H	I	J	K	L
1	Name	Rate	Weekday Overtime Rate	Weekend Overtime Rate	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday	Total Cost
2	J Smith	£40	£60	£80	10	10	10	10	10	10	10	£16,908.00
Sheet1

Cell Formulas
Range	Formula
L2	L2	=IF(E2>=7.24,(7.24*E2*B2),E2*B2)+IF(E2>7.24,((E2-7.24)*C2),0)+ IF(F2>=7.24,(7.24*F2*B2),F2*B2)+IF(F2>7.24,((F2-7.24)*C2),0)+ IF(G2>=7.24,(7.24*G2*B2),G2*B2)+IF(G2>7.24,((G2-7.24)*C2),0)+ IF(H2>=7.24,(7.24*H2*B2),H2*B2)+IF(H2>7.24,((H2-7.24)*C2),0)+ IF(I2>=7.24,(7.24*I2*B2),I2*B2)+IF(I2>7.24,((I2-7.24)*C2),0)+ (J2*D2)+(K2*D2)

 

[amreeves87]

In your example how much would be for Monday?



I am a bit confused.
In the above quote you have written Mondays 10 * 40, however, as per the remaining explanation only 7.24 would be multiplied by 40.

Could you tell us what would be the correct output for Monday in figures? and please show us how you derived at the results.

so if monday would be 10 hours. the first 7.24 hours would be charged at £40, the remaining 2.76 would be charged at £60 as that's classed as overtime.
same for all weekdays.
weekends would be all hours at £80.

i have manually worked it all out and it seems that the first response has worked for me = £3876

 

[amreeves87]

Perhaps:

Book2
	A	B	C	D	E	F	G	H	I	J	K	L
1	Name	Rate	Weekday Overtime Rate	Weekend Overtime Rate	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday	Total Cost
2	J Smith	£ 40.00	£ 60.00	£ 80.00	10	10	10	10	10	10	10	£3,876.00
Sheet1

Cell Formulas
Range	Formula
L2	L2	=(SUM(IF(E2:I2>7.24,7.24,E2:I2))*B2)+SUM(IF(E2:I2>7.24,(E2:I2-7.24)*C2,0))+(SUM(J2:K2)*D2)
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

that worked and its great. thank you

 

[snjpverma]

What if the number of hours is less than 7.24? or this scenario never occurs?

 

[amreeves87]

What if the number of hours is less than 7.24? or this scenario never occurs?

Scott's answer covers that.
if a 0 is in place it returns a 0
if 1 is put in it returns 40
if 8 is put it in returns (7.24 x 40) + (0.76 x 60)

all works great.

 

[Fazza]

without an array formula,
=SUM(E2:I2)*B2+(SUMIF(E2:I2,">="&7.24)-COUNTIF(E2:I2,">="&7.24)*7.24)*(C2-B2)+SUM(J2:K2)*D2