Range Change

[karl12]

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]

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]

You mean when you copy the formula down a row?

In what row does the formula first appear?

[rwgrietveld]

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]

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))))

[karl12]

Thanks so much all

Used martindwilsons approach