# Thread: Return date for max number (2002 SP-2)

1. ## Return date for max number (2002 SP-2)

Not new to the Lounge, but I am new to Excel; so new in fact that I can't even lookup what I need in the help files. In the attached pic you will see that I am returning the MIN, MAX, and AVG (B3 - B-5 resp.). The first problem is that I am returning a limited range (ie. B6 - B465) for each of these values; if I make an entry in B466 it is out of range. If I include cells beyond any blank entry (ie. B466) it throws the MIN, MAX, AVG off. (The formula in B3 is =MIN(B6:B465) and is typical of B4 and B5. It seems to me there must be a "lookup" function that would include the entire row (rather than a specific range) that would ignore null values so the range wouldn't be an issue. Also, I would like to have the date the MIN and MAX values ocurr (perhaps in A3 and A4). Any and all help appreciated (even where to look these issues up in the help files).

2. ## Re: Return date for max number (2002 SP-2)

About your first question: functions such as SUM, MIN, MAX and AVERAGE ignore text and blank values, so you might use =MIN(B6:B65536) etc., unless you use the cells below the range for other purposes.

3. ## Re: Return date for max number (2002 SP-2)

Hans:

Hey! You sure get around <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
Thanks for the reply. I could have sworn the AVG didn't work when I extended the range to include null values, but it seems to be working now. Go figure.

4. ## Re: Return date for max number (2002 SP-2)

About your other question: to get the date of the minimum value (or rather, the date of the first occurrence of the minimum value), use this formula:

=INDEX(\$A\$6:\$A\$65536,MATCH(B3,\$B\$6:\$B\$65536,0))

and similarly, to get the date of (the first occurrence of) the maximum value:

=INDEX(\$A\$6:\$A\$65536,MATCH(B4,\$B\$6:\$B\$65536,0))

Explanation: MATCH searches for the first argument (the value of B3 = MIN or B4 = MAX) in the second argument (\$B\$6:\$B\$65536, the \$ characters mean that the reference is absolute and may not be adapted if you copy the formula to another cell); the third argument 0 means that you are looking for an exact match. MATCH returns the position where the search value is found. The INDEX function looks in the first argument (\$A\$6:\$A\$65536) and returns the element at the position indicated by the second argument, that is the value returned by MATCH.
So combined, the formula looks for the MIN or MAX in column B, and returns the corresponding value in column A.

5. ## Re: Return date for max number (2002 SP-2)

Try this:

<table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><tr><td align=center>1</td><td align=right>

6. ## Re: Return date for max number (2002 SP-2)

Hans:

That works perfectly <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>. . .and thanks for the "tutorial"!

7. ## Re: Return date for max number (2002 SP-2)

John:

Thanks for your input! I entered Hans solution before you replied and it seems to work just fine. You do, however, raise an interesting point. What happens if there is more than one occasion where the MIN or MAX value occurrs? I had not considered that possibility. . .Hmmm

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•