maximum consecutive values formula

[hrben]

I am looking to write a formula that will tell me the columns containing the
five highest consecutive numbers in a given row that I can do a lookup
against in another spreadsheet. For example, I have a spreadsheet that
contains 15-year salary data (each year in its own column) for a group (the
first column would have the identifier of the individual). In the second
spreadsheet, I plugh in the identifier for the individual and want the
spreadsheet to automatically pull the five highest consecutive years of
salary from the other spreadsheet (each year into a row). If I use the max
or large formula in my second spreadsheet, I am only going to get one return
- and I am really looking for more of an array ...

 

[Shane Devenshire]

Hi,

Please show us a sample of your data and the result you would want based on
that sample.

 

[francis]

what formula are you using?
How does your sample look like?
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked


Thank You

cheers, francis

 

[Gary''s Student]

Say we have data in column A from A1 thru A30. In B5 enter:

=SUM(A1:A5) and copy down

In C1 enter:

=MAX(B:B)

Here is an example:

54 353
51
51
72
32 260
76 282
60 291
88 328
41 297
7 272
49 245
88 273
40 225
6 190
48 231
100 282
42 236
66 262
97 353
26 331
88 319
13 290
68 292
48 243
11 228
40 180
45 212
82 226
84 262
97 348

 

[hrben]

In the first spreadsheet, I have salary data in each row for each individual
(cells A2 though P2 for one particular person). For example:

SSN 2007 2006 2005 2004 2003
2002
111-22-3333 $50,000 $45,000 $40,000 $35,000 30,000 $60,000

In the second spreadsheet, I am creating a detailed statement/calculation
for this individual. I want to type in the identifier (social security
number) and have the spreadsheet automatically pull the years and
corresponding dollar amount for these years from the first spreadsheet (I
need the five consecutive years in which compensation was the highest).
Using the example above, I would want the spreadsheet to return 2002-2006) in
this layout:

Column C: Column D:
2006 $45,000
2005 $40,000
2004 $35,000
2003 $30,000
2002 $60,000

 

[T. Valko]

This is somewhat complicated so I put together a small sample file to
demonstrate this.

x5yrSalLookup.xls 19kb

http://cjoint.com/?dfawPmTGHV

Instead of finding the *last* year of the 5 year max and going backwards I
find the *first* year and go forward. If there are duplicate max salaries
the formula will match the first instance from left to right.

The formula to get the first year is an array formula**.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.