(And by Excel, I mean MS Excel, Open Office, and Google Docs.)
I was recently working with a large amount of computer-generated event data. I wanted to analyze the data, but was only concerned with events (rows) that occurred within intervals demarcated by certain start and end events.
At the time, I had no answer for this in Excel. I wrote a small computer program that read the file one line at a time and ignored lines that occurred outside the intervals of interest. Recently I came up with a solution for this problem in Excel, so I thought I would share it here.
In this example, I am going to use a highly simplified traffic study as my example. A computer at a traffic light records 2 kinds of events:
- sensor events
- on or off, indicating whether or not there is a car in the intersection
- light events
- red, amber, or green, indicating the new light color
Here are some sample data collected by this computer:
| seconds | event | state |
|---|---|---|
| 0 | light | green |
| 7 | sensor | on |
| 8 | sensor | off |
| 15 | sensor | on |
| 16 | sensor | off |
| 25 | light | amber |
| 30 | light | red |
| 60 | light | green |
| 85 | light | amber |
| 90 | light | red |
| 92 | sensor | on |
| 93 | sensor | off |
| 120 | light | green |
| 145 | light | amber |
| 150 | light | red |
| 180 | light | green |
| 199 | sensor | on |
| 200 | sensor | off |
| 204 | sensor | on |
| 205 | light | amber |
| 206 | sensor | off |
| 210 | light | red |
| 240 | light | green |
| 265 | light | amber |
| 269 | sensor | on |
| 270 | light | red |
| 271 | sensor | off |
| 300 | light | green |
Let’s say we want to find out how many cars drove through a red light–that is, the light was red when the car started driving through the intersection.
First, add a new column. This column will indicate the current state of the light for each event. That’s trivial for each light event, but associating the state of the light with each sensor event is what we’re after. In this column, add the following formula:
Excel and Google Sheets:
=IF(B2="light",C2,D1)
Open Office Spreadsheets:
=IF(B2="light"; C2; D1)
That formula means:
- IF the current event is a light event
- THEN set this cell to the current state
- ELSE set this cell to the most recent light state.
Next, add another column. This column will indicate whether the row represents a driving through a red light. In this column, add the following formula:
Excel and Google Sheets
=IF(B2="sensor", IF(C2="on", IF(D2="red", 1, 0), 0), 0)
Open Office Spreadsheets
=IF(B2="sensor"; IF(C2="on"; IF(D2="red"; 1; 0); 0); 0)
The above is a nested series of if statements:
- IF the row contains a sensor event AND
- IF the sensor event is an on event AND
- IF the current state of the light is red
- THEN it is a traffic violation
- ELSE it is not a traffic violation
Copy these formulae to the other rows, via Edit–Fill–Down (Excel and Open Office) or ctrl-d (or cmd-d on Mac). The spreadsheet should now indicate that there was one incident of running a red light, which occurred at second 92.