MrExcel Publishing
Your One Stop for Excel Tips & Solutions

MAX number of periods


Posted by Jerry O on October 22, 2000 12:21 PM

694.84
696.00
698.45
701.02
697.86
700.01
The max number in these 5 periods is 701.02 and that is 2 periods ago , but how do I write the formula to get the answer of 2 ?
thanks in advance,
Jerry


Posted by Celia on October 22, 2000 2:59 PM


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


Posted by Jerry on October 22, 2000 4:04 PM

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

Posted by Jerry O on October 22, 2000 5:46 PM

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


Posted by Jerry on October 22, 2000 5:58 PM


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

Posted by Celia on October 22, 2000 6:10 PM


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

Posted by Jerry on October 22, 2000 7:21 PM

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

Posted by Jerry on October 22, 2000 7:21 PM

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

Posted by Ivan Moala on October 22, 2000 7:25 PM

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


Ivan

Posted by jerry on October 22, 2000 8:01 PM


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

Posted by Celia on October 23, 2000 2:42 AM


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