Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find 3 numbers +/- 26 rows from each other
I am trying to do a payroll calculation and was hoping someone may have a
solution for me. I have a column with over 130 numbers in it (130 numbers in a single column.). I want to bring back the largest value in an array. Then I want to bring back the second largest values in the same array so long as it does not fall with +/- 26 rows of the largest number.) Last I want to bring back the third largest value that doesnt faull with +/- 26 rows of the first or second largest numbers found earlier. This is for a payroll. I am trying to find a persons three highest years of earnings. My first column (A) has the dollar amount of every paycheck my person had for each pay period (26 a year) in a five year period. Column (B) sums 26 paychecks to come up with what 12 months of earnings would be (they dont have to be calendar years, they can be April 1st to March 31st for example). I want to sum 26 consecutive paychecks and see which 26 paychecks total the largest amount. They don't need to be in the same year, they just need to be 26 consecutive. Maybe an example of what my spreadsheet looks like now. A1 $2,000.00 A2 $2,300.00 A3 $1,459.00 (keeps going€¦€¦) A26 $1,890.00 B26 =A1:A26 A27 $1,990.00 B27 =A2:A27 (keeps going€¦.) A126 $1,500.00 B126 = A100:A126 A127 $3,000.00 B127 = A101:A127 Line 26 is the first time we can total in column B 26 paychecks. Line 27 is second time. Now if I do LARGE command in column B, it will give me the highest number (say its in cell B26). If I look for the second largest, it might give me B27. The problem with that though is that B27 includes earnings that showed up in B26. I need to find a way that once EXCEL determines what cell in column B is highest, when I look for second highest it excludes the 25 cells that come in front of it (if answer is B26, then need to exclude B1-B26). The second year might start in B127. The 26 paychecks are identified and excluded in the search for the third highest 26 pay period. Thank you in advance for any help I can get for this question :) ~Patrick |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find 3 numbers +/- 26 rows from each other
I think you can do it with a couple of helper columns, C&D. First, we'll
setup your formulas to find the 3 numbers. In E1: =LARGE(B1:B100,0) In F1 =LARGE(IF(C1:C100<"X",B1:B100),1) Input this as an array (Use Ctrl+Shift+Enter, not just enter to confirm) In G1 =LARGE(IF(D1:D100<"X",B1:B100),1) Input this as an array also. Adjust those range sizes as needed to match your data. At this point, they should all display the same number. Now we'll work on limiting their fields. In C27 =IF(ISNUMBER(MATCH($E$1,B1:B53,0)),"X","") Copy this all the way down. In C1: =IF(C27="X","X","") Copy down to C26 Note that your formula in F1 should now be different. In D27 =IF(OR(C27="X",ISNUMBER(MATCH($F$1,B1:B53,0))),"X" ,"") In D1 =IF(D27="X","X","") Copy down to D26 That should do it! -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "StillLearning" wrote: I am trying to do a payroll calculation and was hoping someone may have a solution for me. I have a column with over 130 numbers in it (130 numbers in a single column.). I want to bring back the largest value in an array. Then I want to bring back the second largest values in the same array so long as it does not fall with +/- 26 rows of the largest number.) Last I want to bring back the third largest value that doesnt faull with +/- 26 rows of the first or second largest numbers found earlier. This is for a payroll. I am trying to find a persons three highest years of earnings. My first column (A) has the dollar amount of every paycheck my person had for each pay period (26 a year) in a five year period. Column (B) sums 26 paychecks to come up with what 12 months of earnings would be (they dont have to be calendar years, they can be April 1st to March 31st for example). I want to sum 26 consecutive paychecks and see which 26 paychecks total the largest amount. They don't need to be in the same year, they just need to be 26 consecutive. Maybe an example of what my spreadsheet looks like now. A1 $2,000.00 A2 $2,300.00 A3 $1,459.00 (keeps going€¦€¦) A26 $1,890.00 B26 =A1:A26 A27 $1,990.00 B27 =A2:A27 (keeps going€¦.) A126 $1,500.00 B126 = A100:A126 A127 $3,000.00 B127 = A101:A127 Line 26 is the first time we can total in column B 26 paychecks. Line 27 is second time. Now if I do LARGE command in column B, it will give me the highest number (say its in cell B26). If I look for the second largest, it might give me B27. The problem with that though is that B27 includes earnings that showed up in B26. I need to find a way that once EXCEL determines what cell in column B is highest, when I look for second highest it excludes the 25 cells that come in front of it (if answer is B26, then need to exclude B1-B26). The second year might start in B127. The 26 paychecks are identified and excluded in the search for the third highest 26 pay period. Thank you in advance for any help I can get for this question :) ~Patrick |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find 3 numbers +/- 26 rows from each other
Wow that worked GREAT! Thanks!
"Luke M" wrote: I think you can do it with a couple of helper columns, C&D. First, we'll setup your formulas to find the 3 numbers. In E1: =LARGE(B1:B100,0) In F1 =LARGE(IF(C1:C100<"X",B1:B100),1) Input this as an array (Use Ctrl+Shift+Enter, not just enter to confirm) In G1 =LARGE(IF(D1:D100<"X",B1:B100),1) Input this as an array also. Adjust those range sizes as needed to match your data. At this point, they should all display the same number. Now we'll work on limiting their fields. In C27 =IF(ISNUMBER(MATCH($E$1,B1:B53,0)),"X","") Copy this all the way down. In C1: =IF(C27="X","X","") Copy down to C26 Note that your formula in F1 should now be different. In D27 =IF(OR(C27="X",ISNUMBER(MATCH($F$1,B1:B53,0))),"X" ,"") In D1 =IF(D27="X","X","") Copy down to D26 That should do it! -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "StillLearning" wrote: I am trying to do a payroll calculation and was hoping someone may have a solution for me. I have a column with over 130 numbers in it (130 numbers in a single column.). I want to bring back the largest value in an array. Then I want to bring back the second largest values in the same array so long as it does not fall with +/- 26 rows of the largest number.) Last I want to bring back the third largest value that doesnt faull with +/- 26 rows of the first or second largest numbers found earlier. This is for a payroll. I am trying to find a persons three highest years of earnings. My first column (A) has the dollar amount of every paycheck my person had for each pay period (26 a year) in a five year period. Column (B) sums 26 paychecks to come up with what 12 months of earnings would be (they dont have to be calendar years, they can be April 1st to March 31st for example). I want to sum 26 consecutive paychecks and see which 26 paychecks total the largest amount. They don't need to be in the same year, they just need to be 26 consecutive. Maybe an example of what my spreadsheet looks like now. A1 $2,000.00 A2 $2,300.00 A3 $1,459.00 (keeps going€¦€¦) A26 $1,890.00 B26 =A1:A26 A27 $1,990.00 B27 =A2:A27 (keeps going€¦.) A126 $1,500.00 B126 = A100:A126 A127 $3,000.00 B127 = A101:A127 Line 26 is the first time we can total in column B 26 paychecks. Line 27 is second time. Now if I do LARGE command in column B, it will give me the highest number (say its in cell B26). If I look for the second largest, it might give me B27. The problem with that though is that B27 includes earnings that showed up in B26. I need to find a way that once EXCEL determines what cell in column B is highest, when I look for second highest it excludes the 25 cells that come in front of it (if answer is B26, then need to exclude B1-B26). The second year might start in B127. The 26 paychecks are identified and excluded in the search for the third highest 26 pay period. Thank you in advance for any help I can get for this question :) ~Patrick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
From 2 rows or column how to find common numbers | Excel Discussion (Misc queries) | |||
Help! How do you get excel to find the x(changes daily, marked in a cell from another formula) highest numbers in a group of numbers and sum them up? | Excel Worksheet Functions | |||
Help! How do you get excel to find the x(changes daily, marked in a cell from another formula) highest numbers in a group of numbers and sum them up? | Excel Worksheet Functions | |||
to find missing serial numbers in randomly generated numbers | Excel Worksheet Functions | |||
'find' somtimes can't find numbers. I folowd the 'help' instructi. | Excel Worksheet Functions |