I have a sheet consisting of entries that look like this, which I use to keep track of when the members of my family are at home:

The rows are added via automation from everyone's phone using IFTTT. I have some flexibility in the format, but not a lot.

I would like to create a cell that changes ONLY when everyone is out of the house. Another IFTTT rule will watch that cell, and when it changes it will start the roomba. So the cell should NOT change if anyone returns home, it should only change if everyone has left.

One way I can think to do this is to set the watched cell to the last timestamp when everyone has left the house. That way, it will only update to a new value when everyone has once again left. Anytime there's a new row, if the status of everyone is Out, it will update the last timestamp.

I'm having a little trouble composing the formulas to keep track of when everyone is Out. This involves looking back through the most recent entries and finding the last time everyone who isn't the current user was out. I figure I can use a filter and a reverse sort and a lookup for every row in the table, but this seems a little complicated and inefficient.

Is there a better way to accomplish what I want?

CodePudding user response：

you could track it like:

=SORTN(SORT(B2:C, ROW(B2:B), 0), 9^9, 2, 1, 1)

and then:

=SUMPRODUCT(INDEX(SORTN(SORT(FILTER({B2:C, 
 ROW(B2:B)}, B2:B<>""), 3, 0), 9^9, 2, 1, 1)="Out",, 2))=
 COUNTUNIQUE(B2:B)

where:

TRUE  = everybody out
FALSE = someone in

to get a time when house is empty:

=IF(SUMPRODUCT(INDEX(SORTN(SORT(FILTER({B2:C, 
 ROW(B2:B)}, B2:B<>""), 3, 0), 9^9, 2, 1, 1)="Out",, 2))=COUNTUNIQUE(B2:B), 
 INDEX(SORTN(SORT(A2:C, ROW(B2:B), 0), 9^9, 2, 2, 1), 1, 1), "someone home")

CodePudding user response：

I think I got it, many thanks to player0.

The columns in yellow are written by the IFTTT automation. Columns D and E are set to the status of Mike and Carrie, as of that time. D3 is set to:

=INDEX(TRANSPOSE(SORTN(SORT(A$2:C3, ROW(B$2:B3), 0), 9^9, 2, 2, 1)),3)

and filled down for the rest of column D, which also populates E.

Once you have a row with a timestamp and everyone's status, it's a relatively simple thing to pick out the latest row where everyone is out. Cell G4 is set to:

=INDEX(SORT(FILTER(A2:E,D2:D="OUT",E2:E="OUT"),1,FALSE),1,1)

In this example, it shows that the last time everyone was out was on 6/5, which is correct. As more rows are added, the value does not change again until the next time everyone is out, which is important for the automation that watches that cell for changes to know when to start the vacuum.

I am definitely open to more elegant solutions that don't need to drag a formula down column D, but for now this one seems to do the job.