Find closest number based on string

[robford16]

Hello,

I am trying to figure out a way to find the closest number to another number. Here is what my spreadsheet looks like:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Row[/TD]
[TD]time stamp[/TD]
[TD]event #[/TD]
[TD]unit[/TD]
[TD]unit status[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="class: xl63, width: 77"]1420089320[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[TD]e[/TD]
[TD]hold[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="class: xl65, width: 77"]1420089448[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[TD]e[/TD]
[TD]arrive[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1420089500[/TD]
[TD]1[/TD]
[TD]e[/TD]
[TD]available[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="class: xl65, width: 77"]1420089653[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2[/TD]
[TD]m[/TD]
[TD]hold[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="class: xl65, width: 77"]1420090098[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2[/TD]
[TD]m[/TD]
[TD]arrive[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="class: xl65, width: 77"]1420090377[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2[/TD]
[TD]m[/TD]
[TD]hold[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="class: xl65, width: 77"]1420090400[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2[/TD]
[TD]m[/TD]
[TD]hold[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="class: xl65, width: 77"]1420090412[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2[/TD]
[TD]m[/TD]
[TD]available[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I am trying to find the average time between all instances of arrive-hold and arrive-available for each unit (average time on hold for each unit for each event). For example, I would want to calculate the difference between row 3 and 4, and row 5 (because it is the first hold instance) and 7. Sometimes units have multiple/varying amounts of hold time stamps before they are marked arrive (as in rows 5 and 6). So the formula would have to look for the first hold time stamp, then look for the next closest arrive or available time stamp AND make sure the unit and event number match.

My timestamps are in epoch seconds. I am using Excel 2010 64-bit.

Thank you!!

 

[Mackers]

Hi

I'm not sure I understand what you're looking for, but hopefully the following will help you solve your problem. On the basis you want a 'wait time' for each hold period, the way I'd solve this is to use the following logic in a formula for each row:

1. Is the status hold? If not, don't return anything. If so, continue.
2. Find the minimum time that meets all of my criteria, namely (a) that the time is after the current time, (b) that the event matches, and (c) that the unit matches.
3. Subtract the hold timestamp from this timestamp to find the time difference.

Then I would add a pivot table that aggregated the values on a unit basis.

If your timestamps are in B2:B9, your event is in C2:C9, your unit is in D2:D9 and your status is in E2:E9, then in F2 write the following and execute using control+shift+enter:

Minimum time after:

=IF(E2<>"hold","",MIN(IF(C2=$C$2:$C$9,IF(D2=$D$2:$D$9,IF(B2<$B$2:$B$9,$B$2:$B$9,9E+37)))))

Time difference between minimum time after and current timestamp (you can consolidate these two formulas into a single formula if you prefer):

=IF(F2="","",F2-B2)

Hope that helps - note that if there is no status following the hold it will return a very wacky result.

Mackers