 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / February 2008Trying to return an array of numbers and choose the highest
I am struggling with this: I have a Column of integer values, say: A1: A100 I want to sum the first three values at one time, say sum(A1:A3), then sum the next 3 values: sum(A2:A4), then the next 3: (A3:A5), and so on until sum(A98:A100). I then want to compare the results and return the highest value. Thanks! Well first of all if you're summing 100 values in A1:A100, 3 at a time, your last sum is A97:A99 not A98:A100. What happens with A100? Tyro >I am struggling with this: > [quoted text clipped - 6 lines] > > Thanks! The OP didn't say he/she was summing 100 values He/She said: >> I have a Column of integer values, say: A1: A100 Regards Martin > Well first of all if you're summing 100 values in A1:A100, 3 at a time, > your last sum is A97:A99 not A98:A100. What happens with A100? [quoted text clipped - 11 lines] >> >> Thanks! I have a Column of integer values, say: A1: A100 I want to sum the first three values at one time, say sum(A1:A3), then sum the next 3 values: sum(A2:A4), then the next 3: (A3:A5), and so on until sum(A98:A100). I then want to compare the results and return the highest value. That is what he said. I simply said that in groups of 3 from A1:A3, that A98:A100 *CANNOT* be the last group of three as the op states. The last group of 3 is A96:A99 Tyro > The OP didn't say he/she was summing 100 values > [quoted text clipped - 19 lines] >>> >>> Thanks! Stop thinking in groups of 3s. The groups are not 123, 456 etc. They are 123, 234, 345 etc. incrementing by 1 each time the last group will be 98 99 100 >I have a Column of integer values, say: A1: A100 > [quoted text clipped - 31 lines] >>>> >>>> Thanks! Sub sumthreeatatime() mc = "I" For i = 1 To 100 Step 3 mysum = Application.Sum(Range(Cells(i, mc), Cells(i + 2, mc))) If mysum > highest Then highest = mysum Next i MsgBox highest End Sub  SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com >I am struggling with this: > [quoted text clipped - 6 lines] > > Thanks! I also MIS read the request. Simply change mine below to eliminate the step 3 or Biff's =MAX(SUBTOTAL(9,OFFSET(A1,ROW(INDIRECT("1:"&ROWS(A1:A100)-(B1-1)))-1,,B1))) SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > Sub sumthreeatatime() > mc = "I" [quoted text clipped - 15 lines] >> >> Thanks! One way: Sub Demo() With ActiveSheet .Names.Add "x", [A1:A98] .Names.Add "y", [A2:A99] .Names.Add "z", [A3:A100] End With [B1].FormulaArray = "=MAX(x+y+z)" End Sub SignatureHTH :>) Dana DeLouis Windows XP & Excel 2007 >I am struggling with this: > [quoted text clipped - 6 lines] > > Thanks! Hi, Put your formula in Cell B3 =SUM(A1:A3) Then grab the fill handle and drag down to B100 The range will update automatically as you drag A2:A4, A3:A5 etc. Use =MAX(B3:B100) to return the highest. HTH Martin >I am struggling with this: > [quoted text clipped - 6 lines] > > Thanks! Can't do that. A100 stands alone. It is not in a group of 3. Tyro > Hi, > [quoted text clipped - 20 lines] >> >> Thanks! Try it out, the formula in B100 will be SUM(A98:A100) > Can't do that. A100 stands alone. It is not in a group of 3. > [quoted text clipped - 24 lines] >>> >>> Thanks! Sorry 100 / 3 = 33 and a remainder of 1 So A1:A3 A4:A6 A7:A9 A10:A12 A13:A15 A:16:A18 A19:A21 A22:A24 ... A88:A90 A91:A93 A94:A96 A97:A99 A100 You cannot divide 100 cells into an exact grouping of 3. Mathematically impossible. One cell must stand alone Tyro > Try it out, the formula in B100 will be SUM(A98:A100) > [quoted text clipped - 26 lines] >>>> >>>> Thanks! I misread the OP's request. Just spent the last 36 without sleep. Just got home from hospital. Tyro\ > Try it out, the formula in B100 will be SUM(A98:A100) > [quoted text clipped - 26 lines] >>>> >>>> Thanks! >Hi, > >Put your formula in Cell B3 >=SUM(A1:A3) In other words, a helper column. >Then grab the fill handle and drag down to B100 >The range will update automatically as you drag >A2:A4, A3:A5 etc. > >Use =MAX(B3:B100) to return the highest. This is how I always tackle such calculations, too. SignaturePaul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me) Without having to use an intermediate column of SUM formulas: B1 = interval size = 3 Array entered** : =MAX(SUBTOTAL(9,OFFSET(A1,ROW(INDIRECT("1:"&ROWS(A1:A100)-(B1-1)))-1,,B1))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) SignatureBiff Microsoft Excel MVP >I am struggling with this: > [quoted text clipped - 6 lines] > > Thanks! Other non-volatile alternatives (where x is the range a1:a100) =MAX(MMULT(--((ROW(x)-TRANSPOSE(ROW(x))+1)^2<=1),x)) =MAX(SUM(x)*PROB(ROW(x),x/SUM(x),ROW(x),ROW(x)+2)) (** array entered as above) > Without having to use an intermediate column of SUM formulas: > [quoted text clipped - 17 lines] > > > > Thanks! Very ingenious, I did not even know the PROB function existed. The calculation times ignoring volatility are: Helper column & MAX 0.25 SUBTOTAL(OFFSET 0.35 PROB 0.97 MMULT 6.8 Charles __________________________________________________ Outlines for my Sessions at the Australia Excel Users Group http://www.decisionmodels.com/OZEUC.htm > Other non-volatile alternatives (where x is the range a1:a100) > [quoted text clipped - 25 lines] >> > >> > Thanks! Thank you for the info Charles, It's given me the confidence to post my thoughts on this. First up I must say that the formulae posted by Biff and Tori are way in advance of any thing I could come up with and I admire their skill in creating these formulae. At the same time I can't see the reason why. To my way of thinking these formulae have to first go through the same raw calculations that the helper & max do and then they have extra work in analysing the results and delivering them in a neat little package. My thoughts on spreadsheet design is to keep it as basic as possible and use helper columns and cells freely and without guilt. Obviously there are times when the elegant solutions we see on the newsgroups are a much better option, but in general terms I think it would be far better to just use basic techniques and make it work. Sincerely Martin > Very ingenious, I did not even know the PROB function existed. > [quoted text clipped - 38 lines] >>> > >>> > Thanks! Both approaches have pros and cons, its often a matter of personal preference: The helper column approach can be easier to debug and understand, usually calculates faster and tends to make better use of Excel's Smart Recalc because usually only a few of the helper cells need to be recalculated, whereas an array formula always has to calculate all of its virtual cells. The single-cell array formula approach takes up less space in the workbook (although the actual virtual memory savings are small), and can be considered more "elegant". If the data volume is small enough then any extra calculation time will not be significant and debugging using F9 on the formula bar or the Evaluate Formula button works well. Sometimes the compactness of an array formula can be easier to understand than a sprawl of helper cells. Multi-cell array formulae have some additional advantages in that you can only alter the entire block of cells, so there is some additional protection against unwanted accidental copy/paste/drag/overwrite. There is also a third way: using UDFs. UDFs can often be faster than array formulae when properly written. Charles __________________________________________________ Outlines for my Sessions at the Australia Excel Users Group http://www.decisionmodels.com/OZEUC.htm > Thank you for the info Charles, > [quoted text clipped - 63 lines] >>>> > >>>> > Thanks! Charles, Would it follow that the macro approach or turning into a UDF be better than helper or array formula? Sub sumthreeatatime()'my earlier post mc = "I" For i = 1 To 100 mysum = Application.Sum(Range(Cells(i, mc), Cells(i + 2, mc))) If mysum > highest Then highest = mysum Next i MsgBox highest End Sub =sumthree("I") Function sumthree(mc) 'UDF For i = 2 To 100 mysum = Application.Sum(Range(Cells(i, mc), Cells(i + 2, mc))) If mysum > highest Then highest = mysum Next i sumthree = highest End Function SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > Both approaches have pros and cons, its often a matter of personal > preference: [quoted text clipped - 91 lines] >>>>> > >>>>> > Thanks! "Don Guillett" <dguille...@austin.rr.com> wrote... >Would it follow that the macro approach or turning into a UDF be >better than helper or array formula? ... >=sumthree("I") >Function sumthree(mc) 'UDF [quoted text clipped - 4 lines] >sumthree = highest >End Function ... <Char...@DecisionModels.com> wrote in message ... >>There is also a third way: using UDFs. UDFs can often be faster >>than array formulae when properly written. ... Charles did say 'when properly written'. udfs are always more flexible than macros, and they're subject to automatic recalculation whereas macros must be run. As for 'properly written', wouldn't it be more efficient to load the .Value property of the range argument into a VBA array and use that array rather than repeatedly accessing ranges? Also, wouldn't VBA + operations be faster than Application[.WorksheetFunction].Sum? IOW, Function maxsumn(rng As Range, n As Long) As Variant Dim sumn As Double, v As Variant, nv As Long, k As Long If rng.Columns.Count > 1 Then 'no 2D ranges maxsumn = CVErr(xlErrRef) Exit Function ElseIf rng.Rows.Count <= n Then 'return degenerate results fast maxsumn = Application.WorksheetFunction.Sum(rng) Exit Function End If v = rng.Value nv = rng.Cells.Count - n For k = 1 To n sumn = sumn + v(k, 1) Next k maxsumn = sumn For k = 1 To nv sumn = sumn - v(k, 1) + v(k + n, 1) If sumn > maxsumn Then maxsumn = sumn Next k End Function Timings are very dependent on the number of cells in the range: using Excel 2003 I get - for 100 cells: MMULT 6.8 sumthree 1.56 maxsumn 0.4 Subtotal(offset 0.35 helper 0.25 for 2000 cells: MMULT 2622. sumthree 26. maxsumn 1.56 Subtotal(offset 1.92 helper 0.32 (prob gives #num for 2000 cells, presumably overflow?) So the clear winner is the helper column, with Harlan's UDF edging out the Subtotal-offset array formula as the data gets larger. (But I did cheat slightly by optimising Harlan's UDF to use .Value2 rather than .Value, otherwise the UDF would have lost!) Charles __________________________________________________ Outlines for my Sessions at the Australia Excel Users Group http://www.decisionmodels.com/OZEUC.htm > "Don Guillett" <dguille...@austin.rr.com> wrote... >>Would it follow that the macro approach or turning into a UDF be [quoted text clipped - 50 lines] > > End Function "Charles Williams" <Char...@DecisionModels.com> wrote... >Timings are very dependent on the number of cells in the range: ... What would the time be for the array formula =MAX(A1:A98+A2:A99+A3:A100) ? >(prob gives #num for 2000 cells, presumably overflow?) More likely underflow in the x/SUM(x) term, but that'd mean the range between MIN(ABS(x)) and MAX(ABS(x)) would be more than 308 decimal orders of magnitude, which would render the max of adjacent 3-point sums rather meaningless. > What would the time be for the array formula > > =MAX(A1:A98+A2:A99+A3:A100) Timings are 0.25 for 100 and 1.05 for 2000, which puts it into second place behind helper. Charles __________________________________________________ Outlines for my Sessions at the Australia Excel Users Group http://www.decisionmodels.com/OZEUC.htm The PROB error results from =SUM(x/SUM(x)) being outside the bounds of 1. A check shows that to one sf: =1-4e-16=1+4e-15 returns true but false for larger decimals. There are ways around this e.g. =MAX(PROB(ROW(x)*{1,-1},x*{1,-1}+{0,1}*(ROW(x)=MIN(ROW(x))),ROW(x),ROW(x)+2)) but as the speed tests show other methods are far superior on larger datasets. Am I missing something... doesn't this do what the OP wanted? =SUMPRODUCT(MAX(A1:A98+A2:A99+A3:A100)) Rick > Other non-volatile alternatives (where x is the range a1:a100) > [quoted text clipped - 25 lines] >> > >> > Thanks! Or if you like array formulas: =MAX(A1:A98+A2:A99+A3:A100) as an array formula (ctrl-shift-enter) > Am I missing something... doesn't this do what the OP wanted? > [quoted text clipped - 35 lines] > >> > > >> > Thanks! SignatureDave Peterson >> Am I missing something... doesn't this do what the OP wanted? >> >> =SUMPRODUCT(MAX(A1:A98+A2:A99+A3:A100)) > > Or if you like array formulas: I don't <g>... I **always** seem to forget the Ctrl+Shift+Enter key combination after entering/editing them (SUMPRODUCT takes care of me from having to remember it; and, yes, I realize there are many array-entered formula constructions that can't be converted to non-array-entered ones by just slapping SUMPRODUCT around them). > =MAX(A1:A98+A2:A99+A3:A100) > > as an array formula (ctrl-shift-enter) But I was right (that is, I didn't misread the problem)... we are not talking about anything more complex than my SUMPRODUCT or your array-entered formula, correct? Rick I tend to agree with you about array formulas. It always scares me when I share workbooks with array formulas. And it looks like you understood the question. > >> Am I missing something... doesn't this do what the OP wanted? > >> [quoted text clipped - 17 lines] > > Rick SignatureDave Peterson Yet another one: =MAX(INDEX(A1:A98+A2:A99+A3:A100,,1)) P.S. - I had flexibility in mind when I offered my suggestion. SignatureBiff Microsoft Excel MVP >>> Am I missing something... doesn't this do what the OP wanted? >>> [quoted text clipped - 17 lines] > > Rick ...which is essentially the same as Dana's suggestion - and am sure is adequate for the needs of the op. The alternatives given above related to Biff's formula that are easily adapted for larger interval sizes than 3. i tend to agree with Martin's comments, that simple, easily understood solutions should be a top priority. Offering more general solutions to a problem, however, may be useful both to the op and in future searches - and also provides more interesting challenges! > Or if you like array formulas: > [quoted text clipped - 41 lines] > > >> > > > >> > Thanks! The formula that you guys and gals come up with are pretty much beyond me. I'd surely use the helper column approach. > ...which is essentially the same as Dana's suggestion - and am sure is > adequate for the needs of the op. The alternatives given above related to [quoted text clipped - 54 lines] > > > > Dave Peterson SignatureDave Peterson =MAX(SUM(x)*PROB(ROW(x),x/SUM(x),ROW(x),ROW(x)+2)) That's pretty slick. I've never used the PROB function before. One thing I would do is to make the interval offset more intuitve. SignatureBiff Microsoft Excel MVP > Other non-volatile alternatives (where x is the range a1:a100) > [quoted text clipped - 25 lines] >> > >> > Thanks! For a general interval length i, just change the 2 to i-1. Note also that the formula applies with negative values. The PROB function actually works with any values contrary to what help says, the only restriction is that the values sum to one (it would be even more helpful if this condition were not checked!) > =MAX(SUM(x)*PROB(ROW(x),x/SUM(x),ROW(x),ROW(x)+2)) > [quoted text clipped - 31 lines] > >> > > >> > Thanks! |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.