MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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 2 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

How about adding a column with this formula:


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:

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
If Range("C" & Cnt) < 0 Then
Down = Down + 1
Even = Even + 1
End If
End If
End If
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.