Find 3 numbers +/- 26 rows from each other

[StillLearning]

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 doesn’t faull with +/- 26 rows of the first or
second largest numbers found earlier.

This is for a payroll. I am trying to find a person’s 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
don’t’ 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

 

[Luke M]

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(D1100<>"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!

 

[StillLearning]

Wow that worked GREAT! Thanks!

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(D1100<>"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!*