Results 1 to 7 of 7
Thread: If statement

20010523, 19:27 #1
 Join Date
 May 2001
 Location
 Allen, Texas, USA
 Posts
 727
 Thanks
 1
 Thanked 0 Times in 0 Posts
If statement
I am trying to calculate a spreadsheet for payroll purposes that will place the value greater than 40 to a field, but if it's less than 40, place a value of zero.
I have tried: =IF(D13>40, SUM(C1340),0)
but that doesn't give the value of zero if less than 40.
Can you help?
Thanks!

20010523, 19:37 #2
 Join Date
 Feb 2001
 Location
 Dublin, Ireland, Republic of
 Posts
 2,697
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: If statement
What value does it return ?, the same as if D13 > 0 ?. Check and make sure that D13 is a number anit text. To check that you could try changing the formula to =IF(D13*1>40, SUM(C1340),0). If that works, you need to alter the data in column D to numeric.
Andrew C

20010523, 19:43 #3
 Join Date
 May 2001
 Location
 Allen, Texas, USA
 Posts
 727
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: If statement
My bad! I just figured out that it should have been:
=IF(C13>40, SUM(C1340),0)
C13 and not D13.
Now it works correctly.
Thanks anyway. [img]/forums/images/smilies/smile.gif[/img]

20010523, 20:54 #4
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
Re: If statement
If you'll permit me to be picky, "...,sum(c1340),..." works, but is bad syntax; "...,c1340,...", or "...,sum(c13,40),..." is preferred. The correct way is to use the comma as argument separator and if needed include the sign of the subsequent arguments; this will avoid potential problems with range expressions such as sum(a1:a1340), which will return invalid results, where sum(a1:a13,40)will be correct.
Hope this annoying but wellmeaning advice saves you trouble sometime in the future.John ... I float in liquid gardens
UTC 7ąDS

20010523, 21:06 #5
 Join Date
 May 2001
 Location
 Allen, Texas, USA
 Posts
 727
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: If statement
Thanks for your "pickiness". I'm not sure I understand though.
I did have to change some of the formulas though.
Below is what calculates the first field:
=IF(SUM(C3:C9)>40,40,SUM(C3:C9))
That way it will not go over 40, but will add up to 40.
Then the second field to figure overtime:
=IF(SUM(C3:C9)>40, SUM(C3:C9)40,0)
Is this not correct? Seems like it works fine though.
KZ

20010523, 21:14 #6
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
Re: If statement
Your formulas are now syntactically correct. To see what I was talking about, plug this (or any numbers) into a test spreadsheet starting in a1.
24
35
17
21
13
19
and these formulas nearby:
=SUM(A1:A6)40
=SUM(A1:A6,40)
=SUM(A1:A640)
Are your results the same? For me the first two formulas are correct, the last is wrong.John ... I float in liquid gardens
UTC 7ąDS

20010523, 21:21 #7
 Join Date
 May 2001
 Location
 Allen, Texas, USA
 Posts
 727
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: If statement
Yep. Same as you got. I see now what I did in my first try. It's all working now.
Mucho thanks!
KZ