Respective average and summation of consecutive values that r above a threshold value

  • Hi All,


    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)
    [TABLE="width: 528"]

    [tr]


    [td]

    [ATTACH=CONFIG]58591[/ATTACH]

    [/td]


    [td][/td]


    [td]


    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [/TABLE]
    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.


    Thank you.

  • Re: Respective average and summation of consecutive values that r above a threshold v


    Thank you for the reply.


    Sorry, I am a bit of a beginner. Could you explain to me how that was done? I don't see a formula. Is that a pivot table?


    I would like to extract averages and summations. Does this procedure work for a different set of lap # and lap times, meaning, what are its limitations?


    Thanks again.

  • Re: Respective average and summation of consecutive values that r above a threshold v


    The work was intended for an advanced user.
    I suggest you just paste new data into the LapT column and observe the results.
    Any other changes to my spreadsheet will likely break it.
    Blindly copying parts of it to your spreadsheet will assuredly not succeed.
    Defined Names carry the formulas.
    No PivotTables used.
    Play with it. You can always reload the file.

  • Re: Respective average and summation of consecutive values that r above a threshold v


    Thank you for the response.
    I will play around with it to learn the intricacies.
    Appreciate the feedback.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!