Ask Your Question
0

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

asked 2013-02-08 02:39:11 +0100

PatrickDickey52761 gravatar image

updated 2015-10-21 14:16:40 +0100

Alex Kemp gravatar image

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 flag offensive 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

4 Answers

Sort by » oldest newest most voted
1

answered 2013-02-08 16:25:58 +0100

w_whalley gravatar image

updated 2013-02-08 21:59:08 +0100

qubit gravatar image

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
edit flag offensive delete link more
0

answered 2013-02-08 21:57:46 +0100

m.a.riosv gravatar image

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.

edit flag offensive delete link more
0

answered 2013-02-08 22:08:56 +0100

m.a.riosv gravatar image

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.

edit flag offensive delete link more
0

answered 2013-02-08 09:04:20 +0100

froz gravatar image

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.

edit flag offensive delete link more

Question Tools

1 follower

Stats

Asked: 2013-02-08 02:39:11 +0100

Seen: 6,057 times

Last updated: Feb 08 '13