View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.misc
Gary''s Student Gary''s Student is offline
external usenet poster
 
Posts: 11,058
Default 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