IF Function

[Guest]

My worksheet looks like the following

Mon Tues Wend Thurs Fri Reg O

8 9 8.5 8 8

I am trying to get a formula in column "Reg" so that if it is 40 or less hours then sum, if it's over 40 just put 40. I am using the following: =IF(SUM(B7:F7)>="40",40,IF(SUM(B7:F7)<"40",SUM(B7:F7),"")) but its not working??

Also, I would like to put a formula in the "OT" column to put in hours greater than 40

Any help would be greatly appreciated

 

[Frank Kabel]

Try
Reg-cell
=MIN(SUM(B7:F7),40)

OT-cell
=MAX(0,SUM(B7:F7)-40)

 

[Guest]

Robert

One error that you're making is that the 40 should not be in quotes. However, let me suggest another couple of formulas

For reg: =MIN(SUM(A2:E2),40
For OT: =MAX(0,SUM(A2:E2)-40

This assumes that your numbers are in the first row

Art

 

[Guest]

It works, thanks for the help.