How do I determine row number of a cell containing a certain number?

[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

 

[Ray Kostanty]

Now I see another problem: once I have the row number, how do I combine
it with a column letter so as to reference a particular cell in a
formula. For example, if the highest column A entry is 34 in row 156,
and I want to refer to cell D156, how do I combine the 156 and the D?

Thanks, again.

Ray

 

[Alan Beban]

You didn't really explain what you want to do with the reference to
D156, but the following will return the value from D156 in your
hypothetical situation:

=INDIRECT("D"&MATCH(MAX(A:A),A:A,0))

Alan Beban

 

[Ray Kostanty]

Thank you so much, Alan, for so generously helping me. Your formula
works perfectly. I probably would have never figured it out myself, as
I'm not familiar with Indirect or Match. My ultimate use was to
calculate moving averages, as described below from my original post.

I run a Sunday dance. The rows with numbers in column A are days when
the dance occurs, and they include attendance figures in column D. The
rows with blanks in column A contain expenses (for salaries,
refreshments,etc.). I merely wanted to compute the moving average
attendance for the most recent four dances.

Now I have to figure how to modify your formula to return the contents
of the three previous attendances. Then I merely sum all four and divide
by four.

Regards,

Ray

 

[Bernie Deitrick]

Ray,

=SUMIF(A1:A100,">=" & LARGE(A1:A100,4),D1100)/4

If you have more rows than 100, simply increase the 100 in each of the three places it appears.

HTH,
Bernie
Excel MVP

 

[Ray Kostanty]

Bernie,

While I don't yet fully understand how your formula works, it certainly
does the job perfectly. And it's so elegantly simple.

Thanks for your generous help.

Ray

 

[Ray Kostanty]

Bernie,

While I don't yet fully understand how your formula works, it certainly
does the job perfectly. And it's so elegantly simple.

Thanks for your generous help.

Ray