Find nth largest value, when there are duplicate n-1, n-2 etc values AND blank cells

Hi Ramanan,

Thanks again for a great response. Your solution works perfectly on the example that I originally proposed. However, I’m having trouble now taking your solution and implementing it in my spreadsheet. My data is across the page instead of down, which must be affecting the ROW part of your solution. I’ve tried replacing it with COLUMN but it wasn’t that simple.

In addition, my formula will end up using OFFSET to refer to the data array, and I’m not sure if that’s now going to affect the solution. I suspect it will though.

I’ve added these two scenarios into your spreadsheet – “Data transposed” is just transposing the data and my feeble attempt to adjust the formula. “Data transposed & Offset” is the type of offset situation I need to implement this with.

Unfortunately your formula’s are now well above my skill level and so I’m struggling to understand their magic and troubleshoot myself!

Thanks again

Hi Ramanan,

Sorry I feel like each time you help me I change what I need. I'm used to being able to change these to suit, but I'm still just copying your work without fully understanding it.

Anyway, the next challenge with this is attached. Basically the named range needs to be able to handle the formula being copied down to reference different Lists. I've attached what I mean.

Thanks again

130828 Bobneil_New solution v3.xlsx

Thank you very much. Very patient and helpful with this, and very sophisticated solutions. Most appreciated.

BTW if anyone can give Ramanan a hit of reputation for these posts, I'd appreciate it. Helped me last time so forum won't let me double up on the stars.