Response Time

[Swati Vishwanathan]

Hello

I am struggling to calculate the response time for a large data set. I need to calculate the response time from when the alert was first sent and finally picked. So one alert would go to multiple users and we need to calculate the response time on each row (current alert time-first alert time)

So we could be down 4 rows and I want to subtract the time on row 4 from row 1. All these rows would have the same event identifier. When identifier changes it means that we have a new alert.

So I cannot hardcode the beginning row number. A single alert could have only two rows before it was picked while another alert can have 10 rows before it was picked.
So B2 to b13 are the same alert but have gone to multiple users..so on each row we need response time based on when the alert was first sent.

From row 14, it is a new alert and we need response time for each time the alert was passed on.

 

[NARAYANK991]

Hi ,

I am not sure I have understood you ; see if this is what you want.

Narayan

 

[Swati Vishwanathan]

Hi ,

I am not sure I have understood you ; see if this is what you want.

Narayan

 

[Swati Vishwanathan]

Thanks So much Narayan

THis works like magic

Can you please explain the logic so that I can use this in other context as well

Thanks again...i have spent endless hours trying to figure this out

Regards

 

[NARAYANK991]

Thanks So much Narayan

THis works like magic

Can you please explain the logic so that I can use this in other context as well

Thanks again...i have spent endless hours trying to figure this out

Regards

Hi ,

It is not very advanced Excel ; the INDEX + MATCH combination is functionally similar to the VLOOKUP / HLOOKUP functions , with some additional flexibility.

These two links will tell you all about INDEX and MATCH.

https://www.deskbright.com/excel/using-index-match/

http://www.randomwok.com/excel/how-to-use-index-match/

Narayan

 

[Swati Vishwanathan]

Hello Naryana

Thanks for sharing the links....I love this solution. I also worked on another combination which works well too...


=B2-MIN(INDEX($B$2:$B$21,MATCH(A2,$A$2:$A$21,0)))

If column b has time and column A has the event identifier

I think this formula would work even when date time is not sorted


Thanks again