• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Covid analysis using Excel

Iceman00

New Member
Hi

How to explain this. I have list of people who have all developed Covid symptoms. I have their gate times so all their movement around site is tracked and I'm trying to work out if they've come into contact with each other and that's how they caught the virus.

I know I can use a macro to do this but want to check if it can be done with formulas first.

In column A I have date and time in 10 minute intervals for several weeks. Each person is in column B, C, and D etc with which gate they went through. I'm trying to write a formula so it can look down a list of gate times for someone and plot it in the correct place in my 10 minute intervals. I can do this up to a point see below. The formula I have is-

=IF(AND(E$1>A4,E$1<A5),$F$1,IF(AND(E$2>A4,E$2<A5),$F$2,IF(AND(E$3>A4,E$3<A5),$F$3,IF(AND(E$4>A4,E$4<A5),$F$4,IF(AND(E$5>A4,E$5<A5),$F$5,IF(AND(E$6>A4,E$6<A5),$F$6,""))))))

E1-E6 = Date and Time of person
F1-F6 = which gate they went through

Info. at the bottom is column A and B. This formula goes in column B.

The issue is this only covers 6 lines of gate entries for someone. Some people can have a few hundred gate times so this is not practical! And I have 200 people to look at which I need to put side by side.

Can anyone think of an easier formula to write rather than repeating the one above hundreds of times? I've attache a file for clarity.

Thanks

Mike

Person A
17/06/2020 08:00​
17/06/2020 08:10​
17/06/2020 08:20​
Main Gate1
17/06/2020 08:30​
17/06/2020 08:40​
 

Attachments

Chihiro

Excel Ninja
Not sure what exactly you are trying to do...

But I'd recommend adding helper column in E1:F6 data range.

Ex: in G1:
=FLOOR.MATH(E1,1/24/6)
Copy down.

This will round down the datetime value to nearest 10 min interval.

So any value between 6/17/2020 8:20:00 to 6/17/2020 8:29:59 will result in 6/17/2020 8:20 slot.

Then your formula in B column becomes.
=IFERROR(INDEX(F:F,AGGREGATE(15,6,ROW($F$1:$F$6)/($H$1:$H$6=A1),1)),"")

Copy down.
 

deciog

Active Member
Using formula by colleague Chihiro in G1

My suggestion

=IFERROR(INDEX($F$1:$F$6,AGGREGATE(15,6,ROW($F$1:$F$6)/(MID(TEXT($G$1:$G$6,"####0,00000"),1,10)*1=MID(TEXT(A1,"####0,00000"),1,10)*1),1)),"")

Decio
 
Last edited:

Iceman00

New Member
Hi. Thanks both of you for replying so quick! Both ways work great. Knew they'd be an easier way than what I was doing. I think I'm going to double it up and also report not only the gate name but also the exact time through the gate so its easier to compare with anyone else as below. As a 10 minute window is quite big and don't want to reduce to 5 minute intervals. Thanks for your help really appreciate it. Knew this would be a good forum to join! For completion here's the final version so it shows the gate name and the exact time from the clock in/outs. That should work nicely. Thanks

=IFERROR(INDEX(F:F,AGGREGATE(15,6,ROW($F$1:$F$6)/($G$1:$G$6=A3),1)),"")&" "&TEXT(IFERROR(INDEX(E:E,AGGREGATE(15,6,ROW($F$1:$F$6)/($G$1:$G$6=A3),1)),""),"dd/mm/yyyy hh:mm")
 

deciog

Active Member
Iceman00

In the spreadsheet on lines 33 and 45, the formula does not show the gate, so I used 5 final digits only

Decio
 

Iceman00

New Member
Hi. The Janus Entry 1-6 is the gate name. I just didn't want to share the actual names so I simplified it. Thanks again.
 
Top