Excel 2007 : Dynamic range/average issue?

**gannon_w** · 02-26-2012, 10:06 PM

I need the average of each 1500 rows in a single column with 100,000 rows of data!
I can use one column to say (b1=1500) then (b2 = b1+1500) and drag down until I get to 100k. Now, how can I write a function to take the average from Average(A"b1":A"b2")

Understand my question or is there an easier way to get the these averages?

**DGagnon** · 02-26-2012, 10:30 PM

you could use =AVERAGE(A1:A1500) when you copy this down it will automaticaly increase the 1 and 1500 by 1 for each row.

**gannon_w** · 02-26-2012, 10:32 PM

I need it to increase by 1500 each row!

so I have
Average(A1:A1500)
Average(A1501:A3000)
Average(A3001:A4500)
etc all the way to 100k

**DGagnon** · 02-26-2012, 10:38 PM

ahh ok, sorry about the misunderstanding, in that case you would need to use indirect, you should put in row B1 a 0 and in B2 =B1+1500, and fill down the formula will automaticaly add 1 to the start of the range (1,1501,3001...)

=AVERAGE(INDIRECT("A"&B1+1&":A"&B2))

**gannon_w** · 02-26-2012, 10:41 PM

Gracias! I was getting ready to try the indirect function but for some reason can't ever get it to work for me. THANKS!

**DGagnon** · 02-26-2012, 10:42 PM

no problem, i remember first learning INDIRECT it always seemed to confuse me too, you just need to remember that with indirect you need to form a text string that is equal to a range.