+ Reply to Thread
Results 1 to 4 of 4

Find max value in a range and display adjacent values appearing in same row

  1. #1
    Registered User
    Join Date
    07-10-2012
    Location
    NSW, Australia
    MS-Off Ver
    Excel 2010
    Posts
    76

    Find max value in a range and display adjacent values appearing in same row

    Hi

    I'm looking for a formula that will find the max value (in col C) in a range, and then the value that is 5 rows below the max value, and then place both of these values in separate columns (H and I). There are many subjects (~100) and these are coded numerically in col A (1,2,3 etc), and so I need the formula to apply this range for each subject before moving on to the next one.

    The next stage is to also display the data from col D and E which appear in the same rows as the max and max + 5 values of Col C (in col J and K for value 2, L and M for value 3 etc. Note this are not necesarily the max values in their own column, rather they are just the corresponding values that occur in the same row as the max value in col C.

    There may be problems arising if the max value occurs with less then 5 rows remaining in the range. This will not occur often as the max should usually be towards the top of the range, but in some cases where it might, is it possible for the formula to return the last value in the range for the subject and not the value for the next subject (which may technically be the 5th row after the max)

    I have attached a sample worksheet. Please tell me if you require any additional information

    I understand that this is a reasonably complex problem and so I thank you in advance for your assistance

    T85K
    Attached Files Attached Files

  2. #2
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Find max value in a range and display adjacent values appearing in same row

    Hi,

    These are all array formulas and so need to be confirmed with CTRL+SHIFT+ENTER, not just ENTER:

    In H2 and copy down:

    =MAX(IF($A$2:$A$61=G2,$C$2:$C$61))

    In I2 and copy down:

    =INDEX($C$2:$C$61,MIN(MATCH(G2&H2,$A$2:$A$61&$C$2:$C$61,0)+5,MAX(IF($A$2:$A$61=G2,ROW($A$2:$A$61)-1))))

    In J2 and copy down:

    =INDEX($D$2:$D$61,MATCH(G2&H2,$A$2:$A$61&$C$2:$C$61,0))

    In K2 and copy down:

    =INDEX($D$2:$D$61,MATCH(G2&I2,$A$2:$A$61&$C$2:$C$61,0))

    In L2 and copy down:

    =INDEX($E$2:$E$61,MATCH(G2&H2,$A$2:$A$61&$C$2:$C$61,0))

    In M2 and copy down:

    =INDEX($E$2:$E$61,MATCH(G2&I2,$A$2:$A$61&$C$2:$C$61,0))

    You will notice that these will agree with all of your expected values except for the Value 2 + 5 and Value 3 + 5 figures for Subject=3 since your highlighted Max value of 59 in row 61 for this Subject is equally present in row 42 and you do not appear to have offered a resolution to this ambiguity in your description.

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Find max value in a range and display adjacent values appearing in same row

    no need for any arrays
    h2=
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    i2
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  4. #4
    Registered User
    Join Date
    07-10-2012
    Location
    NSW, Australia
    MS-Off Ver
    Excel 2010
    Posts
    76

    Re: Find max value in a range and display adjacent values appearing in same row

    These are both excellent solutions, thank you very much!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Macro to find all specific values in column and replace adjacent cell values
    By dblock02 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-23-2013, 06:03 AM
  2. How to find closest values in multiple columns and return adjacent values.
    By Patrician in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-28-2013, 01:47 PM
  3. Replies: 0
    Last Post: 09-06-2012, 04:06 AM
  4. Find values position in table between two values, multiply by adjacent value
    By bradfordstl in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-19-2011, 01:14 PM
  5. Find value in one column, display the text from adjacent colomn
    By todley1 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-05-2010, 04:48 AM

Tags for this Thread

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