Find 3 largest numbers in a column that are at least 26 cells apar

[StillLearning]

I have a column with over 130 numbers in it. I want to find the 3 highest
numbers. However these numbers must be seperated by at least 26 cells (they
can't be next to each other). Is there a way to do this using LARGE or some
other function?

 

[PJFry]

=LARGE(array,1) will give you the largest number, =LARGE(array,2) the second,
etc.

I am a wee bit curious about the the second requirement. What is the logic
for the need for a >=26 row seperation between the values?

It sounds like you want to bring back the largest value in an array. Then
you want to bring back the second largest value in the same array so long as
it does not fall with +/- 26 rows of the largest number. Does that sound
right?
--
Regards,

PJ
Please rate this post using the vote buttons if it was helpful.

 

[StillLearning]

HI

YES, thank you that explains my question perfectly!! "does not fall with +/-
26 rows of the largest number". And need to then do it two more times for
the other numbers.

Ok so why I need this (hard to explain). This is a payroll calculation. I
have column "A" has every paycheck for every pay period (26 a year, we paid
bi-weekly) a person received for 5 years. Column "B" (the one I'm trying to
find the three highest) is the total of 26 paychecks. What I am hoping to do
is find a persons three highest years of pay. We identify this as 3
different 12 month periods. They don't have to be calendar years. They
don't have to be consecutive.

So my column B totals 26 paychecks. And then moves forward and does it
again. And again. The problem is that if I don't count +/-26 rows, I run
the risk of using some of the paychecks in column "A" in more than one year.
We wouldn't have an accurate accounting of the person's three highest.

Thank you for your help and I look forward to your suggestions!

 

[T. Valko]

Let's assume your data is in the range A2:A131 (130 rows - 26*5=130)

Enter this formula in B2 and copy down to the end of data in column A:

=IF(MOD(ROWS(B$2:B2),26)=0,SUM(A$2:A2)-SUM(B$1:B1),"")

Note the reference to cell B1. That is intentional. I'm assuming B1 will be
empty or maybe a text column header.

That will return a sum on every 26th row.

Then, to get the 3 highest totals:

Entered in D2:

=LARGE(B$2:B$131,ROWS(D$22))

Copy down to D4.

 

[PJFry]

It's getting clearer.

The function you are proposing will only bring back three individual
paychecks and I don't think that is what you want. It sounds like you 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
consecutive.

Or, are these paychecks even in chronological order or does the order even
matter. If that the case, this gets really easy.

Let me know.

--
Regards,

PJ
Please rate this post using the vote buttons if it was helpful.

 

[StillLearning]

Hi again

26 pay checks have to be in chronological order. Then we have to find
another seperate 26 paychecks that are the second highest. And then 26
paychecks that are the 3rd highest. Maybe an example of what my spreadsheet
looks like now.

A1 $2,000.00
A2 $2,300.00
A3 $1,459.00

A26 $1,890.00 B26 =A1:A26
A27 $1,990.00 B27 =A2:A27

A126 $1,500.00 B126 = A100:A126
A127 $3,000.00 B127 = A100:A126

Line 26 is the first time we can total in column B the 26 paychecks. Line
27 is second time. Now if I do LARGE comand 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 have to check, then somewhere in the person's career another
seperate 26 checks might make up a second year.

Hope this helps clear it up. I think you knew what I meant but hope this
helps.

Thanks again!