Range Change

**karl12** · 03-16-2010, 06:12 PM

Good Morning,

Need a little help, using the below formula in my spreadsheet

=IF(COUNT('LC Raw Data'!B2:B13)=0," ",AVERAGE('LC Raw Data'!B2:B13))

The problem is that i need the range to jump to the next block of data ie

=IF(COUNT('LC Raw Data'!B14:B25)=0," ",AVERAGE('LC Raw Data'!B14:B25))

and so on. Without having to manual adjust the range.

Cheers in advance K.

**tony h** · 03-16-2010, 06:26 PM

Are you trying to cause the same formula to change
or
are you trying to repeat the formula in a different cell with the different value?

PS Sydney: NS or NSW?

**shg** · 03-16-2010, 06:27 PM

You mean when you copy the formula down a row?

In what row does the formula first appear?

**rwgrietveld** · 03-16-2010, 06:28 PM

Do the following

Define a named range:

BaseRng: =OFFSET('LC Raw Data'!$B$1:$B$12,ROW(Data!$A1)*12-11,0)

B1:= IF(COUNT(baseRng)=0," ",AVERAGE(baseRng))

drag down

**martindwilson** · 03-16-2010, 06:37 PM

or
if(COUNT(INDIRECT("'lc raw data'!b"&((ROWS($A$1:A1)*12)-10)&":b"&((ROWS($A$1:A1)*12)+1)))=0,"",average(INDIRECT("'lc raw data'!b"&((ROWS($A$1:A1)*12)-10)&":b"&((ROWS($A$1:A1)*12)+1))))