Search code examples
google-sheetsaveragefrequency

Average of Consecutive values with multiple criteria

How can I get an average of consecutive "win & buy". In the case of "win & buy" an average of events would be 1+1+1+1+2 =6 ( win&buy + win&buy + win&buy + win&buy + ( win&buy + win&buy ))divided by number of occuranses, in this case 5 would give us 1.2 .

Sample pic

Another example for "win" an average of events would be 1+1+1+2+4 (consecutive values, win+win+win+win,win+win,win,win,win, because there are 3 single "win" + 2 consecutive "win" and finally 4 consecutive "win" at the bottom) = 9 divided by number of occurrences, in this case 5 would give us 1.8 .

=ArrayFormula(MAX(FREQUENCY(IF((A2:A="Buy")*($B$2:$B="WIN"),ROW($B$2:$B)),IF(not((A2:A="Buy")*($B$2:$B="WIN")),ROW($B$2:$B)))))

=ArrayFormula(MAX(FREQUENCY(IF((A2:A="Buy")*($B$2:$B="WIN"),ROW($B$2:$B)),IF((A2:A<>"Buy")+($B$2:$B<>"WIN"),ROW($B$2:$B)))))

I got the above formulas from @Tom Sharpe for MAX consecutive values and tried to AVG them, but with all the 0's in the calculation, I can't get a correct answer.

Sample sheet included.

Solution

  • AVG WIN & BUY:

    =AVERAGE(QUERY(ARRAYFORMULA(FREQUENCY(
 IF(    (A2:A="BUY")*($B$2:$B="WIN"),  ROW($B$2:$B)), 
 IF(NOT((A2:A="BUY")*($B$2:$B="WIN")), ROW($B$2:$B)))), 
 "where Col1>0"))

    AVG SELL & BUY:

    =AVERAGE(QUERY(ARRAYFORMULA(MAX(FREQUENCY(
 IF(    (A2:A="SELL")*($B$2:$B="WIN"),  ROW($B$2:$B)),
 IF(NOT((A2:A="SELL")*($B$2:$B="WIN")), ROW($B$2:$B))))),
 "where Col1>0"))

    0