Variable Rows

[SeaBearz]

Hello All,

I'm looking to find the average of 36 cells above the cell I'm working in.

Simple enough, but in trying to use Autofill (due to the hundreds of rows I'm working with) I run into issues with the top 36 rows. I can start at the bottom and Autofill upwards, but I run into #REF! when I get near the top of the spreadsheet. Yes, I know the number of rows referenced in the average would have to decrease, I can work around that.

At a minimum,

<mark>is there a formula/method to specify the number of rows to reference?</mark>

Below is an example of the topic in question:

 

[kevin9999]

See if this gives you what you want...

Book1
	B	C
1	amount	average 36
2	$2,193	$2,193
3	$2,547	$2,370
4	$1,870	$2,203
5	$3,099	$2,427
6	$1,608	$2,263
7	$1,918	$2,206
8	$3,109	$2,335
9	$3,390	$2,467
10	$3,151	$2,543
11	$1,922	$2,481
12	$1,833	$2,422
13	$2,955	$2,466
14	$2,928	$2,502
15	$3,387	$2,565
16	$2,074	$2,532
17	$2,827	$2,551
18	$2,598	$2,553
19	$2,851	$2,570
20	$2,659	$2,575
21	$2,330	$2,562
22	$2,589	$2,564
23	$3,248	$2,595
24	$2,707	$2,600
25	$1,194	$2,541
26	$1,177	$2,487
27	$2,844	$2,500
28	$1,736	$2,472
29	$2,979	$2,490
30	$2,246	$2,482
31	$2,068	$2,468
32	$1,197	$2,427
33	$3,432	$2,458
34	$1,785	$2,438
35	$1,525	$2,411
36	$1,754	$2,392
37	$3,019	$2,326
38	$1,681	$2,410
39	$2,101	$2,395
40	$1,104	$2,383
41	$2,158	$2,362
42	$3,389	$2,336
43	$2,313	$2,385
44	$1,257	$2,396
45	$2,480	$2,345
46	$3,296	$2,319
47	$2,058	$2,323
48	$1,371	$2,327
49	$1,798	$2,314
50	$1,216	$2,282
Sheet1

Cell Formulas
Range	Formula
C2:C50	C2	=IF(ROW()<37,AVERAGE($B$2:B2),SUM(OFFSET(B2,-36,0,36,1))/36)

 

[SeaBearz]

Wow, that's a beautiful solution - thank you! Works perfectly!

 

[kevin9999]

Happy to help, and thanks for the feedback