Ask Your Question
0

calc - vary column in indirect

asked 2016-11-07 11:09:40 +0100

DiesNuts gravatar image

updated 2016-11-07 11:10:12 +0100

Assume sheets that have some values on columns B-K.

Each sheet has

R2=AVERAGE(INDIRECT("b"&$O$2):INDIRECT("b"&$O$3))
R3=STDEV(INDIRECT("b"&$O$2):INDIRECT("b"&$O$3))

for, say,

O2=1436
O3=10260

How can I write the indirect s.t. I can drag it right to sumarise the other columns?

i.e.

S2=AVERAGE(INDIRECT("c"&$O$2):INDIRECT("c"&$O$3))
S3=STDEV(INDIRECT("c"&$O$2):INDIRECT("c"&$O$3))
T2=AVERAGE(INDIRECT("d"&$O$2):INDIRECT("d"&$O$3))
T3=STDEV(INDIRECT("d"&$O$2):INDIRECT("d"&$O$3))
edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
1

answered 2016-11-07 19:13:22 +0100

mark_t gravatar image

updated 2016-11-07 19:15:30 +0100

You could use the OFFSET function instead of INDIRECT.

=AVERAGE(OFFSET(B$1;$O$2-1;0;$O$3-$O$2+1))

Edit to use ";" instead of "," as "," would depend on locale settings.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-11-07 11:09:40 +0100

Seen: 50 times

Last updated: Nov 07 '16