Need to compare multiple values with =if statement, and it's invalid. [closed]

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)

edit retag reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp close date 2015-10-21 14:16:50.079406

Sort by » oldest newest most voted

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

more

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.

more

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.

more

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.

more