Highest n consecutive values

[anon]

Actually.....this regular formula is quite a bit shorter than my previous
one....

D1:
=MAX(INDEX(SUBTOTAL(9,OFFSET(A1,ROW($A$1:INDEX(A:A,ROWS(A1:A20)-C1+1))-1,,6,)),0))

or...for the ARRAY FORMULA version (committed with ctrl+shift+enter):
D1: =MAX(SUBTOTAL(9,OFFSET(A1,ROW($A$1:INDEX(A:A,ROWS(A1:A20)-C1+1))-1,,6,)))

Where cell C1 contains the number of cells you want aggregated and tested.
In your example, C1: 6

Is that something you can work with?

Yes, indeed. I think both formulas have a "6" in them which should be
C1, right?

On my machine, both formulas work either as regular or as array
formulas.

I would really like to understand the formula, so I've taken it apart,
bit by bit to try to do so. But I have failed. :-((

If anybody wants to take a stab at explaining it to me, I would
appreciate it.

I am now generally familiar with the help topics on SUBTOTAL, OFFSET
and INDEX, but something tells me that I don't understand their
nuances very well.

I think I want to work with the array formula because I may need to
drive this array (A1:A20) with a multiplier, where the multiplier
might be zero and, most importantly, where if the multiplier is zero,
the cell drops out of the calculation. I know I'm asking for a lot,
especially since I don't (yet) understand the underlying formula, but
I thought I'd lay out where I now know I (eventually) need to get to.

For example, using the same data, but changed ever so slightly:

A1:8
A2:10
A3:87
A4:100
A5:40
A6:50
A7:39
A8:100
A9:299
A10:0
A11:150
A12:18
A13:93
A14:30
A15:10
A16:40
A17:4
A18:70
A19:9
A20:149

I have dropped A10 to zero (meaning it should have no impact on the
calculations) and therefore really have a 20 item array rather than a
21 item array. I have increased A11 to 150 so that it would be part
of the 6 highest that runs from A5:A11 (skipping A10 entirely). The
six highest are now 678.

I suspect that if I understood the array formula, I could make this
work (eventually). Then again, I might be wrong and the introduction
of this latest wrinkle might mean that I have to go the VBA route.

Can it be done in a cell formula?

Thanks

Jim

 

[Harlan Grove]

Say I've got the following values in A1 through A20:

A1:8 ....
A20:149

In cell b1 I put:

b1:SUM(a1:a6)

and copy that down to cell b15, which has

b15:Sum(a15:a20)

Then, in cell c1 I put:

c1:max(b1:b15)

c1 has the number I'm looking for.

I want to have a formula which can take a value (n), which in
this case is 6, but might be another value at some point, and
come out with the sum of the n consecutive values which is the
largest of all n consecutive values.

....

Another alternative, with the data range names D (your A1:A20) and the
number of consecutive values sought named N (your 6), using only
nonvolatile functions but in an array formula,

=MAX(MMULT(--(MOD(TRANSPOSE(ROW(D))-ROW(D),2*ROWS(D))<N),D))

or with your original ranges and values,

=MAX(MMULT(--(MOD(TRANSPOSE(ROW(A1:A20))-ROW(A1:A20),
2*ROWS(A1:A20))<6),A1:A20))

This assumes there are only positive values in D. If there could be
nonpositive values as well, use the array formula

=MAX(MMULT((ABS(2*(TRANSPOSE(ROW(D))-ROW(D))-N+1)<N)
*(ROW(D)-MIN(ROW(D))<=ROWS(D)-N),D))

 

[Bernd]

Hello Harlan,

My previous answer obviously did not show up.

Your formula will not work for
3
5
4
3
2
-250
-250
-250
-250
1000
with N=5
Your result is 1000 but 17 is correct.

I suggest something like
=MAX(MMULT(--(ABS(TRANSPOSE(ROW(INDIRECT("1:"&COUNTA(D))))-
ROW(INDIRECT("1:"&COUNTA(D)-N+1))-(N-1)/2)<=(N-1)/2),D))
array-entered.

Regards,
Bernd

 

[Harlan Grove]

Your formula will not work for
3
5
4
3
2
-250
-250
-250
-250
1000
with N=5
Your result is 1000 but 17 is correct.

You obviously didn't read or didn't understand the last paragraph and
formula in my previous response in this thread. To repeat,

This [namely my first formula] assumes there are only positive
values in D. If there could be nonpositive values as well, use
the array formula

=MAX(MMULT((ABS(2*(TRANSPOSE(ROW(D))-ROW(D))-N+1)<N)
*(ROW(D)-MIN(ROW(D))<=ROWS(D)-N),D))

With your sample data, the formula immediately above returns 17.

 

[Bernd]

Hello,

Oops, I tested your first formula with negative values, not your
second one. Sorry, you are right.

Regards,
Bernd