If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
highest value between rows
hi..
Datas a A B C D E 1 AA08 AA07 AA09 AA30 2 I want to display the highest value(AA30) in E1. Any formula for this? Thanks. |
#2
|
|||
|
|||
highest value between rows
Hi there,
You might want to concider using the vlookup formula combined with the max in this one. =VLOOKUP(MAX(range, range_including_answer_col, col_ref,0)) Put this formula in cell AA30 Hope it helps you -- Thank you and Regards Garreth Lombard "formula" wrote: hi.. Datas a A B C D E 1 AA08 AA07 AA09 AA30 2 I want to display the highest value(AA30) in E1. Any formula for this? Thanks. |
#3
|
|||
|
|||
highest value between rows
??? Garreth, you lost me. Maybe I'm missing something and you could explain
with sample entries in the VLOOKUP()? "Garreth Lombard" wrote: Hi there, You might want to concider using the vlookup formula combined with the max in this one. =VLOOKUP(MAX(range, range_including_answer_col, col_ref,0)) Put this formula in cell AA30 Hope it helps you -- Thank you and Regards Garreth Lombard "formula" wrote: hi.. Datas a A B C D E 1 AA08 AA07 AA09 AA30 2 I want to display the highest value(AA30) in E1. Any formula for this? Thanks. |
#4
|
|||
|
|||
highest value between rows
It depends on what the numeric portion of the string is.
This array formula** will work on your posted sample data. =INDEX(A11,MATCH(TRUE,COUNTIF(A11,""&A11)=0 ,0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. However, if this was your data: AA100 AA08 AA07 AA30 The formula would still evaluate AA30 as being the "max" value in the range. If you want to see how Excel evaluates this just sort the data in ascending order and you'll get: AA07 AA08 AA100 AA30 If you only want to evaluate the numeric portion of the string like this: 7 8 30 100 Then it get's kind of complicated! -- Biff Microsoft Excel MVP "formula" wrote in message ... hi.. Datas a A B C D E 1 AA08 AA07 AA09 AA30 2 I want to display the highest value(AA30) in E1. Any formula for this? Thanks. |
#5
|
|||
|
|||
highest value between rows
One macro solution
Sub maxnumberintextcells() myrow = 11 ' change to your row fc = 1 'column A lc = Cells(myrow, Columns.Count).End(xlToLeft).Column MMax = 0 For i = fc To lc For j = 1 To Len(Cells(myrow, i)) If IsNumeric(Mid(Cells(myrow, i), j)) Then x = Mid(Cells(myrow, i), j, 9999) Exit For End If Next j If x MMax Then MMax = x Next i MsgBox MMax End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "formula" wrote in message ... hi.. Datas a A B C D E 1 AA08 AA07 AA09 AA30 2 I want to display the highest value(AA30) in E1. Any formula for this? Thanks. |
#6
|
|||
|
|||
highest value between rows
i forgot something.. let's say datas a A B C D E F G 1 AA08 AA18 AB03 AA30 AB15 AC01 2 It's determining highest alpha numeric values. Value to be displayed in G1 must be the highest alpha numeric which is AC01. Pls advise. Thanks. |
#7
|
|||
|
|||
highest value between rows
i forgot something.. let's say datas a
A B C D E F G 1 AA08 AA18 AB03 AA30 AB15 AC01 2 It's determining highest alpha numeric values. there are only four characters per value as "AA01". Value to be displayed in G1 must be the highest alpha numeric which is AC01. Pls advise. Thanks. "T. Valko" wrote: It depends on what the numeric portion of the string is. This array formula** will work on your posted sample data. =INDEX(A11,MATCH(TRUE,COUNTIF(A11,""&A11)=0 ,0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. However, if this was your data: AA100 AA08 AA07 AA30 The formula would still evaluate AA30 as being the "max" value in the range. If you want to see how Excel evaluates this just sort the data in ascending order and you'll get: AA07 AA08 AA100 AA30 If you only want to evaluate the numeric portion of the string like this: 7 8 30 100 Then it get's kind of complicated! -- Biff Microsoft Excel MVP "formula" wrote in message ... hi.. Datas a A B C D E 1 AA08 AA07 AA09 AA30 2 I want to display the highest value(AA30) in E1. Any formula for this? Thanks. . |
#8
|
|||
|
|||
highest value between rows
Did you try my macro
-- Don Guillett Microsoft MVP Excel SalesAid Software "formula" wrote in message ... i forgot something.. let's say datas a A B C D E F G 1 AA08 AA18 AB03 AA30 AB15 AC01 2 It's determining highest alpha numeric values. Value to be displayed in G1 must be the highest alpha numeric which is AC01. Pls advise. Thanks. |
Thread Tools | |
Display Modes | |
|
|