Change range of AVERAGE and STDEV every ten rows

0
votes

I have a big dataset in Excel and I want to perform the same operation over and over until the end of all rows. I have groups of ten values (ten rows) and for each of these groups, I want to only display each row value if it is in the range of the average +- standard deviation of each group. As an example, let's say:

Image showing two groups of ten values. First with the result that I want in red

I already managed to perform the operation that I want for a group of ten values with this formula:

    =IF(AND(F2<=(AVERAGE($F$2:$F$11))+(STDEV($F$2:$F$11)),F2>=(AVERAGE($F$2:$F$11))-(STDEV($F$2:$F$11))),F2, "")

Since my data is kind of big (around 5,000 rows) I would like to have a function that I can drag to the other groups of ten values without the need to update the average and standard deviation range.

1
excel

1 Answers

3
votes

=IF(F2=MEDIAN(F2,AVERAGE(INDEX(F:F,2+10*INT((ROWS($1:1)-1)/10)):INDEX(F:F,11+10*INT((ROWS($1:1)-1)/10)))+{-1,1}*STDEV(INDEX(F:F,2+10*INT((ROWS($1:1)-1)/10)):INDEX(F:F,11+10*INT((ROWS($1:1)-1)/10)))),F2, "")

Copy down as required.

Regards