Meeting room data

[jappi]

Hi - hoping someone may be able to help please. This is way beyond my level of knowledge:

I have one set of data from a meeting room booking system (booked data) in one tab and another set of data from sensors in the meeting room (actual data) in another tab. I want to compare / match various elements of the 2 sets of data to determine if the booked meeting actually took place.

Sample of booked data:

Booking Date Room Event Start Event End
2/3/18 Room 10.1 8:30 am 9:30 am


Sample of actual data:

Time Room In Use
2018-03-02 08:00:00 +1000 AEST Room 10.1 0

Other things to know:
- There will be multiple rooms so I also need to match the room names across each data set.
- The booked data will only ever have one row for a meeting for that given room and start / end time
- The actual data can have many rows of data that has a time that falls within between the Event Start and Event end times from the booked data. This is because the sensors are sending data back every 2 minutes for each seat in the room. For example, if we had 4 seats in Room 10.1, each with a sensor and a 60min meeting, we would have 120 lines of data that may have a time stamp that falls between the meeting start / end time from the booked data.
- The "In Use" element on the actual data is either 0 (seat not in use) or 1 (seat in use). If we have a 1 on any of the lines of actual data that fall within the booked window, then by definition the meeting took place because at least 1 seat was occupied.
- The date / time formats shown above is as it is in Excel

So - in my dumb speak the logic would be something like:

Where actual room name = booked room name and;
actual date = booked date and;
actual time is => event start and =< event end and;
actual in use is = 1 then;
mark row of booking data with "meeting happened" else;
mark row of booking data with "meeting did not happen"

Hope this makes sense and thanks in advance for any help.

 

[RasGhul]

All good Jappi,

So in your Actual Data Sample we need to extract the Dates & Times from Column A

Column D is using
=DATE(LEFT(A2,4),MID(A2,6,2),MID(A2,9,2))

Column E is using
=TIME(MID(A2,12,2),MID(A2,15,2),MID(A2,18,2))

In column C in Actual Data Sample the formula is counting each time a 1 appears eg rows 19,20,21 and so on. So 1 is the counting criteria.

So here's the logical breakdown of the last formula on values from Output Sample;
=IF(COUNTIFS(Does Column D match,Date,Does Column B match,"Room 10.1",Count rows in Column C "In Use",1,>="Event Start",<="Event End")>0,"Yes","No")

Here is the formula used based on your original "Output Sample"

Book1
	A	B	C	D	E
1	Booking Date	Room	Event Start	Event End	Meeting Occurred
2	02-03-18	Room 10.1	8:30 AM	9:30 AM	Yes
3	02-03-18	Room 10.1	10:00 AM	10:45 AM	Yes
4	02-03-18	Room 10.1	11:30 AM	12:00 PM	Yes
5	02-03-18	Room 10.1	1:00 PM	2:00 PM	Yes
6	02-03-18	Room 10.1	2:30 PM	3:30 PM	No
7	02-03-18	Room 10.1	3:30 PM	4:00 PM	No
8	02-03-18	Room 10.1	4:00 PM	4:30 PM	No
9	02-03-18	Room 10.1	4:30 PM	5:30 PM	No
Output Sample

Cell Formulas
Range	Formula
E2	=IF(COUNTIFS('Actual Data Sample'!$D$2:$D$145,A2,'Actual Data Sample'!$B$2:$B$145,B2,'Actual Data Sample'!$C$2:$C$145,1,'Actual Data Sample'!$E$2:$E$145,">="&C2,'Actual Data Sample'!$E$2:$E$145,"<="&D2)>0,"Yes","No")

 

[jappi]

ok - thanks. This all makes perfect sense. Rewind to the example where you showed the "sensor count" column. How do I get that in the results above please? This is the last thing I need...I promise!!!

 

[RasGhul]

No Problemo,

Book1
	A	B	C	D	E	F
1	Booking Date	Room	Event Start	Event End	Sensor Counts	Meeting Occurred
2	02-03-18	Room 10.1	8:30 AM	9:30 AM	8	Yes
3	02-03-18	Room 10.1	10:00 AM	10:45 AM	10	Yes
4	02-03-18	Room 10.1	11:30 AM	12:00 PM	1	Yes
5	02-03-18	Room 10.1	1:00 PM	2:00 PM	3	Yes
6	02-03-18	Room 10.1	2:30 PM	3:30 PM	0	No
7	02-03-18	Room 10.1	3:30 PM	4:00 PM	0	No
8	02-03-18	Room 10.1	4:00 PM	4:30 PM	0	No
9	02-03-18	Room 10.1	4:30 PM	5:30 PM	0	No
Output Sample

Cell Formulas
Range	Formula
E2	=COUNTIFS('Actual Data Sample'!$D$2:$D$145,A2,'Actual Data Sample'!$B$2:$B$145,B2,'Actual Data Sample'!$C$2:$C$145,1,'Actual Data Sample'!$E$2:$E$145,">="&C2,'Actual Data Sample'!$E$2:$E$145,"<="&D2)

 

