Is there an Excel formula to show the highest letter in a row?
You're welcome. Thanks for the feedback!
--
Biff
Microsoft Excel MVP
"DaniMa" wrote in message
...
Thankyou thankyou thankyou that works perfectly. (I did want the max
non-dash
entry - should have written that more clearly!)
Cheers!
"T. Valko" wrote:
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.
--
Biff
Microsoft Excel MVP
"DaniMa" wrote in message
...
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?
|