changing rows *and* columns in automatic formulae

R

richardstorm

Suppose I have an nxn square table; and I want to create a list tha
will tell me, for the jth row, the sum of the first j columns.

With what I know at the moment (pretty limited) I could enter a formula
and drag to get either a row or a column of data; but since I'm actuall
changing row-dependent and column-dependent info for each thing, eithe
way I'm going to have to go through manually and either extend the rang
of the sum (if I'm dragging down to get a column) or change the ro
being summed (if I'm dragging across to get a row).

Is there any way to enter the formula so that, say, when I drag down t
create a column of values, I don't have to go through and manually edi
each one?

Thanks for any help - hope my explanation of the problem isn't to
muddled
 
Z

zvkmpw

Suppose I have an nxn square table; and I want to create a list that
will tell me, for the jth row, the sum of the first j columns.

Is there any way to enter the formula so that, say, when I drag down to
create a column of values, I don't have to go through and manually edit
each one?

Here's one way.

With the table starting in A1, and with the size (your "n") in N1, put this
=SUM(OFFSET($A$1,0,0,$N$1,ROW()))
in row 1 of some column and drag it down. Then the second cell down has the sum of the first two columns of the table; the third cell, three columns; etc.

Explanation: OFFSET() returns an array, and can be used as a function argument where an array is expected. The arguments of OFFSET() allow you to vary the placement of the array and its height and width.

Modify and expand as needed.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top