Trying to return an array of numbers and choose the highest

[Rick Rothstein \(MVP - VB\)]

Am I missing something... doesn't this do what the OP wanted?

=SUMPRODUCT(MAX(A1:A98+A2:A99+A3:A100))

Rick

 

[Dave Peterson]

Or if you like array formulas:

=MAX(A1:A98+A2:A99+A3:A100)

as an array formula (ctrl-shift-enter)

 

[Rick Rothstein \(MVP - VB\)]

Am I missing something... doesn't this do what the OP wanted?

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

 

[Lori]

....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!

 

[Dave Peterson]

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.

 

[Dave Peterson]

The formula that you guys and gals come up with are pretty much beyond me.

I'd surely use the helper column approach.

 

[T. Valko]

Yet another one:

=MAX(INDEX(A1:A98+A2:A99+A3:A100,,1))

P.S. - I had flexibility in mind when I offered my suggestion.

 

[T. Valko]

=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.

 

[Lori]

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!)

 

[Harlan Grove]

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
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 ....

....

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

 

[Charles Williams]

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

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
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 ...

...

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

 

[Harlan Grove]

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.

 

[Charles Williams]

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

 

[Lori]

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.