How to count the Max/Min/Avg for Individual Car when the Status change
Hi, I've the following data. I want to find out the Max, Min and Avg for individual car when the status change. Example, how days in term of MAX, Min and Avg IN/#IN before the status change to OUT/#OUT. This apply to OUT change to IN. # is assessed which i need to count them too.
Example based on the sample data below.
Car A
Max IN = 3.5 days (is the bold below)
Min IN = 0.5 days
Max OUT = 3.5 days
Min OUT = 0.5 days
Date
Car
AM
PM
1/1/2020
A
IN
IN
2/1/2020
A
IN
IN
3/1/2020
A
OUT
OUT
4/1/2020
A
IN
OUT
5/1/2020
A
OUT
OUT
6/1/2020
B
OUT
OUT
6/1/2020
A
IN
IN
7/1/2020
A
IN
IN
8/1/2020
A
#IN
#IN
9/1/2020
A
IN
OUT
10/1/2020
B
IN
IN
10/1/2020
A
#OUT
OUT
11/1/2020
B
IN
IN
See the manual work out column for the counts. I want it to auto count in QilkSense KPI.
Date
Car
AM
PM
Manual work out
1/1/2020
A
IN
IN
2 days IN (0.5 x 4)
2/1/2020
A
IN
IN
3/1/2020
A
OUT
OUT
1 day OUT
4/1/2020
A
IN
OUT
0.5 IN 0.5 OUT
5/1/2020
A
OUT
OUT
2 days OUT
6/1/2020
B
OUT
OUT
6/1/2020
A
IN
IN
3.5 days IN 0.5 day OUT
7/1/2020
A
IN
IN
8/1/2020
A
#IN
#IN
9/1/2020
A
IN
OUT
10/1/2020
B
IN
IN
10/1/2020
A
#OUT
OUT
11/1/2020
B
IN
IN
Those are sample data. My data consist of other cars in the same table. Please help me. Thank you
