Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
maximum consecutive values formula
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 ... -- hrben |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
maximum consecutive values formula
Hi,
Please show us a sample of your data and the result you would want based on that sample. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "hrben" wrote: 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 ... -- hrben |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
maximum consecutive values formula
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 "hrben" wrote: 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 ... -- hrben |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
maximum consecutive values formula
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 -- Gary''s Student - gsnu200835 "hrben" wrote: 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 ... -- hrben |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
maximum consecutive values formula
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 -- hrben "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200835 "hrben" wrote: 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 ... -- hrben |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
maximum consecutive values formula
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. -- Biff Microsoft Excel MVP "hrben" wrote in message ... 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 -- hrben "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200835 "hrben" wrote: 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 ... -- hrben |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Consecutive Values | Excel Discussion (Misc queries) | |||
Exclude #N/A values and Return Numeric values to consecutive cells in Single Row | Excel Worksheet Functions | |||
Formula with minimum & maximum values | Excel Worksheet Functions | |||
Using Formula based Cell Content Return Unique Consecutive Duplicate Values | Excel Worksheet Functions | |||
Return Consecutive Values | Excel Worksheet Functions |