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

 

[xlhelp]

Laurie.

A hint:

3 mins is 0.0020833 of a day
15 mins is 0.0104166 of a day

If you still can't figure out, we'll teach you in Texas.

Also go to FAQ and read Skip's FAQ on Why Date and time cause so much trouble

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

 

[SkipVought]

Laurie,

Pretty cold in DFW area!

1. Use actual Time Values. So 3 minutes is TimeSerial(0,3,0)

2. Set up a TABLE using Duration (late Time Value) and Points

Sort in descending order and use

INDEX & MATCH -1


Skip,

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

 

[ladyck3]

Hey xlhelp and Skip...

Thanks for the tips... I will check these tips out later. I'm setting up my new laptop and I'm on a learning curve with Windows 7 (64bit) argh

Anyway, Index and Match... I know these exist, I'll play around with them before I holla

Thanks!!!

LadyCK3
aka: Laurie

 

[ladyck3]

Hi, I'm back... and I'm SO LOST. Here's what I've got.

Schedule:
Login - 8:00 a.m. (8th hr of the day is 8*60*60) = 28800 seconds
Log Out - 5:00 p.m. (17th hr 17*60*60) = 61200 seconds

0 Points: <28800
0.25 Pts: >28801 to 28980 (1 second to 3 minutes past)
0.5 Pts: >28981 to 29700 (3 min, 1 second to 15 minutes)
1 Point: >29700

So what I've tried, I can't figure out how to do the inbetween things. I know Skip, you said Index or Match but I stuck on this. In my limited but growing knowledge I understand one if statement but to add them together into a nested if statement... HELP.

xlhelp, you said a hint about the n'th of a day that 3 minutes is or 15 minutes but I think I'm kinda in the right area. I am SO FRUSTRATED and they are asking me for a finished product. I was in meetings all day today (Tuesday 1/12) so I've been frustrated at home tonight.

I tried using a formula of If's where I start with the greatest value... in my head that seemed the easiest
=if(b1>29700),1,if(b1>28981),0.5,if(b1>28801),0.25,if(b1<=28800),0)))))

but it didn't work.. excel bawked at the first If.

HELP!

I'm going to bed now.. and I don't expect anyone to answer tonight so I'll read you in the morning

Thanks for any guidance you might lend. If I start using these, I'll have a handle on it but I've never utilized a nested IF statement before... and the best way I learn is when I need it as it applies to something I can relate to... rather than samples of things that require imagination to comprehend... when it gets to formulas I need practical application to 'get it'.

Thanks


LadyCK3
aka: Laurie

 

[kwbMitel]

Ladyck3,

Excel is not my forte but math is and I'm learning too.

What skip and xlhelp are trying to tell you above is that time is not calculated by excel in the method you've assumed.

3 minutes actually calculates as 0.002083333
15 Minutes actually calculates as 0.010416667

You'll notice that the 2nd value is 5 times the first as one might expect.

It is my understanding that some posters on this forum are actually using this forum to do homework for school assignments. Hopefully you are not one of these and I am not breaking any rules by providing a specific answer to your question.

Here is the formula you need,

=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"))))

The Bad input part is if the formula cannot find a match based upon the data provided. Shouldn't happen as long as the data is input in the proper format.

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

 

[SkipVought]

Hers's the table
[tt]
TIM VAL
12:00 AM 0
8:00 AM 0.25
8:03 AM 0.5
8:15 AM 1
[/tt]
Name the ranges.

The formula...
[tt]
=INDEX(VAL,MATCH(C9,TIM,1),1)
[/tt]
where C9 contains the TIME VALUE of the [actual login time]


Skip,

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

 

[TonyJollans]

Hi Laurie,

Many ways to skin a cat, so, to do it as you first asked with a simple nested IF ...

