3 Replies Latest reply on Mar 12, 2014 2:58 PM by Desiree Koh

# How do you find the second (or nth) highest value in an un-ordered group of records using a calculation?

I'm trying write a calculation that would return the second (or nth) highest value in the attached workbook. So for product a I would like to get 19 since that is the second highest value for a.

I would like it to work similarly to the MAX function, but instead of returning the MAX, return the nth value (n would be based off another function).

• ###### 1. Re: How do you find the second (or nth) highest value in an un-ordered group of records using a calculation?

It sounds like the Rank Calculation might do what you need.

Then you can simply filter by rank to get the values you are looking for.

Best,

Dallin

1 of 1 people found this helpful
• ###### 2. Re: Re: How do you find the second (or nth) highest value in an un-ordered group of records using a calculation?

Desiree,

Here is what it looks like , using the rank function as mentioned by Dallin.

You will also have to bring [Volume] in the Detail and use the Window_Min/Max  functions to get those values.

You can check in the Validation sheet to understand the logic , and see how the pills are rearranged in Sheet 3 to keep a single row per product.

Michel

• ###### 3. Re: How do you find the second (or nth) highest value in an un-ordered group of records using a calculation?

Thank you Dallin and Michel! I'm still trying to figure out how the calculations are working, but that's what I needed. Thanks!