Page 1 of 1

arithmetic on rows

Posted: Thu Dec 06, 2007 4:21 am
by grizdog
This must be a FAQ, but I can't find it on the forum, in the docs, or the tutorials. Please direct me to the appropriate place -

I have a lot of data to import into the spreadsheet, and because of the way it is imported, I have a lot of arithmetic to do on rows, rather than columns. For example, in one data set I want to take a weighted average of the values in a row, with same weight for every element in the same column, and I want to repeat this on many rows. In another case I have to do more complex operations, adding, subtracting, multiplying different values in the same row, again repeated on each row.

All of the documentation and tutorials I have seen tell you how to do arithmetic on columns, but not rows. I did find a way to do this using a matrix, but that doesn't work well for me because the matrix is rather inflexible - once you create one, there isn't much you can do with it.

So is there a simple way to do this? For example to add the values in columns A, B, and C, subtract the value in D and store the answer in E?

Thanks.

Re: arithmetic on rows

Posted: Thu Dec 06, 2007 6:17 am
by acknak
What's wrong with
E1: =SUM(A1:C1)-D1

You can clone that into E2, E3, ... and it will do the same calculation for each row.

Re: arithmetic on rows

Posted: Thu Dec 06, 2007 3:13 pm
by grizdog
OK, thank you that works. At the risk of appearing greedy, is there a way to apply that to the entire column at once without typing C-V, C-C every time?

Thanks very much.

Re: arithmetic on rows

Posted: Thu Dec 06, 2007 6:53 pm
by acknak
Sure.

1) Enter the formula in cell E1.
2) Click on cell E1 to make it the current cell; it should have a thick black outline.
3) Move the mouse pointer over the little blob in the lower right corner of the black outline; the pointer should become a "+".
4) Click, hold and drag the mouse down the column. A red outline will stretch to surround more cells in column E.
5) When you have outlined all the cells where you want the formula copied to, release the mouse button.

This operation uses the cells you have selected at the start (E1 in this case) to fill the outlined cells. If there are formulas in the starting selection, they will be adjusted to apply to their new location.

You can also use copy/paste: copy E1; select all the cells you want to fill, then paste. The formula will be copied and adjusted as needed to fill the selected cells.

Re: arithmetic on rows

Posted: Fri Dec 07, 2007 6:34 pm
by grizdog
That's great, thanks very much.