Excel 'indirect' formula across sheets with an 'average' formula...

[RGriev]

Hi all,

I have looked here a lot in the past for help but this is the first time I have had to post a problem as I just can't figure out how indirect functions work.

I have a workbook with two sheets, one with raw data (named RAW) and one in which I would like to have average values etc. Unfortunately I have the common problem that excel will not recognise the pattern in my average formula when I try to drag it down a column.

Basically I want cell E3 in sheet 2 to average cells D3:D9 in sheet 1, E4 to average D10:D18, E5 to average D19:D30 and E6 to average D31:D43 then the pattern repeats. I'm thinking that I will need a block of four indirect functions that I can then drag down and they will all auto fill correctly, but I just don't know how these formula work...

Any help would be greatly appreciated,

Roddy.

Windows 7
Excel 2010 (Microsoft Office Professional Plus 2010)

 

[Andrew Poulsom]

Welcome to MrExcel.

One way (no need for INDIRECT):

=AVERAGE(INDEX(Sheet2!D:D,(9*ROWS(E$3:E3))+1):INDEX(Sheet2!D:D,(9*ROWS(E$3:E3))+9))

 

[RGriev]

Hi and thanks for the quick response!

That formula doesn't seem to work though... it works fine for the value I want in E4 but the other values aren't what they should be. Also, when I drag the formula down to auto fill, the values aren't what they are supposed to be. I don't know if I am supposed to change some of the values?

The numbers I want to average aren't evenly spaced i.e. the formula in E3 has to average 7 values (D3:D9), the one in E4 has to average 9 (D10:D18), E5 has to average 12 (D19:D30) and E6 has to average 13 (D31:D43) then the pattern repeats itself (that's the pattern I can't get excel to follow). I think I have to change some of your values - to make four formula that I can then drag down in a block - for it to work, is that right?

Thanks again,

Roddy.

 

[Andrew Poulsom]

Sorry I misunderstood your pattern. The formula I posted always averages 9 cells. What comes after D31:D43?

 

[RGriev]

After D31:D43 its D44:D50 (7 values again) then D51:D59 (9 values again) then D60:D71 (12 values again) then D72:D84 (13 values again).

The pattern of blocks of 7 values then 9 then 12 then 13 repeats itself to the end of the sheet - this pattern will change for later sheets but once I can see how the formula works I will be able to modify it when I need to.

Thanks again,

Roddy.

 

[Andrew Poulsom]

A bit convoluted, but try:

=AVERAGE(INDEX(Sheet2!D:D,ROWS(E$3:E3)+2+(37*INT((ROWS(E$3:E3)-1)/4))+LOOKUP(MOD(ROWS(E$3:E3),4),{0,1,2,3},{25,0,6,14})):INDEX(Sheet2!D:D,ROWS(E$3:E3)+8+(37*INT((ROWS(E$3:E3)-1)/4))+LOOKUP(MOD(ROWS(E$3:E3),4),{0,1,2,3},{31,0,8,19})))