+ Reply to Thread
Results 1 to 4 of 4

Return cell value offset from max with conditions

  1. #1
    Forum Contributor
    Join Date
    02-23-2006
    Location
    Near London, England
    MS-Off Ver
    Office 2003
    Posts
    770

    Question Return cell value offset from max with conditions

    Ok ,so I am hoping I can do this without VBA, if it needs VBA I'll have another think on it and can probably do it, but would prefer to use the built in functions if possible.

    So first my data table:
    Please Login or Register  to view this content.
    What I want to do is look for the maximum QTY value, that has no WORKER assigned to it, and then return either the value of corresponding cell in the UNIQUE column, or else at least the address of the max value that I can then use to find the UNIQUE cell myself.

    I've tried using array formulae to find the max value that doesn't have a worker, and that works well, but I can't see how to get the unique from that.
    Similarly using the array formula I can get the max value using an array formula, and then use a Match function to get the address of a cell with that value in the QTY column, but the match function can pickup a cell with the correct value, but with a worker assigned (as it's just matching value, not the conditions used in the max formula).

    The above example should return either, $B$5, or $A$5, or "8765", I would be happy with any of those to indicate which row has the max qty with no worker.
    In the situation where there are 2 possible cells, (ie. if row 3 had no worker there would be two '58's available), then just returning the first one is fine.

    Any ideas?

    Thanks
    Last edited by Phil_V; 09-08-2010 at 09:14 AM.
    If you find the response helpful please click the scales in the blue bar above and rate it
    If you don't like the response, don't bother with the scales, they are not for you

  2. #2
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,331

    Re: Return cell value offset from max with conditions

    =INDEX(A2:A7,MATCH(MAX(IF(C2:C7="",B2:B7)),IF(C2:C7="",B2:B7)))
    array-entered would do it.
    Everyone who confuses correlation and causation ends up dead.

  3. #3
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,644

    Re: Return cell value offset from max with conditions

    Try this comfirmed with Ctrl+shift+enter:

    =INDEX(A2:A100,MATCH(MAX(IF(C2:C100="",B2:B100,))&"",B2:B100&C2:C100,0))

  4. #4
    Forum Contributor
    Join Date
    02-23-2006
    Location
    Near London, England
    MS-Off Ver
    Office 2003
    Posts
    770

    Re: Return cell value offset from max with conditions

    Thanks to both, got the result I wanted
    Now to disect and understand

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1