display specific next highest value from a column containing multi
To simplify the formula we will cheat and use a "helper column". Say the
data is:
C23
C43
C56
4
5
C77
in G1 enter:
=IF(LEFT(F1,1)="C",--RIGHT(F1,LEN(F1)-1),"") and copy down
we see:
C23 23
C43 43
C56 56
4
5
C77 77
In another cell enter:
=MATCH(LARGE(G:G,2),G:G) this displays 3 ( the row with the second largest)
so LARGE() gets the second largest and MATCH() ids the row where it is.
--
Gary''s Student - gsnu200785
"bvasquez" wrote:
Just cant seem to get it. I am working with data in F10:F100 that contains
numbers like (1,2,3,4) as well as lettered numbers like (C1,C2,C3,C4). I can
not come up with a formula that will index this column and return the next
highest C number. F9 needs to display the next highest C number. I can get
this to work with the standard numbers only (1,2,3,4). Any suggestions
|