arithmetic on rows

[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.

[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.

[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.

[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.

[grizdog]

That's great, thanks very much.