Second Minimum Value from a list of Values

[mrthomasshelby]

Hi,

We can extract the minimum value from amongst multiple values by using RangeMin(exp1,exp2,...). Is there anyway to extract the 2nd Minimum Value from multiple values?

Let's consider this for an example:

M1	M2	M3	M4	M5	M6	M7	M8
15	16	17	18	19	21	26	31

RangeMin(M1,M2,M3,M4,M5,M6,M7,M8) will return 15. What I would like is to extract the 2nd Minimum value from the list. How to go about it given that there isn't a rank argument available in the RangeMin function? Attached sample app with 3 measures M1,M2 and M3. Please help calculate the 2nd and 3rd minimum columns.

Thanks in advance.

@sunny_talwar @tresesco @MarcoWedel 

[mrthomasshelby]

Here's a sample app. Need to calculate 2nd & 3rd Minimums of var1, var2 and var3 which are variables.

[tresesco]

If you have multiple rows, then this won't work as expected. If you reduce your row to one, you get it using simpler expression:

For multiple rows, I guess you would need bit more complex expression - this in combination with pick(... to make it row-wise calculation

[MarcoWedel]

Maybe something like

=RangeMin(
          If(M1>RangeMin(M1,M2,M3,M4,M5),M1),
          If(M2>RangeMin(M1,M2,M3,M4,M5),M2),
          If(M3>RangeMin(M1,M2,M3,M4,M5),M3),
          If(M4>RangeMin(M1,M2,M3,M4,M5),M4),
          If(M5>RangeMin(M1,M2,M3,M4,M5),M5)
         )

 ?