# statistical analysis of stockmarket

Posted by Patrik on January 11, 2002 3:04 PM

Hi my name is Patrik and i´m from Sweden.

I wonder someone you can help me with one thing?

I have a column with positive and negative numbers. It´s the percentual changing from day to day of a stockmarket index here in sweden.

I would like to examine what happens the third day if there are two days with increasing index before the third day.

day 1 increasing...day 2 increasing.....day 3 ???

Is it possible to make a formula that can solve how often third day is up, down or just equal?

Thanks so much for your help!!!!!!

Posted by Tom DIckinson on January 11, 2002 6:08 PM

=IF(AND(A5>0,B5>0),IF(C5>0,"Up",IF(C5&LT;0,"Down","Even")),"")

This assumes the data starts in row 5, and day 1 is in Column A, day 2 in Column B, and day 3 in Column C. Adjust the formula for your columns. Then at the top you can put something like this:

=COUNTIF(D5:D20,"Up")
=COUNTIF(D5:D20,"Down")
=COUNTIF(D5:D20,"Even")
These cells will provide the numbers.

If it would be better in a macro, then

Sub IncreasesCnt()
Dim Cnt, Up, Down, Even as Integer
Up = 0
Down = 0
Even = 0
For Cnt = 5 to 500
If Range("A" & Cnt) > 0 And Range("B" & Cnt) > 0 then
If Range("C" & Cnt) > 0 Then
Up = Up + 1
Else
If Range("C" & Cnt) &LT; 0 Then
Down = Down + 1
Else
Even = Even + 1
End If
End If
End If
Next
Range("D1") = Up & " Increases"
Range("D2") = Up & " Decreases"
Range("D3") = Up & " Unchanged"
End Sub

This will go through rows 5 to 500; adjust as necessary.

Hopw this helps.