Tag Archives: Formula

Aoristic Analysis Example

1 Oct

I have started analyzing crime. The data I get has the time of the crime being the time the report was filed. So, on Monday at 8:00 there are a lot of car burglaries — well, reports of car burglary. When the crime is reported and when it happened are two different things and need to be kept separate. But how does someone who is sleeping know when their car was broken in to?

Digging deeper in to the reports, I can get an estimated start and end time that a crime may have occurred between.  Using Aoristic analysis, I can assign weights to the times to come up with a better understanding of when the highest probability times for certain crimes may be. Here is how it works:

In Excel create a sheet with columns for start and end and then one for each hour of the day 00:00 to 23:00.

For each incident note the start and end time, then count the time between them and mark the value of each hour divided by the total hours.

Start     End          …    1:00   2:00    3:00    4:00    5:00   6:00    …..

2:00    6:00                              .25      .25        .25        .25

3:00    4:00                                            1

1:30     6:30                 .166    .166     .166    .166     .166    .166

SUM                               .166     .416    1.416    .416     .416    .166

The probability is the SUM/Number of incidents (in this case 3)

Each time (1:00) is really 1:00-1:59. So if I crime occurred between 1:00 and 6:00- 1,2,3,4 and 5 are selected, 6 is not.

The highest probability for this type of crime to occur would be at 3:00.  The more data you have the better your results. And use common sense when looking at the results. In my studies, the results have made sense. I have noticed Auto Burglary in the late night and early morning. Home burglary has occurred while people were at work from 9:00 to 4:00.  This makes sense and is shown with the data.

Military Time in Excel

1 Oct

I was working with military time in Excel and needed to calculate the hours:minutes:seconds between two times. The answer seemed simple – subtract the end time from the start time (1:30 to 2:00 is 2:00-1:30= 00:30.). This worked except when the start time was at around 23:45 and the end time was the next day at 00:34.  Here is my solution:

Column B (Start Time)                                  Column C (End Time)

23:51                                                                                00:01

=if( C1>B1, C1-B1, (24-B1)+C1) will give you 00:10

What the formula does is determine if it can just subtract the end from the start. If the start time is greater than the end time (the time ends the next day), the formula subtracts the end time from 24 to get the remaining time left in the day then adds the time from the next day. So in the example 24-23:51 = 9 then add 9 + 00:01 = 00:10.

Not exactly rocket science, but it works. A lot of the answers I found online suggest converting to other formats then back to military time and blah blah blah. Too much work.  I need a simple solution and found one. So far it has worked.

Report Revit Unconnected Wall Height

16 Apr

Saw someone searching for this. Revit does not report Unconnected wall height so how can you put it in a schedule?


Area = Length*Height

Revit reports Area and Length. Solve for Height and:

Unconnected Wall Height = Area/Length

Create a Calculated Value Parameter and put in that formula to report unconnected height.