avg # attempts before failure

[illinigator]

Trying to calculate average successful attempts before failure. Have several hundred data points (1=success, 0=failure). A1:A10 = 1 1 1 1 1 0 1 1 1 0
Cell A11 should calculate average attempts between failures for the whole range A1:A10.

Thanks in advance.

[daddylonglegs]

So what's the expected result for that example?

What about

0 0 1 1 1 0 0 1 1 1

or

1 1 1 1 1 0 1 1 1 1

[illinigator]

For my original post I would expect: (5+3)/2 = 4

For your data points:
(1) (0+0+3+0)/4 = .75
(2) (5)/1 = 5

[shg]

This is inelegant ...

Please Login or Register  to view this content.

The array formula in C3 and copied down is =CHOOSE(MATCH(B2 & B3, {"00","01","10","11"}, 0), 0, "", ROWS(B$2:B2) - MATCH(2, 1 / (B$2:B2=0), 1), "" ) (must be confirmed with Ctrl+Shift+Enter)

[daddylonglegs]

Perhaps try

=COUNTIF(A1:INDEX(A1:A10,MATCH(2,INDEX(1/(A1:A10=0),0))),1)/COUNTIF(A1:A10,0)

[shg]

Very nice, DLL

[illinigator]

daddylonglegs - could you please explain this to me in layman's terms ??