LARGE formula that finds consecutive numbers

[cothren21]

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?

Q1 2011	Q2 2011	Q3 2011	Q4 2011	Q1 2012	Q2 2012	Q3 2012	Q4 2012	Q1 2013	Q2 2013	Q3 2013	Q4 2013	Average of highest 4 consecutive months
34,150	41,888	42,699	39,514	55,818	41,968	13,140	17,211	17,910	32,589	13,917	27,598	=AVERAGE(LARGE(A2:L2,4))

<colgroup><col width="64" style="width: 48pt;" span="12"> <col width="192" style="width: 144pt; mso-width-source: userset; mso-width-alt: 7021;"> <tbody> </tbody>

 

[DanteAmor]

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

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.

 

[cothren21]

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.

 

[Marcelo Branco]

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.

 

[DanteAmor]

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.

I knew there is a hero with a magic formula!

 

[Marcelo Branco]

I knew there is a hero with a magic formula!

Hi Dante

Thank you for your kind words.

I'm not a "hero", just someone who learned a lot in this forum

I really appreciated your comment.

M.

 

[DanteAmor]

Hi Dante

Thank you for your kind words.

I'm not a "hero", just someone who learned a lot in this forum

I really appreciated your comment.

M.

In addition to hero, modest.
We all keep learning, this excel never ends

 

[Marcelo Branco]

We all keep learning, this excel never ends

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.


M.

 

[Rick Rothstein]

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

 

[DanteAmor]

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

Hello Rick, as always, awesome with formulas (another hero).