[tt]=IF([/tt][blue][tt]B67[/tt][/blue][tt]>[/tt][green][tt]B2[/tt][/green][tt]+"00:15:00",1,IF([/tt][blue][tt]B67[/tt][/blue][tt]>[/tt][green][tt]B2[/tt][/green][tt]+"00:03:00",0.5,IF([/tt][blue][tt]B67[/tt][/blue][tt]>[/tt][green][tt]B2[/tt][/green][tt],0.25,0)))[/tt]

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

 

[combo]

Or without nesting IFs:
=SUM(IF(B67>TIME(8,0,0),0.25),IF(B67>=TIME(8,3,0),0.25),IF(B67>=TIME(8,15,0),0.5))

combo

 

[ladyck3]

Oh my, I logged into my work PC this morning and came directly here... what a wealth of information. I have a report to do and then I'm jumping in.

To kwbMitel: No this is not for school... but good looking out on that... its for work, they think I'm a guru and I keep telling them no I'm not (but I am a guru in training I guess)

Skip, that is so short and sweet, I am going to take a look at that first (working in order of 'ways to skin a cat') to see what works best but that's simple and I might actually retain that

TONY!!! <waving> Hi Tony, you probably don't remember me, I've not 'seen you' in ages.. you helped me with some numerology database stuff years ago... that still is not off the ground... another time. Back to the cats, this will help me get a grip on the nested if and using the time factor as you have is good, as all of my stuff is done in h:mm:ss this too is fantastic.

and combo... well this looks extremely interesting as well. I am going to work with all of them and see which is less frustrating and more straightforward for me personally.

It appears from first glance, these are all pretty spectacular. I'll be back to you all later this morning!

Thanks SO MUCH for your help...

Notta Guru, in Texas
(oh and Skip, it was up to 36 this morning, its getting warmer FINALLY)



LadyCK3
aka: Laurie

 

[SkipVought]

Supposed to climb to 60^oF today in DFW area.

Skip,

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

 

[TonyJollans]

Hi Laurie,

Yes I do remember! Shame to hear it's still not off the ground, but my computer is littered with half-started ideas, so I do know it happens.

I'm not quite following the temperature aspect of this thread, but I'm off out shortly and hoping (not really expecting, but hoping) it'll climb above 32.

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

 

[SkipVought]

Tony, its a Texas thing. North Texas winters are normally 6 - 8 weeks in Jan & Feb. I can still remember driving into Dallas from Boston on New Years Day 30 years ago, listening to The Cotton Bowl Game, with the windows down.

Skip,

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

 

[ladyck3]

Our temps last week were in the teens in the morning with windchills at zero.. for Texas that's cold. I was shocked and amazed when I moved here and the city beautification locations all had pansies blooming, IN JANUARY and the first week in February we were wearing short sleeves and shorts and flip flops... I've been here almost 19 years now and trust me, winter in Texas is near the blink of an eye, compared to "back home" in Wisconsin. They said yesterday, we had 56 WHOLE HOURS of SUB FREEZING Temps... We had snow on Christmas about 2 inches at DFW and that was the first time in recorded history that Dallas had snow of that accumulation at Christmas. Its been a strange year.. now can we say OFF TOPIC

I'm digging in, wish me luck.

Later


LadyCK3
aka: Laurie

 

[Loomah]

not pretty but i'm bored (again!) and it follows the 'more ways to skin a cat' rule...
=CHOOSE(MATCH(A1,{0,0.333344907407407,0.335416666666667,0.34375},1),0,0.25,0.5,1)

shame you can't put formulas into the array......or can you!

tony, i'm guessing that's 32 'old money'! london looks likely to grind to a halt again - nearly a cm of snow out there.

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[SkipVought]

Loomah,

Put the array in a range on the sheet, which is basically the SAME values as my TIM range. So you use CHOOSE with embedded values rather than an INDEX with my VAL range. Theme and Variations. I like Carnival of Venice myself.

Skip,

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

 

[ladyck3]

For comprehension purposes, I can manipulate Combo's recommendation the easiest.

kwbMitel
As Math goes, this is probably awesome but as I look at it my head is going whoosh whoosh whoosh... so I started with Skip first.

Skip
I get the table but I don't understand how to link the table and make it work... the formula seems to be sort of like a vlookup type configuration but I'm clueless.

Tony & Combo
Your "skinned cat" appears to be the most understood to me or at this point seems to be. It and Combo's works as well.

I can get the log in time to work great with each and I totally get it and its fabulous. BUT when I looked at Combo's to try and do the reverse for log out time, I got totally confused and then Tony's seemed to make more sense.

You see, I figured if I asked for help with one end of the spectrum, I'd be able to reverse it myself and not have to ask yet again.... HOWEVER its not working

For Tony's I tried this:
=IF(E59<$F$56-"00:15:00",1,IF(E59<$F$56-"00:03:00",0.5,IF(E59<$F$56,0.25,0)))

Where E59 is the actual time and F56 is the Scheduled time (Static)
Here were the results:

17:00:10 0
17:00:13 0
15:38:48 0 Should be 1
16:56:18 0 Should be .5
16:57:41 0 Should be .25 Right?

Combo, I tried yours and I got .75 responses I reversed the > to <
=SUM(IF(E19<TIME(17,0,0),0.25),IF(E19<=TIME(17,3,0),0.25),IF(E19<=TIME(17,15,0),0.5))

(different location in the spreadsheet) E19 contains the actual time.


17:00:10 0
17:00:13 0
15:38:48 1
16:56:18 1
16:57:41 1 Which is totally screwed up

Then when I pasted this into my message to you all, it dawned on me.. DUMB...

SO I changed the log out formula to
=SUM(IF(E19<TIME(17,0,0),0.25),IF(E19<=TIME(16,57,0),0.25),IF(E19<=TIME(16,45,0),0.5))

and VOILA, you guessed it
SUCCESs!!

17:00:10 0
17:00:13 0
15:38:48 1
16:56:18 0.5
16:57:41 0.25


COMBO WINS THE STARS... as this is the one that I was able to look at AND figure out most easily.


Tony, I tried using <b2="00:15:00" in the formula but that didn't work and quite frankly after all of the time and these 4 different scenarios I don't remember what happened but the results were wrong

Guys, this has been such a FANTASTIC learning experience... I want to thank you all but I have to award the star to Combo... y'all have received many stars from me in the past.

Incidentally, Skip, from your conversation are you too in the Dallas area?

Now that I have this part figured out, let the FUN being. Score cards and conditional formatting and yadda yadda yadda

Thanks SO MUCH!!!!!!!!!!!!!!!!!!!!!!!!




LadyCK3
aka: Laurie

 

[ladyck3]

Loomah, Skip... now your posts have me spinning, truly.




LadyCK3
aka: Laurie

 

[TonyJollans]

Laurie, Skip, .. Thank you. I've just worked out what DFW means.

Loomah, .. Yes. Although I can use either, I prefer what I learnt as a child, I guess. I have never understood why, in a democracy where the people quite clearly don't want it, the government keep trying to impose metric units.

It was actually 33 ([&#x00b0;]F), mid-afternoon - the weather here is the worst I can recall for nearly 30 years. The road outside is still only passable with care, although main routes are all clear.

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

 

[SkipVought]

Work in Grand Prairie.

For these kind of instances, where there could be multiple nested IF statements, I much prefer the table approch.

1) it is usually a simpler formula
2) it can be accomplished without hard coded values in the formula.
3) it is much easier to change the data values or increase the number of values
4) works for many values, when you exhaust the limit of nested IFs

I get the table but I don't understand how to link the table and make it work... the formula seems to be sort of like a vlookup type configuration but I'm clueless.

The table is not linked. It is referenced in the formula by using the Named Range NAMES: TIM refers to the time range and VAL refers to the values range. If you are not using Named Ranges, you are missing out on a hugely neat feature of Excel -- faq68-1331

Yes, INDEX & MATCH are very much like VLOOKUP, but a whole lot more flexible, IMHO. I rarely use VLOOKUP but most often use INDEX & MATCH.

Glad you found a solution that you feel comfortable with.

Skip,

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