Need some help creating my IF formula.

[NathanB]

Ok, here's my situation. I'm trying to modify an existing payroll spreadsheet to add in extra information for overtime bonus pay, which will be paid out for any hours over 40 worked per week. We are currently on a bi-weekly pay schedule, and are not paid hourly. We are paid piecework, not hourly, so I already added in one column (call it column A) to begin keeping track of hours and a second new column (column B) to begin keeping track of the hourly rate per project.

I need to create a formula in the bottom line to do the following, and I can't seem to create it correctly:

1. If the sum of the hours in cells A1-A7 (one week) is greater than 40, I need to get that number (x) -40 to find the total overtime hours for that week. If the sum is less than 40, I just need it to give me a zero. At the same time, I would like to have a similar logical staement for the second part of the two week period, so if the sum of the hours in cells A8-A14 is greater than 40, it will perform a similar calculation to get me the overtime hours of the second week. Again, if the number is less than 40, that would mean no overtime hours exist and it can just give me a zero.

2. The number of overtime hours for each of the two weeks would then be added together and multiplied by 0.5 x the average $/hr rate for all of the jobs worked in that period. This number is already calculated and exists in another cell (call it B15).

Thanks so much for any help you can offer!

 

[ACBCI]

Try-

=IF(SUM($A$1:$A$7)>40,SUM($A$1:$A$7)-40,0)

 

[NathanB]

Try-

=IF(SUM($A$1:$A$7)>40,SUM($A$1:$A$7)-40,0)

Thanks. That gets me part way to where I need to be. Can you get me the rest of the way there?

 

[ACBCI]

1) =IF(SUM($A$1:$A$7)>40,SUM($A$1:$A$7)-40,0) Say this is in B1
2) =IF(SUM($A$8:$A$14)>40,SUM($A$8:$A$14)-40,0) Say this is in B8
3) The multiplying formula to find the dollar value will be different for all the different hourly rates. I would group those in say C1-C5. Then use this formula in B15 =((SUM(B1,B8)*.5)*C#) where C# is the hourly rate established in cells C1-C5.