Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Random Times
Ok. I guess the government had to go and make this even more difficult for
me. I need to figure out how to get random times for check during one day using an Excel spreadsheet. They start at 6:00 a.m. up until about 6:00 p.m. They have to be no more than 55 minutes apart, but less than 15 min apart. They cannot be between 10:00 am and 10:30 am. I received a response to my last question which was between 6 and 6pm, no more than 55 min apart and not between 10 and 10:30 am. I set it up. It works wonders. But, now I'm told they have to be at least 15 min apart. I tried to adjust the formula accordingly, but I just can't figure it out. Can anyone help me please??? |
#2
|
|||
|
|||
1. post the oringinal solution so I don't have to completely start from
scratch. 2. How many times need to be gererated for each day? "Tiffany" wrote: Ok. I guess the government had to go and make this even more difficult for me. I need to figure out how to get random times for check during one day using an Excel spreadsheet. They start at 6:00 a.m. up until about 6:00 p.m. They have to be no more than 55 minutes apart, but less than 15 min apart. They cannot be between 10:00 am and 10:30 am. I received a response to my last question which was between 6 and 6pm, no more than 55 min apart and not between 10 and 10:30 am. I set it up. It works wonders. But, now I'm told they have to be at least 15 min apart. I tried to adjust the formula accordingly, but I just can't figure it out. Can anyone help me please??? |
#3
|
|||
|
|||
OK, I got it, interesting little problem
First column header, Check Number, second is Check Time. Just number the forst column i to say 30. First cell next the the first check, enter this formula: =RAND()/24+(6/24) (to change the start time, change the 6 to the new hour to start) Cell below that, and copy to every other cell downwards: =((RAND()*40+15)/24/60)+B2 ( to change the minimum time, change the 15. then change the maximum time, change the 40 in this way: for 45, 45 - 15 is 30, so replace the 40 with 30.) If you have trouble, post back. "Tiffany" wrote: Ok. I guess the government had to go and make this even more difficult for me. I need to figure out how to get random times for check during one day using an Excel spreadsheet. They start at 6:00 a.m. up until about 6:00 p.m. They have to be no more than 55 minutes apart, but less than 15 min apart. They cannot be between 10:00 am and 10:30 am. I received a response to my last question which was between 6 and 6pm, no more than 55 min apart and not between 10 and 10:30 am. I set it up. It works wonders. But, now I'm told they have to be at least 15 min apart. I tried to adjust the formula accordingly, but I just can't figure it out. Can anyone help me please??? |
#4
|
|||
|
|||
Your problem statement is somewhat ambiguous.
Do your *checks* start at 6:00 am (i.e, 6:00 am every day), or should the first check start between 6:00 am and, say, 6:55 am? If no checks can happen between 10:00 am and 10:30 am, are you still required to meet the 55 minute maximum? If so, you'll probably need an iterative process and checks will be clustered just before 10:00 and just after 10:30. If you have to have one check between 6:00 and 6:55, and you can just skip any checks falling between 10:00 and 10:30, you can use something like this: A1: =TIME(6, RAND()*55, 0) A2: =A1 + TIME(0, 15+RAND()*40, 0) and copy down through A49, discarding anything after 6:00 pm or between 10:00 and 10:30. In article , "Tiffany" wrote: Ok. I guess the government had to go and make this even more difficult for me. I need to figure out how to get random times for check during one day using an Excel spreadsheet. They start at 6:00 a.m. up until about 6:00 p.m. They have to be no more than 55 minutes apart, but less than 15 min apart. They cannot be between 10:00 am and 10:30 am. I received a response to my last question which was between 6 and 6pm, no more than 55 min apart and not between 10 and 10:30 am. I set it up. It works wonders. But, now I'm told they have to be at least 15 min apart. I tried to adjust the formula accordingly, but I just can't figure it out. Can anyone help me please??? |
#5
|
|||
|
|||
Hmm... Not sure why you chose RAND()/24 for the first entry. Granted,
Tiffany's problem was ambiguous, but I would have thought that the first check should happen between 6:00 and 6:55. Yours allows a start as late as 7:00. Also, your 2nd formula does not account for there being no checks allowed between 10:00 and 10:30 am. In order to be "random" and still meet the no more than 55 minutes, this would require an iterative process. Of course, I have no idea if that requirement is imposed. In article , Rob wrote: OK, I got it, interesting little problem First column header, Check Number, second is Check Time. Just number the forst column i to say 30. First cell next the the first check, enter this formula: =RAND()/24+(6/24) (to change the start time, change the 6 to the new hour to start) Cell below that, and copy to every other cell downwards: =((RAND()*40+15)/24/60)+B2 ( to change the minimum time, change the 15. then change the maximum time, change the 40 in this way: for 45, 45 - 15 is 30, so replace the 40 with 30.) If you have trouble, post back. |
#6
|
|||
|
|||
Ok. This is what I started with last week:
A1 is 6:00, B1 is =AND(NOT(AND(A2=TIMEVALUE("10:00:00"),A2<=TIMEVAL UE("10:30:00"))),A2<TIMEVALUE("6:00:00 PM")) A2 is =A2+RAND()*55/1440 This provides random times between 6 am and 6 pm that are at least 55 minutes apart and not between 10 and 10:30 am. The problem is, I cannot get them to be at least 15 minutes apart. Somtimes the random times produced are like 11:00, then 11:02 then 11:05. That's not enough time for the QC techs to do their actual checks. I do not believe the first check is at 6:00 am, as this is not a random time every day. The first check has to be between 6 am and 6:55. The reason I'm doing this is to make it almost automated. I have not tried the formula's you gave me, but I will. I wanted to explain the situation to you first. Thanks for your help! "JE McGimpsey" wrote: Your problem statement is somewhat ambiguous. Do your *checks* start at 6:00 am (i.e, 6:00 am every day), or should the first check start between 6:00 am and, say, 6:55 am? If no checks can happen between 10:00 am and 10:30 am, are you still required to meet the 55 minute maximum? If so, you'll probably need an iterative process and checks will be clustered just before 10:00 and just after 10:30. If you have to have one check between 6:00 and 6:55, and you can just skip any checks falling between 10:00 and 10:30, you can use something like this: A1: =TIME(6, RAND()*55, 0) A2: =A1 + TIME(0, 15+RAND()*40, 0) and copy down through A49, discarding anything after 6:00 pm or between 10:00 and 10:30. In article , "Tiffany" wrote: Ok. I guess the government had to go and make this even more difficult for me. I need to figure out how to get random times for check during one day using an Excel spreadsheet. They start at 6:00 a.m. up until about 6:00 p.m. They have to be no more than 55 minutes apart, but less than 15 min apart. They cannot be between 10:00 am and 10:30 am. I received a response to my last question which was between 6 and 6pm, no more than 55 min apart and not between 10 and 10:30 am. I set it up. It works wonders. But, now I'm told they have to be at least 15 min apart. I tried to adjust the formula accordingly, but I just can't figure it out. Can anyone help me please??? |
#7
|
|||
|
|||
OK, you resolved one of the ambiguities (I think - was your A2 example
supposed refer to A1 rather than being a circular reference?), but I'm not sure about the other. Perhaps a specific example: Say a series of random checks is 06:01 06:34 06:57 07:40 08:22 08:37 09:06 09:46 Since there must be 15 minutes between checks, no more than 55, and none between 10:00 and 10:30, that would seem to indicate that the next check must happen between 10:31 and 10:41. Is that correct? If the last check had been 09:40, would that then mean that the next check would be either 09:55-09:59 or 10:31-10:35? In either case, that will significantly "unrandomize" the checks - they'll happen much more frequently in the 10 minutes before or 10 minutes after the 10:00-10:30 window than at any other time of the day. OTOH, if any randomly generated time between 10:00 and 10:30 could be skipped, i.e., that the 10:14 check in: 09:06 09:46 10:14 10:59 was skipped, allowing a 1:13 gap between checks, there would be no compression around 10:00-10:30. In article , "Tiffany" wrote: Ok. This is what I started with last week: A1 is 6:00, B1 is =AND(NOT(AND(A2=TIMEVALUE("10:00:00"),A2<=TIMEVAL UE("10:30:00"))),A2<TIMEVALU E("6:00:00 PM")) A2 is =A2+RAND()*55/1440 This provides random times between 6 am and 6 pm that are at least 55 minutes apart and not between 10 and 10:30 am. The problem is, I cannot get them to be at least 15 minutes apart. Somtimes the random times produced are like 11:00, then 11:02 then 11:05. That's not enough time for the QC techs to do their actual checks. I do not believe the first check is at 6:00 am, as this is not a random time every day. The first check has to be between 6 am and 6:55. The reason I'm doing this is to make it almost automated. I have not tried the formula's you gave me, but I will. I wanted to explain the situation to you first. Thanks for your help! |
#8
|
|||
|
|||
Tiffany,
Simply change: =A2+RAND()*55/1440 to =A2+(15 + RAND()*40)/1440 HTH, Bernie MS Excel MVP "Tiffany" wrote in message ... Ok. This is what I started with last week: A1 is 6:00, B1 is =AND(NOT(AND(A2=TIMEVALUE("10:00:00"),A2<=TIMEVAL UE("10:30:00"))),A2<TIMEVA LUE("6:00:00 PM")) A2 is =A2+RAND()*55/1440 This provides random times between 6 am and 6 pm that are at least 55 minutes apart and not between 10 and 10:30 am. The problem is, I cannot get them to be at least 15 minutes apart. Somtimes the random times produced are like 11:00, then 11:02 then 11:05. That's not enough time for the QC techs to do their actual checks. I do not believe the first check is at 6:00 am, as this is not a random time every day. The first check has to be between 6 am and 6:55. The reason I'm doing this is to make it almost automated. I have not tried the formula's you gave me, but I will. I wanted to explain the situation to you first. Thanks for your help! "JE McGimpsey" wrote: Your problem statement is somewhat ambiguous. Do your *checks* start at 6:00 am (i.e, 6:00 am every day), or should the first check start between 6:00 am and, say, 6:55 am? If no checks can happen between 10:00 am and 10:30 am, are you still required to meet the 55 minute maximum? If so, you'll probably need an iterative process and checks will be clustered just before 10:00 and just after 10:30. If you have to have one check between 6:00 and 6:55, and you can just skip any checks falling between 10:00 and 10:30, you can use something like this: A1: =TIME(6, RAND()*55, 0) A2: =A1 + TIME(0, 15+RAND()*40, 0) and copy down through A49, discarding anything after 6:00 pm or between 10:00 and 10:30. In article , "Tiffany" wrote: Ok. I guess the government had to go and make this even more difficult for me. I need to figure out how to get random times for check during one day using an Excel spreadsheet. They start at 6:00 a.m. up until about 6:00 p.m. They have to be no more than 55 minutes apart, but less than 15 min apart. They cannot be between 10:00 am and 10:30 am. I received a response to my last question which was between 6 and 6pm, no more than 55 min apart and not between 10 and 10:30 am. I set it up. It works wonders. But, now I'm told they have to be at least 15 min apart. I tried to adjust the formula accordingly, but I just can't figure it out. Can anyone help me please??? |
#9
|
|||
|
|||
Well, setting up the auto filter with true/false seems to prevent that from
happening. I just keep selecting true for the filter and eventually they all show true. But, as opposed to the system they had set up previously which took about a half an hour to produce one days checks, I think this works better. I used the formula's you gave me and was able to produce times of 9:19 and then 10:42. I think I've finally figured it out, thanks to you, and can make the qc dept happy finally! Thanks so much for your help! I've been working on this for at least a week or so. "JE McGimpsey" wrote: OK, you resolved one of the ambiguities (I think - was your A2 example supposed refer to A1 rather than being a circular reference?), but I'm not sure about the other. Perhaps a specific example: Say a series of random checks is 06:01 06:34 06:57 07:40 08:22 08:37 09:06 09:46 Since there must be 15 minutes between checks, no more than 55, and none between 10:00 and 10:30, that would seem to indicate that the next check must happen between 10:31 and 10:41. Is that correct? If the last check had been 09:40, would that then mean that the next check would be either 09:55-09:59 or 10:31-10:35? In either case, that will significantly "unrandomize" the checks - they'll happen much more frequently in the 10 minutes before or 10 minutes after the 10:00-10:30 window than at any other time of the day. OTOH, if any randomly generated time between 10:00 and 10:30 could be skipped, i.e., that the 10:14 check in: 09:06 09:46 10:14 10:59 was skipped, allowing a 1:13 gap between checks, there would be no compression around 10:00-10:30. In article , "Tiffany" wrote: Ok. This is what I started with last week: A1 is 6:00, B1 is =AND(NOT(AND(A2=TIMEVALUE("10:00:00"),A2<=TIMEVAL UE("10:30:00"))),A2<TIMEVALU E("6:00:00 PM")) A2 is =A2+RAND()*55/1440 This provides random times between 6 am and 6 pm that are at least 55 minutes apart and not between 10 and 10:30 am. The problem is, I cannot get them to be at least 15 minutes apart. Somtimes the random times produced are like 11:00, then 11:02 then 11:05. That's not enough time for the QC techs to do their actual checks. I do not believe the first check is at 6:00 am, as this is not a random time every day. The first check has to be between 6 am and 6:55. The reason I'm doing this is to make it almost automated. I have not tried the formula's you gave me, but I will. I wanted to explain the situation to you first. Thanks for your help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Seed numbers for random number generation, uniform distribution | Excel Discussion (Misc queries) | |||
Charting and analyzing Times' times data for trends | Excel Discussion (Misc queries) | |||
Random Times | Excel Worksheet Functions | |||
Selecting at random with weighted probability | Excel Worksheet Functions | |||
Formulas stop calculating at random times when editing a few spreadsheets. | Excel Discussion (Misc queries) |