Highest n consecutive values

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

anon - 01 Apr 2007 01:13 GMT

Is there an easy way to extract the highest x (say, 6) consecutive
values from an array (say a1:a20)?

Thanks

Jim

Reply to this Message

Stan Brown - 01 Apr 2007 01:45 GMT

Sat, 31 Mar 2007 17:13:50 -0700 from anon <anon@anon.invalid.net>:

> Is there an easy way to extract the highest x (say, 6) consecutive
> values from an array (say a1:a20)?

Presumably you mean one at a time, or one per cell.

LARGE(array,k) returns the k-th largest number in a data set.

B1 has =LARGE($A$1:$A$20,1)
B2 has =LARGE($A$1:$A$20,2)
and so forth. This puts the largest in B1, next largest in B2, and so
on.

You don't have to type all the formulas. Type this in B1
=LARGE($A$1:$A$20, ROW(B1))
and then click the Fill handle and drag for as many rows as desired.
ROW(B1) evaluates to 1, but it's a relative reference so when you
drag it will become ROW(B2), ROW(B3), etc.

Say you're not starting in B1, but in C27. Then you do it this way.
C27 gets
=LARGE($A$1:$A$20, ROW(C27)-ROW(C$26))
and when you click and drag the C27 reference will change but the
others will not.

Signature

Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/

Reply to this Message

anon - 01 Apr 2007 06:12 GMT

>Sat, 31 Mar 2007 17:13:50 -0700 from anon <anon@anon.invalid.net>:
>> Is there an easy way to extract the highest x (say, 6) consecutive
>> values from an array (say a1:a20)?
>
>Presumably you mean one at a time, or one per cell.

Guess I didn't describe it very well.

Let me try again.

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

A1:8
A2:10
A3:87
A4:100
A5:40
A6:50
A7:39
A8:100
A9:299
A10:49
A11:10
A12:18
A13:93
A14:30
A15:10
A16:40
A17:4
A18:70
A19:9
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.

Better?

Thanks

Jim

Reply to this Message

Stan Brown - 01 Apr 2007 15:15 GMT

Sat, 31 Mar 2007 22:12:26 -0700 from anon <anon@anon.invalid.net>:

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

Why not just sort the values first? Then it's a piece of cake with
e.g. =SUM(... INDIRECT ... ) ... )

Signature

Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/

Reply to this Message

anon - 01 Apr 2007 18:37 GMT

>Sat, 31 Mar 2007 22:12:26 -0700 from anon <anon@anon.invalid.net>:
>> I want to have a formula which can take a value (n), which in this
[quoted text clipped - 4 lines]
>Why not just sort the values first? Then it's a piece of cake with
>e.g. =SUM(... INDIRECT ... ) ... )

Sorting would destroy the original series, thereby precluding me from
extracting a set of CONSECUTIVE values from the original series.

Jim

Reply to this Message

Harlan Grove - 02 Apr 2007 09:18 GMT

anon <a...@anon.invalid.net> wrote...
...

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

...

>A20:149
>
[quoted text clipped - 16 lines]
>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))

Reply to this Message

Bernd - 02 Apr 2007 21:17 GMT

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

Reply to this Message

Harlan Grove - 02 Apr 2007 21:41 GMT

"Bernd" <bplumh...@gmail.com> wrote...

>Your formula will not work for
>3
[quoted text clipped - 9 lines]
>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.

Reply to this Message

Bernd - 03 Apr 2007 12:12 GMT

Hello,

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

Regards,
Bernd

Reply to this Message

Ragdyer - 01 Apr 2007 03:41 GMT

Care to explain what you exactly mean by "consecutive"?

You can enter this formula *anywhere*, and drag down to copy as many rows as
you wish to return the Nth largest values.

=LARGE(A1:A100,ROWS($1:1))

Signature

HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

> Is there an easy way to extract the highest x (say, 6) consecutive
> values from an array (say a1:a20)?
>
> Thanks
>
> Jim

Reply to this Message

anon - 01 Apr 2007 06:13 GMT

>Care to explain what you exactly mean by "consecutive"?

See my response to Stan. Sorry, guess I wasn't very clear.

>You can enter this formula *anywhere*, and drag down to copy as many rows as
>you wish to return the Nth largest values.
>
>=LARGE(A1:A100,ROWS($1:1))

Jim

Reply to this Message

T. Valko - 01 Apr 2007 06:44 GMT

Try this:

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

=MAX(SUBTOTAL(9,OFFSET(A1:A20,ROW(A1:A20)-MIN(ROW(A1:A20)),,6)))

Change the 6 to adjust the height as needed.

Based on your sample data returns 628

Biff

>>Care to explain what you exactly mean by "consecutive"?
>
[quoted text clipped - 7 lines]
>
> Jim

Reply to this Message

anon - 01 Apr 2007 08:12 GMT

>Try this:
>
[quoted text clipped - 6 lines]
>
>Based on your sample data returns 628

Thanks, I think we are getting close.

I think that formula requires cells A21 through A25 to be blank,
doesn't it? Is there a way to exclude the results from the cells
beneath the A1:a20 array?

Thanks

Jim

Reply to this Message

Ron Coderre - 01 Apr 2007 14:50 GMT

With your posted data in A1:A20
and
C1: 6

Then try this regular formula:

