Appreciate any assistance with developing a formula that identifies the third largest value if there are 9 or 10 cell values in a cell range, second largest value if there are 6, 7 or 8 cell values in a cell range and the largest value if there 1,2,3,4 or 5 cell values in cell range. Here is a simplified example:
There will always be 10 cells in a cell range. The 10 cells may have one to ten values reflected in the cell range. If 9 to 10 of the cells contain a value the formula needs to return the third largest value. If 6 to 8 cells contain a value then the formula needs to return the second largest value and finally if 1 to 5 cells contain a value then the formula needs to return the largest value.
Column A
87
37
90
99
93
57
91
51
92
103
In this example all ten cells in the cell range have values therefore want to know the third largest value, which in this case is 93. If only eight of the cells had values (assume the first two cells are empty) then want to know the second largest value, or in this example 99.
Bookmarks