Excel 2007 - Nested IF Statement 3

[ladyck3]

I'm trying to put together a "points" system for tardiness.
If 8:00 a.m. is the login time, that is = to 28800 seconds

Criteria for this experiment.

If on time, no points
If 3 mintues (180 seconds) late .25 points
Over 3 minuts up to 15 minutes (900 seconds) is .5 points
Anything over 15 minutes is 1 point

My data is as follows:

Col N Col O Col P
Login Time/Sec Diff/Sec Points
Row 68 8:16:05 29765 965 1
Row 69 8:03:14 28994 194 .5
Row 70 7:57:17 28637 -163 0
Row 71 8:10:17 29442 642 .5
Row 72 8:01:17 28877 77 .25

I do not know VBA... My Logic is as follows:
(Using Line 68)

If O68 <=0 Report 0
If O68 <=180 Report .25
If O68 >180<900 Report .5
If 068 >900 Report 1

So I wrote the following IF Statement, but it is not reporting the values I am expecting. Where am I going wrong? Is it an order issue? I tried it in reverse as well, with the largest going to the smallest. Also, the "False" for the last statement, I was not sure what to put. I entered "0" but then it does report zero. But one of these statements is TRUE... it would be "1" because the number is higher than 900

Additionally, I also tried a cell reference for the 180 and 900 factors 180 in Cell O29 and 900 in cell P29, same results.

=IF(068<=0,0,IF(O68<=180,0.25,IF(O68>180<900,0.5,IF(O68>900,1))))

HELP!

Thanks in advance.


LadyCK3
aka: Laurie

 

[xlhelp]

=IF(O68<=0,0,IF(O68<=180,0.25,IF(O68<=900,0.5,1)))

A man has only two choices: He can be right or he can be happy.

 

[ladyck3]

OH MY.... thanks xlhelp!!

I get it, I needed the "1" for Greater than 900 as my "false" statement. Who knew (well I didn't).

Thanks SO VERY MUCH... this is my first attempt at doing a nested IF statement... I was on the right track, I just thought too hard

A Star for YOU

I do so appreciate the assistance!!!


LadyCK3
aka: Laurie

 

[xlhelp]

You are welcome. For future, simple rule: You need one less if statement then there are conditions. You had four conditions, you need three IF statements.

A man has only two choices: He can be right or he can be happy.

 

[kjv1611]

For future, simple rule: You need one less if statement then there are conditions.

Star for that. I may realize it when necessary, but this'll actually give me a point of reference. Makes perfect sense, but I can't say I've always remembered it.. could save a little bit of time... if I remember.

--

"If to err is human, then I must be some kind of human!" -Me

 

[ladyck3]

Thanks again xlhelp.... that is the type of "rule of thumb" that I remember. I forget a lot of stuff but 'rules of thumb' are what keep me sane!

WOO HOO

LadyCK3
aka: Laurie