31 Replies Latest reply: Feb 22, 2018 4:08 PM by Farrukh Shaikh RSS

    Second Max Value in Column Range (Straight Table)

    Farrukh Shaikh

      Hi Guys,

       

      Can you please advise me how can I get second or third max value in column range?

         

      Col_1Col_2Col_3Col_4Col_5
      1020304050

       

       

      Currently I am using RangeMax & RangeMin to get Max or Min value. But I get to get 2 or 3 from the range.

       

      Kind regards,

      Farrukh

        • Re: Second Max Value in Column Range (Straight Table)
          Settu Periyasamy

          Hi,

          Col_1, Col_2.. etc  are Label of expressions, right? Then try like this..(for 3rd Max)

           

          Pick(Match(

          RangeMax(Col_1,Col_2,Col_3,Col_4,Col_5),

              Col_1,Col_2,Col_3,Col_4,Col_5)-2,

          Col_1,Col_2,Col_3,Col_4,Col_5)

          • Re: Second Max Value in Column Range (Straight Table)
            Settu Periyasamy

            Hi Farruukh,

             

            Can you check the attachment? is that you needed?

             

            due to large expression (may be there is a easy way), i have created variables like

            v1H - 1st Higher

            v2H - 2nd Higher

            v3H - 3rd Higher

            v1L - 1st Low

            v2L - 2nd Low

             

            v3H: (To find the 3rd maximum based on column label)

             

            =rangemax(
            IF(RangeMax(
            If(RangeMax(CDF1, CDF2, CDF3, CDF4,CDF5) = CDF1, 0, CDF1),
            If(RangeMax(CDF1, CDF2, CDF3, CDF4,CDF5) = CDF2, 0, CDF2), 
            If(RangeMax(CDF1, CDF2, CDF3, CDF4,CDF5) = CDF3, 0, CDF3),
            IF(RangeMax(CDF1, CDF2, CDF3, CDF4,CDF5) = CDF4, 0 ,CDF4),
            IF(RangeMax(CDF1, CDF2, CDF3, CDF4,CDF5) = CDF5, 0 ,CDF5))=CDF1,

            RangeMax(
            If(RangeMax(CDF2, CDF3, CDF4,CDF5) = CDF2, 0, CDF2), 
            If(RangeMax(CDF2, CDF3, CDF4,CDF5) = CDF3, 0, CDF3),
            IF(RangeMax(CDF2, CDF3, CDF4,CDF5) = CDF4, 0 ,CDF4),
            IF(RangeMax(CDF2, CDF3, CDF4,CDF5) = CDF5, 0 ,CDF5) ),

            IF(RangeMax(
            If(RangeMax(CDF1, CDF2, CDF3, CDF4,CDF5) = CDF1, 0, CDF1),
            If(RangeMax(CDF1, CDF2, CDF3, CDF4,CDF5) = CDF2, 0, CDF2), 
            If(RangeMax(CDF1, CDF2, CDF3, CDF4,CDF5) = CDF3, 0, CDF3),
            IF(RangeMax(CDF1, CDF2, CDF3, CDF4,CDF5) = CDF4, 0 ,CDF4),
            IF(RangeMax(CDF1, CDF2, CDF3, CDF4,CDF5) = CDF5, 0 ,CDF5))=CDF2,

            RangeMax(
            If(RangeMax(CDF1, CDF3, CDF4,CDF5) = CDF1, 0, CDF1), 
            If(RangeMax(CDF1, CDF3, CDF4,CDF5) = CDF3, 0, CDF3),
            IF(RangeMax(CDF1, CDF3, CDF4,CDF5) = CDF4, 0 ,CDF4),
            IF(RangeMax(CDF1, CDF3, CDF4,CDF5) = CDF5, 0 ,CDF5)),

            IF(RangeMax(
            If(RangeMax(CDF1, CDF2, CDF3, CDF4,CDF5) = CDF1, 0, CDF1),
            If(RangeMax(CDF1, CDF2, CDF3, CDF4,CDF5) = CDF2, 0, CDF2), 
            If(RangeMax(CDF1, CDF2, CDF3, CDF4,CDF5) = CDF3, 0, CDF3),
            IF(RangeMax(CDF1, CDF2, CDF3, CDF4,CDF5) = CDF4, 0 ,CDF4),
            IF(RangeMax(CDF1, CDF2, CDF3, CDF4,CDF5) = CDF5, 0 ,CDF5))=CDF3,

            RangeMax(
            If(RangeMax(CDF1, CDF2, CDF4,CDF5) = CDF1, 0, CDF1), 
            If(RangeMax(CDF1, CDF2, CDF4,CDF5) = CDF2, 0, CDF2),
            IF(RangeMax(CDF1, CDF2, CDF4,CDF5) = CDF4, 0 ,CDF4),
            IF(RangeMax(CDF1, CDF2, CDF4,CDF5) = CDF5, 0 ,CDF5)),

            IF(RangeMax(
            If(RangeMax(CDF1, CDF2, CDF3, CDF4,CDF5) = CDF1, 0, CDF1),
            If(RangeMax(CDF1, CDF2, CDF3, CDF4,CDF5) = CDF2, 0, CDF2), 
            If(RangeMax(CDF1, CDF2, CDF3, CDF4,CDF5) = CDF3, 0, CDF3),
            IF(RangeMax(CDF1, CDF2, CDF3, CDF4,CDF5) = CDF4, 0 ,CDF4),
            IF(RangeMax(CDF1, CDF2, CDF3, CDF4,CDF5) = CDF5, 0 ,CDF5))=CDF4,

            RangeMax(
            If(RangeMax(CDF1, CDF2, CDF3, CDF5) = CDF1, 0, CDF1), 
            If(RangeMax(CDF1, CDF2, CDF3, CDF5) = CDF2, 0, CDF2),
            IF(RangeMax(CDF1, CDF2, CDF3, CDF5) = CDF3, 0 ,CDF3),
            IF(RangeMax(CDF1, CDF2, CDF3, CDF5) = CDF5, 0 ,CDF5)),

            IF(RangeMax(
            If(RangeMax(CDF1, CDF2, CDF3, CDF4,CDF5) = CDF1, 0, CDF1),
            If(RangeMax(CDF1, CDF2, CDF3, CDF4,CDF5) = CDF2, 0, CDF2), 
            If(RangeMax(CDF1, CDF2, CDF3, CDF4,CDF5) = CDF3, 0, CDF3),
            IF(RangeMax(CDF1, CDF2, CDF3, CDF4,CDF5) = CDF4, 0 ,CDF4),
            IF(RangeMax(CDF1, CDF2, CDF3, CDF4,CDF5) = CDF5, 0 ,CDF5))=CDF5,

            RangeMax(
            If(RangeMax(CDF1, CDF2, CDF3, CDF4) = CDF1, 0, CDF1), 
            If(RangeMax(CDF1, CDF2, CDF3, CDF4) = CDF2, 0, CDF2),
            IF(RangeMax(CDF1, CDF2, CDF3, CDF4) = CDF3, 0 ,CDF3),
            IF(RangeMax(CDF1, CDF2, CDF3, CDF4) = CDF4, 0 ,CDF4))

            ) ) ) ) ) )

             

             

             

            Select_F1 Expression :

             

            if("CDC1">0,
            Pick(
            Match(CDF1,$(v1H),$(v2H),$(v3H),$(v1L),$(v2L)),
            '1H','2H','3H','1L','2L'))



            Like wise other expression..