Sum over a variable number of cells
 

I am building a financial model and would like to sum values over a variable
number of preceeding cells (the number of cells should be inputted by a
user). How can I implement this functionality?

Answer: Sum over a variable number of cells
 

1. Start by selecting the cell where you want to display the sum.
2. Type the following formula: =SUM(OFFSET(reference,0,0,-n,1))
   - "reference" is the cell from which you want to start summing.
   - "n" is the number of cells you want to include in the sum. This value can be inputted by the user.
3. Press Enter to calculate the sum.

For example, if you want to sum the values in the 5 cells above the current cell, you would use the following formula: =SUM(OFFSET(A1,-5,0,5,1))

This formula will sum the values in cells A1:A5.

Note that the OFFSET function returns a range of cells, which is then used as the argument for the SUM function. The first two arguments of the OFFSET function specify the starting cell (reference) and the number of rows and columns to offset (0,0). The third and fourth arguments (-n and 1) specify the height and width of the range to return.

Sum over a variable number of cells
 

You can use sum and offset. For instance say your data is in A1 on down. You
put your variable in B1 And your result comes in C1. In C1 you put the formula

=SUM(OFFSET(A1,0,0,B1))

B1 is the number of cells that it will offset to. So if you put 3 and will
sum the first 3 cells, etc, etc.

"SD" wrote:

I am building a financial model and would like to sum values over a variable
number of preceeding cells (the number of cells should be inputted by a
user). How can I implement this functionality?

Sum over a variable number of cells
 

The following formula will sum the values begriming in cell D2 and
extending to the right for the number of columns specified in cell A1.

=SUM(OFFSET($D$2,0,0,1,A1))

So, for example, if A1 = 4, the formula will sum the range D2:G2.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Wed, 22 Oct 2008 11:26:01 -0700, SD
wrote:

I am building a financial model and would like to sum values over a variable
number of preceeding cells (the number of cells should be inputted by a
user). How can I implement this functionality?

Sum over a variable number of cells
 

To sum Row 2, starting at A2, and user enters the number of columns to total
into A1:

=SUM(A2:INDEX(2:2,A1))

If A1 is empty, entire row will total.

To sum Column B, starting at B1, and user enters the number of rows to total
into A1:

=SUM(B1:INDEX(B:B,A1))
Same holds true if A1 is empty.

To allow user to define both start and end points, entering boundaries in
say A1 and A2 for Column B:

=SUM(INDEX(B:B,A1):INDEX(B:B,A2))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------


"SD" wrote in message
...
I am building a financial model and would like to sum values over a
variable
number of preceeding cells (the number of cells should be inputted by a
user). How can I implement this functionality?