How do I sum numbers in rolling format? (4 qtrs=4 cells to left)

[Deb]

I am trying to create a rolling total based on the previous 4 quarters. My
plan is to add a column to the left of the sum column. How do I sum the
numbers using the last four columns to the left without having to redefine
the range everytime. I know the "left" function counts a specific number of
characters in the cell to the left, but how do you set a range to the "4
cells to the left" without referencing the specific cells (a1:a4)?

[Ron Coderre]

Try something like this:

For values in A1:D1
E1: =SUM(OFFSET(E1,0,-4,1,4))

That formula always sums the 4-cell range preceding the formula cell.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Deb" wrote:

> I am trying to create a rolling total based on the previous 4 quarters. My
> plan is to add a column to the left of the sum column. How do I sum the
> numbers using the last four columns to the left without having to redefine
> the range everytime. I know the "left" function counts a specific number of
> characters in the cell to the left, but how do you set a range to the "4
> cells to the left" without referencing the specific cells (a1:a4)?

[Deb]

Thank you very much for your timely response! You saved the day.

"Ron Coderre" wrote:

> Try something like this:
>
> For values in A1:D1
> E1: =SUM(OFFSET(E1,0,-4,1,4))
>
> That formula always sums the 4-cell range preceding the formula cell.
>
> Does that help?
>
> ***********
> Regards,
> Ron
>
> XL2002, WinXP-Pro
>
>
> "Deb" wrote:
>
> > I am trying to create a rolling total based on the previous 4 quarters. My
> > plan is to add a column to the left of the sum column. How do I sum the
> > numbers using the last four columns to the left without having to redefine
> > the range everytime. I know the "left" function counts a specific number of
> > characters in the cell to the left, but how do you set a range to the "4
> > cells to the left" without referencing the specific cells (a1:a4)?