Counting

Posted by Mark O'Donnell on May 16, 2001 4:46 AM

Hello,

I have a spreadsheet set up with the values of an index. I am trying to count how many periods it takes to recover from a fall in value.

For example
100
102
95
98
103

So how many periods does it take to get back above 102. Bearing in mind it can be more than 7 periods (the maximum number of nested if functions).

Thanks for any suggestions. (A spreadsheet example can be given if needed)

Mark

Posted by Sean on May 16, 2001 5:36 AM

=====

Mark,

I have solved this using a User Defined Function in VBA(Visual Basic for Applications)

Go to VBA
Insert a Module (If you dont have one already)
Paste in the following Function

Function BackToLevel(Curr, rng)
x = 1
For Each Value In rng
If Curr &LT; Value Then
BackToLevel = x
Exit For
End If
x = x + 1
Next
End Function

Now go back to Excel and call the function as per the table below. The results in column B give the number of weeks before the current index value is exceeded.

If there are no values larger it returns 0 for the number of weeks.

The first value in the function is your current index value, the 2nd part is the range of future index values.

In Excel add the following in Cols A & B
A B
1 Index Number of weeks
2 100 =backtolevel(A2,A3:A6)
3 102 =backtolevel(A3,A4:A7)
4 95 =backtolevel(A4,A5:A8)
5 98 =backtolevel(A5,A6:A9)
6 103 =backtolevel(A6,A7:A10)

My solution came back as follows:

A B
1 Index Number of weeks
2 100 1
3 102 3
4 95 1
5 98 1
6 103 0

If you have any queries get to me.

Sean
s-o-s@lineone.net

Posted by Aladin Akyurek on May 16, 2001 9:24 AM

Another way...

Sean's UDF appears OK. The following formula is another way.

In B1 array-enter: =MIN(IF(A2:$A$5>=A1,ROW(INDIRECT("1:"&COUNT(A2:$A$5)))))

and copy down as far as needed. (In order to array-enter a formula, hit CONTROL+SHIFT+ENTER at the same time, instead of just ENTER.)

Here are data with results:

{100,1;102,3;95,1;98,1;103,1}

Aladin

==================