Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenTue, 12 Jun 2018 00:56:08 +0200how do I distribute an operation across a range of cells?https://ask.libreoffice.org/en/question/157737/how-do-i-distribute-an-operation-across-a-range-of-cells/I have a spreadsheet which I use to track deliveries and sales to calculate the cost and profits for a small range of items. I have the items described in columns so that the column head has the item description, the next row has its price, then the cost, and finally the calculated unit profit.
Below that I have the various deliveries of that item (i.e. the number of that item delivered on a date) followed by the sales (again by date or date range).
I want to be able to calculate the gross sales ($), for example, of a delivery or sale by a formula that would take range of columns and apply the same operation to each cell. For example, to take C2 (the price) * C23 (the number sold that day) and do it across columns such B to R to produce the total price of all goods sold that day.
I've tried googling the answer but I can't even think of a good set of search terms. I think I'd have to know the answer to ask the question. :(Tue, 12 Jun 2018 00:33:07 +0200https://ask.libreoffice.org/en/question/157737/how-do-i-distribute-an-operation-across-a-range-of-cells/Answer by garydale for <p>I have a spreadsheet which I use to track deliveries and sales to calculate the cost and profits for a small range of items. I have the items described in columns so that the column head has the item description, the next row has its price, then the cost, and finally the calculated unit profit. </p>
<p>Below that I have the various deliveries of that item (i.e. the number of that item delivered on a date) followed by the sales (again by date or date range).</p>
<p>I want to be able to calculate the gross sales ($), for example, of a delivery or sale by a formula that would take range of columns and apply the same operation to each cell. For example, to take C2 (the price) * C23 (the number sold that day) and do it across columns such B to R to produce the total price of all goods sold that day.</p>
<p>I've tried googling the answer but I can't even think of a good set of search terms. I think I'd have to know the answer to ask the question. :(</p>
https://ask.libreoffice.org/en/question/157737/how-do-i-distribute-an-operation-across-a-range-of-cells/?answer=157738#post-id-157738Ok, got it. SumProduct seems to do what I want.Tue, 12 Jun 2018 00:48:09 +0200https://ask.libreoffice.org/en/question/157737/how-do-i-distribute-an-operation-across-a-range-of-cells/?answer=157738#post-id-157738Answer by Lupp for <p>I have a spreadsheet which I use to track deliveries and sales to calculate the cost and profits for a small range of items. I have the items described in columns so that the column head has the item description, the next row has its price, then the cost, and finally the calculated unit profit. </p>
<p>Below that I have the various deliveries of that item (i.e. the number of that item delivered on a date) followed by the sales (again by date or date range).</p>
<p>I want to be able to calculate the gross sales ($), for example, of a delivery or sale by a formula that would take range of columns and apply the same operation to each cell. For example, to take C2 (the price) * C23 (the number sold that day) and do it across columns such B to R to produce the total price of all goods sold that day.</p>
<p>I've tried googling the answer but I can't even think of a good set of search terms. I think I'd have to know the answer to ask the question. :(</p>
https://ask.libreoffice.org/en/question/157737/how-do-i-distribute-an-operation-across-a-range-of-cells/?answer=157739#post-id-157739Read https://help.libreoffice.org/Calc/Addresses_and_References,_Absolute_and_Relative e.g. and use relative addressing concerning the column part for the formulae that need to be applied to many columns of the same row.
If you enter such a formula into the first of the columns it's applicabl to, and fill the formula to the right then (by dragging the little habdle with the mouse e.g.) the coulmn address will adapt as you need.
(BTW
What you intend to do in columns is done in rows commonly. The columns are treated as "data field containers" then, and the spreadsheet gets a format similar to the ususal representation of a database table.
If your business scales up to unexpected size, you can import such a table into a database that you will need then.)Tue, 12 Jun 2018 00:56:08 +0200https://ask.libreoffice.org/en/question/157737/how-do-i-distribute-an-operation-across-a-range-of-cells/?answer=157739#post-id-157739