none
how can you find the max on a row based on a condition on another row RRS feed

  • General discussion

  • I would like to find the max value of a row based on values selected based on a condition in another row. The data looks like the example below. I want to find the max date on row 2 for asset 1 based on the condition that there is a 1 the specific column. What function can I use? I have tried HLOOKUP and played a bit with Index but I am not sure how to set up a formula that I can copy down to all row for which I would like to test the condition and get the max date value. Any help is welcome. Thank you.

    Asset Lookup Value Max Date Needed Av In Un Un Un In In Av In Av Av Av
          2015-05 2016-04 2015-02 2016-02 2016-03 2016-02 2016-03 2015-04 2016-05 2015-06 2015-09 2016-01
    Asset 1 Av Question: Return   Max date from array in row 2 ONLY for columns that match lookup value and a   have the value 1 in row 3 1   1     1 1 1   1 1  
    Asset 2 In Question: Return   Max date from array in row 2 ONLY for columns that match lookup value and a   have the value 1 in row 4 1 1       1 1          
    Asset 3 Un Question: Return   Max date from array in row 2 ONLY for columns that match lookup value and a   have the value 1 in row 5 1   1 1 1              

    Saturday, August 22, 2015 3:46 AM

All replies

  • Option 1: sort the range D1:O5 from left to right on row 2, so that the months are in ascending order.

    You can then use the following array formula confirmed with Ctrl+Shift+Enter in C2:

    =INDEX($A$2:$O$2,MAX(($D$1:$O$1&$D3:$O3=$B3&"1")*COLUMN($D$1:$O$1)))

    Fill down from C2 to C5 (or as far as needed)

    Option 2: instead of sorting D1:O5, change the values in D2:O2 from text strings to real dates:

    Change D2 to 2015-05-01, E2 to 2016-04-01 etc. Format D2:O2 with the custom format yyyy-mm so that it looks the same as before.

    Now use the following array formula confirmed with Ctrl+Shift+Enter in C2:

    =MAX(IF(($D$1:$O$1=$B3)*($D3:$O3=1),$D$2:$O$2))

    Format C2 as yyyy-mm too, just like D2:O2. Then fill down to C5 or as far as needed.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, August 22, 2015 10:27 AM