Trying to return an array of numbers and choose the highest

[meghnaubhan]

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!

 

[Tyro]

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

 

[Don Guillett]

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

 

[Dana DeLouis]

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

 

[MartinW]

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

 

[MartinW]

The OP didn't say he/she was summing 100 values

He/She said:
Regards
Martin

 

[Tyro]

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

 

[Tyro]

Can't do that. A100 stands alone. It is not in a group of 3.

Tyro

 

[MartinW]

Try it out, the formula in B100 will be SUM(A98:A100)

 

[MartinW]

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

 

[Tyro]

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

 

[Tyro]

I misread the OP's request. Just spent the last 36 without sleep. Just got
home from hospital.

Tyro\

 

[T. Valko]

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)

 

[Paul Hyett]

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.

 

[Lori]

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)

 

[Charles Williams]

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

 

[MartinW]

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

 

[Don Guillett]

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

 

[Charles Williams]

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

 

[Don Guillett]

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