Ask Your Question
0

how do I get a whole column to multiply

asked 2016-05-10 21:36:37 +0200

bizgirl2016 gravatar image

eg: column D x column E = results in column F

edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted
0

answered 2017-10-27 21:10:09 +0200

erAck gravatar image

updated 2017-10-27 21:13:54 +0200

You can either use the cell per cell approach (copying =E1*D1 down to further rows), or, assuming data is in rows 1 to 5, you could use =D1:D5*E1:E5 entered as array formula (closed with Shift+Ctrl+Enter instead of just Enter) which automatically spreads over 5 rows starting at the input cell position. This though fixes the result to 5 rows and inserting rows into the range covered by the array formula is not possible, whereas copying an individual formula cell to an inserted row is easy. YMMV..

edit flag offensive delete link more
-1

answered 2019-03-30 14:04:11 +0200

jonnie45 gravatar image

updated 2019-03-30 14:05:53 +0200

From a very similar angle to the OP. I am designing a template for a estimate costs on a self build project. Numeric columns include Labour cost | Material cost | Actual Cost | Estimate | Difference.

The final row is for totals.

Autosum works just fine for the columns - in particular if I add a row then the expressions are altered so that for example

=SUM(C1:C29)

becomes

=SUM(C1:C30) when I add a new row.

Great !

However cells in "Actual Cost" column are calculate from SUM of cells in the same row.

I have added the SUM formula to a particular cell and copied it down a column, fine no problems there, relative formula are the default and so the formula row indices are adjusted as I expected.

However the result does not seem to be smart in the context of adding a new row - the required formula does not seem to be added automatically - strange since by default formula on cells are interpreted as relative.

the current behaviour when adding a row to

SUM(B1,C1)

SUM(B2,C2)

is

SUM(B1,C1)

BLANK

SUM(B3,C3)

I would have thought the code would either remember all formula in a column were relative copies of one original or simply check for equivalence and offer the most likely formula - its pretty obvious in the above what a an educated guess for the formula should be? Surely that exists? If not then I would suggest a simple enhancement would be to simply check for equivalence on the row above and the row below and add the row index adjusted formula accordingly.

It seems clumsy and arduous to have to copy formulae when simply adding a new costing item, I was hoping to be able to create a template where the user simply taps in costs and a name for the item. Costing spreadsheets like this are not one-offs, new rows are added all the time as the project takes shape.

I noted erAck's comment about multiplying entire columns but caution is offered since this will apply beyond the last data row - also probably messes up if a footer is added.

edit flag offensive delete link more

Comments

Please don't add an Answer if it is not an answer to the original question. Either use add a comment or in your case rather create a new question. Thanks.

erAck gravatar imageerAck ( 2019-04-03 12:23:53 +0200 )edit
-1

answered 2016-05-11 07:44:49 +0200

IanC1811 gravatar image

What do you mean by "column D x column E" ? Do you mean, for example, "value in cell D5 X value in cell E5" with the result stored in cell F5, and then repeated for every pair of cells going down the columns, or do you mean some thing else? Typically in doing calculations you work with cells not columns, but having arranged an operation on two cells it is very easy to copy that operation on to a large range of cells. Cheers Ian

edit flag offensive delete link more

Comments

Ok, worst answer ever. What is this internet dunce, isn't the question spouting bodily fluids with how obvious it is? You can't multiply entire columns, but you can multiply cells and then copy the pattern. Click on E5 and type in "=D5*E5". Once you have that answer hover over the black bar till you get the + icon and spread your formula to other cells. IanC, you suck.

pwrbeats gravatar imagepwrbeats ( 2017-10-27 19:25:17 +0200 )edit

Not to say worst comment ever (or is it?), pwrbeats, but actually you can multiply entire columns using an array formula. Though it multiplies also all empty cells beyond the last data row and spreads the result as an array with one million rows. Asker probably wants something like =D1:D5*E1:E5 (assuming data is in rows 1 to 5) entered as array formula (closed with Shift+Ctrl+Enter). Btw, "Click on E5" in your example is wrong, should be F5 instead.

erAck gravatar imageerAck ( 2017-10-27 20:17:48 +0200 )edit

erAck you are very cool. that should be the answer, I would like to delete mine so people don't have to read through it to get to yours. Would you include yours as a submitted answer?

pwrbeats gravatar imagepwrbeats ( 2017-10-27 20:58:59 +0200 )edit

Done. .

erAck gravatar imageerAck ( 2017-10-27 21:10:46 +0200 )edit

@pwrbeats: Where did you come from? Do you also know civilized ways of criticism?

Lupp gravatar imageLupp ( 2017-10-27 21:19:51 +0200 )edit
Login/Signup to Answer

Question Tools

2 followers

Stats

Asked: 2016-05-10 21:36:37 +0200

Seen: 4,766 times

Last updated: Mar 30