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

[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?

 

[Max]

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

 

[Max]

In column A "=MAX(B1:IV1)" and copy down.

Trouble is, that simply returns a zero instead of the desired "C" for the
2nd row

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---

 

[T. Valko]

Assuming there won't be any rows that contain both text and numbers as is
demonstrated in your sample data.

This will return the "max" entry excluding the dashes:

=IF(COUNT(A2:F2),MAX(A2:F2),LOOKUP(2,1/((COUNTIF(A2:F2,">"&A2:F2)=0)*(A2:F2<>"-")),A2:F2))

Or, are you wanting the rightmost entry that isn't a dash? In your sample
data the rightmost non-dash entry also happens to be the "max" entry. If
that's what you want:

=LOOKUP(2,1/((A2:F2<>"")*(A2:F2<>"-")),A2:F2)

This will work if there are both text and numbers on the same row.

 

[DaniMa]

Thankyou thankyou thankyou that works perfectly. (I did want the max non-dash
entry - should have written that more clearly!)

Cheers!

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[DaniMa]

Ok, now something strange is going on. The formula worked perfectly on the
day i copied it across, and i changed the cell range to the ones i wanted.
Now, three days later, whenever i change the cell range (eg H37:Z37 for all
three sums) the formula is displayed, rather than a result. I have tried hide
all and show all formulae, and shift+ctrl+enter.

Any suggestions?

 

[T. Valko]

If the formula is displayed rather than the result of the formula:

1. Make sure the cell isn't formatted TEXT. Select the cell with the
formula. Goto the menu Format>Cells. On the Number tab select General and OK
out. With the formula cell still selected hit function key F2 then hit
Enter. This will re-register the formula with the correct format. If that
doesn't work...

2. Make sure you're not in View Formula Mode. Goto the menu
Tools>Options>View tab>in the Window Options group, uncheck Formulas>OK

 

[DaniMa]

Yes that was it, i had it set to text. Thought i was being clever justifying
the column but obviously not. Thanks again!