Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 25

Thread: find the highest number in a string of numbers

  1. #1
    Board Regular
    Join Date
    Oct 2012
    Posts
    190
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default find the highest number in a string of numbers

    I have asked two questions today both answered. sooooo

    is it possible to find the highest number is a string of numbers, in different cells:

    cell c3 = 12+13+14
    cell d3 = 20+21+22
    cell e3 = 12+20+20

    there are alot more and was wondering if there was a way for me to just add everything and not worry about the "HIGH Game"? the formula i have =LARGE(C3:K3,1) Which brings the highest number, not the single highest number

  2. #2
    Board Regular
    Join Date
    Apr 2009
    Location
    Northeast PA, USA
    Posts
    17,589
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    6 Thread(s)

    Default Re: find the highest number in a string of numbers

    Jerk24,

    Sample raw data:

    Excel 2007
    CDEFGHIJKL
    3396352446857497362

    Sheet1



    Worksheet Formulas
    CellFormula
    C3=12+13+14
    D3=20+21+22
    E3=12+20+20
    F3=12+13+14+5
    G3=20+21+22+5
    H3=12+20+20+5
    I3=12+13+14+10
    J3=20+21+22+10
    K3=12+20+20+10



    After the macro:

    Excel 2007
    CDEFGHIJKL
    339635244685749736222

    Sheet1



    Worksheet Formulas
    CellFormula
    C3=12+13+14
    D3=20+21+22
    E3=12+20+20
    F3=12+13+14+5
    G3=20+21+22+5
    H3=12+20+20+5
    I3=12+13+14+10
    J3=20+21+22+10
    K3=12+20+20+10



    Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

    1. Copy the below code
    2. Open your NEW workbook
    3. Press the keys ALT + F11 to open the Visual Basic Editor
    4. Press the keys ALT + I to activate the Insert menu
    5. Press M to insert a Standard Module
    6. Where the cursor is flashing, paste the code
    7. Press the keys ALT + Q to exit the Editor, and return to Excel
    8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

    Code:
    Option Explicit
    Sub GetHighestNumber()
    ' hiker95, 11/02/2013
    ' http://www.mrexcel.com/forum/excel-questions/736816-find-highest-number-string-numbers.html
    Dim c As Range, rng As Range, h As String, s, i As Long, maxn As Long
    Range("L3").ClearContents
    maxn = 0
    For Each c In Range("C3:K3")
      h = c.Formula
      s = Split(Right(h, Len(h) - 1), "+")
      For i = LBound(s) To UBound(s)
        If s(i) > maxn Then maxn = s(i)
      Next i
    Next c
    Range("L3") = maxn
    End Sub
    Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

    Then run the GetHighestNumber macro.


    Let me see if I can change the macro into a user defined function - no macro to run.
    Have a great day,
    hiker95

    Windows 10, Excel 2007, on a PC.

  3. #3
    Board Regular
    Join Date
    Oct 2012
    Posts
    190
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: find the highest number in a string of numbers

    Thank you. I will see if I can intergrate the Macro. but if you can find a way to do it without one, that would be better... either way Thank you.

    I ran the macro, I needed to change Range("L3").ClearContents to read O because that is where I currently have the high game.

    s = Split(Right(h, Len(h) - 1), "+") this line is getting a invaild call or arugument error. (i tried with both the oringial post and my adjusted)

    any ideas?
    Last edited by Jerk24; Nov 3rd, 2013 at 08:18 AM. Reason: Missing Information

  4. #4
    Board Regular
    Join Date
    Apr 2009
    Location
    Northeast PA, USA
    Posts
    17,589
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    6 Thread(s)

    Default Re: find the highest number in a string of numbers

    Jerk24,

    In order to continue, and, so that I can get it right this next time:

    You can upload your workbook to Box Net,
    sensitive data changed
    mark the workbook for sharing
    and provide us with a link to your workbook.
    Have a great day,
    hiker95

    Windows 10, Excel 2007, on a PC.

  5. #5
    Board Regular
    Join Date
    Oct 2012
    Posts
    190
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: find the highest number in a string of numbers

    The site will not work on my computer. If you would like to send me a private message I will email you.

  6. #6
    Board Regular
    Join Date
    Apr 2009
    Location
    Northeast PA, USA
    Posts
    17,589
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    6 Thread(s)

    Default Re: find the highest number in a string of numbers

    Jerk24,

    If you would like to send me a private message I will email you.
    No, thank you.


    One last try:


    1. What is the range of cells that contain the formulae?

    2. List each cell, and, each cell formula:
    C3 = ?
    D3 = ?
    .....

    3. What cell do you want the result in?
    Have a great day,
    hiker95

    Windows 10, Excel 2007, on a PC.

  7. #7
    Board Regular
    Join Date
    Oct 2012
    Posts
    190
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: find the highest number in a string of numbers

    Ok below is a shot from my sheet. I have wk1 starting in C3. O5 is where i need the High game (the highest individual number out of all cells in yellow)
    There are more players, but i can adjust accodingly. So if you basically i will need the data pulled from C3:K3, C5:k5.

    the data set is not imporant, since 48 people will not have the same scores. you post early will work for the acutal data.

    Wk 1 Wk 2 Wk 3 Wk 4 Wk 5 Wk 6 Wk 7 Wk 8 Wk 9
    43 17 28 36
    Wk 10 Wk 11 Wk 12 Wk 13 Wk 14 Wk 15 Wk 16 Wk 17 Wk 18 # Games Points AVG Hi Gm Hi Trp Turks Lst Wk
    12 124 10.33 21 43 0 36 10

  8. #8
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    33,903
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    28 Thread(s)

    Default Re: find the highest number in a string of numbers

    Does the macro output the value you want?

    Code:
    Sub MaxAddend()
      Dim Pluses As String, Numbers As Variant
      With WorksheetFunction
        Pluses = .Trim(Join(.Index(Range("C3:K3").Formula, 1, 0), " ")) & _
                 "+" & .Trim(Join(.Index(Range("C5:K5").Formula, 1, 0), " "))
        Pluses = .Trim(Replace(Replace(Pluses, "+", " "), "=", " "))
        Range("O5").Value = Evaluate("MAX(" & Replace(Pluses, " ", ",") & ")")
      End With
    End Sub
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  9. #9
    Board Regular
    Join Date
    Oct 2012
    Posts
    190
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: find the highest number in a string of numbers

    Thank you. that works. how would i bring that to the rest of my sheet, and then do it automatically.

  10. #10
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    33,903
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    28 Thread(s)

    Default Re: find the highest number in a string of numbers

    Quote Originally Posted by Jerk24 View Post
    Thank you. that works. how would i bring that to the rest of my sheet, and then do it automatically.
    What exactly do you mean by "bring that to the rest of my sheet"? You have other groupings like you showed in Message #7? If so, where are they (it is kind of hard to calculate values from, or write output to, other cells if you do not know where they are located at)?
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •