LARGE formula that finds consecutive numbers

C

cothren21

New Member
Joined
Apr 25, 2019
Messages
8
Is there a way to utilize the LARGE formula, but have it pick the highest consecutive periods? I am looking at a very wide set of data, 20 years by quarter, and need to find the peak volume for any four-month (consecutive) span. The LARGE function is only finding the biggest four months, although the logic is almost what I need. Here is a small sample of what I a working on, with the current formula I was using (that does not work) written out.


Any ideas on an easy way to do this?



[TABLE="width: 720"]
<colgroup><col width="64" style="width: 48pt;" span="12"> <col width="192" style="width: 144pt; mso-width-source: userset; mso-width-alt: 7021;"> <tbody>[TR]
[TD="width: 64, bgcolor: #D9D9D9"]Q1 2011[/TD]
[TD="width: 64, bgcolor: #D9D9D9"]Q2 2011[/TD]
[TD="width: 64, bgcolor: #D9D9D9"]Q3 2011[/TD]
[TD="width: 64, bgcolor: #D9D9D9"]Q4 2011[/TD]
[TD="width: 64, bgcolor: #D9D9D9"]Q1 2012[/TD]
[TD="width: 64, bgcolor: #D9D9D9"]Q2 2012[/TD]
[TD="width: 64, bgcolor: #D9D9D9"]Q3 2012[/TD]
[TD="width: 64, bgcolor: #D9D9D9"]Q4 2012[/TD]
[TD="width: 64, bgcolor: #D9D9D9"]Q1 2013[/TD]
[TD="width: 64, bgcolor: #D9D9D9"]Q2 2013[/TD]
[TD="width: 64, bgcolor: #D9D9D9"]Q3 2013[/TD]
[TD="width: 64, bgcolor: #D9D9D9"]Q4 2013[/TD]
[TD="width: 192, bgcolor: #FFC000"]Average of highest 4 consecutive months[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]34,150[/TD]
[TD="bgcolor: transparent"]41,888[/TD]
[TD="bgcolor: #FFE699"]42,699[/TD]
[TD="bgcolor: #FFE699"]39,514[/TD]
[TD="bgcolor: #FFE699"]55,818[/TD]
[TD="bgcolor: #FFE699"]41,968[/TD]
[TD="bgcolor: transparent"]13,140[/TD]
[TD="bgcolor: transparent"]17,211[/TD]
[TD="bgcolor: transparent"]17,910[/TD]
[TD="bgcolor: transparent"]32,589[/TD]
[TD="bgcolor: transparent"]13,917[/TD]
[TD="bgcolor: transparent"]27,598[/TD]
[TD="bgcolor: transparent"] =AVERAGE(LARGE(A2:L2,4))[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
You need something like this

<br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:58.93px;" /><col style="width:58.93px;" /><col style="width:58.93px;" /><col style="width:58.93px;" /><col style="width:58.93px;" /><col style="width:58.93px;" /><col style="width:58.93px;" /><col style="width:58.93px;" /><col style="width:58.93px;" /><col style="width:58.93px;" /><col style="width:58.93px;" /><col style="width:58.93px;" /><col style="width:143.52px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td><td >L</td><td >M</td></tr><tr style="height:37px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="background-color:#92d050; ">Q1 2011</td><td style="background-color:#92d050; ">Q2 2011</td><td style="background-color:#92d050; ">Q3 2011</td><td style="background-color:#92d050; ">Q4 2011</td><td style="background-color:#92d050; ">Q1 2012</td><td style="background-color:#92d050; ">Q2 2012</td><td style="background-color:#92d050; ">Q3 2012</td><td style="background-color:#92d050; ">Q4 2012</td><td style="background-color:#92d050; ">Q1 2013</td><td style="background-color:#92d050; ">Q2 2013</td><td style="background-color:#92d050; ">Q3 2013</td><td style="background-color:#92d050; ">Q4 2013</td><td style="background-color:#92d050; ">Average of highest 4 consecutive months</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="text-align:right; ">34,150</td><td style="text-align:right; ">41,888</td><td style="text-align:right; ">42,699</td><td style="text-align:right; ">39,514</td><td style="text-align:right; ">55,818</td><td style="text-align:right; ">41,968</td><td style="text-align:right; ">13,140</td><td style="text-align:right; ">17,211</td><td style="text-align:right; ">17,910</td><td style="text-align:right; ">32,589</td><td style="text-align:right; ">13,917</td><td style="text-align:right; ">27,598</td><td style="text-align:right; ">44,999.75</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td >sum each 4 months</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="text-align:right; ">34,150</td><td style="text-align:right; ">41,888</td><td style="text-align:right; ">42,699</td><td style="text-align:right; ">39,514</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">158,251</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td > </td><td style="text-align:right; ">41,888</td><td style="text-align:right; ">42,699</td><td style="text-align:right; ">39,514</td><td style="text-align:right; ">55,818</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">179,919</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td > </td><td > </td><td style="text-align:right; ">42,699</td><td style="text-align:right; ">39,514</td><td style="text-align:right; ">55,818</td><td style="text-align:right; ">41,968</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="background-color:#ffff00; text-align:right; ">179,999</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td > </td><td > </td><td > </td><td style="text-align:right; ">39,514</td><td style="text-align:right; ">55,818</td><td style="text-align:right; ">41,968</td><td style="text-align:right; ">13,140</td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">150,440</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">55,818</td><td style="text-align:right; ">41,968</td><td style="text-align:right; ">13,140</td><td style="text-align:right; ">17,211</td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">128,137</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">41,968</td><td style="text-align:right; ">13,140</td><td style="text-align:right; ">17,211</td><td style="text-align:right; ">17,910</td><td > </td><td > </td><td > </td><td style="text-align:right; ">90,229</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">13,140</td><td style="text-align:right; ">17,211</td><td style="text-align:right; ">17,910</td><td style="text-align:right; ">32,589</td><td > </td><td > </td><td style="text-align:right; ">80,850</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >22</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">17,211</td><td style="text-align:right; ">17,910</td><td style="text-align:right; ">32,589</td><td style="text-align:right; ">13,917</td><td > </td><td style="text-align:right; ">81,627</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >23</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">17,910</td><td style="text-align:right; ">32,589</td><td style="text-align:right; ">13,917</td><td style="text-align:right; ">27,598</td><td style="text-align:right; ">92,014</td></tr></table>

Add every 4 columns, but I do not know how it would be with a formula.
I give you an example macro to get the result of row 13


Code: 
Sub highest_4()
    wMax = 0
    For i = Columns("A").Column To Columns("L").Column - 3 'Step 4
        wSuma = WorksheetFunction.Sum(Range(Cells(13, i), Cells(13, i + 3)))
        If wSuma > wMax Then wMax = wSuma
    Next
    Range("M13").Value = wMax / 4
End Sub

Maybe someone will review it and help you with the formula, while you can use the macro option.
 
Upvote 0
Thanks again Dante, but I was hoping to find a formula that would do this rather than building additional data. Maybe someone will read this and have a solution. Thank you again, and I will use your method if a better solution is not offered.
 
Upvote 0
Maybe...

Array formula
=MAX(SUBTOTAL(9,OFFSET(A2,0,COLUMN(A2:I2)-COLUMN(A2),1,4)))/4
confirmed with Ctrl+Shift+Enter, not just Enter

M.
 
Last edited:
Upvote 0
DanteAmor said:
We all keep learning, this excel never ends;)
Click to expand...

Certainly! Excel has its mysteries and idiosyncrasies and we have to face them every day.

This is the purpose of this forum and I try to make the most of it by looking at the suggestions (solutions) here proposed.
:beerchug:

M.
 
Upvote 0
Since we only need to add 4 numbers at a time, this array-entered formula should also work (the four red highlighted column letters are the last four column references for the range)...

=MAX(A2:I2+B2:J2+C2:K2+D2:L2)/4

Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself
 
Last edited:
Upvote 0
Rick Rothstein said:
Since we only need to add 4 numbers at a time, this array-entered formula should also work (the four red highlighted column letters are the last four column references for the range)...

=MAX(A2:I2+B2:J2+C2:K2+D2:L2)/4

Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself
Click to expand...

Hello Rick, as always, awesome with formulas (another hero).
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Question Question
Date formula to find value
2
Replies
11
Views
1K
Jonmo1
Jonmo1
J
  • Question Question
How can I use vlookups, or other methods, to filter data based on numerous drop down lists?
Replies
5
Views
537
Oeldere
O
A
  • Question Question
Complex Formula Needed
Replies
1
Views
809
Robert Mika
Robert Mika

Forum statistics

Threads
1,224,816
Messages
6,181,138
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top