英文:
how to calculate timestamp of 2 events which are irregularly spaced apart
问题
所以我有一个事件日志，我想知道当a为真时和b为假时经过的时间，然后将计算时间戳差。我想知道如何搜索最近的以前a为真并参考该时间戳以减去b为假的时间戳。在这两者之间可能会有不规则数量的事件项。
作为额外要求，我需要验证事件的顺序：
a=true -> b=true -> a=false -> b=false
我尝试使用if语句来确认我何时有一个b是假的，但不知道如何向上搜索并参考最后一个以前的a为真。
另外，我不知道如何验证这4个事件的特定顺序。
英文:
So I have an event log in which I would like to know the time elapsed between when a is true, and when b is false. then the timestamp difference will be calculated. I would like to know how to search the nearest previous a is true and refer to that timestamp to subtract with the b is a false timestamp. There may be an irregular number of event items in between.
as an extra requirement, I need to verify that the events are in the order:
a=true -> b=true -> a=false -> b=false
I have tried to use if statement to confirm where i have a b is false, but don't know how to search up and refer to the last previous a is true.
also, I have no clue how to verify the sequence of 4 events in a certain order.
答案1
得分: 0
你可以创建一个支持列来帮助公式识别。
=C2&D2
然后主要公式如下：
=if((C2&D2)="dFALSE",(B2-ARRAYFORMULA(MAX(IF($A$2:A2="aTRUE",$B$2:B2))))*1440,"")
但请注意，只有在您的数据是按时间顺序排列时才能使用此方法。
这是我在Google表格中创建的示例文件。
https://docs.google.com/spreadsheets/d/138tULefCoMdMHsOYIudugOssworU3wtzh1f7lEWelWs/edit#gid=0
如果您需要在Excel上使用它，可能需要替换arrayformula()。
英文:
You could create a supportive column to help the formula identify.
=C2&D2
And then the main formula would be:
=if((C2&D2)="dFALSE",(B2-ARRAYFORMULA(MAX(IF($A$2:A2="aTRUE",$B$2:B2))))*1440,"")
But please be notified that this method can only be used when your data is time-sequenced.
Here is the example file I created in Google Sheet.
https://docs.google.com/spreadsheets/d/138tULefCoMdMHsOYIudugOssworU3wtzh1f7lEWelWs/edit#gid=0
If you need to use it on Excel, you may need to replace the arrayformula().
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论