Hi

Some math first:

Let x = Total Pos

Let y = Total Neg

Let z = Total

The the probability of any one cell being Pos is x/z and two Positive would be (x/z)*((x-1)/z)

which resolves to x(x-1)/(z^2)

so in excel lets say row 1 has the data

x=countif(A1:IV1,">0")

z=counta(A1:IV1)

so

=(countif(A1:IV1,">0"))*(countif(A1:IV1,">0")-1)/counta(A1:IV1)^(2)

This should be correct.

HTH

Jacob

If I understand correctly, you want a formula solution to count how many times two positive numbers appear in sequence, as a percentage of how many possible times they ever could, within a given range.

Let's say your row is row 2, and your data starts in B2 and goes to L2.

So, in C3 (not B3, C3), enter

=IF(AND(B2>0,C2>0),1,0)

and copy to cell L3.

Note, even though you have 11 cells of data in row 2, you only have 10 possible chances for two positive outcomes in sequence.

In cell M3, enter

=COUNTIF(C3:L3,1)/COUNTA(C3:L3)

Format M3 as a percentage, maybe with 2 decimal places or whatever your preference is, and that hopefully answers your question.

This assumes you treat 0 as neither positive or negative.

This is probably not the most efficient formula solution, so I'd be interested to know if there is a non-VBA solution that doesn't need to involve another row, and/or if there is a reasonable way around the COUNTA function.

Hope this helps in the meantime.

Tom Urtis

Thanks for your help - its much appreciated!