A
anon
Is there an easy way to extract the highest x (say, 6) consecutive
values from an array (say a1:a20)?
Thanks
Jim
values from an array (say a1:a20)?
Thanks
Jim
S
Stan Brown
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.
R
Ragdyer
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))
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))
A
anon
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
A
anon
See my response to Stan. Sorry, guess I wasn't very clear.
Jim
T
T. Valko
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
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
A
anon
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
R
Ron Coderre
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
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
S
Stan Brown
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".
J
JE McGimpsey
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
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
J
jan
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
(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
R
Ron Coderre
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
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
J
jan
Sorry, it is not the same you aked for.
Jan
Jan
A
anon
Sorting would destroy the original series, thereby precluding me from
extracting a set of CONSECUTIVE values from the original series.
Jim
A
anon
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
T
T. Valko
Ron's formulas work.
Biff
Biff
anon said:
S
Stan Brown
Sun, 01 Apr 2007 08:24:45 -0600 from JE McGimpsey
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.
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.
A
anon
Looks very promising. I'll do some testing and get back to you.
Much appreciated, one way or the other.
Thanks
Jim
R
Ragdyer
"<<<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).
>>>"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).