D1:
=MAX(INDEX(SUBTOTAL(9,OFFSET(INDEX($A$1:$A$20,ROW($A$1:INDEX($A:$A,ROWS($A$1:$A$20)-$C$1+1))-1),ROW($A$1:INDEX($A:$A,ROWS($A$1:$A$20)-$C$1+1))-1,,$C$1)),0))

Does that help?
***********
Regards,
Ron

XL2002, WinXP

> >Try this:
> >
[quoted text clipped - 16 lines]
>
> Jim

Reply to this Message

Ron Coderre - 01 Apr 2007 16:42 GMT

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?
***********
Regards,
Ron

XL2002, WinXP

> With your posted data in A1:A20
> and
[quoted text clipped - 32 lines]
> >
> > Jim

Reply to this Message

anon - 01 Apr 2007 23:12 GMT

>Actually.....this regular formula is quite a bit shorter than my previous
>one....
[quoted text clipped - 9 lines]
>
>Is that something you can work with?

Looks very promising. I'll do some testing and get back to you.

Much appreciated, one way or the other.

Thanks

Jim

Reply to this Message

anon - 02 Apr 2007 08:09 GMT

>Actually.....this regular formula is quite a bit shorter than my previous
>one....
[quoted text clipped - 9 lines]
>
>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

Reply to this Message

Stan Brown - 01 Apr 2007 15:13 GMT

Sat, 31 Mar 2007 19:41:16 -0700 from Ragdyer <RagDyer@cutoutmsn.com>:

> > Is there an easy way to extract the highest x (say, 6) consecutive
> > values from an array (say a1:a20)?

> Care to explain what you exactly mean by "consecutive"?
>
> You can enter this formula *anywhere*, and drag down to copy as many rows as
> you wish to return the Nth largest values.
>
> =LARGE(A1:A100,ROWS($1:1))

Is that right, $1 and not $A1? I've never seen that style of
reference, without a column, and there's nothing about it in the help
topic "About cell and range references".

Signature

Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/

Reply to this Message

JE McGimpsey - 01 Apr 2007 15:24 GMT

From Help ("About cell and range references", "The A1 reference style"
subtopic):

All cells in row 5 5:5

All cells in rows 5 through 10 5:10

All cells in column H H:H

All cells in columns H through J H:J

> Is that right, $1 and not $A1? I've never seen that style of
> reference, without a column, and there's nothing about it in the help
> topic "About cell and range references".

Reply to this Message

Stan Brown - 01 Apr 2007 23:05 GMT

Sun, 01 Apr 2007 08:24:45 -0600 from JE McGimpsey
<jemcgimpsey@mvps.org>:

> > Is that right, $1 and not $A1? I've never seen that style of
> > reference, without a column, and there's nothing about it in the help
> > topic "About cell and range references".

> From Help ("About cell and range references", "The A1 reference style"
> subtopic):
[quoted text clipped - 6 lines]
>
> All cells in columns H through J H:J

Thanks. I hadn't expanded that subtopic because it didn't seem
relevant, but I see that it was. Now I have to find an occasion to
use this. :)

Signature

Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/

Reply to this Message

Ragdyer - 02 Apr 2007 00:53 GMT

"<<<Now I have to find an occasion to use this. :)>>>"

The occasion is *every* time, where a user is allowed to manipulate formats
and formulas within a WS.

As I stated in my post, it can be entered just about anywhere, without
concerns about formula placement changing the results. Users, for some
reason, like to move and copy things.

Also, it's robust enough to retain its "identity" when you change WS
configurations.
Try inserting a *new* Row 1, 2, and 3 using your "Row(B1)", and see what
happens, compared to the "Rows($1:1)" formula.

The same holds true for copying across columns - Columns($A:A).

Signature

Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

> Sun, 01 Apr 2007 08:24:45 -0600 from JE McGimpsey
> <jemcgimpsey@mvps.org>:
[quoted text clipped - 17 lines]
> relevant, but I see that it was. Now I have to find an occasion to
> use this. :)

Reply to this Message

jan - 01 Apr 2007 16:16 GMT

Using the formula Ragdyer has written and with the amount of values in F1
(in your example 6) you can use this array-formula (Ctr+Shift+Enter)

=SUM(LARGE(--(A1:A100),ROW(INDIRECT("1:" & F1))))

It sums the 6 (F1) largest numbers and that is the same you asked for.

Jan

Reply to this Message

jan - 01 Apr 2007 18:35 GMT

Sorry, it is not the same you aked for.

Jan

Reply to this Message

anon - 01 Apr 2007 18:42 GMT

>Using the formula Ragdyer has written and with the amount of values in F1
>(in your example 6) you can use this array-formula (Ctr+Shift+Enter)
>
>=SUM(LARGE(--(A1:A100),ROW(INDIRECT("1:" & F1))))
>
>It sums the 6 (F1) largest numbers and that is the same you asked for.

No, it is not what I asked for.

I need the 6 consecutive numbers which add up to the highest value.

Finding the 6 largest is trivial, whether I do a pre-sort or not. I
wish that was what I needed, but it isn't.

Jim

Reply to this Message

T. Valko - 01 Apr 2007 19:18 GMT

Ron's formulas work.

Biff

>>Using the formula Ragdyer has written and with the amount of values in F1
>>(in your example 6) you can use this array-formula (Ctr+Shift+Enter)
[quoted text clipped - 11 lines]
>
> Jim

Reply to this Message

Rate this thread: