Formula that has increments and can be transposed.

[Ow321]

Hi everybody,

I am trying to work out the median across a row E.g. C1:F1. I need to drag this calculation to the right and in doing so, keep the column the same but have the row skip 7,

So on a new sheet A1 contains =Median('Sheet1'!C1:F1), B1 contains =Median('Sheet1'!C8:F8) etc.

It would be great if it is possible to do this while maintaining the ability to drag the calculation down to change the row.

so A2 contains =Median('Sheet1'!C2:F2) etc.

Not sure if this is possible?

Thank you


EDIT:
A simplified version of my workbook here:
https://drive.google.com/file/d/1LEC...ew?usp=sharing

 

[DRSteele]

Welcome to the forum.

With this sample data I used INDIRECT to increment the rows into columns. The data must start in ColC and go to ColH, start in Row1 then seven more rows, then 7...etc. You will have to properly adjust the syntax for the sheet name inside INDIRECT.

Book1
	B	C	D	E	F	G	H
1	data1	0.15843	0.460289	0.944211	0.478155	0.869841	0.36003
2
3
4
5
6
7
8	data2	0.390606	0.882333	0.648111	0.87588	0.941497	0.556156
9
10
11
12
13
14
15	data3	0.904423	0.009752	0.541233	0.562182	0.621026	0.677017
16
17
18	proof	0.469222	0.761996	0.591604
19	expanding indirect	0.469222	0.761996	0.591604
Sheet8

Cell Formulas
Range	Formula
C18	=MEDIAN(C1:H1)
C19	=MEDIAN(INDIRECT("c"&(COLUMNS(C$13:$C13)-1)*7+1&":h"&(COLUMNS(C$13:$C13)-1)*7+1))
D18	=MEDIAN(C8:H8)
D19	=MEDIAN(INDIRECT("c"&(COLUMNS($C$13:D13)-1)*7+1&":h"&(COLUMNS($C$13:D13)-1)*7+1))
E18	=MEDIAN(C15:H15)
E19	=MEDIAN(INDIRECT("c"&(COLUMNS($C$13:E13)-1)*7+1&":h"&(COLUMNS($C$13:E13)-1)*7+1))

 

[Ow321]

With a bit of adapting this worked perfectly for me, 4 hours saved! Thank you.

Please, could I ask you to explain what the formula is actually doing in plain words? I'd like to be able to understand it.