find the highest number in a string of numbers

Jerk24

Board Regular
Joined
Oct 10, 2012
Messages
190
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

<tbody>
</tbody><colgroup><col></colgroup>
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Jerk24,

Sample raw data:


Excel 2007
CDEFGHIJKL
3396352446857497362
Sheet1
Cell Formulas
RangeFormula
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
Cell Formulas
RangeFormula
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.
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
The site will not work on my computer. If you would like to send me a private message I will email you.
 
Upvote 0
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?
 
Upvote 0
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 1Wk 2Wk 3Wk 4Wk 5Wk 6Wk 7Wk 8Wk 9
43172836
Wk 10Wk 11Wk 12Wk 13Wk 14Wk 15Wk 16Wk 17Wk 18# GamesPointsAVGHi GmHi TrpTurksLst Wk
1212410.33214303610

<colgroup><col style="width: 34pt; mso-width-source: userset; mso-width-alt: 1645;" span="9" width="45"> <col style="width: 47pt; mso-width-source: userset; mso-width-alt: 2267;" width="62"> <col style="width: 46pt; mso-width-source: userset; mso-width-alt: 2230;" width="61"> <col style="width: 49pt; mso-width-source: userset; mso-width-alt: 2377;" width="65"> <col style="width: 44pt; mso-width-source: userset; mso-width-alt: 2157;" width="59"> <col style="width: 38pt; mso-width-source: userset; mso-width-alt: 1828;" width="50"> <col style="width: 31pt; mso-width-source: userset; mso-width-alt: 1499;" width="41"> <col style="width: 38pt; mso-width-source: userset; mso-width-alt: 1828;" width="50"> <col style="width: 41pt; mso-width-source: userset; mso-width-alt: 1974;" width="54"> <tbody>
</tbody>
 
Upvote 0
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
 
Upvote 0
Thank you. that works. how would i bring that to the rest of my sheet, and then do it automatically.
 
Upvote 0
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)?
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top