vba - Drag a formula down with Row changes by another interval other than one

Question

I have been trying to figure this out for hours now. I have this formula:

 =PROMEDIO.SI.CONJUNTO(Flankers!C15:C54;Flankers!D15:D54;1)

I have done this one manually. I have my data in the Flankers sheet and now I'm calculating the means for my reaction times and my accuracy scores on a second sheet. The data for each participant is the same (93 rows per participant) and what I am trying to do is to drag this formula down to fill in each participant automatically (I have my 29 participants in a column). Of course, each reference changes only by 1 number. For example, I have the formula in C2, so when I drag it down to C3, the formula changes to

=AVERAGEIFS(Flankers!C16:C55;Flankers!D16:D55;1)

I need all of the rows to go up by 93 and keep the range the same (40 cells), so that C3's cell should be =AVERAGEIFS(Flankers!C108:C147;Flankers!D108:D147;1) and then C4's cell should be

 =AVERAGEIFS(Flankers!C201:C240;Flankers!D201:D240;1)

, etc. Manually this is very tedious (2698 rows in total). Can someone shed light on this and explain it to where I could modify it for future analyses?

Answer 1

Almost marked this as a duplicate of Duplicated cells skip 10 rows but in fact it is the reverse of it. However, the solution is the same. Use the INDEX function with a little maths to achieve the stagger.

The easiest way to do this is with the OFFSET function but that function is considered volatile1 and will recalulate whenever anything in the workbook changes. Large numbers of these will result in calculation lag everytime something is typed into any cell.

With the volatile1 OFFSET,

=AVERAGEIFS(OFFSET(Flankers!$C$15, (ROW(1:1)-1)*93, 0, 40, 1),
            OFFSET(Flankers!$C$15, (ROW(1:1)-1)*93, 1, 40, 1), 1)

With the non-volatile INDEX,

=AVERAGEIFS(INDEX(Flankers!C:C, 15+(ROW(1:1)-1)*93):INDEX(Flankers!C:C, 54+(ROW(1:1)-1)*93),
            INDEX(Flankers!D:D, 15+(ROW(1:1)-1)*93):INDEX(Flankers!D:D, 54+(ROW(1:1)-1)*93), 1)

The second formula may look more complicated but all it really does is provide a starting cell and and ending cell for each range in the AVERAGEIFS function. INDEX references the entire column and some basic maths do the rest.

In ES-ES formula language (with semicolon list separators) as,

=PROMEDIO.SI.CONJUNTO(DESREF(Flankers!$C$15; (FILA(1:1)-1)*93; 0; 40; 1);
                      DESREF(Flankers!$C$15; (FILA(1:1)-1)*93; 1; 40; 1); 1)

=PROMEDIO.SI.CONJUNTO(INDICE(Flankers!C:C; 15+(FILA(1:1)-1)*93):INDICE(Flankers!C:C; 54+(FILA(1:1)-1)*93);
                      INDICE(Flankers!D:D; 15+(FILA(1:1)-1)*93):INDICE(Flankers!D:D; 54+(FILA(1:1)-1)*93); 1)

---

1 _{Volatile functions recalculate whenever anything in the entire workbook changes, not just when something that affects their outcome changes. Examples of volatile functions are INDIRECT, OFFSET, TODAY, NOW, RAND and RANDBETWEEN. Some sub-functions of the CELL and INFO worksheet functions will make them volatile as well.}