Need to compare multiple values with =if statement, and it's invalid

[PatrickDickey52761]

Hello there,

What I’m doing is creating a spreadsheet to track my overtime. I get overtime in two ways 1) If I work over 40 hours in a week or 2) if I work over 8 hours in one day (regardless of the total amount of hours worked in the week).

I have my values set in columns B through H (where H is Sunday). What I’m putting in as my =if statement is this

=if(I9>40,I9-40+H9,((=if((B9-8)>0,B9-8,0))+(=if((C9-8)>0,C9-8,0))+(=if((D9-8)>0,D9-8,0))+(=if((E9-8)>0,E9-8,0))+(=if((F9-8)>0,F9-8,0))+H9))

and I’m getting Invalid Expression. I’m not sure what’s wrong exactly, unless it’s that I can’t have multiple =if statements in the last part. If that’s the case, is there an easy way of doing this? My original statement is this:

=if(I9>40,I9-40+H9,(B9-8+C9-8+D9-8+E9-8+F9-8+H9))

which places a negative value in the overtime column (it should be 0 unless I have overtime), and it slowly moves towards the correct value (either 0 or the overtime amount) as I add hours.

Thanks for any help with this, and have a great day:)
Patrick.

P.S. An example of what I’m looking for is this:

A9=8 B9=10 C9=8 D9=8 E9=0 F9=0 G9=0 H9=0 and the overtime should read 2

A9=8 B9=8 C9=8 D9=8 E9=8 F9=8 G9=0 H9=0 and the overtime should read 0

A9=8 B9=8 C9=8 D9=8 E9=8 F9=8 G9=8 H9=0 and the overtime should read 8

A9=8 B9=8 C9=8 D9=8 E9=8 F9=8 G9=0 H9=8 and the overtime should read 16 (double time on Sunday)

[w_whalley]

I’m not really sure I follow your examples. They contain 8 values, not 7.

I would try a user-defined function. See if this comes close to what you want.

function ot(d1,d2,d3,d4,d5,d6,d7)
ot=0
if d1>8 then ot=d1-8
if d2>8 then ot=ot+d2-8
if d3>8 then ot=ot+d3-8
if d4>8 then ot=ot+d4-8
if d5>8 then ot=ot+d5-8
if d6>8 then ot=ot+d6-8
ot=ot+2*d7  rem # double time on Sunday
wt=d1+d2+d3+d4+d5+d6+d7 rem # total hours in week
if wt >40 then
  ot=ot+wt-40 rem # overtime for > 40 hours per week 
  ot=ot-d7  rem # do not count Sunday twice
end if
end function

[froz]

You are using the wrong syntax:

1. “=IF” character can only be used as first character in formula to indicate of formula in cell. “=IF” in middle of the formula should be changed to only IF.
2. You need to follow syntax:
   IF(conditional, true, false)
   so if you embed IF conditional inside another IF then
   IF(conditional, IF(second-conditional, true, false), false)
   So itch of IF has to have three arguments.

[mariosv]

If I understand your question a formula like:
=SUMPRODUCT((B9:F9>8)*(B9:F9-8))
only rest multiply by two when the day is Sunday.

[mariosv]

If I have understand your question, I think the next formula can work.
=SUMPRODUCT((B9:F9>8)*(B9:F9-8))
only remains add the condition to double the value for Sunday.