Forum Discussion
Solved
Calculate the time difference with a specific criteria
Dear Experts, I have a data like below in the Worksheet "Main-Page_Data" :- Column "A" -> can have only 2 values Inactive->Connected OR Connected->Inactive Column "...
Here is an option that I think will work in legacy versions but not sure and may require ctrl-shift-enter:
=IFERROR([@Time]-1/(1/SUMPRODUCT(MAX([Time]*([Time]<[@Time])*([ueIdCu]=[@ueIdCu])*([Starting RRC transition procedure]<>[@[Starting RRC transition procedure]])))),"")NOTE: this version does not provide that added warning when the prior entry is the same procedure as the current.
Here is a formula you can use that will be robust enough to address multiple potential errors and changing the sorting. Now I did format the table as a table (Home -> Format as Table).
=LET(prior,MAXIFS([Time],[ueIdCu],[@ueIdCu],[Time],"<"&[@Time]),
out, IF(IFERROR(prior=0,0),"",
IF(XLOOKUP([@ueIdCu]&prior,[ueIdCu]&[Time],[Starting RRC transition procedure])=[@[Starting RRC transition procedure]],
"Error - Last entry was same transition",
[@Time]-prior)),
out)
Thanks m_tarler , may I ask to provide a solution with normal legacy functions? LET is really powerful function and I am still digesting it.. or any PQ with the UI steps?
Thanks & Regards
Anupam Shrivastava
Here is an option that I think will work in legacy versions but not sure and may require ctrl-shift-enter:
=IFERROR([@Time]-1/(1/SUMPRODUCT(MAX([Time]*([Time]<[@Time])*([ueIdCu]=[@ueIdCu])*([Starting RRC transition procedure]<>[@[Starting RRC transition procedure]])))),"")NOTE: this version does not provide that added warning when the prior entry is the same procedure as the current.
An alternative could be Power Query. In the attached file you can add data to the left dynamic table. Then you can click in any cell of the right result table and right-click with the mouse and select refresh to update the right result table.
