Results 1 to 4 of 4

Thread: Function Help?

  1. #1
    New Lounger
    Join Date
    May 2003
    Location
    N. Andover, Massachusetts, USA
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Function Help?

    In a large spreadsheet in which I have used the function min (to pick the lowest value in a row), I would like to return in the next cell to the cell which contains the min function the value two cells to the right of the cell selected by the min function.
    If Min(A1:A12) returned the value in in A6 as being lowest
    I would like a function which would return the value in A8.

    If anyone knows if/how this could be done I would be grateful for your help. Thanks.

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Function Help?

    Are you talking about rows or columns. You mention rows but your example suggests columns (column A). Also what if the minimum is the last, or last but one, entry in the row (or column). If the minimum is in row A6, then 2 to the right is C6, not A8. What you want should be easy enough to achieve if you could clarify. The following function will return the value 2 cells to the right of of the first cell to equal the minimum in column A (If there could be more that one).

    =OFFSET(A1,MATCH(MIN(A1:A12),A2:A12,0),2).

    So that in your example it would return the value in C6. If that is not what want please explain in more detail.

    Andrew C

  3. #3
    New Lounger
    Join Date
    May 2003
    Location
    N. Andover, Massachusetts, USA
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function Help?

    I'm sorry, I made an error in my question. You are correct we are talking about rows. This function would be placed in row a and copied down several hundred rows. The example you gave me appears written for columns. I tried to convert it unsucessfully. An example for rows would be most helpful. Thanks-you.

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Function Help?

    Try the following and see if it gets what you want. It gives the contents of the cell that is 2 to the right of the first cell containing the minimum of a range. You should place this formula in a cell at least 2 columns to the right of the last cell to be evaluated, otherwise you could have a circular reference. What if the minimum is the last, or last but one, in range? . The formula as is covers the range from A2 to H2, and so should be placed in or to the right of K2. Adapt the range as required.

    =OFFSET(A2,0,MATCH(MIN(A2:H2),A2:H2,0)+1)

    Andrew C

Posting Permissions

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