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)?

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)?

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)?