Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenWed, 03 Apr 2019 12:23:53 +0200how do I get a whole column to multiplyhttps://ask.libreoffice.org/en/question/69670/how-do-i-get-a-whole-column-to-multiply/ eg: column D x column E = results in column FTue, 10 May 2016 21:36:37 +0200https://ask.libreoffice.org/en/question/69670/how-do-i-get-a-whole-column-to-multiply/Answer by jonnie45 for <p>eg: column D x column E = results in column F</p>
https://ask.libreoffice.org/en/question/69670/how-do-i-get-a-whole-column-to-multiply/?answer=188682#post-id-188682From 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.Sat, 30 Mar 2019 14:04:11 +0100https://ask.libreoffice.org/en/question/69670/how-do-i-get-a-whole-column-to-multiply/?answer=188682#post-id-188682Comment by erAck for <p>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.</p>
<p>The final row is for totals.</p>
<p>Autosum works just fine for the columns - in particular if I add a row then the expressions are altered so that for example</p>
<p>=SUM(C1:C29) </p>
<p>becomes </p>
<p>=SUM(C1:C30) when I add a new row.</p>
<p>Great !</p>
<p>However cells in "Actual Cost" column are calculate from SUM of cells in the same row.</p>
<p>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.</p>
<p>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.</p>
<p>the current behaviour when adding a row to </p>
<p>SUM(B1,C1)</p>
<p>SUM(B2,C2)</p>
<p>is</p>
<p>SUM(B1,C1)</p>
<p>BLANK</p>
<p>SUM(B3,C3)</p>
<p>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.</p>
<p>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. </p>
<p>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.</p>
https://ask.libreoffice.org/en/question/69670/how-do-i-get-a-whole-column-to-multiply/?comment=189122#post-id-189122Please 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.Wed, 03 Apr 2019 12:23:53 +0200https://ask.libreoffice.org/en/question/69670/how-do-i-get-a-whole-column-to-multiply/?comment=189122#post-id-189122Answer by IanC1811 for <p>eg: column D x column E = results in column F</p>
https://ask.libreoffice.org/en/question/69670/how-do-i-get-a-whole-column-to-multiply/?answer=69689#post-id-69689What 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
IanWed, 11 May 2016 07:44:49 +0200https://ask.libreoffice.org/en/question/69670/how-do-i-get-a-whole-column-to-multiply/?answer=69689#post-id-69689Comment by pwrbeats for <p>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</p>
https://ask.libreoffice.org/en/question/69670/how-do-i-get-a-whole-column-to-multiply/?comment=136235#post-id-136235Ok, 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.Fri, 27 Oct 2017 19:25:17 +0200https://ask.libreoffice.org/en/question/69670/how-do-i-get-a-whole-column-to-multiply/?comment=136235#post-id-136235Comment by erAck for <p>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</p>
https://ask.libreoffice.org/en/question/69670/how-do-i-get-a-whole-column-to-multiply/?comment=136237#post-id-136237Not 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.Fri, 27 Oct 2017 20:17:48 +0200https://ask.libreoffice.org/en/question/69670/how-do-i-get-a-whole-column-to-multiply/?comment=136237#post-id-136237Comment by pwrbeats for <p>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</p>
https://ask.libreoffice.org/en/question/69670/how-do-i-get-a-whole-column-to-multiply/?comment=136239#post-id-136239erAck 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?Fri, 27 Oct 2017 20:58:59 +0200https://ask.libreoffice.org/en/question/69670/how-do-i-get-a-whole-column-to-multiply/?comment=136239#post-id-136239Comment by erAck for <p>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</p>
https://ask.libreoffice.org/en/question/69670/how-do-i-get-a-whole-column-to-multiply/?comment=136242#post-id-136242Done. .Fri, 27 Oct 2017 21:10:46 +0200https://ask.libreoffice.org/en/question/69670/how-do-i-get-a-whole-column-to-multiply/?comment=136242#post-id-136242Comment by Lupp for <p>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</p>
https://ask.libreoffice.org/en/question/69670/how-do-i-get-a-whole-column-to-multiply/?comment=136243#post-id-136243@pwrbeats: Where did you come from? Do you also know civilized ways of criticism?Fri, 27 Oct 2017 21:19:51 +0200https://ask.libreoffice.org/en/question/69670/how-do-i-get-a-whole-column-to-multiply/?comment=136243#post-id-136243Answer by pwrbeats for <p>eg: column D x column E = results in column F</p>
https://ask.libreoffice.org/en/question/69670/how-do-i-get-a-whole-column-to-multiply/?answer=136236#post-id-136236You 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.Fri, 27 Oct 2017 19:25:48 +0200https://ask.libreoffice.org/en/question/69670/how-do-i-get-a-whole-column-to-multiply/?answer=136236#post-id-136236Answer by erAck for <p>eg: column D x column E = results in column F</p>
https://ask.libreoffice.org/en/question/69670/how-do-i-get-a-whole-column-to-multiply/?answer=136241#post-id-136241You 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..Fri, 27 Oct 2017 21:10:09 +0200https://ask.libreoffice.org/en/question/69670/how-do-i-get-a-whole-column-to-multiply/?answer=136241#post-id-136241