Quick Question

Posted by New user on January 05, 2002 11:40 AM

I hope someone can help with what is probably a much simpler question than the users of this message board are used to seeing.

When faced with a long row containing both positive and negative values in Excel, does anyone know a formula that can calculate the percentage likelihood of a positive number following a second positive number in the row (i.e., what is the probablility of seeing two consecutive positive numbers in a selected row). Thanks in advance.

Posted by Jacob on January 05, 2002 1:48 PM

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)

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

This should be correct.

HTH
Jacob

Posted by Tom Urtis on January 05, 2002 2:28 PM

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

Posted by New User on January 05, 2002 3:15 PM

Thanks for your help - its much appreciated!