I have 2 columns that has lap # and lap time.
I would like to know the average and total summation of lap-times that are consecutively above a threshold value.
(i.e) if threshold is 85 seconds. I would like to extract the lap-times that are repetitively more than threshold seconds for 2 or more laps (consecutive)
formula I used is =IF(AND($B2>$D$2,OR($B3>$D$2,$B1>$D$2)),$B2,""). This yielded the values on the last column.
My question is, how can I sum and find the average and summation of consecutive value till blank cell. Using the example above,
I would require the average, ((118.2+92.74)/2) in one cell. I would require the next cell to give me the average, ((87.5+86.5)/2.
I hope that makes sense.
Please view the attached for the spreadsheet.