A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

highest value between rows



 
 
Thread Tools Display Modes
  #1  
Old March 19th, 2010, 08:26 AM posted to microsoft.public.excel.newusers
FORMULA
external usenet poster
 
Posts: 9
Default 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  
Old March 19th, 2010, 10:22 AM posted to microsoft.public.excel.newusers
Garreth Lombard
external usenet poster
 
Posts: 17
Default 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  
Old March 19th, 2010, 02:41 PM posted to microsoft.public.excel.newusers
JLatham
external usenet poster
 
Posts: 1,896
Default 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  
Old March 20th, 2010, 06:38 PM posted to microsoft.public.excel.newusers
T. Valko
external usenet poster
 
Posts: 15,759
Default 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  
Old March 21st, 2010, 02:34 PM posted to microsoft.public.excel.newusers
Don Guillett[_2_]
external usenet poster
 
Posts: 607
Default 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  
Old March 23rd, 2010, 06:49 AM posted to microsoft.public.excel.newusers
FORMULA
external usenet poster
 
Posts: 9
Default 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  
Old March 23rd, 2010, 07:55 AM posted to microsoft.public.excel.newusers
FORMULA
external usenet poster
 
Posts: 9
Default 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  
Old March 23rd, 2010, 01:20 PM posted to microsoft.public.excel.newusers
Don Guillett[_2_]
external usenet poster
 
Posts: 607
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:11 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.