Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Mar 2003
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    returning a row with the smallest number (2000)

    How can I get excel to return a row that contains the smallest number? I tried the DMIN command, however, I don't want the smallest number from each column. Is there a different way to use this command to make it do what I want? If not, is there another way to do this? Attached is an example of exactly what I am trying to do. Thanks and look forward to the responce.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: returning a row with the smallest number (2000)

    The row of the spreadsheet can be obtained from the ARRAY (ctrl-shift-enter to confirm):
    =MIN(IF(G10:N15=MIN(G10:N15),ROW(G10:N15)))
    =13 in the sheet

    The row WITHIN the range can be obtained
    =MIN(IF(G10:N15=MIN(G10:N15),ROW(G10:N15)))-ROW(G10:N15)+1
    =4 (4th row of data range)

    G10:N15, could be generalized into a range name if desired.
    Steve

  3. #3
    Star Lounger
    Join Date
    Mar 2003
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: returning a row with the smallest number (2000)

    I tried this formula but I keep getting the #value sign. What am I doing wrong? See attachment.
    Thanks.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: returning a row with the smallest number (2000)

    Select cell
    hit F2 key [now in edit mode]
    Do not hit enter key!
    hold <ctrl>+<shift> then hit <enter> key

    Now the formula will work. (that is what i meant by confirm with ctrl-shift-enter). The formula is An ARRAY formula (see help for more info)

    Steve

Posting Permissions

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