All geek questions in one place

Drag the formula down with line changes to a different interval than one

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

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

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

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

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

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

etc. Manually this is very tiring (a total of 2698 lines). Can someone shed some light on this and explain it to those where I could change it for future analyzes?

+4
source share
1 answer

It is almost marked as duplicate. Duplicate cells skip 10 rows , but in fact it is the other way around. However, the solution is the same. Use the INDEX function with a little math to achieve a step.

OFFSET, volatile¹ , - . , - .

volatile¹ 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)

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)

, , , AVERAGEIFS. INDEX , .

ES-ES ( ) 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)

¹ , - , , -, . INDIRECT, OFFSET, TODAY, NOW, RAND RANDBETWEEN. CELL INFO .

+4

Source: https://habr.com/ru/post/1616548/

More articles:


All Articles