arithmetic on rows

Discuss the spreadsheet application

arithmetic on rows

Postby grizdog » Thu Dec 06, 2007 4:21 am

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.
grizdog
 
Posts: 3
Joined: Thu Dec 06, 2007 4:07 am

Re: arithmetic on rows

Postby acknak » Thu Dec 06, 2007 6:17 am

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.
AOO4/LO5 • Linux • Fedora 23
User avatar
acknak
Moderator
 
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: arithmetic on rows

Postby grizdog » Thu Dec 06, 2007 3:13 pm

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.
grizdog
 
Posts: 3
Joined: Thu Dec 06, 2007 4:07 am

Re: arithmetic on rows

Postby acknak » Thu Dec 06, 2007 6:53 pm

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.
AOO4/LO5 • Linux • Fedora 23
User avatar
acknak
Moderator
 
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: arithmetic on rows

Postby grizdog » Fri Dec 07, 2007 6:34 pm

That's great, thanks very much.
grizdog
 
Posts: 3
Joined: Thu Dec 06, 2007 4:07 am


Return to Calc

Who is online

Users browsing this forum: No registered users and 17 guests