Home |
Search |
Today's Posts |
#1
|
|||
|
|||
changing rows *and* columns in automatic formulae
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.
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 actually changing row-dependent and column-dependent info for each thing, either way I'm going to have to go through manually and either extend the range of the sum (if I'm dragging down to get a column) or change the row 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 to create a column of values, I don't have to go through and manually edit each one? Thanks for any help - hope my explanation of the problem isn't too muddled! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
changing rows *and* columns in automatic formulae
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing Rows to Columns | New Users to Excel | |||
Changing Columns to Rows | Excel Discussion (Misc queries) | |||
Changing Rows into Columns | Excel Discussion (Misc queries) | |||
Automatic CSV saving pains - unwanted rows and columns | Excel Programming | |||
Changing Columns to Rows | Excel Discussion (Misc queries) |