Note: the LARGE function will easily return nth values, but LARGE will return duplicates when they exist in the source data. In the example shown, the formula in E6 is: Where “rng” is the named range B5:B11 Once we have the largest value established, we create another formula that simply checks all values in the named range “rng” against the “last largest value”: Note: this is an array formula and must be entered with control + shift + enter. Any value that is lower than the “last largest” survives the test, and any value that’s not ends up FALSE. The resulting array looks like this: As the formula is copied down the column, E5 increments at each row, so that MAX and IF return a new array. The MAX function continues to return the largest value in each array — the nth value in the series. It would be great to have an all in one formula that deals with n directly. If you have one, chime in below.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
