Hello. I'm trying to write a set of formulas to find the nth next biggest number from an unsorted range, but only if the adjacent cell isn't blank.
So in the example above, if I put 105 into a cell (lets call it C1), i want C2 to return 120, C3 to return 130, and C4 to return 160. C1,C2, C3, C4 are not adjacent in my actual sheet if that matters. I was using =SMALL(B5:B20, COUNTIF(B5:B20, "<"&C1)+1) to make it work when I didn't have to account for if Column A was blank or not (and incrementing the "+1" to get the next highest match). But now I can't wrap my head how to exclude values where column A is blank.
Many thanks in advance.
X | 160 |
X | 130 |
140 | |
110 | |
X | 120 |
100 |
So in the example above, if I put 105 into a cell (lets call it C1), i want C2 to return 120, C3 to return 130, and C4 to return 160. C1,C2, C3, C4 are not adjacent in my actual sheet if that matters. I was using =SMALL(B5:B20, COUNTIF(B5:B20, "<"&C1)+1) to make it work when I didn't have to account for if Column A was blank or not (and incrementing the "+1" to get the next highest match). But now I can't wrap my head how to exclude values where column A is blank.
Many thanks in advance.