Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com
Answertopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions
Privacy Policy

  




 

 

OpenOffice Calc 3.x Guide
Previous Page Home Next Page

Multiple operations in columns or rows

In your spreadsheet, enter a formula to calculate a result from values that are stored in other cells. Then, set up a cell range where you enter some fixed values, and the Multiple Operations command will calculate the results depending on the formula.

In the Formula field, enter the cell reference to the formula that applies to the data range. In the Column input cell/Row input cell field, enter the cell reference to the corresponding cell that is part of the formula. This can be explained best by examples. (Cell references correspond to those in the following figures.)

Let’s say you produce toys that you sell for $10 each (cell B1). Each toy costs $2 to make (cell B2), in addition to which you have fixed costs of $10,000 per year (cell B3). How much profit will you make in a year if you sell a particular number of toys?

Calculating with one formula and one variable

  1. To calculate the profit, first enter any number as the quantity (items sold); in this example, 2000 (cell B4). The profit is found from the formula Profit=Quantity * (Selling price – Direct costs) – Fixed costs. Enter this formula in B5: =B4*(B1-B2)-B3.
  2. In column D enter given annual sales, one below the other; for example, 500 to 5000, in steps of 500.
  3. Select the range D2:E11, and thus the values in column D and the empty cells alongside in column E.
  4. Choose Data > Multiple Operations.
  5. With the cursor in the Formulas field of the Multiple operations dialog, click cell B5.
  6. Set the cursor in the Column input cell field and click cell B4. This means that B4, the quantity, is the variable in the formula, which is replaced by the selected column values. The figure below shows the worksheet and the Multiple operations dialog.
  7. Sheet and Multiple operations dialog showing input
  8. Click OK. The profits for the different quantities are now shown in column E.
  9. Sheet showing results of multiple operations calculations
Image:Tip.png You may find it easier to mark the required reference in the sheet if you click the Shrink icon to reduce the Multiple operations dialog to the size of the input field. The icon then changes to the Maximize icon; click it to restore the dialog to its original size.

Calculating with several formulas simultaneously

  1. In the sheet from the previous example, delete the contents of column E.
  2. Enter the following formula in C5: =B5/B4. You are now calculating the annual profit per item sold.
  3. Select the range D2:F11, thus three columns.
  4. Choose Data > Multiple Operations.
  5. With the cursor in the Formulas field of the Multiple operations dialog, select cells B5 thru C5.
  6. Set the cursor in the Column input cell field and click cell B4. The figure below shows the worksheet and the Multiple operations dialog.
  7. Sheet and dialog showing input
  8. Click OK. Now the profits are listed in column E and the annual profit per item in column F. (You might want to format column F to show 2 decimal points, for easier comparison of the amounts.)
  9. Results of multiple operations calculations

OpenOffice Calc 3.x Guide
Previous Page Home Next Page

 
 
  Published under the terms of the Creative Commons License Design by Interspire