Results 1 to 7 of 7
  1. #1
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    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).
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    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.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    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>
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    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"!
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  7. #7
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    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
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

Posting Permissions

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