Nested IF Statement using Time Variables 2

[ladyck3]

HELP....
This is the statement I concocted and I know its screwed up big time but let me paste it so I don't lose it:

=IF(d67<=B2,0,IF(d67(<b2+3),0.25),if(b67>(b2+3)<(b2+15),0.05,if(b67>(b2+15),1.0))))

B2 = 8:00:00 (login scheduled time)
B67 = 8:02:35 (actual login time)

I need to calculate a point system with the following criteria.

If the actual login is

Earlier or = to 8:00 then enter the value 0
If it is > 8:00:00 but less than 3 minutes 0.25
If it is > 3 minutes but less than 15 0.50
If it is > 15 minutes 1

HELP...
First question, do I have to have a separate calculation sheet to convert the time to seconds? I have one already, I can just add this criteria.

If I am doing this I guess I can link to the seconds and refer to the calculation sheet but I'm new to nested IF Statements and the kink for me is the >3<15 part.

HELP! I'm sure my initial formula above is total junk but I tried

Thanks!
Learning in Texas


LadyCK3
aka: Laurie

 

[TonyJollans]

All I can think is that you have a date and time in E59. If so, it will always be greater than a simple time. If so, it should work if you replace each occurrence of [tt]E59[/tt] with [blue][tt]MOD(E59,1)[/tt][/blue]

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[ladyck3]

Oh, I'm GOING to have a FIT.
I just applied the forumula to my workbook (not a test page) and where it should say ZERO it shows 1

Cell E112 shows a time of 7:45:25 and should calculate to zero. My formula (well Combo's)
=SUM(IF(E112>TIME(8,0,0),0.25),IF(E112>=TIME(8,3,0),0.25),IF(E112>=TIME(8,15,0),0.5))

There's no reserve for "0" in this but in the test work, if I enter this information it turns to zero its formatted (tried both) as h:mm:ss and/or [h]:mm:ss

Ok I don't know the difference but I tried both.

I'm going to go read your FAQ article referenced, Skip. I've been to Grand Prairie.. I work in Plano, live in Garland.


LadyCK3
aka: Laurie

 

[TonyJollans]

> Cell E112 shows a time of 7:45:25 (my bolding)

But what does it contain? Read Skip's FAQ, I'm sure it will help, but the more you post, the more I suspect you have times with dates, set to show just the times.

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[ladyck3]

Tony you have not confused me really, I'm working with time but in 24 hr blocks 1pm would be 13:00:00 (military time) .. I'm not sure how you got date from that.. I'm sure there is something in there that I said that caused you to think but but I'm lost.

I looked at Skip's faq and I'm using 2007, I know how to get to the Define the name section but I don't get the use of the offset thing...

The point is... when I copied the working formula into the proper location the cell reference changed but not properly. I Fixed it and its working ok, its a stupid user trick

User error <wiping egg off face>

I need to take a break, heck I might take lunch today

I'm ok, seriously it IS working. (Thank God)



LadyCK3
aka: Laurie

 

[SkipVought]

Also relevant...

faq68-5827

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[TonyJollans]

If you don't have any dates - ignore me!

Another possibility (for my formula not working) is that your static time in $F$56 is text rather than a time value. You are not using a reference to it in Combo's formula.

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[ladyck3]

OH MY GOD I cannot get this for the life of me...

If I use for login the following formula:

=SUM(IF(B113>TIME(8,0,0),0.25),IF(B113>=TIME(8,3,0),0.25),IF(B113>=TIME(8,15,0),0.5))

it will show "0" if the cell is blank or less than 8:00 and that is fine.

But for Log out... (the formula that I THOUGHT I had working) if I use it, and the cell is blank it returns a value of zero, BUT if the value is more than 15 minutes early it shows a value of .75, not 1 so apparently its adding together the .25 and the .5 for .75?

I tried to reverse the order of the formula and that made no difference.


=SUM(IF(C73>TIME(17,0,0),0),IF(C73<=TIME(16,57,0),0.25),IF(C73<=TIME(16,45,0),0.5))

I've got such a headache now... and my head is spinning. This "should be" easier or I "should" know more ;(

what is causing this to show the following values

Cells C71:C74

16:57:47 0 Correct
16:55:00 0.25 Correct
17:07:15 0 Correct
16:15:41 0.75 Should be 1 This is 4:15:41, basically 44 minutes early.

Thanks







LadyCK3
aka: Laurie

 

[SkipVought]

try this
[tt]
=SUM(IF(C73>TIME(17,0,0),0),IF(C73>=TIME(16,57,0),0.25),IF(C73>=TIME(16,45,0),0.5),IF(C73<TIME(16,45,0),1))
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ladyck3]

Skip, when I use what you just sent I get the following

Old Values from above to save scrolling

16:57:47 0
16:55:00 0.25
17:07:15 0
16:15:41 0.75

New Values with your formula (I'd try your indexie thing but I don't get it)

16:57:47 0.75
16:55:00 0.5
17:07:15 0.75
16:15:41 1


So now the last one appears correctly but I'm lost on those which were correct, they are now askew.



LadyCK3
aka: Laurie

 

[SkipVought]

Laurie,

I'm back to suggesting ONE table with ONE formula...
[tt]
TIM VAL
0:00 0
8:00 0.25
8:03 0.5
8:15 1
16:45 0.5
16:56 0.25
17:00 0
[/tt]
[tt]
=INDEX(VAL,MATCH(WhateverTimeCellREF,TIM,1),1)
[/tt]
All you may need to do is tweek the TIM values for the SECOND before or after the time fence to get the return value to 'cross over' EXACTLY where you want it.

