R
Ray Kostanty
I have a spreadsheet in which some of the entries in column A contain
integers starting with 1 and increasing by 1 until a maximum value
(never more than 50). In between, the A cells are blank, even though
their rows contain data in the other columns. The number of rows between
those with entries in column A is variable. For example, entries might
be: A5=1, A9=2, A11=3, A17=4. So there are three blank rows between A5
and A9, one between A9 and A11, and five between A11 and A17.
The problem is to determine the number of the row that has the highest
number in column A. In my spreadsheet, the highest number so far is 34,
appearing in A156. (There are also rows after 156 with nothing in column
A.) I can determine the highest number by using MAX or COUNT, but how do
I extract the 156?
Ultimately, I'm trying to compute a moving average of entries in column
D of the rows with the four highest integers in column A (e.g., A156=34,
A152=33, A148=32, and A143=31).
Yes, I could sort the spreadsheet so the rows with entries in column A
follow one after the other. Then there would be a fixed difference
between an entry in column A and its row number, the difference being
the number of rows used for the table name and the column headings.
Thanks for your help.
Ray
integers starting with 1 and increasing by 1 until a maximum value
(never more than 50). In between, the A cells are blank, even though
their rows contain data in the other columns. The number of rows between
those with entries in column A is variable. For example, entries might
be: A5=1, A9=2, A11=3, A17=4. So there are three blank rows between A5
and A9, one between A9 and A11, and five between A11 and A17.
The problem is to determine the number of the row that has the highest
number in column A. In my spreadsheet, the highest number so far is 34,
appearing in A156. (There are also rows after 156 with nothing in column
A.) I can determine the highest number by using MAX or COUNT, but how do
I extract the 156?
Ultimately, I'm trying to compute a moving average of entries in column
D of the rows with the four highest integers in column A (e.g., A156=34,
A152=33, A148=32, and A143=31).
Yes, I could sort the spreadsheet so the rows with entries in column A
follow one after the other. Then there would be a fixed difference
between an entry in column A and its row number, the difference being
the number of rows used for the table name and the column headings.
Thanks for your help.
Ray