Average a cell across worksheets in Excel

  MS35 18:19 10 Jul 06

I'm using the formula below to average a specific cell (c6) across a number of worksheets. There is 1 sheet per week of the year (1-52)

e.g. =AVERAGE('Week 1:Week 4'!$C$6)

My problem is I want the sheet number to be variable so I can change it to say weeks 20:30 by typing the values 20 and 30 into cells in a worksheet called "selection" where b1 would be 20 and b2 would be 30.

Just can't seem to get it to work! any thoughts on what the formula should be?


  VoG II 18:59 10 Jul 06


=AVERAGE("'Week "&INDIRECT(selection!B1)& ":Week " & INDIRECT(selection!B2) &"'!$C$6")

  MS35 21:42 10 Jul 06

Pasted this in and still returns Ref#, can see why Indirect would be right but is this just Excel being fussy with quotes or am i still doing something wrong?

  VoG II 08:55 11 Jul 06

Right, I spent hours trying to get the syntax right and failed miserably. In the end I went to MrExcel.com and here are two solutions both of which appear to work:

=SUMPRODUCT(N(INDIRECT("'Week "&ROW(INDIRECT(selection!B1&":"&selection!B2))&"'!C6")))/SUMPRODUCT(COUNTIF(INDIRECT("'Week "&ROW(INDIRECT(selection!B1&":"&selection!B2))&"'!C6"),"<>"))

or an array formula

=AVERAGE(N(INDIRECT("'week "&ROW(INDIRECT(selection!B1&":"&selection!B2))&"'!C6")))

which has to be typed in then confirmed by pressing CTRL + SHIFT + ENTER and will then appear in the formula bar as

={AVERAGE(N(INDIRECT("'week "&ROW(INDIRECT(selection!B1&":"&selection!B2))&"'!C6")))}

  VoG II 18:26 12 Jul 06

No good?

  MS35 18:57 12 Jul 06

Perfect & Thanks very much for your time, got both to work today and will probably go with the first as although it looks more complicated will probably be more flexible.
I do appreciate your time, Work longer on Wed.s and only just got back in.

  MS35 18:59 12 Jul 06

& resolved

This thread is now locked and can not be replied to.

What is ransomware and how do I protect my PC from WannaCry?

What I learned from my mentor, Oscar-winning VFX supervisor Phil Tippett

Siri vs Google Assistant