eg: column D x column E = results in column F
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
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.
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 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?
Done. .
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…
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.
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.