[jappi]

Thanks RasGhul - you've ben a huge help and I really appreciate your patience and knowledge.

 

[RasGhul]

Youre welcome

 

[jappi]

Hi RasGhul - I need to revisit this one to go a little deeper. Wondering if you can apply some of your magic again please. My working sheet is on the link below:

https://www.dropbox.com/s/qaibewdd1w8c72q/L34 Working.xlsx?dl=0

Exchange data tab - meetings that that have been booked on Exchange. with Date, start and end time

Sensor Data - data from the sensors that are at each seat in the meeting room. The data has some existing derived columns based on what you gave me before to pull out date and time. For background......the sensors are sending data for every seat each 2 minutes in the hour. You will see I have now added columns for all sensor hits for each seat in each room across the hour. Data goes from right to left starting on the "hour start time". 1 is seat occupied and 0 is no presence. Rows that have no data in them at all had no presence for any seat for the hour i.e. rooms empty so mark meeting as did not occur.

Output tab - as per below, where I need results to go.

What our previous data was missing is any meeting that might have occurred inside the hour. e.g. from 10:15 - 10:45. We were assuming that did not happen because we could not see data to match inside the hour. This is erroneous to the outcome. So this is what I need the formula on the output page to do please:

1. Check the Exchange meeting start and end time for each room
2. Using the meeting start and end time from Exchange, search through the sensor data to the corresponding "hour start time", then look across the columns to find the 2 min intervals that fall within (or equal to) the meeting start and end time
3. Look for any 1 values that fall inside (and including) the meeting start and end time. If there is at least a single 1 value anywhere in that interval then mark meeting as "happened"
4. Else mark meeting as not happened
5. The total sensor counts across all the 2 minute intervals for each meeting would be good as well as per the current column on the Output tab.

Put simply, are there any sensor hits that occurred during the meeting start and end time. If at least one seat anywhere in the room during that time was occupied, then we assume the meeting did occur otherwise it did not.

 

[RasGhul]

Hi Jappi,

1.
Ok change over to SUMIFS, in 'Sensor Data' add Totals column to AI with;

The Totals helper column assists with sensor totals during the minute intervals & sumifs totals these rows against Date, Room, Start & End.

=SUM(E2:AH2)

Copy this sum formula down 50k rows to be same size as formula.

2.
In Output Tab E2

=SUMIFS('Sensor Data'!$AI$2:$AI$50000,'Sensor Data'!$C$2:$C$50000,A2,'Sensor Data'!$B$2:$B$50000,B2,'Sensor Data'!$D$2:$D$50000,">="&C2,'Sensor Data'!$D$2:$D$50000,"<="&D2)

3.
For the sake of efficiency any positive number means true for meeting occured, copy this formula down;

In Output Tab F2

=IF(E2>1,"Happened","Not Happened")

*Note that the structure of sumifs is very different to countifs but see if this suits.

https://www.dropbox.com/s/drdhd9opyiyyd7o/L34 Working_jp.xlsx?dl=0

 

[jappi]

Thanks again for your prompt response RasGhul. The Calculations are still not right. Have a look at the very small sample in the file attached. Your logic of summing the sensor data across all the 2 minute intervals is not allowing the correct checks to occur within a subset of the sensor data. See if my notes in the attached make sense.

https://www.dropbox.com/s/weqivang0blhwrk/L34 Working_jp revised.xlsx?dl=0

 

[RasGhul]

Hmm that will be difficult as the time logging in Column D would also need to be listed in 2 minute intervals for each hours as well for that level of accuracy eg.

[TABLE="width: 294"]
<tbody>[TR]
[TD="align: right"]34.05[/TD]
[TD="align: right"]21-09-18[/TD]
[TD="align: right"]1:00 PM[/TD]
[/TR]
[TR]
[TD="align: right"]34.05[/TD]
[TD="align: right"]21-09-18[/TD]
[TD="align: right"]1:02 PM[/TD]
[/TR]
[TR]
[TD="align: right"]34.05[/TD]
[TD="align: right"]21-09-18[/TD]
[TD="align: right"]1:04 PM[/TD]
[/TR]
[TR]
[TD="align: right"]34.05[/TD]
[TD="align: right"]21-09-18[/TD]
[TD="align: right"]1:06 PM[/TD]
[/TR]
</tbody>[/TABLE]

The idea behind the helper column is that it totals all sensor hits within each hour range, as the sensor log seems to compile the hits for each hour.

For your main goal there will need to be some type of data transpose to convert your E-AH columns into rows that are the correct time range for the sumifs or countifs to work.

Is this how the sensor data is rec'd from the sensors by default?

 

[jappi]

Thanks. The sensor data that I expanded across columns actually has to be decrypted from decimal to binary so its pretty chunky. The binary result is what you see with a status for each 2mins across the hour. Desperate to find an "automated" solution to this so any other ideas are welcome......