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.

Nintendo Switch review: Hands on with the intuitive modular console and its disappointing games…

1995-2015: How technology has changed the world in 20 years

Here's what should be coming to Adobe Project Felix in 2017

Apple AirPods review: Apple's beautiful new Bluetooth headphones bring true intelligence to…