Help creating a moving average function

[felpslima]

Hi there, I was wondering if anyone could help me build the following:

I have a whole lot of data on column b, I want moving averages in column c.
in cell d3 I would like to tell this functions how many rows to select below the active B cell needs to be analysed in that average.

as in

active cell: b2 -> the value in d3 is 5 then the function analyses the average of the range (b2:b7)
If d3 is 6, the te function analyses the average of the range (b2:b8)

can anyone help me?

Tthanks!

 

[T. Valko]

Hi there, I was wondering if anyone could help me build the following:

I have a whole lot of data on column b, I want moving averages in column c.
in cell d3 I would like to tell this functions how many rows to select below the active B cell needs to be analysed in that average.

as in

active cell: b2 -> the value in d3 is 5 then the function analyses the average of the range (b2:b7)
If d3 is 6, the te function analyses the average of the range (b2:b8)

can anyone help me?

Tthanks!

What do you mean by "active cell" ?

If that starting cell is B2 and you enter 5 in D3 shouldn't the range to average be B2:B6?

B2 = 1
B3 = 2
B4 = 3
B5 = 4
B6 = 5

 

[felpslima]

Yes, you're right! b2:b6 is what I meant.

can you help me?

 

[T. Valko]

Yes, you're right! b2:b6 is what I meant.

can you help me?

Try this...

=AVERAGE(B2:INDEX(B2:B1000,D3))

Adjust for the correct end of range.

Note that if D3 is an empty cell the formula will calculate the entire range.