I'm using Excel 97-2003 version.
In a spreadsheet, I have a threshold limit (Number) in Cell N126 which i want to compare with number values in Column Range N130:N161 and return second highest value in Cell P131 where i am getting the second highest value result with Array formula
. Cntrl Shift Ent
Similarly, I want to retrieve second smallest value, compared to cell content of N126 in cell Q131, where i am getting confused, and seek help.
Any better suggestion to existing formula is also welcome whereby desired result can be obtained.
Later, I want to simply combine both formula and get the desired result anywhere in spreadsheet based on criteria of Cell M124. To Illustrate, IF M124="B", then retrieve second highest value, else, second lowest value.
If I have not explaied well here, i am attaching sample file with color coding and what exactly i seek to avoid confusion.
Look forward for suggested corrected formula, thanks.