Is there an Excel formula to show the highest letter in a row?

D

DaniMa

I am trying to create a formula so that i can have one column which displays
the highest number or letter in any given row.
eg
1 - 2 - - 3
- - A B C -
- 1 - - - 2
etc.
Some cells are left blank (marked by dashes above). I would like a column
which displays the highest ranking (so 3 for the first row, C for the second
row and 2 for the third row) and automatically updates when new columns are
added.
Anyone know if this can be done?
 
M

Max

Pl don't multipost.

I've responded to your post in .worksheet.functions :
----------
One play, along these thoughts
-use CODE to base convert source data in each row range to numbers
- use MAX to grab the highest number in the row range (rowR), excluding code
45 (real dashes)
- use MATCH(MAX(rowR),rowR,0) to get the relative position num where the MAX
is
- use INDEX(rowR,MATCH(...)) to return the desired item in rowR

Insert a new col A for the purpose. Data as posted is now assumed in B2:G2
down, where the dashes are assumed to be real dashes (code 45)

Place in A2, array-enter, ie confirm the formula by pressing
CTRL+SHIFT+ENTER (instead of just pressing ENTER):
=INDEX(B2:G2,MATCH(MAX(IF(CODE(B2:G2)<>45,CODE(B2:G2))),IF(CODE(B2:G2)<>45,CODE(B2:G2)),0))
Copy A2 down to return required results
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top