Finding consecutive numbers and then taking average of middle 50%

[gmcgough600]

In the table below the first column is my data, the second and third are what I want. Any ideas how to do this would be really appreciated. Basically I want to find consecutive blocks of 4 or more numbers within the #N/As and copy these. I then want to use TRIMMEAN on each block to trim 50% (calculate mean excluding 50% of the points) and put the result somewhere e.g. in the first cell of each block.
[TABLE="width: 400"]
<tbody>[TR]
[TD="align: center"]What I have[/TD]
[TD="align: center"]What I want a formula to do (step 1)[/TD]
[TD="align: center"]What I want a formula to do (step 2)[/TD]
[/TR]
[TR]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2.5[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]6[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

Welcome to the board.

I found it easier to jump right to step 2.

Excel 2012

	A	B	C	D	E

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]What I have[/TD]
[TD="align: center"]What I want a formula to do (step 1)[/TD]
[TD="align: center"]What I want a formula to do (step 2)[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"][/TD]
[TD="align: right"]#N/A[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"][/TD]
[TD="align: right"]#N/A[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"][/TD]
[TD="align: right"]#N/A[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"][/TD]
[TD="align: right"]#N/A[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2.5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2.5[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"][/TD]
[TD="align: right"]#N/A[/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"][/TD]
[TD="align: right"]#N/A[/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"][/TD]
[TD="align: right"]#N/A[/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"][/TD]
[TD="align: right"]#N/A[/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"][/TD]
[TD="align: right"]#N/A[/TD]

[TD="align: center"]12[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"][/TD]
[TD="align: right"]#N/A[/TD]

[TD="align: center"]13[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"][/TD]
[TD="align: right"]#N/A[/TD]

[TD="align: center"]14[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"][/TD]
[TD="align: right"]#N/A[/TD]

[TD="align: center"]15[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]16[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"][/TD]
[TD="align: right"]#N/A[/TD]

[TD="align: center"]17[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"][/TD]
[TD="align: right"]#N/A[/TD]

[TD="align: center"]18[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"][/TD]
[TD="align: right"]#N/A[/TD]

[TD="align: center"]19[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"][/TD]
[TD="align: right"]#N/A[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]{=IF(A2="#N/A",NA(),IF(ISNUMBER(A1),NA(),IF(MATCH(1,ISNA(A2:A$19)+0,0)-1<4,NA(),TRIMMEAN(OFFSET(A2,0,0,MATCH(1,ISNA(A2:A$19)+0,0)-1),50%))))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



This is an array formula. Enter the formula into the formula bar, change the ranges to match your sheet, then press Control+Shift+Enter, not just Enter. Let us know how it works.