Re: Array formulas to calculate the five highest consecutive years
Jerry W. Lewis
&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
Kay wrote:
> 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: -----
>
> Hi
> if your valuea are within the range A1:Ax (without any blanks and
> starting in row 1) try the following array formula (entered with
> CTRL+SHIFT+ENTER):
> =MAX(SUMIF(OFFSET($A$1,ROW(INDIRECT("1:" &
> COUNTA(A:A)-4))-1,0,5),">0"))
>
>
> --
> Regards
> Frank Kabel
> Frankfurt, Germany
>
>
> Kay wrote:
> > Hi everyone,
> >> I am trying to calculate the 5 highest consecutive annual salary
> > years out of 10 to show soc/sec. benefits. I tried an array formula
> > =sum(large(data, 1,2,3,4,5) which works quite nicely if you just want
> > the largest numbers, but I need to see the grand total for the five
> > largest in a consecutive order. Of course, I could create totals
> > based on 1-5,2-6,3-7, etc and then lookup the highest grand total,
> > but that seems to cumbersone for many employess. Do you have any
> > suggestions?
> >> Thanks
> > Kay
Leo Heuser
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>
--
Best Regards
Leo Heuser
Followup to newsgroup only please.
"Kay" <anon...@discussions.microsoft.com> skrev i en meddelelse
Leo Heuser
safe side, using my formula is only advisable with non-negative
numbers.
--
Best Regards
Leo Heuser
Followup to newsgroup only please.
"Leo Heuser" <leo.h...@NOSPAMadslhome.dk> skrev i en meddelelse
news:OKU9RKeN...@tk2msftngp13.phx.gbl...
Leo Heuser
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.
--
Best Regards
Leo Heuser
Followup to newsgroup only please.
"Leo Heuser" <leo.h...@NOSPAMadslhome.dk> skrev i en meddelelse
news:unrFKbgN...@tk2msftngp13.phx.gbl...
Kay
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 references, but it does not give any answer.
Jerry I particulary wanted to use the formula you gave based on Frank's 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: -----
Daniel.M
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" <anon...@discussions.microsoft.com> wrote in message
news:0BF73C88-FBDD-40AA...@microsoft.com...
Kay
----- Daniel.M wrote: -----
Harlan Grove
..
>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)))
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)))
--
To top-post is human, to bottom-post and snip is sublime.
Daniel.M
> Yes, I have tried both and can't get either to work.
>
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.
Jerry W. Lewis
- 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
Daniel.M
> 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)))
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.