# Find closest number based on string

#### robford16

##### New Member
Hello,

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

Rowtime stampevent #unitunit status
1
 1420089320

<tbody>
</tbody>
1ehold
2
 1420089448

<tbody>
</tbody>
1earrive
314200895001eavailable
4
 1420089653

<tbody>
</tbody>
2mhold
5
 1420090098

<tbody>
</tbody>
2marrive
6
 1420090377

<tbody>
</tbody>
2mhold
7
 1420090400

<tbody>
</tbody>
2mhold
8
 1420090412

<tbody>
</tbody>
2mavailable

<tbody>
</tbody>

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

##### Well-known Member
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:
Code:
``=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):
Code:
``=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

Last edited:

1,082,319
Messages
5,364,535
Members
400,804
Latest member
davileal

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...