Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using date and time of past crimes to project the next occurance
Trying to find the formulas to use to project the next probable occurance of
an event based on date and time....2 separate fields date in one time in the other. The events usually 3 or more. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using date and time of past crimes to project the next occurance
Are you working on the TV program "Numbers" <g
-- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "WR1CH" wrote in message ... Trying to find the formulas to use to project the next probable occurance of an event based on date and time....2 separate fields date in one time in the other. The events usually 3 or more. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using date and time of past crimes to project the next occurance
P(a)=No ways event can happen/No possible outcomes.
Take throwing a 6 with a standard dice. P(a)= 1/6 You need to be a lot more specific about what you are trying to predict if you want to incorporate date/time into the equation. "WR1CH" wrote: Trying to find the formulas to use to project the next probable occurance of an event based on date and time....2 separate fields date in one time in the other. The events usually 3 or more. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using date and time of past crimes to project the next occuran
Mike
I am trying to predict the next date/time period that another crime will occur based on the periods between the dates/times that have already occured. Thanks "Mike" wrote: P(a)=No ways event can happen/No possible outcomes. Take throwing a 6 with a standard dice. P(a)= 1/6 You need to be a lot more specific about what you are trying to predict if you want to incorporate date/time into the equation. "WR1CH" wrote: Trying to find the formulas to use to project the next probable occurance of an event based on date and time....2 separate fields date in one time in the other. The events usually 3 or more. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using date and time of past crimes to project the next occuran
Your best bet is to fit a curve to the 3 or more values
I'd ignore the times, if the dates are not too close The advantage of a chart, is that as more data is added the accuracy of the chart can be seen. Much better of course if you weren't using crimes as data :) Steve On Tue, 06 Feb 2007 19:57:00 -0000, WR1CH wrote: Mike I am trying to predict the next date/time period that another crime will occur based on the periods between the dates/times that have already occured. Thanks "Mike" wrote: P(a)=No ways event can happen/No possible outcomes. Take throwing a 6 with a standard dice. P(a)= 1/6 You need to be a lot more specific about what you are trying to predict if you want to incorporate date/time into the equation. "WR1CH" wrote: Trying to find the formulas to use to project the next probable occurance of an event based on date and time....2 separate fields date in one time in the other. The events usually 3 or more. -- Steve (3) |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using date and time of past crimes to project the next occuran
Steve
Thanks for the response, actually the times are important as well. In trying to predict a pattern for a crime...like burglary, we not only use date and time of day that it happened but location as well. Of course the location is mapped. We need both the date and time to predict what day and what time of day to patrol or stake out a potential area that a burglary, robbery or ? may take place. So I am trying to predict the next earliest and latest date and the time range within those dates that the incident may likely occur. That way police resources can be deployed with some statistical probability of success....hopefully. Rich "SteveW" wrote: Your best bet is to fit a curve to the 3 or more values I'd ignore the times, if the dates are not too close The advantage of a chart, is that as more data is added the accuracy of the chart can be seen. Much better of course if you weren't using crimes as data :) Steve On Tue, 06 Feb 2007 19:57:00 -0000, WR1CH wrote: Mike I am trying to predict the next date/time period that another crime will occur based on the periods between the dates/times that have already occured. Thanks "Mike" wrote: P(a)=No ways event can happen/No possible outcomes. Take throwing a 6 with a standard dice. P(a)= 1/6 You need to be a lot more specific about what you are trying to predict if you want to incorporate date/time into the equation. "WR1CH" wrote: Trying to find the formulas to use to project the next probable occurance of an event based on date and time....2 separate fields date in one time in the other. The events usually 3 or more. -- Steve (3) |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using date and time of past crimes to project the next occuran
I think you'd be better posting on a forum where the bad guys are
They obviously have the dates/times in a spreadsheet and plan there future crimes accordingly :) Steve On Wed, 07 Feb 2007 03:09:00 -0000, WR1CH wrote: Steve Thanks for the response, actually the times are important as well. In trying to predict a pattern for a crime...like burglary, we not only use date and time of day that it happened but location as well. Of course the location is mapped. We need both the date and time to predict what day and what time of day to patrol or stake out a potential area that a burglary, robbery or ? may take place. So I am trying to predict the next earliest and latest date and the time range within those dates that the incident may likely occur. That way police resources can be deployed with some statistical probability of success....hopefully. Rich "SteveW" wrote: Your best bet is to fit a curve to the 3 or more values I'd ignore the times, if the dates are not too close The advantage of a chart, is that as more data is added the accuracy of the chart can be seen. Much better of course if you weren't using crimes as data :) Steve On Tue, 06 Feb 2007 19:57:00 -0000, WR1CH wrote: Mike I am trying to predict the next date/time period that another crime will occur based on the periods between the dates/times that have already occured. Thanks "Mike" wrote: P(a)=No ways event can happen/No possible outcomes. Take throwing a 6 with a standard dice. P(a)= 1/6 You need to be a lot more specific about what you are trying to predict if you want to incorporate date/time into the equation. "WR1CH" wrote: Trying to find the formulas to use to project the next probable occurance of an event based on date and time....2 separate fields date in one time in the other. The events usually 3 or more. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using date and time of past crimes to project the next occuran
In other words you don't have a "clue".
"SteveW" wrote: I think you'd be better posting on a forum where the bad guys are They obviously have the dates/times in a spreadsheet and plan there future crimes accordingly :) Steve On Wed, 07 Feb 2007 03:09:00 -0000, WR1CH wrote: Steve Thanks for the response, actually the times are important as well. In trying to predict a pattern for a crime...like burglary, we not only use date and time of day that it happened but location as well. Of course the location is mapped. We need both the date and time to predict what day and what time of day to patrol or stake out a potential area that a burglary, robbery or ? may take place. So I am trying to predict the next earliest and latest date and the time range within those dates that the incident may likely occur. That way police resources can be deployed with some statistical probability of success....hopefully. Rich "SteveW" wrote: Your best bet is to fit a curve to the 3 or more values I'd ignore the times, if the dates are not too close The advantage of a chart, is that as more data is added the accuracy of the chart can be seen. Much better of course if you weren't using crimes as data :) Steve On Tue, 06 Feb 2007 19:57:00 -0000, WR1CH wrote: Mike I am trying to predict the next date/time period that another crime will occur based on the periods between the dates/times that have already occured. Thanks "Mike" wrote: P(a)=No ways event can happen/No possible outcomes. Take throwing a 6 with a standard dice. P(a)= 1/6 You need to be a lot more specific about what you are trying to predict if you want to incorporate date/time into the equation. "WR1CH" wrote: Trying to find the formulas to use to project the next probable occurance of an event based on date and time....2 separate fields date in one time in the other. The events usually 3 or more. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using date and time of past crimes to project the next occuran
No, seriously you will need some sort of curve fit
First step would be to use charting and try various curve fits. Use Line chart, then add trendline Probably polynomial But this is the thing, you can fit *any* curve through 3, 4 or 5 points, especially if the points are evenly spaced. The problem is that this would surely only mean that all future points on the line are potential *projection dates* Steve On Wed, 07 Feb 2007 03:25:00 -0000, WR1CH wrote: In other words you don't have a "clue". "SteveW" wrote: I think you'd be better posting on a forum where the bad guys are They obviously have the dates/times in a spreadsheet and plan there future crimes accordingly :) Steve On Wed, 07 Feb 2007 03:09:00 -0000, WR1CH wrote: Steve Thanks for the response, actually the times are important as well. In trying to predict a pattern for a crime...like burglary, we not only use date and time of day that it happened but location as well. Of course the location is mapped. We need both the date and time to predict what day and what time of day to patrol or stake out a potential area that a burglary, robbery or ? may take place. So I am trying to predict the next earliest and latest date and the time range within those dates that the incident may likely occur. That way police resources can be deployed with some statistical probability of success....hopefully. Rich "SteveW" wrote: Your best bet is to fit a curve to the 3 or more values I'd ignore the times, if the dates are not too close The advantage of a chart, is that as more data is added the accuracy of the chart can be seen. Much better of course if you weren't using crimes as data :) Steve On Tue, 06 Feb 2007 19:57:00 -0000, WR1CH wrote: Mike I am trying to predict the next date/time period that another crime will occur based on the periods between the dates/times that have already occured. Thanks "Mike" wrote: P(a)=No ways event can happen/No possible outcomes. Take throwing a 6 with a standard dice. P(a)= 1/6 You need to be a lot more specific about what you are trying to predict if you want to incorporate date/time into the equation. "WR1CH" wrote: Trying to find the formulas to use to project the next probable occurance of an event based on date and time....2 separate fields date in one time in the other. The events usually 3 or more. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using date and time of past crimes to project the next occuran
Lets say you live in an apartment complex. In the past month, a number of
vehicles parked in the unsecured apartment complex have been broken into. Items stolen included laptops, credit cards, purses, wallets, stereo equipment etc. Of those reported....some folks don't bother.....you have a list of the dates and times that the victims discovered and reported the crime. Times may or may not be accurate, because the victim might have been asleep and didn't discover they were a victim until the next morning. The apartment residents want some action and help to catch the criminal and you need to advise the boss when would be a good time to send undercover officers to the area to observe activities in the parking lot. The dates and times are not necessarily evenly spaced. Days vary from 1 to 5, 10,20 between incidents. Times vary within the 24 hour clock. However, you can see patterns in the data, i.e. the time may be between 1AM and 5AM, sometimes earlier or later but the majority are in one time frame. Days could be say always Wednesday or maybe Sunday and Thursday, or Tuesday, Wednesday and Thursday. There is a pattern there. So all I'm trying to do is find the formula(s) that will give the best probability of when the next incident will occur based on the days between past events and the time of day that past events occured. I know it can be done.....but not how to do it. Rich "SteveW" wrote: No, seriously you will need some sort of curve fit First step would be to use charting and try various curve fits. Use Line chart, then add trendline Probably polynomial But this is the thing, you can fit *any* curve through 3, 4 or 5 points, especially if the points are evenly spaced. The problem is that this would surely only mean that all future points on the line are potential *projection dates* Steve On Wed, 07 Feb 2007 03:25:00 -0000, WR1CH wrote: In other words you don't have a "clue". "SteveW" wrote: I think you'd be better posting on a forum where the bad guys are They obviously have the dates/times in a spreadsheet and plan there future crimes accordingly :) Steve On Wed, 07 Feb 2007 03:09:00 -0000, WR1CH wrote: Steve Thanks for the response, actually the times are important as well. In trying to predict a pattern for a crime...like burglary, we not only use date and time of day that it happened but location as well. Of course the location is mapped. We need both the date and time to predict what day and what time of day to patrol or stake out a potential area that a burglary, robbery or ? may take place. So I am trying to predict the next earliest and latest date and the time range within those dates that the incident may likely occur. That way police resources can be deployed with some statistical probability of success....hopefully. Rich "SteveW" wrote: Your best bet is to fit a curve to the 3 or more values I'd ignore the times, if the dates are not too close The advantage of a chart, is that as more data is added the accuracy of the chart can be seen. Much better of course if you weren't using crimes as data :) Steve On Tue, 06 Feb 2007 19:57:00 -0000, WR1CH wrote: Mike I am trying to predict the next date/time period that another crime will occur based on the periods between the dates/times that have already occured. Thanks "Mike" wrote: P(a)=No ways event can happen/No possible outcomes. Take throwing a 6 with a standard dice. P(a)= 1/6 You need to be a lot more specific about what you are trying to predict if you want to incorporate date/time into the equation. "WR1CH" wrote: Trying to find the formulas to use to project the next probable occurance of an event based on date and time....2 separate fields date in one time in the other. The events usually 3 or more. |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using date and time of past crimes to project the next occuran
I wish.......this is volunteer work at a police department.
"Bernard Liengme" wrote: Are you working on the TV program "Numbers" <g -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "WR1CH" wrote in message ... Trying to find the formulas to use to project the next probable occurance of an event based on date and time....2 separate fields date in one time in the other. The events usually 3 or more. |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using date and time of past crimes to project the next occuran
Rich, sorry to end up discussing the rights and wrongs
I think the brain is the best tool in this case. You really are after patterns, rather than formula That's my two-pennyworth Good luck and keep those eyes peeled Steve On Wed, 07 Feb 2007 15:08:01 -0000, WR1CH wrote: Lets say you live in an apartment complex. In the past month, a number of vehicles parked in the unsecured apartment complex have been broken into. Items stolen included laptops, credit cards, purses, wallets, stereo equipment etc. Of those reported....some folks don't bother.....you have a list of the dates and times that the victims discovered and reported the crime. Times may or may not be accurate, because the victim might have been asleep and didn't discover they were a victim until the next morning. The apartment residents want some action and help to catch the criminal and you need to advise the boss when would be a good time to send undercover officers to the area to observe activities in the parking lot. The dates and times are not necessarily evenly spaced. Days vary from 1 to 5, 10,20 between incidents. Times vary within the 24 hour clock. However, you can see patterns in the data, i.e. the time may be between 1AM and 5AM, sometimes earlier or later but the majority are in one time frame. Days could be say always Wednesday or maybe Sunday and Thursday, or Tuesday, Wednesday and Thursday. There is a pattern there. So all I'm trying to do is find the formula(s) that will give the best probability of when the next incident will occur based on the days between past events and the time of day that past events occured. I know it can be done.....but not how to do it. Rich "SteveW" wrote: No, seriously you will need some sort of curve fit First step would be to use charting and try various curve fits. Use Line chart, then add trendline Probably polynomial But this is the thing, you can fit *any* curve through 3, 4 or 5 points, especially if the points are evenly spaced. The problem is that this would surely only mean that all future points on the line are potential *projection dates* Steve On Wed, 07 Feb 2007 03:25:00 -0000, WR1CH wrote: In other words you don't have a "clue". "SteveW" wrote: I think you'd be better posting on a forum where the bad guys are They obviously have the dates/times in a spreadsheet and plan there future crimes accordingly :) Steve On Wed, 07 Feb 2007 03:09:00 -0000, WR1CH wrote: Steve Thanks for the response, actually the times are important as well. In trying to predict a pattern for a crime...like burglary, we not only use date and time of day that it happened but location as well. Of course the location is mapped. We need both the date and time to predict what day and what time of day to patrol or stake out a potential area that a burglary, robbery or ? may take place. So I am trying to predict the next earliest and latest date and the time range within those dates that the incident may likely occur. That way police resources can be deployed with some statistical probability of success....hopefully. Rich "SteveW" wrote: Your best bet is to fit a curve to the 3 or more values I'd ignore the times, if the dates are not too close The advantage of a chart, is that as more data is added the accuracy of the chart can be seen. Much better of course if you weren't using crimes as data :) Steve On Tue, 06 Feb 2007 19:57:00 -0000, WR1CH wrote: Mike I am trying to predict the next date/time period that another crime will occur based on the periods between the dates/times that have already occured. Thanks "Mike" wrote: P(a)=No ways event can happen/No possible outcomes. Take throwing a 6 with a standard dice. P(a)= 1/6 You need to be a lot more specific about what you are trying to predict if you want to incorporate date/time into the equation. "WR1CH" wrote: Trying to find the formulas to use to project the next probable occurance of an event based on date and time....2 separate fields date in one time in the other. The events usually 3 or more. |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using date and time of past crimes to project the next occuran
You would want an "XY (Scatter)" chart, not a "Line" chart.
The "Line" chart is very misleadingly named. It has nothing whatsoever to do with whether you want to fit a line to the data; rather a "Line" chart is a chart where the x axis consists of categories (possibly with numeric labels) instead of ordered numeric values. Why Excel would offer to fit a trend to categories is a mystery to me; but if you request it, then Excel will assume that the x values are 1,2,3,... even if you have provided different numeric labels for the categories. I.e. the results will be totally meaningless. Jerry "SteveW" wrote: No, seriously you will need some sort of curve fit First step would be to use charting and try various curve fits. Use Line chart, then add trendline Probably polynomial But this is the thing, you can fit *any* curve through 3, 4 or 5 points, especially if the points are evenly spaced. The problem is that this would surely only mean that all future points on the line are potential *projection dates* Steve |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using date and time of past crimes to project the next occuran
Yes, an XY chart would help you spot patterns
Good points about trrendlines and the x values. I had assumed that x would be dates and a full set of dates not just the offedning ones :) Steve On Wed, 07 Feb 2007 17:01:01 -0000, Jerry W. Lewis wrote: You would want an "XY (Scatter)" chart, not a "Line" chart. The "Line" chart is very misleadingly named. It has nothing whatsoever to do with whether you want to fit a line to the data; rather a "Line" chart is a chart where the x axis consists of categories (possibly with numeric labels) instead of ordered numeric values. Why Excel would offer to fit a trend to categories is a mystery to me; but if you request it, then Excel will assume that the x values are 1,2,3,... even if you have provided different numeric labels for the categories. I.e. the results will be totally meaningless. Jerry "SteveW" wrote: No, seriously you will need some sort of curve fit First step would be to use charting and try various curve fits. Use Line chart, then add trendline Probably polynomial But this is the thing, you can fit *any* curve through 3, 4 or 5 points, especially if the points are evenly spaced. The problem is that this would surely only mean that all future points on the line are potential *projection dates* Steve |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combine Date & Time Cells | Excel Discussion (Misc queries) | |||
How to calculate Date & Time differences | Excel Worksheet Functions | |||
Using VLOOKUP with a Date and Time | Excel Discussion (Misc queries) | |||
How do I calculate if a date is in a certain time frame? | Excel Worksheet Functions | |||
Imported Date & Time format with calcs. managed in excel from imrp | Excel Worksheet Functions |