Find closest number based on string

robford16

New Member
Joined
Jan 11, 2016
Messages
1
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
Joined
Nov 4, 2013
Messages
532
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:

Forum statistics

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

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top