Formula to sum the previous n rows

[andrewsutton]

Can anyone tell me a formula that sums the previous n rows of that column?

i.e. if I put it in B5, and n is 3 (this number will come from another cell, let's say A1), the result will be SUM(B2:B4).

If I put it in Q200 and n is 100, it comes out as SUM(Q300:Q399).

I would like the formula to be draggable, so that I can spread it to cells all over the place without having to adjust anything in the fomula.

Thanks,

Andrew

 

[fairwinds]

i.e. if I put it in B5, and n is 3 (this number will come from another cell, let's say A1), the result will be SUM(B2:B4).

Hi,

Try:


=SUM(OFFSET(B4,,,-A1))

in B5



As for your Q200 example, I could not make sense, was it a typo?

 

[andrewsutton]

It works! I knew it was around there somewhere, but it still took me an hour not to find it.

As for my typo, it was a typo.

Thanks.