MAX number of periods

Jerry
Assuming your data is in A1:A6, then put the following array formula in A8 (must enter with Ctrl+Shift+Enter) :-

=(ROW()-2)-MIN(IF(A1:A6=MAX(A1:A6),ROW(A1:A6),""))

If you want to put the formula in a cell other than A8, you need to adjust the first part of the formula (ROW()-2). But don't put it in a cell with a smaller row number than the last entry in the range being checked.
ROW() returns the row number of the cell that contains the formula, so in the above example it returns 8 and ROW()-2 returns 6.
The second part of the formula returns the row number of the maximum value of the range being checked.

Celia

Thanks Celia,
The answer is supposed to go in column e and a6 using your formula. This is from a book of Technical study of the stock mkt and the spread sheet with the answers are there, (so I knew the answer)but some formula's are missing. Now how does this change the formula for a column. Sorry I should have said column.
Jerry

From the book, as your formula kept giving a -2 answer but couldn't copy and paste an array.
A B C D |E F
line high low # highest | periods high |
1 27.5 ## |
2 28.125 ## |
3 29 ## |
4 31.313 ## |
5 29.75 ## |
6 28.563 ## 31.313 |2 60
7 29.813 ## 30.313 |3 40
8 30.969 ## 30.969 |0 100
9 30.594 ## 30.969 |1 80
10 31.125 ## 31.125 |0 100
A B C D E F
n =5
Column f (5-e6/5)*100
After I get this formula then the low column should be easy.
Jerry

column B----column E
27.500
28.125
29.000
30.313
29.750
28.563--------answer 2
29.813--------answer 3
30.969--------answer 0
30.594--------answer 1

Jerry
Sorry, but I don't understand your last 3 messages.
You should be able to adjust the formula to fit your needs. Try putting it in different cells and you will see from the results what adjustment you have to make. The column it goes in doesn't make any difference - only the row it goes in will affect the formula.
If you can't do it, send me your file.
Celia

Formula working but can't seem to copy and paste it down the column as warning "can't change part of array message appears" I have to manually change cell ref.
Sorry I did e-mail also if you get two messages.
Jerry

Formula working but can't seem to copy and paste it down the column as warning "can't change part of array message appears" I have to manually change cell ref.
Sorry I did e-mail also if you get two messages.
Jerry

Jerry
something like this may do;
=COUNT(A1:A6)-MATCH(MAX(A1:A6),A1:A6,0)

Ivan

That was it!
=COUNT(DE3:DE8)-MATCH(MAX(DE3:DE8),DE3:DE8,0)
Thanks
Jerry

Now why didn't I think of that !!
Celia