Array formulas to calculate the five highest consecutive years

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Frank

Thank you for your help! Your formula works great as I explained the layout of the spreadsheet. However, I jsut found out that the years are listed across the spreadsheet and for the life of me I can't get the formula to convert to column versus rows. The data lists the associate's name in column A and in B,C,D there could be other information and then in the columns to the right of that area is where the last 10 salary years are listed...1995 to 2004. Then of course, the row reference would have to change for each associate. Does this make since at all. If not I could forward an excel spreadsheet. Thanks!!

----- Frank Kabel wrote: ----

H
if your valuea are within the range A1:Ax (without any blanks an
starting in row 1) try the following array formula (entered wit
CTRL+SHIFT+ENTER)
=MAX(SUMIF(OFFSET($A$1,ROW(INDIRECT("1:"
COUNTA(A:A)-4))-1,0,5),">0")


-
Regard
Frank Kabe
Frankfurt, German


Kay wrote
 
=MAX(SUMIF(OFFSET($A$1,0,COLUMN(INDIRECT("A:"
&CHAR(64+COUNTA(1:1)-4)))-1,,5),">0"))

will work if no data is beyond column Z. The problem is that columns
are not accessed numerically unless you switch to R1C1 notation.

Jerry
 
Hi Kay

Here's another option assuming your data is in E2:N2 and no blanks
in the range.

=MAX(MMULT((TRANSPOSE(COLUMN(E2:N2))<=COLUMN(E2:N2))*
(TRANSPOSE(COLUMN(E2:N2)+4)>=COLUMN(E2:N2)),TRANSPOSE(E2:N2)))

The array formula must be entered with <Shift><Ctrl><Enter>,
also if edited later.

The general formula is

=MAX(MMULT((TRANSPOSE(COLUMN(E2:N2))<=COLUMN(E2:N2))*
(TRANSPOSE(COLUMN(E2:N2)+(NUMBER-1))>=COLUMN(E2:N2)),TRANSPOSE(E2:N2)))

where NUMBER is the number of consecutive cells.


If data is in a column, e.g. A2:A11 this array formula will do the job:

=MAX(MMULT((ROW(A2:A11)<=TRANSPOSE(ROW(A2:A11)))*
(ROW(A2:A11)+(NUMBER-1)>=TRANSPOSE(ROW(A2:A11))),A2:A11))

Again to be entered with <Shift><Ctrl><Enter>
 
I guess, it's not a problem in this context, but to be on the
safe side, using my formula is only advisable with non-negative
numbers.
 
My first formulae only worked, if the range started in A2.
To allow the range to start in an arbitrary cell, use these formulae
instead. Still all non-negative numbers and no blanks:

For data in a row:

=MAX(MMULT((TRANSPOSE(COLUMN(E2:N2))<=COLUMN(E2:N2))*
(TRANSPOSE(COLUMN(E2:N2)-COLUMN(E2)+NUMBER)>=COLUMN(E2:N2)-
COLUMN(E2)+1),TRANSPOSE(E2:N2)))

for data in a column:

=MAX(MMULT((ROW(E2:E11)<=TRANSPOSE(ROW(E2:E11)))*
(ROW(E2:E11)-ROW(E2)+NUMBER>=TRANSPOSE(ROW(E2:E11)-ROW(E2)+1)),E2:E11))

where NUMBER is the number of consecutive cells.
 
Hi,

Leo Heuser provided a solution. Have you tried it?

Consult Google archives to find out old answers.
http://groups.google.com/ and provide this NG name and any other criteria you
see fit.

Regards,

Daniel M.


Kay said:
Hello again everyone,

I am so dissapointed in myself, but I just can't get either formula suggestion to work.

I copied them exactly as you gave them to me and used the same row and column
Click to expand...
references, but it does not give any answer.
Jerry I particulary wanted to use the formula you gave based on Frank's
Click to expand...
original, but it keeps giving me zero. Any suggestions or maybe more detailed
instructions although I can't imagine what.
Thanks
Kay
----- Jerry W. Lewis wrote: -----

=MAX(SUMIF(OFFSET($A$1,0,COLUMN(INDIRECT("A:"
&CHAR(64+COUNTA(1:1)-4)))-1,,5),">0"))

will work if no data is beyond column Z. The problem is that columns
are not accessed numerically unless you switch to R1C1 notation.

Jerry
Click to expand...
layout of the spreadsheet. However, I jsut found out that the years are listed
across the spreadsheet and for the life of me I can't get the formula to convert
to column versus rows. The data lists the associate's name in column A and in
B,C,D there could be other information and then in the columns to the right of
that area is where the last 10 salary years are listed...1995 to 2004. Then of
course, the row reference would have to change for each associate. Does this
make since at all. If not I could forward an excel spreadsheet. Thanks!!!
 
...
...
For data in a row:

=MAX(MMULT((TRANSPOSE(COLUMN(E2:N2))<=COLUMN(E2:N2))*
(TRANSPOSE(COLUMN(E2:N2)-COLUMN(E2)+NUMBER)>=COLUMN(E2:N2)-
COLUMN(E2)+1),TRANSPOSE(E2:N2)))
Click to expand...
...

Or reverse the order of the MMULT operands and eliminate some TRANSPOSE calls.

=MAX(MMULT(E2:N2,--(ABS(TRANSPOSE(COLUMN(E2:N2))
-COLUMN(OFFSET(E2:N2,0,0,1,COLUMNS(E2:N2)-NUMBER+1))-(NUMBER-1)/2)<NUMBER/2)))
 
Kay,
Yes, I have tried both and can't get either to work.
Click to expand...

Works for me (you might try Harlan's solution).

Please note it's an ARRAY formula so you need to enter it via Ctrl-Shift-Enter
and not only Enter (see Excel help on how to enter ARRAY formulas).

Regards,

Daniel M.
 
The only ways I can get zero is if
- the formula is not array entered and A1:E1 are nonnumeric
- the formula is array entered and A1:x1 are empty cells where columns
A:x span at least as many columns as the number of non-empty cells in row 1.

In the process of this testing, I noticed that the COUNTA(1:1)
critically requires that there be no empty cells between A1 and the end
of the data range.

Jerry
 
Harlan,
Or reverse the order of the MMULT operands and eliminate some TRANSPOSE calls.

=MAX(MMULT(E2:N2,--(ABS(TRANSPOSE(COLUMN(E2:N2))
-COLUMN(OFFSET(E2:N2,0,0,1,COLUMNS(E2:N2)-NUMBER+1))-(NUMBER-1)/2)<NUMBER/2)))
Click to expand...

I like the idea!

FWIW, I tried to generate a 'similar' matrix (2nd arg to MMULT).

=MAX(MMULT(E2:N2,--(ABS(TRANSPOSE(COLUMN(E2:N2))
-COLUMN(E2:N2)-(Number-1)/2)<Number/2)))

It's a simplified formula but it implies a bigger matrix (nCols * nCols, always,
so a little more calcs) and it works only if E2:N2 contains >=0 numbers.

Regards,

Daniel M.
 
Back
Top