Don't mess with TWO DIFFERENT FORMULAE!!!!!!!



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

BTW,

My results for your time list...
[tt]
16:57:47 0.25
16:55:00 0.5
17:07:15 0
16:15:41 1
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlhelp]

Skip... Ian entering this after a long spell (in terms of posts), but shouldn't your formula be something like

=SUM(IF(C73>=TIME(17,0,0),0,IF(C73>=TIME(16,57,0),0.25,IF(C73>=TIME(16,45,0),0.5,IF(C73<TIME(16,45,0),1,0)))))

Canadian eh! Check out the new social forum Tek-Tips in Canada.
With the state of the world today, monkeys should get grossly insulted when humans claim to be their decendents

 

[ladyck3]

Ok, scratching the last star for now. Define Name I have used for drop down data validation lists... so I'm not sure what that help file told me or what I need to do.

So please help me understand where I put the TIM VAL table. My data on the page cannot be moved, so do I create a new worksheet?

From previous knowledge used as mentioned regarding Insert/Named/List I should name it =schedule ?
If I do this, how does this formula reference that named list? That's where I'm confused.

Also the table above does not reflect "1" if it is >15 minutes before log out so would I add 16:45:01 to TIM and make that VAL 1?

(and thanks for your help)

If I am accurate as above, creating the '=schedule' named list then I'm set to go further. If this is wrong, please help me to understand where I'm misunderstanding.


LadyCK3
aka: Laurie

 

[ladyck3]

xlhelp, that puts a where cells are empty and have no data.



LadyCK3
aka: Laurie

 

[ladyck3]

See that's the thing, dont know what to DO with the table... I'm getting a #NAME? error, which I expected as it does not know where the list is.


LadyCK3
aka: Laurie

 

[SkipVought]

1. your table on a new sheet

2. Select ALL data, including the headings.

3. Insert > Name > Create - Create name in TOP row

Now your data is Named by the Column Heading.

The previously posted formula reference the Range Names in the table. No need to give the TABLE a name.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ladyck3]

Skip, this is great. So if I were to create a different table, I can place it anywhere or does it have to be in cell A1 on a page.. oh I will play with that.

But now it makes sense I believe.. creating the table (Office 2007) under Formulas > Define Names > Create from Selection.

It lists Top, Bottom, Left, Right... choosing the column headers TIM and VAL, then the formula found it.

I can see where this might require a new page for each but maybe not... I'm not sure what Index does... does that tell the formula to go looking for that table?

Match tells it to look at the value in cell whatever and match it to the time, then place the value in the cell where the formula resides.

=INDEX Go find the named range where VAL and TIM are located

Whatever information in the CELL REF, "MATCHES" the data in the TIM column, then place the VAL in the cell where the forlmula is....

(VAL,MATCH(C102,TIM,1),1)

If it doesn't match anything in that table, then enter "1"

RIGHT? Did I get it?

Skip thanks for keeping on me... I should be able to do this for the login time too but I'll save that for another day.

I'm going home now (may play to night). Thanks SO MUCH.

I will not continue this thread, if I need more help I'll start a new one for help with Index and Match. This has gotten ridiculous but let me know if I've "got it". I think I do

HUGS AND MANY


LadyCK3
aka: Laurie

 

[SkipVought]

Table can be ANYWHERE. It makes sense, most of the time to have tables close to the TOP-LEFT part of the sheet -- hence A1. But not required.

MATCH returns an OFFSET within a range.

Index references a range, in this case a one-column range named VAL. Then MATCH returns a row offset and the 1 just says, it's the first column, DUH, well there's only ONE column in VAL.

If it doesn't match anything in that table, then enter "1"

RIGHT? Did I get it?

No. You'll notice 2 1's.

Match - the 1 means not an exact match, but a greater than match

Index - the 1 means FIRST COLUMN in VAL.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[kwbMitel]

I'm not trying to push this as the right solution (skip being the obvious expert here) but as one novice to another, this formula works. Here's a breakdown of what it means.

=IF(B67<=B2,0,IF(B67-B2<0.002083333,0.25,IF(AND(B67-B2>=0.002083333,B67-B2<0.010416667),0.5,IF(B67-B2>=0.010416667,1,"Bad Input"))))


Where B67 = actual start time
Where B2 = expected start time

=IF(B67<=B2,0,
If start time is less than or equal to expected value then result is Zero

IF(B67-B2<0.002083333,0.25,
If start time is less than 3 minutes more than expected value then result is 0.25

IF(AND(B67-B2>=0.002083333,B67-B2<0.010416667),0.5,
If start time is more than 3 minutes more AND less than 15 minutes more than expected value then result is 0.5

IF(B67-B2>=0.010416667,1,
If start time is Greater than 15 minutes more than expected value then result is 1

"Bad Input"))))
None of the expected parameters have been matched


*******************************************************
Occam's Razor - All things being equal, the simplest solution is the right one.

 

[combo]

Wow, I can see that the size of the thread doubled and I was awarded a star since my last logging - thanks Laurie.
If I can add a suggestion to my hint, to avoid extra calculations and for easier tracking the formula: one can replace overlapping IFs by exclusive ones, i.e.:
=IF(AND(A1<TIME(16,57,0),A1>=TIME(16,45,0)),0.25)+IF(A1<TIME(16,45,0)),1)
Names can simplify formula, personally I like to add constans as names, in 'refers to' it is possible to write value or formula (as =TIME(16,57,0)) instead of range.


combo