Calc Guide Using Spreadsheets in OpenOffice.org This PDF is designed to be read onscreen, two pages at a time. If you want to print a copy, your PDF viewer should have an option for printing two pages on one sheet of paper, but you may need to start with page 2 to get it to print facing pages correctly. (Print this cover page separately.
Copyright This document is Copyright © 2005–2010 by its contributors as listed in the section titled Authors. You may distribute it and/or modify it under the terms of either the GNU General Public License, version 3 or later, or the Creative Commons Attribution License, version 3.0 or later. Note that Chapter 8, Using the DataPilot, is licensed under the Creative Commons Attribution-Share Alike License, version 3.0. All trademarks within this guide belong to their legitimate owners.
Contents Chapter 1 Introducing Calc.........................................................................9 What is Calc?....................................................................................10 Spreadsheets, sheets, and cells........................................................10 Parts of the main Calc window..........................................................11 Starting new spreadsheets...............................................................16 Opening existing spreadsheets..
Chapter 3 Creating Charts and Graphs.....................................................72 Introduction...................................................................................... 73 Creating a chart................................................................................ 73 Editing charts...................................................................................78 Formatting charts.............................................................................84 Formatting 3D charts.
Modifying images............................................................................ 136 Using the picture context menu......................................................142 Using Calc’s drawing tools..............................................................145 Positioning graphics........................................................................ 148 Creating an image map...................................................................151 Chapter 6 Printing, Exporting, and E-mailing..
DataPilot functions in detail............................................................241 Function GETPIVOTDATA...............................................................267 Chapter 9 Data Analysis..........................................................................271 Introduction.................................................................................... 272 Consolidating data..........................................................................272 Creating subtotals...............
Chapter 12 Calc Macros...........................................................................340 Introduction.................................................................................... 341 Using the macro recorder...............................................................341 Write your own functions................................................................345 Accessing cells directly...................................................................353 Sorting.........................
Appendix B Description of Functions........................................................428 Functions available in Calc.............................................................429 Mathematical functions..................................................................430 Financial analysis functions............................................................435 Statistical analysis functions...........................................................449 Date and time functions.........................
1 Chapter Introducing Calc Using Spreadsheets in OpenOffice.
What is Calc? Calc is the spreadsheet component of OpenOffice.org (OOo). You can enter data (usually numerical) in a spreadsheet and then manipulate this data to produce certain results. Alternatively, you can enter data and then use Calc in a ‘What if...’ manner by changing some of the data and observing the results without having to retype the entire spreadsheet or sheet.
Parts of the main Calc window When Calc is started, the main window looks similar to Figure 1. Figure 1: Parts of the Calc window Note If any part of the Calc window in Figure 1 is not shown, you can display it using the View menu. For example, View > Status Bar will toggle (show or hide) the Status Bar. It is not always necessary to display all the parts, as shown; show or hide any of them, as desired. Title bar The Title bar, located at the top, shows the name of the current spreadsheet.
• File contains commands that apply to the entire document such as Open, Save, Wizards, Export as PDF, and Digital Signatures. • Edit contains commands for editing the document such as Undo, Changes, Compare Document, and Find and Replace. • View contains commands for modifying how the Calc user interface looks such as Toolbars, Full Screen, and Zoom. • Insert contains commands for inserting elements such as cells, rows, columns, sheets, and pictures into a spreadsheet.
Figure 2: Apply Style, Font Name and Font Size lists Note If any of the icons (buttons) in Figure 2 is not shown, you can display it by clicking the small triangle at the right end of the Formatting toolbar, selecting Visible Buttons in the dropdown menu, and selecting the desired icon (for example, Apply Style) in the drop-down list. It is not always necessary to display all the toolbar buttons, as shown; show or hide any of them, as desired.
Clicking the Function button inserts an equals (=) sign into the selected cell and the Input line, thereby enabling the cell to accept a formula. When you enter new data into a cell, the Sum and Equals buttons change to Cancel and Accept buttons . The contents of the current cell (data, formula, or function) are displayed in the Input line, which is the remainder of the Formula Bar. You can either edit the cell contents of the current cell there, or you can do that in the current cell.
Figure 5: Right end of Calc status bar Sheet sequence number ( ) Shows the sequence number of the current sheet and the total number of sheets in the spreadsheet. The sequence number may not correspond with the name on the sheet tab. Page style ( ) Shows the page style of the current sheet. To edit the page style, double-click on this field. The Page Style dialog opens. Insert mode ( ) Click to toggle between INSRT (Insert) and OVER (Overwrite) modes when typing.
When the cursor is on an object such as a picture or chart, the information shown includes the size of the object and its location. Zoom ( )—new in OOo 3.1 To change the view magnification, drag the Zoom slider or click on the + and – signs. You can also right-click on the zoom level percentage to select a magnification value or double-click to open the Zoom & View Layout dialog. Starting new spreadsheets You can create a new, blank spreadsheet from the Start Center (Welcome to OpenOffice.
http://extensions.services.openoffice.org/ and installing them as described in Chapter 14 (Customizing Calc). Figure 6: Starting a new spreadsheet from a template Opening existing spreadsheets You can open an existing spreadsheet from the Start Center or from any component of OOo. Calc can open spreadsheets in a wide range of file formats, including Microsoft Excel (*.xls and *.xlsx). From the Start Center Click the Open a document icon. From the Menu bar Choose File > Open.
From the keyboard Press the key combination Control+O. Each of these options displays the Open dialog, where you can locate the spreadsheet that you want to open. Tip You can also use the Recent Documents list to open a spreadsheet. This list is located on the File menu, directly below Open. The list displays the last 10 files that were opened in any of the OOo components. Opening CSV files Comma-separated-values (CSV) files are text files that contain the cell contents of a single sheet.
Figure 7: Text Import dialog, with Comma (,) selected as the separator and double quotation mark (“) as the text delimiter. Saving spreadsheets Spreadsheets can be saved in three ways. From the Menu bar Choose File > Save (or Save All or Save As). From the toolbar Click the Save button on the Standard toolbar. If the file has been saved and no subsequent changes have been made, this button is grayed-out and not clickable. From the keyboard Press the key combination Control+S.
Note If the spreadsheet has been previously saved, then saving it using the Save (or Save All) command will overwrite an existing copy. However, you can save the spreadsheet in a different location or with a different name by selecting File > Save As. Password protection To protect an entire document from being viewable without a password, use the option on the Save As dialog to enter a password. This option is only available for files saved in OpenDocument formats or the older OpenOffice.org 1.x formats.
Some users of Microsoft Excel may be unwilling or unable to receive *.ods files. (Perhaps their employer does not allow them to install the plug-in.) In this case, you can save a document as a Excel file (*.xls or *.xlsx). 1) Important—First save your spreadsheet in the file format used by OpenOffice.org, *.ods. If you do not, any changes you may have made since the last time you saved it will only appear in the Microsoft Excel version of the document. 2) Then choose File > Save As.
Tip To have Calc save documents by default in a Microsoft Excel file format, go to Tools > Options > Load/Save > General. In the section named Default file format and ODF settings, under Document type, select Spreadsheet, then under Always save as, select your preferred file format. Saving as a CSV file To save a spreadsheet as a comma separate value (CSV) file: 1) Choose File > Save As. 2) In the File name box, type a name for the file. 3) In the File type list, select Text CSV (*.csv;*.txt;*.
Saving in other formats Calc can save spreadsheets in a range of formats, including HTML (Web pages), through the Save As dialog. Calc can also export spreadsheets to the PDF and XHTML file formats. See Chapter 6 (Printing, Exporting, and E-mailing) for more information. Navigating within spreadsheets Calc provides many ways to navigate within a spreadsheet from cell to cell and sheet to sheet. You can generally use whatever method you prefer.
Figure 10. (left) One selected cell and (right) a group of selected cells Using the mouse To move the focus using the mouse, simply move the mouse pointer to the cell where you want the focus to be and click the left mouse button. This action changes the focus to the new cell. This method is most useful when the two cells are a large distance apart. Using the Tab and Enter keys • Pressing Enter or Shift+Enter moves the focus down or up, respectively.
Tip Use one of the four Alt+Arrow key combinations to resize the height or width of a cell. (For example: Alt+↓ increases the height of a cell.) Table 1.
Customizing the effects of the Enter key You can customize the direction in which the Enter key moves the focus, by selecting Tools > Options > OpenOffice.org Calc > General. The four choices for the direction of the Enter key are shown on the right hand side of Figure 11. It can move the focus down, right, up, or left. Depending on the file being used or on the type of data being entered, setting a different direction can be useful.
Move to the first sheet Move left one sheet Move right one sheet Move to the last sheet Sheet tabs Figure 12. Sheet tab arrows Notice that the sheets here are not numbered in order. Sheet numbering is arbitrary; you can name a sheet as you wish. Note The sheet tab arrows that appear in Figure 12 only appear if you have some sheet tabs that can not be seen. Otherwise, they appear faded as in Figure 1.
3) Hold down the Shift key and click. Tip You can also select a contiguous range of cells by first clicking in the STD field on the status bar and changing it to EXT, before clicking in the opposite corner of the range of cells in step 3 above. If you use this method, be sure to change EXT back to STD or you may find yourself extending the selection unintentionally. To select a range of cells without using the mouse: 1) Select the cell that will be one of the corners in the range of cells.
Selecting columns and rows Entire columns and rows can be selected very quickly in OOo. Single column or row To select a single column, click on the column identifier letter (see Figure 1). To select a single row, click on the row identifier number. Multiple columns or rows To select multiple columns or rows that are contiguous: 1) Click on the first column or row in the group. 2) Hold down the Shift key. 3) Click the last column or row in the group.
Multiple contiguous sheets To select multiple contiguous sheets: 1) Click on the sheet tab for the first desired sheet. 2) Move the mouse pointer over the sheet tab for the last desired sheet. 3) Hold down the Shift key and click on the sheet tab. All the tabs between these two sheets will turn white. Any actions that you perform will now affect all highlighted sheets. Multiple noncontiguous sheets To select multiple noncontiguous sheets: 1) Click on the sheet tab for the first desired sheet.
2) Choose either Insert > Columns or Insert > Rows. Using the mouse: 1) Select the cell, column, or row where you want the new column or row inserted. 2) Right-click the header of the column or row. 3) Choose Insert Rows or Insert Columns. Multiple columns or rows Multiple columns or rows can be inserted at once rather than inserting them one at a time.
Working with sheets Like any other Calc element, sheets can be inserted, deleted, and renamed. Inserting new sheets There are several ways to insert a new sheet. The first step for all of the methods is to select the sheets that the new sheet will be inserted next to. Then any of the following options can be used. • Choose Insert > Sheet from the menu bar. • Right-click on the sheet tab and choose Insert Sheet. • Click in an empty space at the end of the line of sheet tabs.
Deleting sheets Sheets can be deleted individually or in groups. Single sheet Right-click on the tab of the sheet you want to delete and choose Delete Sheet from the pop-up menu, or choose Edit > Sheet > Delete from the Menu bar. Either way, an alert will ask if you want to delete the sheet permanently. Click Yes.
In addition to using the Zoom slider (new in OOo 3.1) on the Status bar (see page 16), you can open the Zoom dialog and make a selection on the left-hand side. • Choose View > Zoom from the Menu bar, or • Double-click on the percentage figure in the Status bar at the bottom of the window. Figure 16. Zoom dialog Optimal Resizes the display to fit the width of the selected cells. To use this option, you must first highlight a range of cells. Fit Width and Height Displays the entire page on your screen.
Figure 17 shows some frozen rows and columns. The heavier horizontal line between rows 3 and 14 and the heavier vertical line between columns C and H denote the frozen areas. Rows 4 through 13 and columns D through G have been scrolled off the page. The first three rows and columns remained because they are frozen into place. Figure 17. Frozen rows and columns You can set the freeze point at one row, one column, or both a row and a column as in Figure 17.
Splitting the screen Another way to change the view is by splitting the window, also known as splitting the screen. The screen can be split horizontally, vertically, or both. You can therefore have up to four portions of the spreadsheet in view at any one time. Figure 18. Split screen example Why would you want to do this? Imagine you have a large spreadsheet and one of the cells has a number in it that is used by three formulas in other cells.
2) Immediately above this button, you will see a thick black line (Figure 19). Move the mouse pointer over this line, and it turns into a line with two arrows (Figure 20). Figure 20. Split-screen bar on vertical scroll bar with cursor 3) Hold down the left mouse button. A gray line appears, running across the page. Drag the mouse downwards and this line follows. 4) Release the mouse button and the screen splits into two views, each with its own vertical scroll bar.
3) Hold down the left mouse button, and a gray line appears, running up the page. Drag the mouse to the left and this line follows. 4) Release the mouse button, and the screen is split into two views, each with its own horizontal scroll bar. You can scroll the left and right parts of the window independently. Removing split views To remove a split view, do any of the following: • Double-click on each split line. • Click on and drag the split lines back to their places at the ends of the scroll bars.
Figure 22: The Navigator in Calc Table 2: Function of icons in the Navigator Icon Action Data Range. Specifies the current data range denoted by the position of the cell cursor. Start/End. Moves to the cell at the beginning or end of the current data range, which you can highlight using the Data Range button. Contents. Shows or hides the list of categories. Toggle. Switches between showing all categories and showing only the selected category. Displays all available scenarios.
Moving quickly through a document The Navigator provides several convenient ways to move around a document and find items in it: • To jump to a specific cell in the current sheet, type its cell reference in the Column and Row boxes at the top of the Navigator and press the Enter key; for example, in Figure 22 the cell reference is A7. • When a category is showing the list of objects in it, double-click on an object to jump directly to that object’s location in the document.
2 Chapter Entering, Editing, and Formatting Data
Introduction You can enter data into Calc in several ways: using the keyboard, the mouse (dragging and dropping), the Fill tool, and selection lists. Calc also provides the ability to enter information into multiple sheets of the same document at the same time. After entering data, you can format and display it in various ways. Entering data using the keyboard Most data entry in Calc can be accomplished using the keyboard.
Note Caution When a plain apostrophe is used to allow a leading 0 to be displayed, it is not visible in the cell after the Enter key is pressed. If “smart quotes” are used for apostrophes, the apostrophe remains visible in the cell. To choose the type of apostrophe, use Tools > AutoCorrect Options > Custom Quotes. The selection of the apostrophe type affects both Calc and Writer.
Figure 23: The Special Characters dialog Inserting dashes To enter en and em dashes, you can use the Replace dashes option under Tools > AutoCorrect Options. This option replaces two hyphens, under certain conditions, with the corresponding dash. In the following table, the A and B represent text consisting of letters A to z or digits 0 to 9.
On the Replace tab, you can also delete unwanted word pairs and add new ones as required. AutoInput When you are typing in a cell, Calc automatically suggests matching input found in the same column. To turn the AutoInput on and off, set or remove the check mark in front of Tools > Cell Contents > AutoInput. Automatic date conversion Calc automatically converts certain entries to dates. To ensure that an entry that looks like a date is interpreted as text, type an apostrophe at the beginning of the entry.
Figure 24: Using the Fill tool Using a fill series A more complex use of the Fill tool is to use a fill series. The default lists are for the full and abbreviated days of the week and the months of the year, but you can create your own lists as well. To add a fill series to a spreadsheet, select the cells to fill, choose Edit > Fill > Series. In the Fill Series dialog, select AutoFill as the Series type, and enter as the Start value an item from any defined series.
Figure 26: Result of fill series selection shown in Figure 25 You can also use Edit > Fill > Series to create a one-time fill series for numbers by entering the start and end values and the increment. For example, if you entered start and end values of 1 and 7 with an increment of 2, you would get the sequence of 1, 3, 5, 7. In all these cases, the Fill tool creates only a momentary connection between the cells. Once they are filled, the cells have no further connection with one another.
Figure 28: Defining a new fill series Using selection lists Selection lists are available only for text, and are limited to using only text that has already been entered in the same column. To use a selection list, select a blank cell and press Ctrl+D. A drop-down list appears of any cell in the same column that either has at least one text character or whose format is defined as Text. Click on the entry you require.
Validating cell contents When creating spreadsheets for other people to use, you may want to make sure they enter data that is valid or appropriate for the cell. You can also use validation in your own work as a guide to entering data that is either complex or rarely used. Fill series and selection lists can handle some types of data, but they are limited to predefined information.
Figure 29: Typical validity test choices. The validity test options vary with the type of data selected from the Allow list. For example, Figure 30 shows the choices when a cell must contain a cell range. Figure 30: Validity choices for a cell range. To provide input help for a cell, use the Input Help page of the Validity dialog (Figure 31). To show an error message when an invalid value is entered, use the Error Alert page (Figure 32).
Figure 31: Defining input help for a cell Figure 32: Defining an error message for a cell with invalid data Editing data Editing data is done is in much the same way as entering it. The first step is to select the cell containing the data to be edited. Removing data from a cell Data can be removed (deleted) from a cell in several ways. Removing data only The data alone can be removed from a cell without removing any of the formatting of the cell.
the cell can be deleted. To delete everything in a cell (contents and format), check Delete all. Figure 33: Delete Contents dialog Replacing all the data in a cell To remove data and insert new data, simply type over the old data. The new data will retain the original formatting.
Formatting data The data in Calc can be formatted in several ways. It can either be edited as part of a cell style so that it is automatically applied, or it can be applied manually to the cell. Some manual formatting can be applied using toolbar icons. For more control and extra options, select the appropriate cell or cells range, right-click on it, and select Format Cells. All of the format options are discussed below.
Figure 35: Automatic text wrap Using manual line breaks To insert a manual line break while typing in a cell, press Ctrl+Enter. This method does not work with the cursor in the input line. When editing text, first double-click the cell, then single-click at the position where you want the line break. When a manual line break is entered, the cell width does not change. Figure 36 shows the results of using two manual line breaks after the first line of text.
Formatting numbers Several different number formats can be applied to cells by using icons on the Formatting toolbar. Select the cell, then click the relevant icon. Some icons may not be visible in a default setup; click the down-arrow at the end of the Formatting bar and select other icons to display. Figure 38: Number format icons. Left to right: currency, percentage, date, exponential, standard, add decimal place, delete decimal place.
Formatting the font To quickly choose the font used in a cell, select the cell, then click the arrow next to the Font Name box on the Formatting toolbar and choose a font from the list. Tip To choose whether to show the font names in their font or in plain text, go to Tools > Options > OpenOffice.org > View and select or deselect the Show preview of fonts option in the Font Lists section. For more information, see Chapter 14 (Setting Up and Customizing Calc).
Figure 40: Format Cells > Font Effects Setting cell alignment and orientation Some of the cell alignment and orientation icons are not shown by default on the Formatting toolbar. To show them, click on the small arrow at the right-hand end of the toolbar and select them from the list of icons.
For more control and other choices, use the Alignment tab (Figure 34) of the Format Cells dialog to set the horizontal and vertical alignment and rotate the text. If you have Asian languages enabled, then the Text orientation section shows an extra option (labeled Asian layout mode) under the Vertically stacked option, as shown in Figure 41. Figure 42: Asian layout mode option The difference in results between having Asian layout mode on or off is shown in Figure 43.
Note The cell border properties apply to a cell, and can only be changed if you are editing that cell. For example, if cell C3 has a top border (which would be equivalent visually to a bottom border on C2), that border can only be removed by selecting C3. It cannot be removed in C2. Formatting the cell background To quickly choose a background color for a cell, click the small arrow next to the Background Color icon on the Formatting toolbar.
Note If the selected cell range does not have column and row headers, AutoFormat is not available. 3) To select which properties (number format, font, alignment, borders, pattern, autofit width and height) to include in an AutoFormat, click More. Select or deselect the required options. 4) Click OK. If you do not see any change in color of the cell contents, choose View > Value Highlighting from the menu bar. Defining a new AutoFormat You can define a new AutoFormat that is available to all spreadsheets.
As soon as you select a theme, some of the properties of the custom styles are applied to the open spreadsheet and are immediately visible. 3) Click OK. If you wish, you can now go to the Styles and Formatting window to modify specific styles. These modifications do not change the theme; they only change the appearance of this specific spreadsheet document. Using conditional formatting You can set up cell formats to change depending on conditions that you specify.
Figure 45: Conditional formatting dialog Cell style Choose the cell style to be applied if the specified condition matches. The style must have been defined previously. See the Help for more information and examples of use. To apply the same conditional formatting later to other cells: 1) Select one of the cells that has been assigned conditional formatting. 2) Copy the cell to the clipboard. 3) Select the cells that are to receive this same formatting. 4) Choose Edit > Paste Special.
Hiding and showing data When elements are hidden, they are neither visible nor printed, but can still be selected for copying if you select the elements around them. For example, if column B is hidden, it is copied when you select columns A and C. When you need a hidden element again, you can reverse the process, and show the element. To hide or show sheets, rows, and columns, use the options on the Format menu or the right-click (context) menu.
When you close the dialog, the outline group controls are visible between either the row or column headers and the edges of the editing window. The controls resemble the tree-structure of a file-manager in appearance, and can be hidden by selecting Data > Group and Outline > Hide Details. They are strictly for online use, and do not print. The basic outline controls have plus or minus signs at the start of the group to show or hide hidden cells.
Standard filters are more complex than automatic filters. You can set as many as three conditions as a filter, combining them with the operators AND and OR. Standard filters are mostly useful for numbers, although a few of the conditional operators, such as = and < > can also be used for text. Other conditional operators for standard filters include options to display the largest or smallest values, or a percentage of them.
Figure 48: Choosing the criteria and order of sorting Figure 49: Options for sorting Case sensitive If two entries are otherwise identical, one with an upper case letter is placed before one with a lower case letter in the same position if the sort is descending; if the sort is ascending, then the entry with an upper case letter is placed after one with a lower case letter in the same position. 66 OpenOffice.org 3.
Range contains column labels Does not include the column heading in the sort. Include formats A cell's formatting is moved with its contents. If formatting is used to distinguish different types of cells, then use this option. Copy sort results to Sets a spreadsheet address to which to copy the sort results. If a range is specified that does not have the necessary number of cells, then cells are added. If a range contains cells that already have content, then the sort fails.
Caution Use Replace All with caution; otherwise, you may end up with some highly embarrassing mistakes. A mistake with Replace All might require a manual, word-by-word search to fix, if it is not discovered in time to undo it. Figure 50: Expanded Find & Replace dialog Finding and replacing formulas or values You can use the Find & Replace dialog to search in formulas or in the displayed values that result from a calculation.
3) Select Formulas or Values in the Search in drop-down list. • Formulas finds parts of the formulas. • Values finds the results of the calculations. 4) Type the text you want to find in the Search for box. 5) To replace the text with different text, type the new text in the Replace with box. 6) When you have set up your search, click Find. To replace text, click Replace instead.
To use wildcards and regular expressions when searching and replacing: 1) On the Find & Replace dialog, click More Options to see more choices. On this expanded dialog, select the Regular expressions option. 2) Type the search text, including the wildcards, in the Search for box and the replacement text (if any) in the Replace with box. 3) Click Find, Find All, Replace, or Replace All (not recommended). Tip The online help describes many of the regular expressions and their uses.
• Using \n in the Replace with box will replace with the literal characters \n, not a hard line break. • The Find & Replace dialog has an option to search Formulas, Values, or Notes. This applies to any search, not just one using regular expressions. Searching with the Formulas option would find SUM in a cell containing the formula =SUM(A1:A6).
3 Chapter Creating Charts and Graphs Presenting information visually
Introduction Charts and graphs can be powerful ways to convey information to the reader. OpenOffice.org Calc offers a variety of different chart and graph formats for your data. Using Calc, you can customize charts and graphs to a considerable extent. Many of these options enable you to present your information in the best and clearest manner. For readers who are interested in effective ways to present information graphically, two excellent introductions to the topic are William S.
Figure 52: Selecting data for plotting Next, open the Chart Wizard dialog using one of two methods. • Choose Insert > Chart from the menu bar. • Or, click the Chart icon on the main toolbar. Figure 53: Insert chart from main toolbar Either method inserts a sample chart on the worksheet, opens the Formatting toolbar, and opens the Chart Wizard, as shown in Figure 54. Tip 74 Before choosing the Chart Wizard, place the cursor anywhere in the area of the data.
Figure 54: Chart Wizard, Step 1—Choose a chart type Choosing a chart type The Chart Wizard includes a sample chart with your data. This sample chart updates to reflect the changes you make in the Chart Wizard. The Chart Wizard has three main parts: a list of steps involved in setting up the chart, a list of chart types, and the options for each chart type. At any time you can go back to a previous step and change selections.
the icons. For the moment, we will stick to the Column chart and click on Next again. Changing data ranges and axes labels In Step 2, Data Range, you can manually correct any mistakes you have made in selecting the data. On this page you can also change the way you are plotting the data by using the rows—rather than the columns—as data series. This is useful if you use a style of chart such as Donut or Pie to display your data.
Selecting data series Figure 56: Amending data series and ranges On the Data Series page, you can fine tune the data that you want to include in the chart. Perhaps you have decided that you do not want to include the data for canoes. If so, highlight Canoes in the Data series box and click on Remove. Each named data series has its ranges and its individual Y-values listed. This is useful if you have very specific requirements for data in your chart, as you can include or leave out these ranges.
Adding or changing titles, legend, and grids Figure 57: Titles, legend and grids On the Chart Elements page, you can give your chart a title and, if desired, a subtitle. Use a title that draws the viewers’ attention to the purpose of the chart: what you want them to see. For example, a better title for this chart might be The Performance of Motor and Other Rental Boats. It may be of benefit to have labels for the x axis or the y axis.
Changing the chart type You can change the chart type at any time. To do so: 1) First select the chart by double-clicking on it. The chart should now be surrounded by a gray border. 2) Then do one of the following: • Choose Format > Chart Type from the menu bar. • Click the chart type icon on the Formatting toolbar. • Right-click on the chart and choose Chart Type. In each case, a dialog similar to the one in Figure 54 opens. See page 75 for more information.
Chart floor Figure 59: Elements of 3D chart You can add other elements using the commands on the Insert menu. The various choices open dialogs in which you can specify details. First select the chart so the green sizing handles are visible. This is done with a single click on the chart. The dialogs for Titles, Legend, Axes, and Grids are self-explanatory. The others are a bit more complicated, so we’ll take a look at them here. Data labels Data labels put information about each data point on the chart.
Figure 60: Data Labels dialog Show value as percentage Displays the percentage value of the data points in each column. When selected, this option activates the Percentage format... button. Percentage format... Opens the Number Format dialog, where you can select the percentage format. Show category Shows the data point text labels. Show legend key Displays the legend icons next to each data point label. Separator Selects the separator between multiple text strings for the same object.
Trend lines When you have a scattered grouping of points in a graph, you may want to show the relationship of the points. A trend line is what you need. Calc has a good selection of regression types you can use for trend lines: linear, logarithm, exponential, and power. Choose the type that comes closest to passing through all of the points. To insert trend lines for all data series, double-click the chart to enter edit mode.
If you insert a trend line on a chart type that uses categories, such as Line or Column, then the numbers 1, 2, 3, … are used as x-values to calculate the trend line. The trend line has the same color as the corresponding data series. To change the line properties, select the trend line and choose Format Trend Line. This opens the Line tab of the Trend Lines dialog.
Standard deviation – shows error calculated on standard deviation – Error margin – you designate the error • Cell range – calculates the error based on cell ranges you select. The Parameters section at the bottom of the dialog changes to allow selection of the cell ranges. – Figure 62: Specifying the parameters of error bars Formatting charts The Format menu has many options for formatting and fine-tuning the appearance of your charts.
Arrangement Provides two choices: Bring Forward and Send Backward, of which only one may be active for some items. Use these choices to arrange overlapping data series. Title Formats the titles of the chart and its axes. Legend Formats the location, borders, background, and type of the legend. Axis Formats the lines that create the chart as well as the font of the text that appears on both the X and Y axes. Grid Formats the lines that create a grid for the chart.
Moving chart elements You may wish to move or resize individual elements of a chart, independent of other chart elements. For example, you may wish to move the legend to a different place. Pie charts allow moving of individual wedges of the pie (in addition to the choice of “exploding” the entire pie). 1) Double-click the chart so that it is enclosed by a gray border. 2) Double-click any of the elements—the title, the legend, or the chart graphic. Click and drag to move the element.
Figure 63: Chart Area dialog Changing the chart graphic background The chart wall is the area that contains the chart graphic. 1) Double-click the chart so that it is enclosed by a gray border. 2) Choose Format > Chart Wall. The Chart Wall dialog has the same formatting options as described in “Changing the chart area background” above. 3) Choose your settings and click OK.
Rotation and perspective To rotate a 3D chart or view it in perspective, enter the required values on the Perspective page of the 3D View dialog. You can also rotate 3D charts interactively; see page 90. Figure 64: Rotating a chart Some hints for using the Perspective page: • Set all angles to 0 for a front view of the chart. Pie charts and • • • • • • 88 donut charts are shown as circles.
Appearance Use the Appearance page to modify some aspects of a 3D chart’s appearance. Figure 65: Modifying appearance of 3D chart Select a scheme from the list box. When you select a scheme, the options and the light sources are set accordingly. If you select or deselect a combination of options that is not given by the Realistic or Simple schemes, you create a Custom scheme. Select Shading to use the Gouraud method for rendering the surface. Otherwise, a flat method is used.
seven normal, uniform light sources. The first light source projects a specular light with highlights. For the selected light source, you can then choose a color and intensity in the list just below the eight buttons. The brightness values of all lights are added, so use dark colors when you enable multiple lights. Figure 66: Setting the illumination Each light source always points at the middle of the object initially. To change the position of the light source, use the small preview inside this page.
Formatting the chart elements Depending on the purpose of your document, for example a screen presentation or a printed document for a black and white publication, you might wish to use more detailed control over the different chart elements to give you what you need. To format an element, left-click on the element that you wish to change, for example one of the axes. The element will be highlighted with green squares. Then, right-click and choose an item from the context menu.
Figure 68: Formatting axis labels Formating data labels You can choose properties for the labels of the data series. Carefully click on the chart element, then right-click and choose the property you want to change. This opens a dialog with several tabs where you can change the color of the label text, the size of the font, and other attributes. The Label tab is shown in Figure 68.
the gallery, or if you have pictures you need to use instead, you can insert them using Select > From file. Figure 69: Symbol selection Resizing and moving the chart You can resize or move all elements of a chart at the same time, in two ways: interactively, or by using the Position and Size dialog. You may wish to use a combination of both methods: interactive for quick and easy change, then the dialog for precise sizing and positioning.
Using the Position and Size dialog To resize or move a chart using the Position and Size dialog: 1) Click on the chart to select it. Green sizing handles appear around the chart. 2) Right-click and choose Position and Size from the pop-up menu. 3) Make your choices on this dialog. Figure 70: Defining the position and size of an object Position is defined as an X,Y coordinate relative to a fixed point (the base point), typically located at the upper left of the document.
Tip If you cannot move an object, check to see if its position is protected. Gallery of chart types Its important to remember that while your data can be presented with a number of different charts, the message you want to convey to your audience dictates the chart you ultimately use. The following sections present examples of the types of charts that Calc provides, with some of the tweaks that each sort can have and some notes as to what purpose you might have for that chart type.
• The second chart is the 3D option in the chart wizard with a simple border and the 3D chart area twisted around. • The third chart is an attempt to get rid of the legend and put labels showing the names of the companies on the axis instead. We also changed the colors to a hatch pattern. Pie charts Pie charts are excellent when you need to compare proportions. For example, comparisons of departmental spending: what the department spent on different items or what different departments spent.
Data Labels and choose Show value as number. Then carefully select the piece you wish to highlight, move the cursor to the edge of the piece and click (the piece will have nine green highlight squares to mark it), and then drag it out from the rest of the pieces. The pieces will decrease in size, so you need to highlight the chart wall and drag it at a corner to increase the size.
As shown in Figure 73, an area chart is sometimes tricky to use. This may be one good reason to use transparency values in an area chart. After setting up the basic chart using the Chart Wizard, do this: • Right-click on the Y axis and choose Delete Major Grid. As the data overlaps, some of it is missing behind the first data series. This is not what you want. A better solution is shown in Chart 2. • After deselecting the Y axis grid, right-click on each data series in turn and choose Format Data Series.
Line charts A line chart is a time series with a progression. It is ideal for raw data, and useful for charts with plentiful data that show trends or changes over time where you want to emphasize continuity. On line charts, the x-axis is ideal to represent time series data. Things to do with lines: thicken them, make them 3D, smooth the contours, just use points. 3D lines confuse the viewer, so just using a thicker line often works better.
Figure 76: A particularly volatile time in the world currency market. Bubble charts A bubble chart is a variation of a scatter chart in which the data points are replaced with bubbles. It shows the relations of three variables in two dimensions. Two variables are used for the position on the X-axis and Y-axis, while the third is shown as the relative size of each bubble. One or more data series can be included in a single chart. Bubble charts are often used to present financial data.
Net charts A net chart is similar to a polar or radar chart. They are useful for comparing data that are not time series, but show different circumstances, such as variables in a scientific experiment or direction. The poles of the net chart are equivalent to the y-axes of other charts. Generally, between three and eight axes are best; any more and this type of chart becomes confusing.
Coloured light on mood 12 10 8 6 4 2 0 green Figure 79: Filled net or radar chart Stock charts A stock chart is a specialized column graph specifically for stocks and shares. You can choose traditional lines, candlestick, and two-column type charts. The data required for these charts is quite specialized, with series for opening price, closing price, and high and low prices. Of course the x-axis represents a time series.
A nice touch is that OpenOffice.org Chart color-codes the rising and falling shares: white for rising and black for falling in the candlestick chart, and red and blue in the traditional line chart. Column and line charts A column and line chart is a combination of two other chart types. It is useful for combining two distinct but related data series, for example sales over time (column) and the profit margin trends (line). You can choose the number of columns and lines in the Chart Wizard.
For the background, highlight the chart wall, right-click and choose Format Wall. On the Area tab, change the drop-down box to show Gradient. Choose one of the preset gradient patterns and make it lighter by going to the Transparency tab and making the gradient 50% transparent. To make the chart look cleaner without the grid, go to Insert > Grids and deselect the X-axis option. 104 OpenOffice.org 3.
4 Chapter Using Styles and Templates in Calc Bringing uniformity to your spreadsheets
What is a template? A template is a model that you use to create other documents. For example, you can create a template for invoices that has your company’s logo and address at the top of the page. New spreadsheets created from this template will all have your company’s logo and address on the first page.
Styles help improve consistency in a document and can greatly speed up formatting. They also make major formatting changes easy. For example, you may decide to change the appearance of all subtotals in your spreadsheet to be 10 pt. Arial instead of 8 pt. Times New Roman after you have created a 15-page spreadsheet; you can change all of the subtotals in the document by simply changing the properties for the subtotal style.
Figure 82: Calc cell style types Page styles Page styles in Calc are applied to sheets. Although one sheet may print on several pages (pieces of paper), only one page style can be applied to a sheet. If a spreadsheet file contains more than one sheet, the different sheets can have different page styles applied to them.
• Toolbar: Click the icon on the far left of the Formatting toolbar. The Styles and Formatting window can be docked at the left or right of the main Calc window. To dock or undock the window, hold down the Ctrl key and double-click a gray part of the window next to the icons at the top. The first button on the top left of the window, the second, , is for cell styles and , is for page styles.
Using Fill Format mode This method is quite useful when you need to apply the same style to many scattered cells. 1) Open the Styles and Formatting window and select the style you want to apply. 2) Click the Fill Format mode icon . The mouse pointer changes to this icon. 3) Position the moving icon on the cell to be styled and click the mouse button. 4) To quit Fill Format mode, click the Fill Format mode icon again or close the Styles and Formatting window.
2) On the submenu, click Apply Style. The menus close and the Apply Style list now appears on the toolbar between the Styles and Formatting icon and the Font Name list. Assigning styles to shortcut keys You can create keyboard shortcuts to apply commonly-used cell or page styles, including custom styles that you have created. See Chapter 14 (Setting up and Customizing Calc) for instructions. Applying page styles 1) Select the sheet to be styled (click on its sheet tab at the bottom of the screen).
The Style dialog has several tabs. The Organizer tab, shown in Figure 86 for cell styles, is found in all components of OOo. It provides basic information about the style. The Organizer tab for page styles is similar to the one shown for cell styles. Figure 86: Organizer tab of Cell Style dialog Name This is the style’s name. You cannot change the name of a built-in style, but you can change the name of a custom style. Linked with This option is only available for cell styles; page styles cannot be linked.
Cell style options When editing or creating cell styles, you can set several options, which are similar to those for directly formatting cells. A more detailed coverage of cell formatting is given in Chapter 2 (Entering, Editing, and Formatting Data). A brief summary is provided here. Numbers On the Numbers tab, you can control the behavior of the data in a cell with this style. This includes specifying the type of data, the number of decimal places, and the language.
Page Use the Page tab to edit the overall appearance of the page and its layout. The available options are shown in Figure 87. Figure 87: Page Style: Page tab Paper format Here you can set a generic paper type to be used. Letter or A4 are most common, but you can also use legal, tabloid, envelope sizes, or user-defined paper types. You can also define the orientation of the page and which print tray for the paper to come from (if your printer has more than one tray).
Layout settings: Format This area specifies the page numbering style for this page style. Layout settings: Table alignment This option specifies the alignment options for the cells on a printed page, either horizontal or vertical. Borders The Border and Background tabs for pages duplicate the tabs of the same name on cell styles, and are over-ridden by the cell style or manual settings. You may choose to ignore the Border and Background tabs altogether in page styles.
Creating new (custom) styles You may want to add some new styles. You can do this in two ways: • Creating a new style using the Style dialog • Creating a new style from a selection Note New styles apply only to this document; they are not be saved in the template. To save new styles in a template, see “Copying and moving styles“ on page 117 and “Creating a template” on page 120.
Figure 88: Naming a new style created from a selection. Creating a new style by dragging and dropping Select a cell and drag it to the Styles and Formatting window. Copying and moving styles Occasionally you may want to copy a style from one spreadsheet to another, or between a spreadsheet and a template, instead of recreating it in the second spreadsheet. You can do this using the Template Management dialog. 1) Click File > Templates > Organize.
Figure 89: Choosing to copy styles from a document, not a template. Figure 90: Copying a style from one document to another. 118 OpenOffice.org 3.
Deleting styles You cannot remove (delete) any of Calc’s predefined styles, even if you are not using them. You can remove any user-defined (custom) styles; but before you do, you should make sure the styles are not in use. If an unwanted style is in use, you will want to replace it with a substitute style. Replacing styles (and then deleting the unwanted ones) can be very useful if you are dealing with a spreadsheet that has been worked on by several people.
Figure 91: Templates and Documents dialog. Creating a template You can create a template from a document: 1) Open a new or existing document of the type you want to make into a template (text document, spreadsheet, drawing, presentation). 2) Add the content and styles that you want. 3) From the main menu, choose File > Templates > Save. The Templates dialog opens (see Figure 92). 4) In the New template field, type a name for the new template.
Figure 92: Saving a new template Any settings that can be added to or modified in a document can be saved in a template.
Figure 93: Template management dialog 2) In the box on the left, double-click the folder that contains the template that you want to edit. A list of all the templates contained in that folder appears underneath the folder name. 3) Select the template that you want to edit. 4) Click the Commands button and choose Edit from the dropdown menu. 5) Edit the template just as you would any other document. To save your changes, choose File > Save from the main menu.
Caution Note If you choose Keep Old Styles in the message box shown in Figure 94, that message will not appear again the next time you open the document after changing the template it is based on. You will not get another chance to update the styles from the template, although you can use the macro given in the Note below to re-enable this feature. To re-enable updating from a template: 1) Use Tools > Macros > Organize Macros > OpenOffice.org Basic.
2) In OOo, choose Tools > Extension Manager from the menu bar. In the Extension Manager dialog, click Add. 3) A file browser window opens. Find and select the package of templates you want to install and click Open. The package begins installing. You may be asked to accept a license agreement. 4) When the package installation is complete, the templates are available for use through File > New > Templates and Documents and the extension is listed in the Extension Manager.
To set a custom template as the default: 1) From the main menu, choose File > Templates > Organize. The Template Management dialog opens. 2) In the box on the left, select the folder containing the template that you want to set as the default, then select the template. 3) Click the Commands button and choose Set As Default Template from the drop-down menu. The next time that you create a document by choosing File > New, the document will be created from this template.
Chapter 2 (Entering, Editing, and Formatting Data) for more about replacing styles using Find and Replace. 1) Use File > New > Templates and Documents. Choose the template you want. A new file, based on the template, opens. If the template has unwanted text or graphics in it, delete them. 2) Open the spreadsheet you want to change. (It opens in a new window.) Press Control+A to select everything in the spreadsheet.
To delete a template folder: 1) In the Template Management dialog, select the folder that you want to delete. 2) Click the Commands button and choose Delete from the dropdown menu. A message box appears and asks you to confirm the deletion. Click Yes. Moving a template To move a template from one template folder to another template folder: 1) In the Template Management dialog, double-click the folder that contains the template you want to move.
2) Click the Commands button and choose Import Template from the drop-down menu. A standard file browser window opens. 3) Find and select the template that you want to import and click Open. The file browser window closes and the template appears in the selected folder. 4) If you want, type a new name for the template, and then press Enter.
5 Chapter Using Graphics in Calc
Graphics in Calc Calc is often used to present data and make forecasts and predictions. Graphics can turn an average document into a memorable one. Calc can import various vector (line drawing) and raster (bitmap) file formats. The most commonly used graphic formats are GIF, JPG, PNG, and BMP. See the Help for a full list of the formats OOo can import.
To insert an image from a file, use either of the following methods: • Insert Picture dialog • Drag and drop Insert Picture dialog 1) Click in the location in the Calc document where you want the image to appear. Do not worry too much about the exact placement of the image at this stage; placement can be changed easily as described in “Positioning graphics” on page 148. 2) Choose Insert > Picture > From File from the menu bar, or click the Insert Picture icon on the Picture toolbar).
Note Your Insert Picture dialog may look quite different from the one shown here, depending on your operating system and your choice in Tools > Options > OpenOffice.org > General of whether to use the OOo Open and Save dialogs. Drag and drop 1) Open a file browser window and locate the image you want to insert. 2) Drag the image into the Calc document and drop it where you want it to appear. A faint vertical line marks where the image will be dropped.
images in a subfolder named Images (under the folder containing the Writer document); the recipient of the Calc file needs to put the images in a subfolder with the same name (under the folder containing the Writer document). Note When inserting the same image several times in the document, it might appear beneficial to create links. However, this is not necessary, as OOo embeds in the document only one copy of the image file. (Deleting one or more of the copies does not affect the others.
Inserting an image from the clipboard Using the clipboard, you can copy images into a Calc document from another Calc document, from another component of OOo (Writer, Draw, and so on), and from other programs. To do this: 1) Open both the source document and the Calc document into which you want to copy the image. 2) In the source document, select the image to be copied. 3) Press Control+C to copy the image to the clipboard. 4) Switch to the Calc window.
By default, the Gallery is docked above the Calc workspace. To expand the Gallery, position the pointer over the line that divides it from the top of the workspace. When the pointer changes to parallel lines with arrows, click and drag downward. The workspace resizes in response. Figure 98: Gallery in Calc To expand the Gallery without affecting the workspace, undock it so it floats over the workspace.
Modifying images When you insert a new image, you may need to modify it to suit the document. This section describes the use of the Picture toolbar, resizing, cropping, and a workaround for rotating a picture. Changes made in Calc do not affect the original picture, whether it is embedded or linked. Calc provides many tools for working with images. These tools are sufficient for most people’s everyday requirements.
Table 3: Picture toolbar functions (from left to right) Icon Name Behavior From File Use of this icon is described in “Inserting an image file” on page 130. Filter Displays the Graphic Filter toolbar. See page 138. Graphics Mode Provides several color modes in the drop-down list. See page 138. Color Opens the Color toolbar, described on page 139. Transparency Sets the transparency of the selected image. See page 139. Line Adjusts the border style of the selected image.
Choosing a graphics mode You can change color images to grayscale by selecting the image and then selecting Grayscale from the Graphics mode list. Table 4: Graphics modes Graphics mode Behavior Default Keeps the picture the same as it was inserted. Grayscale Shows the picture in gradual shades of gray. Black / White Converts the picture into a monochromatic black and white image. Watermark Makes the picture into a watermark that blends into the background.
Icon Name Behavior Pop Art Applies a Pop Art style to the image. Charcoal Sketch Applies a Charcoal Sketch look to the image. Relief Displays a dialog for creating reliefs. The position of the imaginary light source that determines the type of shadow can be chosen. Mosaic Joins small groups of pixels into rectangular areas of the same color. The larger the individual rectangles are, the fewer details the graphic image has.
Cropping pictures When you are only interested in a section of the image for the purpose of your document, you may wish to crop (cut off) parts of it. The user interface in Calc for cropping an image is not very friendly, so it may be a better choice to use a graphics package. Click the Crop icon to open a dialog where you can select which portion of the image you want to remove. It is not possible to use the mouse to select the area to be cropped, as you can in Draw.
Left, Right, Top, and Bottom The image is cropped by the amount entered in these boxes. For example, a value of 3cm in the Left box cuts 3 cm from the left side of the picture. • When Keep scale is selected, the size of the image also changes, so in this example the width will be reduced by 3 cm.
Resizing a bit-mapped (raster) image such as a photograph adversely affects the resolution, causing some degree of blurring. It is better to use a graphics package to size your picture correctly before inserting it into your document, if possible. For more accurate resizing, use the Position and Size dialog, described on page 143. Rotating a picture Calc does not provide a tool for rotating a picture, but you can use this workaround: 1) Open a new Draw or Impress document.
text entered is part of the graphic, so if the graphic is moved the text will move with it. Position and Size Opens the dialog shown below where you can change the size, location, rotation, slant and corner radius of the image. Figure 101: Position and Size Dialog Original Size Resets the dimensions of the image to the values when it was originally inserted into the document. Description You can add metadata in the form of a title and description to the image.
Name You can add a custom name to be assigned to the image, to make it easier to find in the Navigator. Tip When collaborating with a team on a large, multi-page publication, it may be beneficial to give graphics, figures, and other objects meaningful names and descriptions to aid in clear communication. Flip Flips the image either horizontally or vertically. Assign Macro Adds programmable functionality to the image. Calc provides rich macro functionality.
Using Calc’s drawing tools Calc, like the other components of OOo, has a range of tools to create custom drawings. This chapter covers the default options in Calc. For a more detailed explanation of the drawing tools and their uses, see the Draw Guide. In general, if you need to create complex drawings, it is recommended to use OpenOffice.org Draw or another drawing program. To begin using the drawing tools, choose View > Toolbars > Drawing. The Drawing toolbar appears at the bottom of the screen.
Icon Name Behavior Flowcharts Opens the Flowchart toolbar of shapes. Callouts Opens the Callouts toolbar of shapes. Stars Opens the Stars toolbar of shapes. Points Allows editing of the points of a selected polygon. Fontwork Gallery Opens the Fontwork Gallery. From File Inserts a picture using the Insert Picture dialog. Extrusion On/Off Opens the 3-D Setting toolbar and converts the selected shape (if any) to 3-D.
If support for Asian languages has been enabled (in Tools > Options > Language Settings > Languages), two more tools can be added to the Drawing toolbar: Vertical Text and Vertical Callouts. To use a drawing tool: 1) Click in the document where you want the drawing to be anchored. You can change the anchor later, if necessary. 2) Select the tool from the Drawing toolbar (Figure 102). The mouse pointer changes to a cross-hair pointer.
1 Line 5 Line Color 9 Change Anchor 13 Alignment 2 Arrow Style 6 Area 10 Bring to Front 3 Line Style 7 Area Style / Filling 11 Send to Back 4 Line Width 8 Rotate 12 To Foreground / To Background Figure 103. Drawing Object Properties toolbar Resizing a drawing object Select the object, click on one of the eight handles around it and drag it to its new size. For a scaled resizing, select one of the corner handles and keep the Shift key pressed while dragging the handle.
Arranging graphics Graphics in a Calc document are maintained in a similar manner to a deck of cards. As you add more images to the document, each image occupies a new layer at the top of the stack. To arrange graphics, you tell Calc to change the order of layers in the stack. Figure 104: Layering effect Calc provides four basic options to re-arrange the order of images.
To Background and To Foreground An image or a drawing object can be sent to the background as well. This is not the same as Bring Forward and Send Backward, which set the order of a number of overlapping graphics. This feature pushes a graphic behind the cells, allowing cells to be edited without affecting the graphic. A graphic in the background will have To Foreground as a menu item, instead of To Background. Anchoring graphics Anchors tell a graphic where to stay in relation to other items.
. Figure 106: Anchoring 2 Aligning graphics You can align several graphics relative to each other. To do this: 1) Select all of the graphics to be aligned (Shift+click on each in turn). The graphics will be surrounded by an invisible bounding box with 8 green handles. 2) On the Picture toolbar, click on the Alignment icon and select one of the six options. Or, position the mouse pointer over any of the graphics.
3) Use the tools and fields in the dialog (described on the next page) to define the hotspots and links necessary. 4) Click the Apply icon to apply the settings. 5) When done, click the Save icon to save the imagemap to a file, then click the X in the upper right corner to close the dialog. Figure 107: The dialog to create or edit an image map The main part of the dialog shows the image on which the hotspots are defined. A hotspot is identified by a line indicating its shape.
• Properties: sets the hyperlink properties and adds the Name attribute to the hyperlink. Below the toolbar, specify for the selected hotspot: • Address: the address pointed by the hyperlink. You can also point to an anchor in a document; to do this, write the address in this format: file:////document_name#anchor_name • Text: type the text that you want to be displayed when the mouse pointer is moved over the hotspot.
6 Chapter Printing, Exporting, and E-mailing
Quick printing Click the Print File Directly icon to send the entire document to the default printer defined for your computer. Note You can change the action of the Print File Directly icon to send the document to the printer defined for the document instead of the default printer for the computer. Choose Tools > Options > Load/Save > General and the Load printer settings with the document option. Controlling printing For more control over printing, use File > Print. Figure 108.
what paper size to print on. The properties available depend on the selected printer; consult the printer’s documentation for details. • What sheets and pages to print, how many copies to print, and in what order to print them. Use dashes to specify page ranges and commas or semicolons to separate ranges; for example: 1, 5, 11– 14, 34–40. Selection refers to the highlighted part of a page or pages. • Whether to print to a printer or to a file.
3) Deselect Print only selected sheets. This affects the print preview, export, and printing of your spreadsheet. Click OK. Note The Options button is different from the Properties button. Properties deals with the settings of the printer, whereas Options deals with OOo’s settings. Selecting the page order, details, and scale To select the page order, details, and scale to be printed: 1) Choose Format > Page from the main menu.
Where a sheet prints to more than one page of paper, it can be printed either by column, where the first column of pages prints, and then the second column and so on, or by row as shown in the graphic on the top right of the dialog in Figure 110. Print You can specify which details to print.
Using print ranges Print ranges have several uses, including printing only a specific part of the data or printing selected rows or columns on every page. Defining a print range To define a new print range or modify an existing print range: 1) Highlight the range of cells that comprise the print range. 2) Choose Format > Print Ranges > Define. The page break lines display on the screen. Tip You can check the print range by using File > Page Preview. OOo will only display the cells in the print range.
Ranges > Edit. If you have already selected a print range, the Edit Print Ranges dialog looks something like Figure 111. Figure 111: Edit a print range In this example, three rectangles are selected, each separated by a semicolon. The first is bounded by cell A3 ($A$3) in the top left and cell C9 ($C$9) in the bottom right corners. Clicking anywhere in the text entry box shows the currently selected print range on the screen, with each rectangle in a different color, as in Figure 112.
For example, if the top two rows of the sheet as well as column A need to be printed on all pages, do the following: 1) Choose Format > Print Ranges > Edit. On the Edit Print Ranges dialog, type the rows in the text entry box under Rows to repeat. For example, to repeat rows 1 and 2, type $1:$2. This automatically changes Rows to repeat from - none - to - user defined -. Figure 113: Specifying repeating rows 2) To repeat columns, type the columns in the text entry box under Columns to repeat.
3) Click the More button in the dialog and then select the Print range option. Click the Add button. 4) To include more than one group of cells in the selection, type in the additional ranges. For example, to select the rectangle with A3 as the top left cell and F20 as the bottom right cell, enter ;$A$3:$F$20 or ;A3:F20 (both work and are equivalent) after the initial selection. Make sure that each group of cells is separated with a semicolon. 5) Click OK.
Page breaks While defining a print range can be a powerful tool, it may sometimes be necessary to manually adjust Calc’s printout. To do this, you can use a manual break. A manual break helps to ensure that your data prints properly. You can insert a horizontal page break above, or a vertical page break to the left of, the active cell. Inserting a page break To insert a page break: 1) Navigate to the cell where the page break will begin. 2) Select Insert > Manual Break.
Note Multiple manual row and column breaks can exist on the same page. When you want to remove them, you have to remove each one individually. This may be confusing at times, because although there may be a column break set on the page, when you go to Edit > Manual Break, the Column break choice may not be available (grayed out). In order to remove the break, you have to be in the cell next to the break.
Figure 115: Header dialog Header or footer appearance To change the appearance of the header or footer, click the More button in the dialog. This opens the Border/Background dialog. Figure 116: Header/Footer Border/Background dialog From this dialog you can set the background and border style of the header or footer. See Chapter 4 (Using Styles and Templates) for more information.
Setting the contents of the header or footer The header or footer of a Calc spreadsheet has three columns for text. Each column can have different contents. To set the contents of the header or footer, click the Edit button in the header or footer dialog shown in Figure 115 to display the dialog shown in Figure 117. Figure 117: Edit contents of header or footer Areas Each area in the header or footer is independent and can have different information in it.
Inserts the Time field. Inserts the current page number. Exporting to PDF Calc can export documents to PDF (Portable Document Format). This industry-standard file format is ideal for sending the file to someone else to view using Adobe Reader or other PDF viewers. Quick export to PDF Click the Export Directly as PDF icon to export the entire document using your default PDF settings.
Figure 118: General page of PDF Options dialog Images section • Lossless compression: Images are stored without any loss of quality. Tends to make large files when used with photographs. Recommended for other kinds of images or graphics. • JPEG compression: Allows for varying degrees of quality. A setting of 90% works well with photographs (small file size, little perceptible loss). • Reduce image resolution: Lower-DPI (dots per inch) images have lower quality.
Note EPS images with embedded previews are exported only as previews. EPS images without embedded previews are exported as empty placeholders. General section • PDF/A-1: PDF/A is an ISO standard established in 2005 for longterm preservation of documents, by embedding all the pieces necessary for faithful reproduction (such as fonts) while forbidding other elements (including forms, security, encryption, and tagged PDF). If you select PDF/A-1, the forbidden elements are greyed-out (not available).
Figure 119: Initial View page of PDF Options dialog Figure 120: User Interface page of PDF Options dialog Window options section • Resize window to initial page: Causes the PDF viewer window to resize to fit the first page of the PDF. 170 OpenOffice.org 3.
• Center window on screen: Causes the PDF viewer window to be centered on the computer screen. • Open in full screen mode: Causes the PDF viewer to open fullscreen instead of in a smaller window. • Display document title: Causes the PDF viewer to display the document’s title in the title bar. User interface options section • Hide menubar: Causes the PDF viewer to hide the menu bar. • Hide toolbar: Causes the PDF viewer to hide the toolbar.
Export URLs relative to file system If you have defined relative links in a document, this option exports those links to the PDF. Cross-document links Defines the behavior of links clicked in PDF files. Security page of PDF Options dialog PDF export includes options to encrypt the PDF (so it cannot be opened without a password) and apply some digital rights management (DRM) features. • With an open password set, the PDF can only be opened with the password.
• With both the open password and permission password set, the PDF can only be opened with the correct password, and its permissions can be restricted. Note Permissions settings are effective only if the user’s PDF viewer respects the settings. Figure 123 shows the dialog displayed when you click the Set open password button on the Security page of the PDF Options dialog. After you set a password for permissions, the other choices on the Security page (shown in Figure 122) become available.
Saving as Web pages (HTML) Calc can save files as HTML documents. Use File > Save As and select HTML Document, or File > Wizards > Web Page. If the file contains more than one sheet, the additional sheets will follow one another in the HTML file. Links to each sheet will be placed at the top of the document. Calc also allows the insertion of links directly into the spreadsheet using the Hyperlink dialog.
Digital signing of documents To sign a document digitally, you need a personal key, the certificate. A personal key is stored on your computer as a combination of a private key, which must be kept secret, and a public key, which you add to your documents when you sign them. You can get a certificate from a certification authority, which may be a private company or a governmental institution.
Removing personal data You may wish to ensure that personal data, versions, notes, hidden information, or recorded changes are removed from files before you send them to other people or create PDFs from them. In Tools > Options > OpenOffice.org > Security > Options, you can set Calc to remind (warn) you when files contain certain information and remove personal information automatically on saving. To remove personal and some other data from a file, go to File > Properties.
7 Chapter Using Formulas and Functions
Introduction In previous chapters, we have been entering one of two basic types of data into each cell: numbers and text. However, we will not always know what the contents should be. Often the contents of one cell depends on the contents of other cells. To handle this situation, we use a third type of data: the formula. Formulas are equations using numbers and variables to get a result. In a spreadsheet, the variables are cell locations that hold the data needed for the equation to be completed.
more manageable parts, described below, also helps to minimise errors and aid troubleshooting. Lack of documentation Lack of documentation is a very common failing. Many users prepare a simple worksheet which then develops into something much more complicated over time. Without documentation, the original purpose and methodology is often unclear and difficult to decipher. In this case it is usually easier to start again from the beginning, wasting the work done previously.
You can even set up a formula to calculate the difference between the two totals and report an error in case a non-zero result is returned (see Figure 124). Creating formulas You can enter formulas in two ways, either directly into the cell itself, or at the input line. Either way, you need to start a formula with one of the following symbols: =, + or –. Starting with anything else causes the formula to be treated as if it were text.
Simple Calculation in 1 Cell Calculation by Reference Figure 125: A simple calculation Table 7: Common ways to enter formulas Formula Description =A1+10 Displays the contents of cell A1 plus 10. =A1*16% Displays 16% of the contents of A1. =A1*A2 Displays the result of the multiplication of A1 and A2. =ROUND(A1;1) Displays the contents of cell A1 rounded to one decimal place. =EFFECTIVE(5%;12) Calculates the effective interest for 5% annual nominal interest with 12 payments a year.
Formula Description =B8-SUM(B10:B14) Calculates B8 minus the sum of the cells B10 to B14. =SUM(B8;SUM(B10:B14)) Calculates the sum of cells B10 to B14 and adds the value to B8. =SUM(B1:B65536) Sums all numbers in column B. =AVERAGE(BloodSugar) Displays the average of a named range defined under the name BloodSugar. =IF(C31>140; "HIGH"; "OK") Displays the results of a conditional analysis of data from two sources.
characteristic of the number found in the cell, for example -37. The example for Exponentiation illustrates how to enter a number that is being multiplied by itself a certain number of times, for example 2 3 = 2*2*2.
If cell A1 contains the numerical value 4 and cell B1 the numerical value 5, the above examples would yield results of FALSE, FALSE, TRUE, FALSE, TRUE, and TRUE. Text operators It is common for users to place text in spreadsheets. To provide for variability in what and how this type of data is displayed, text can be joined together in pieces coming from different places on the spreadsheet. Figure 126 shows an example.
Figure 127: Defining a name for a range of cells Figure 128: Naming a cell or range of cells for inclusion in a formula Chapter 7 Using Formulas and Functions 185
Figure 129: Defining Names on a worksheet Reference operators In its simplest form a reference refers to a single cell, but references can also refer to a rectangle or cuboid range or a reference in a list of references. To build such references you need reference operators. An individual cell is identified by the column identifier (letter) located along the top of the columns and a row identifier (number) found along the left-hand side of the spreadsheet.
Figure 130: Reference Operator for a range In the upper left corner of Figure 130 the reference A1:D12 is shown, corresponding to the cells included in the drag operation with the mouse to highlight the range. Examples A2:B4 Reference to a rectangle range with 6 cells, 2 column width × 3 row height. When you click on the reference in the formula in the input line, a border indicates the rectangle. (A2:B4):C9 Reference to a rectangle range with cell A2 top left and cell C9 bottom right.
Reference concatenation operator The concatenation operator is written as a tilde. An expression using the concatenation operator has the following syntax: reference left ~ reference right The result of such an expression is a reference list, which is an ordered list of references. Some functions can take a reference list as an argument, SUM, MAX or INDEX for example. The reference concatenation is sometimes called 'union'.
You can use the intersection operator to refer a cell in a cross tabulation in an understandable way. If you have columns labeled 'Temperature' and 'Precipitation' and the rows labeled 'January', 'February', 'March', and so on, then the following expression 'February' ! !Temperature' will reference to the cell containing the temperature in February. The intersection operator (!) should have a higher precedence than the concatenation operator (~), but do not rely on precedence.
Figure 131: Relative references You can think of a relative address as a pair of offsets to the current cell. Cell B1 is 1 column to the left of Cell C5 and 4 rows above. The address could be written as R[-1]C[-4]. In fact earlier spreadsheets allowed this notation method to be used in formulas. Whenever you copy this formula from cell B5 to another cell the result will always be the sum of the two numbers taken from the two cells one and two rows above the cell containing the formula.
Steps 2: Setting the exchange rate of Eur at 7.5, then copying it to E3 Copying formula from E2 to E3 & changing the formula to read absolute reference Applying the correct formula from E2 to E3 Figure 132: Absolute References The $ signs before the D and the 1 convert the reference to cell D1 from relative to absolute or fixed. If the formula is copied to another cell the second part will always show $D$1.
Hint To change references in formulas highlight the cell and press Shift-F4 to cycle through the four different types of references. This is of limited value in more complicated formulas, it is usually quicker to edit the formula by hand. Knowledge of the use of relative and absolute references is essential if you want to copy and paste formulas and to link spreadsheets. Order of calculation Order of calculation refers to the sequence in which numerical operations are performed.
identify where specific data may be found. A name such as Payroll or Boise Sales is much more meaningful than Sheet1. The function named SHEET() returns the sheet number in the collection of spreadsheets. There are several worksheets in each book and they are numbered from the left: Sheet1, Sheet2, and so forth. If you drag the worksheets around to different locations among the tabs, the function returns the number referring to the current position of this worksheet.
Sheet containing data for Branch 3. Sheet containing combined data for all branches. Figure 133: Combining data from several sheets into a single sheet The spreadsheets have been set up with identical structures. The easiest way to do this is to set up the first Branch spreadsheet, input data, format cells, and prepare the formulas for the various sums of rows and columns. 1) On the worksheet tab, right-click and select Rename Sheet.... Type Branch1.
Figure 134: Copying a worksheet 3) Enter the data for Branch 2 and Branch 3 into the respective sheets. Each sheet stands alone and reports the results for the individual branches. 4) In the Combined worksheet, click on cell K7. Type =, click on the tab Branch1, click on cell K7, press +, repeat for sheets Branch2 and Branch3 and press Enter. You now have a formula in cell K1 which adds the revenue from Greenery Sales for the 3 Branches.
Figure 136: Linking sheets: pasting a formula to a cell range 6) Click Yes. You have now copied the formulas into each cell while maintaining the format you set up in the original worksheet. Of course, in this example you would have to tidy the worksheet up by removing the zeros in the non-formatted rows. Figure 137: Linking Sheets: Copy Paste Special from K7...N17 Note OOo default is to paste all the attributes of the original cell(s) formats, notes, objects, text strings and numbers.
Understanding functions Calc includes over 350 functions to help you analyze and reference data. Many of these functions are for use with numbers, but many others are used with dates and times, or even text. A function may be as simple as adding two numbers together, or finding the average of a list of numbers. Alternatively, it may be as complex as calculating the standard deviation of a sample, or a hyperbolic tangent of a number.
Understanding the structure of functions All functions have a similar structure. If you use the right tool for entering a function, you can escape learning this structure, but it is still worth knowing for troubleshooting. To give a typical example, the structure of a function to find cells that match entered search criteria is: = DCOUNT (Database;Database field;Search_criteria) Since a function cannot exist on its own, it must always be part of a formula.
To get an idea of what nested functions can do, imagine that you are designing a self-directed learning module. During the module, students do three quizzes, and enter the results in cells A1, A2, and A3. In A4, you can create a nested formula that begins by averaging the results of the quizzes with the formula =AVERAGE(A1:A3). The formula then uses the IF function to give the student feedback that depends upon the average grade on the quizzes.
Figure 138: Function List docked to right side of Calc window Function Wizard The most commonly used input method is the Function Wizard (Figure 139). To open the Function Wizard, choose Insert > Function, or click the fx button on the Function tool bar, or press Ctrl+F2. Once open, the Function Wizard provides the same help features as the Function List, but adds fields in which you can see the result of a completed function, as well as the result of any larger formula of which it is part.
Figure 140: Functions page of Function Wizard. To select cells, either click directly upon the cell or hold down the left mouse button and drag to select the required area. When the area has been selected, click the Shrink button again to return to the wizard. If multiple arguments are needed select the next text box below the first and repeat the selection process for the next cell or range of cells. Repeat this process as often as required.
Figure 141: Structure page of Function Wizard After you enter a function on the Input line, press the Enter key or click the Accept button on the Function toolbar to add the function to the cell and get its result. 1 2 3 Name Box showing list of common functions Function Wizard 4 Accept Cancel 5 Input Line Figure 142: The Function toolbar If you see the formula in the cell instead of the result, then Formulas are selected for display in Tools > Options > OpenOffice.org Calc > View > Display.
Strategies for creating formulas and functions Formulas that do more than a simple calculation or summation of rows or columns of values usually take a number of arguments. For example, the classic equation of motion s = s0+ vt - ½at2 calculates the position of a body knowing its original position, its final velocity, its acceleration, and the time taken to move from the initial state to the final state.
months time. Use comments and notes liberally to document your work. You might be aware that you cannot use negative values or zero values for a particular argument, but if someone else inputs such a value will your formula be robust or simply return a standard (and often not too helpful) Err: message? It is a good idea to trap errors using some form of logic statements or with conditional formatting.
Finding and fixing errors It is common to find situations where errors are displayed. Even with all the tools available in Calc to help you to enter formulas, making mistakes is easy. Many people find inputting numbers difficult and many may make a mistake about the kind of entry that a function's argument needs. In addition to correcting errors, you may want to find the cells used in a formula to change their values or to check the answer.
502 The column, row, or sheet for the referenced cell is missing. Examples of common errors Err:503 Division by zero This error is the result of dividing a number by either the number zero (0) or a blank cell. There is an easy way to avoid this type of problem. When you have a zero or blank cell displayed, use a conditional function. Figure 144 depicts division of column B by column C yielding 2 errors arising from a zero and a blank cell showing in column C.
Figure 145: Division by zero solution #VALUE Non-existent value and #REF! Incorrect references The non-existent value error is also very common. The most common appearance of this error arises when a user copies a formula over a selected area. When copying, it is typical for the program to increment the represented cells. If you were copying downward from cell B3 the program would automatically substitute the cell B4 into the next lower cell and so on until the end of the copying process.
The Detective In a long or complicated spreadsheet, color coding becomes less useful. In these cases, consider using the the submenu under Tools > Detective. The Detective is a tool for checking which cells are used as arguments by a formula (precedents) and which other formulas it is nested in (dependents), and tracking errors.
a) Initiate trace by clicking Trace Precedents b) Source area highlighted in Blue, with arrow pointing to the calculation cell (continued): Tracing precedents using the Detective Chapter 7 Using Formulas and Functions 209
We are concerned that the number shown in Cell C3 is incorrectly stated. The cause can be seen in the highlighted cells. In this case cell C16 contains both numeric data as well as letters. Removing the letters resolves the problem in the calculation. In other cases we must trace the error. Use the Trace Error function, found under Tools > Detective > Trace Error, to find the cells that cause the error. Examples of functions For novices, functions are one of the most intimidating features of OpenOffice.
A similar choice is also available if you want to raise a number by the power of another. Instead of entering =POWER(A1;2), you can enter =A1^2. Moreover, they have the advantage that you enter formulas with them in an order that more closely approximates human readable format than the spreadsheet-readable format used by the equivalent function. For instance, instead of entering =SUM (A1:A2), or possibly =SUM (A1;A2), you enter =A1+A2.
For more flexibility in similar operations, you could use LARGE or SMALL, both of which add a specialized argument of rank. If the rank is 1 used with LARGE, you get the same result as you would with MAX. However, if the rank is 2, then the result is the second largest result. Similarly, a rank of 2 used with SMALL gives you the second smallest number. Both LARGE and SMALL are handy as a permanent control, since, by changing the rank argument, you can quickly scan multiple results.
Using these functions In some cases, you may be able to get similar results to some of these functions by setting up a filter or a custom sort. However, in general, functions are more easily adjusted than filters or sorts, and provide a wide range of possibilities. At times, you may just want to enter one or more formulas temporarily in a convenient blank cell, and delete it once you have finished.
symmetric arithmetic rounding: a decimal place of .4 or less gets rounded down, while one of .5 or more gets rounded up. However, at times, you may not want to follow these rules. For instance, if you are one of those contractors who bills a full hour for any fraction of an hour you work, you would want to always round up so you didn’t lose any money. Conversely, you might choose to round down to give a slight discount to a long-established customer.
mode is set to 0, and both the number and the significance are negative numbers, then the result of either function is rounded up; if it is set to 1, and both the number and the significance are negative numbers, then the results are rounded down. In other words, if the number is -11 and the significance is -5, then the result is -10 when the mode is set to 0, but -15 when set to 1.
Figure 147: Enabling regular expressions in formulas For example =COUNTIF(A1:A6;"r.d") with Enable regular expressions in formulas selected will count cells in A1:A6 which contain red and ROD. Additionally if Search criteria = and <> must apply to whole cells is not selected, then Fred, bride, and Ridge will also be counted. If that setting is selected, then it can be overcome by wrapping the expression thus: =COUNTIF(A1:A6;".*r.d.*"). Figure 148: Using the COUNTIF function.
Regular expressions will not work in simple comparisons. For example: A1="r.d" will always return FALSE if A1 contains red, even if regular expressions are enabled. It will only return TRUE if A1 contains r.d (r then a dot then d). If you wish to test using regular expressions, try the COUNTIF function: COUNTIF(A1; "r.d") will return 1 or 0, interpreted as TRUE or FALSE in formulas like =IF(COUNTIF(A1; "r.d");"hooray"; "boo").
8 Chapter Using the DataPilot This chapter is adapted from a German original written by Stefan Weigel and translated into English by Sigrid Kronenberger. It is licensed under the Creative Commons Attribution-Share Alike License, version 3.0.
Introduction Many requests for software support are caused by complicated formulas and solutions to simple day to day procedures. For more efficient and effective solutions use the DataPilot, a tool for combining, comparing, and analyzing large amounts of data easily. By using the DataPilot, you can view different summaries of the source data, display the details of areas of interest, and create reports, whether you are a beginner or an intermediate or advanced user.
the connection between cells through formulas, for example addition with the plus operator or the SUM function. This small exercise might be useful for a first contact with the program, but it shows only a very small fraction of the tasks in an office. To create such a sales overview, you also need the original data. That is, before you can use a spreadsheet for creating the sales overview, you need to add many single purchases from different lists and then enter the sums into the relevant cells C5 to F7.
Figure 150: Basic data in Calc You can create the sales volume overview by following these instructions: 1) Select the cell A1 (or any other single cell within the list). 2) Select Data > DataPilot > Start. On the Select Source dialog, select Current selection and click OK. 3) The DataPilot dialog (Figure 151) has four white layout areas and several fields that look like buttons. These small fields are the titles of the different columns of your list. • Move the date field into the Column Fields area.
6) Click OK. Figure 151: DataPilot dialog 7) The result appears on a new sheet. It has the desired structure, but the columns are not yet grouped into months. Figure 152: DataPilot result without grouping 8) To group the columns, select cell B4 or any other cell that contains a date. Then select Data > Group and Outline > Group. On the Grouping dialog (Figure 153), make sure Intervals and Months are selected in the Group by section, and click OK. The result is now grouped for months (Figure 154).
Figure 153: Grouping on months Figure 154: DataPilot result grouped for months In this result you will recognize the beginners’ example. It is very easy to produce, without any further knowledge about the spreadsheet. You do not have to enter any formulas. Advantages 1) No manual entering or adding of any values is necessary. There is less work and fewer errors. 2) The layout is very flexible: months are listed horizontally and fields vertically or vice versa, in two mouse clicks.
We will now demonstrate some of these advantages. Starting with the result of Figure 154, drag the Date field under the Category field, as shown in Figure 155. Figure 155: Drag Date field under Category field Now the summary is as shown in Figure 156. Figure 156: Changed layout 224 OpenOffice.org 3.
To transpose the table completely, just drag the Category field above the area of the displayed values, to cell C3 (see Figure 157). The result of this action is shown in Figure 158. Figure 157: Drag Category field to right of Data field Figure 158: Transposed layout of Figure 156 In contrast to the beginners’ example in Figure 149, it is now very simple to view or add different aspects of the underlying data.
Figure 159: Additional subdivision into regions, added later In another variation you may want to add the employees. 1) Select the cell A3 (or any other single cell that is part of the DataPilot result). 2) Select Data > DataPilot > Start, to start the DataPilot again. • You do not need the Region field in this case. Drag it out of the layout area. • Drag the Employee field into the Page Fields area. 3) Click OK. The result is shown in Figure 160.
Figure 160: Selection of subtotals for several employees. Example 2: Timekeeping This example is often used by consultants and in several variations in user support. The task is to provide a means for one or more users to keep track of working hours. A typical way of doing this is to create a spreadsheet per month and a sum sheet with all the results of one year. For each employee, there is one file (see Figures 161 and 162 for examples of two pages from the file for one employee).
Figure 161: One month of timekeeping for one employee Figure 162: Yearly sums for one employee 228 OpenOffice.org 3.
Solution To use the DataPilot for this task, collect all the data into one single table. This can be done either manually or by importing a file from an (electronic) timekeeping machine. In very simple cases, each employee takes care of their own working hours. If you need calculations that cover several employees, departments, or the whole company, just copy everything into one huge table (Figure 163).
Figure 164: Part of DataPilot dialog The result appears on a new sheet. Figure 165: The evaluation, done within seconds with DataPilot 230 OpenOffice.org 3.
The result is much more powerful than is possible with the classic formula-based calculation. For example, you can summarize the daily results to a monthly result very easily: 1) To group together the rows, select the cell A5 (or any other cell that contains a date). 2) Choose Data > Group and Outline > Group and click OK. The result is now grouped into months. Figure 166: Monthly sums If you need a result with a percentage, start the DataPilot from this page.
Figure 167: Properties of the data field Figure 168: Result with percentages To get a comparison between employees, start the DataPilot again: 1) Select the cell A3 (or any other cell that contains a result of the DataPilot). 2) Choose Data > DataPilot > Start. 3) Double-click on Sum – hours to open the Data Field dialog. 4) Click More, to see more options. • Switch the Type of the displayed value to Difference from. • Switch the Base Field to name. • Switch the Base Item to Brigitte. 5) Click OK twice.
Figure 169: Absolute comparison with Brigitte As a final example we switch to an accumulated view; that is, continuing sums of all values: 1) Choose Data > DataPilot > Start. 2) Double-click on Sums – hours to open the Data Field dialog. 3) Click More, to see more options. • Switch the type of the displayed value to Running total in. • Switch the Base Field to Date. 4) Click OK twice.
Example 3: Frequency distribution For showing the frequency of incidents, Calc uses the function FREQUENCY. This formula has to be used in a so-called matrix formula. This is an advanced feature. Alternatively you can use the DataPilot, which requires almost no knowledge of spreadsheets. In our example we want to investigate the number of emails that go to the Germanophone support mailing list (users@de.openoffice.org). We want to know how the activity on the list is distributed during the day.
To import the data into a table in Calc, open the text file users.txt containing the raw data. The Text Import dialog appears. Here you can choose your import options. Use the following properties: Separator options: Fixed width Divide into columns at position 20, 31 and 40 Column types: Column Column Column Column 1: 2: 3: 4: Hide Date (DMY) Standard Hide Figure 172: Import settings Figure 173 shows the imported raw data with a line added at the top containing titles for each column.
Figure 173: Raw data in Calc Solution with a matrix formula To calculate the frequency you have to create 24 classes, one for each hour. In the next column you enter the number of emails with the function FREQUENCY. Figure 174: FREQUENCY function in a matrix formula 236 OpenOffice.org 3.
The first argument is the cell area with the times of all approximately 38,000 emails. The second argument is the cell area E2:E25 that describes the frequency classes. To enter the formula, first select the cell area F2:F25, then enter the formula. Then finish the formula by using the key combination Shift+Ctrl+Enter. This indicates to the program that you want to use a matrix formula. To indicate the matrix formula, the program uses curly brackets.
3) In the DataPilot: • Drag Time into the Row Fields area. • Drag Date into the Data Fields area. 4) Click More to show more options in the lower part of the dialog. 5) Choose – new sheet – for Results to. 6) In this case we need to count the number of values, not their sum. Double-click on Sum – Date to open the Data Field dialog and select the function Count (see Figure 176). 7) Click OK. As an intermediate result, you get a DataPilot table that has for every time within the raw data a separate line.
Figure 177: properties for grouping according to hours 10) Figure 178 shows the Data Field dialog for the data field Number – Date. Click More and select as type % of column. The result is shown in Figure 180. Figure 179 shows the absolute occurrence. Whether the relative values are shown as a decimal (0.1) or as a percentage (10%) depends only on the cell formatting itself and has nothing to do with the DataPilot.
Figure 179: Frequency distribution with the DataPilot 240 Figure 180: Relative occurrence OpenOffice.org 3.
DataPilot functions in detail This part describes the use and options of the DataPilot in detail. The database (preconditions) The first thing needed to work with the DataPilot is a list of raw data, similar to a database table, consisting of rows (data sets) and columns (data fields). The field names are in the first row above the list. The data source could be an external file or database.
Rule Calc lists must have the normal form; that is, they must have a simple linear structure. When entering the data, do not add outlines, groups, or summaries. This becomes clear when we think about what we could have done wrong in our Sales list example in the section “Examples with step by step instructions” starting at page 219. Here are some mistakes commonly made by inexperienced spreadsheet users: 1) You could have made several sheets, for example, a sheet for each group of articles.
Figure 181: After the start of the DataPilot Data source There are usually three possibilities for the DataPilot to have a data source: a Calc spreadsheet, an external data source that has to be registered in OpenOffice.org, and access to an OLAP system (not available at this time). Calc spreadsheet The simplest and most often used case is analyzing a list in a Calc spreadsheet. The list might be updated regularly or the data might be imported from a different application.
Calc can import data from a huge number of foreign data formats, for example from other spreadsheets (Excel, Lotus 1, 2, 3), from databases (like dBase), and from simple text files. The drawback of copying or importing foreign data is that it will not update automatically if there are changes in the source file. With a Calc file you are limited to 65535 rows. Registered data source A registered data source in OpenOffice.org is a connection to data held in a database outside Calc.
Figure 182: DataPilot dialog Row Fields and Column Fields indicate in which groups the result will be sorted in the rows and columns. If there are no entries in one of these area, then partial sums will not be provided for the corresponding rows or columns. Often more than one field is used at the same time get partial sums for rows or columns. The order of the fields gives the order of the sums from overall to specific.
Figure 183: Expanded dialog of the DataPilot Results to Results to defines where your result will be shown. If you do not enter anything, the DataPilot will put your result below the list that contains your data. This could result in overwriting any data that is already in that location. To avoid overwriting data, you can leave Results to as – undefined – and enter a cell reference to tell the DataPilot where to show the results.
Figure 185: DataPilot result with Identify categories selected Without category recognition, the DataPilot shows an (empty) category (Figure 186). Figure 186: DataPilot result without Identify categories selected Logically, the behavior without category recognition is better. A list with the shown missing entries is also less useful, because you cannot use other functions such as sorting or filtering.
More settings for the fields The options discussed in the previous section are valid for the DataPilot in general. Additionally, you can change settings for every field that you have added to the DataPilot layout. Do this either by clicking on the Options button in the DataPilot dialog or by doubleclicking on the appropriate field. There are differences between data fields, row or column fields, and page fields of the DataPilot.
Type Base field Base element Analysis Normal — — Simple use of the chosen aggregate function (for example, sum) Difference from Selection of a field from the data source of the DataPilot (for example, employee) Selection of an element from the selected base field (for example, Brigitte) Result as difference to the result of the base element (for example, Sales volume of the employees as difference of the sales volume of Brigitte) % of Selection of a field from the data source of the DataPilot (
Type Base field Base element Analysis % of column — — Result as relative part of the total column (for example, the column sum) % of total — — Result as relative part of the overall result (for example the total sum) Index — — Default result x total result / (row result x column result) Row and column fields In the preferences dialog for the row or column fields, you can display the partial sums for each category. Partial sums are deactivated by default.
Figure 189: Division of the regions for employees without partial sums Figure 190: Division of the regions for employees with partial sums Select the option Automatically to use the aggregate function for the partial results that can also be used for the data fields (see above).
set up the aggregate function for the partial results independently from the overall settings of the DataPilot, select User-defined. Figure 191: Preferences dialog of a row or column field Normally, the DataPilot does not show a row or column for categories that have no entries in the underlying database. By choosing the Show items with no data option, you can force this.
Page fields The preferences dialog for page fields is the same as for row and column fields, even though it appears to be useless to do the same settings as described for the row and column fields. With the flexible use of the DataPilot you can often switch the use of the different fields for pages, columns or rows. The fields keep the settings that you made for them. For this, the page field has the same properties as a row or column field.
Note Before you can group, you have to produce a DataPilot with ungrouped data. The time needed for creating a DataPilot depends mostly on the number of columns and rows and not on the size of the basic data. Through grouping you can produce the DataPilot with a small number of rows and columns. The DataPilot can contain a lot of categories, depending on your data source. Computing time may take several minutes.
You can define in which value range (start/end) the grouping should take place. The default setting is the whole range from smallest to biggest value. In the Grouping after field you can enter the class size, this means the interval size (in this example groups of 10 km/h each). Grouping of categories with date or time values For grouping date or time values select a single cell in the column or row of the category that should be grouped.
Tip For grouping the DataPilot in calendar weeks, define the beginning as a Sunday or Monday and manually enter the grouping interval of 7 days. Grouping without the automatic creation of intervals If the categories contain text fields, then the automatic creation of intervals isn't possible. You can define for each category of any data type which categories you want to put together in one group.
Figure 200: Summary of single categories in one group Figure 201: Grouping finished You can change the automatically given names for the groups and the newly created group field by right-clicking on a name. The DataPilot will remember these settings, even if you change the layout later on. For the following pictures, the dialog was called again (with a rightclick) and within the preferences menu the option Automatic was selected.
Sort manually by using drag and drop You can change the order within the categories by moving the cells with the category values in the result table of the DataPilot. Please be aware that in Calc a cell must be selected. It is not enough that this cell contains the cell cursor. The background of a selected cell is marked with a different color. To achieve this, click in one cell with no extra key pressed and redo this by pressing also the Shift or Ctrl key.
For Sort by select how to display the information, either Ascending or Descending. On the left side is a drop-down list where you can select the field this setting should apply to. With this method you can specify that sorting does not happen according to the categories but according to the results of the data field. Drilling (showing details) Drill allows you to show the related detailed data for a single, compressed value in the DataPilot result.
Figure 207: After the drill down To hide the details again, double-click on the cell containing golfing or choose Data > Group and Outline > Hide Details. The DataPilot remembers your selection (in our example the employee field), so that for the next drill down for a category in the region field, the dialog does not appear. To remove the employee selection, open the DataPilot dialog by right-clicking and choosing Start, then delete the unwanted selection in the row or column field.
Note An Autofilter or default filter used on the sheet has no effect for the analysis of the DataPilot. The DataPilot always uses the complete list that was selected when it was started. To do this, click Filter on the top left side above the results. Figure 209: Filter field in the upper left area of the DataPilot In the Filter dialog, you can define of up to 3 filter options that are used in the same way as Calc’s default filter.
2) You have added or deleted data sets in the original list. In this case the change means that the DataPilot has to use a different area of the spreadsheet for its analysis. A change of the data reference for an existing DataPilot is not possible. You must redo the DataPilot. Cell formatting The cells in the results area of the DataPilot are automatically formatted in a simple format by Calc.
Figure 211: Multiple data fields in the DataPilot Figure 212: DataPilot shows sales value and number of entries For manipulating the existing data fields, DataPilot result contains a field called Data. You can move it just like row or column fields by using drag and drop. This is an easy way to archive a different structure for the results (see Figures 213 and 214).
Figure 213: Layout option for presenting the sums and numbers of the sales values Figure 214: Another layout option for presenting the sums and numbers of the sales values If you want to put the different data fields in different columns and your DataPilot does not contain another column field or you sort the different data fields in different rows and don’t have another row field, then it is useful to disable the view of the row or column sums Figure 215: Unnecessary columns 264 OpenOffice.org 3.
Figure 216: Disabled column sums A frequent use case for multiple data fields is the aggregation of one value according to different aggregate functions at the same time. You can create a DataPilot that shows you the monthly sales values and shows you additionally the smallest and the largest amounts. Figure 217: Multiple analyses for the same data field Unfortunately, Calc does not allow you to use the same data field multiple times.
Figure 218: Multiple identical fields for sales value, that have been created as duplicates within your source. Shortcuts If you use the DataPilot very often, you might find the frequent use of the menu paths (Data > DataPilot > Start and Data > Group and Outline > Group) inconvenient. For grouping, a shortcut is already defined: F12. For starting the DataPilot, you can define your own keyboard shortcut.
Function GETPIVOTDATA The function GETPIVOTDATA can be used with formulas in Calc if you want to reuse the results from the DataPilot elsewhere in your spreadsheet. Difficulty Normally you create a reference to a value by entering the address of the cell that contains the value. For example, the formula =C6*2 creates a reference to cell C6 and returns the doubled value.
Figure 220: The value that you really want to use can be found now in a different location. The function GETPIVOTDATA allows you to have a reference to a value inside the DataPilot by using the specific identifying categories for this value. Syntax The syntax has two variations: GETPIVOTDATA(target field, DataPilot; [ Field name / Element; ... ]) GETPIVOTDATA(DataPilot; specification) First syntax variation The target field to specify which data field of the DataPilot is used within the function.
If you enter only the first two arguments, then the function returns the total result of the DataPilot. You can add more arguments as pairs with field name and element to retrieve specific partial sums.
Figure 222: Second syntax variation 270 OpenOffice.org 3.
9 Chapter Data Analysis Using Scenarios, Goal Seek, Solver, others
Introduction Once you are familiar with functions and formulas, the next step is to learn how to use Calc's automated processes to quickly perform useful analysis of your data. Calc includes several tools to help you manipulate the information in your spreadsheets, ranging from features for copying and reusing data, to creating subtotals automatically, to varying information to help you find the answers you need. These tools are divided between the Tools and Data menus.
1) Open the document containing the cell ranges to be consolidated. 2) Choose Data > Consolidate to open the Consolidate dialog. Figure 223 shows this dialog after making the changes described below. Figure 223: Defining the data to be consolidated 3) The Source data range list contains any existing named ranges (created using Data > Define Range) so you can quickly select one to consolidate with other areas.
cell of the target range. Copy results to takes only the first cell of the target range instead of the entire range as is the case for Source data range. 7) Select a function from the Function list. This specifies how the values of the consolidation ranges will be calculated. The default setting is Sum, which adds the corresponding cell values of the Source data range and gives the result in the target range.
Creating subtotals SUBTOTAL is a function listed under the Mathematical category when you use the Function Wizard (Insert > Function). Because of its usefulness, the function has a graphical interface accessible from Data > Subtotals. As the name suggests, SUBTOTAL totals data arranged in an array— that is, a group of cells with labels for columns. Using the Subtotals dialog, you can select up to three arrays, then choose a statistical function to apply to them.
4) In the Calculate subtotals for box, select the columns containing the values that you want to create subtotals for. If the contents of the selected columns change later, the subtotals are automatically recalculated. 5) In the Use function box, select the function that you want to use to calculate the subtotals. 6) Click OK.
Using “what if” scenarios Scenarios are a tool to test “what-if” questions. Each scenario is named, and can be edited and formatted separately. When you print the spreadsheet, only the contents of the currently active scenario are printed. A scenario is essentially a saved set of cell values for your calculations. You can easily switch between these sets using the Navigator or a drop-down list which can be shown beside the changing cells.
Figure 226: Creating a scenario Settings The lower portion of the Create Scenario dialog contains several options. The default settings (as shown in Figure 226) are likely to be suitable in most situations. Display border Places a border around the range of cells that your scenario alters. To choose the color of the border, use the field to the right of this option. The border has a title bar displaying the name of the active scenario.
If you are viewing a scenario which has Copy back enabled and then create a new scenario by changing the values and selecting Caution Tools > Scenarios, you also inadvertently overwrite the values in the first scenario. This is easily avoided if you leave the current values alone, create a new scenario with Copy back enabled, and then change the values only when you are viewing the new scenario. Copy entire sheet Adds to your document a sheet that permanently displays the new scenario in full.
Changing scenario cell values Table 11 summarizes the interaction of various settings in preventing or allowing changes in scenario cell values. Table 11: Prevent changes behavior for scenario cell value changes Settings Change allowed Sheet protection ON Scenario cell protection OFF Prevent changes ON Copy back ON Scenario cell values cannot be changed. Sheet protection ON Scenario cell protection OFF Prevent changes OFF Copy back ON Scenario cell values can be changed, and the scenario is updated.
To delete a scenario, right-click the name in the Navigator and choose Delete. To edit a scenario, including its name and comments, right-click the name in the Navigator and choose Properties. The Edit Properties dialog is the same as the Create Scenario dialog (Figure 226). Figure 227: Scenarios in the Navigator Tracking values in scenarios To learn which values in the scenario affect other values, choose Tools > Detective > Trace Dependents.
formula. Although this tool is not listed among the functions, it is really a function that acts on other functions, allowing you to calculate different results without having to enter and run them separately. To use the Multiple Operations tool, you need two arrays of cells. The first array contains the original or default values and the formulas applied to them. The formulas must be in a range. The second array is the formula array.
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 containing a list of alternatives for one of the values used in the formula. The Multiple Operations command produces a list of results adjacent to your alternative values by running the formula against each of these alternatives.
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 to be replaced by the column of alternative values. Figure 228 shows the worksheet and the Multiple operations dialog. 7) Click OK. The profits for the different quantities are now shown in column E. See Figure 229.
Figure 229: Sheet showing results of multiple operations calculations 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 and C5.
Figure 230: Sheet and dialog showing input 7) Click OK. Now the profits are listed in column E and the annual profit per item in column F. Figure 231: Results of multiple operations calculations 286 OpenOffice.org 3.
Multiple operations across rows and columns You can carry out multiple operations simultaneously for both columns and rows in so-called cross-tables. The formula must use at least two variables, the alternative values for which should be arranged so that one set is along a single row and the other set appears in a single column. These two sets of alternative values will form column and row headings for the results table produced by the Multiple Operations procedure.
Figure 232: Sheet and dialog showing input 6) Click OK. The profits for the different selling prices are now shown in the range E2:H11. Figure 233: Results of multiple operations calculations Working backwards using Goal Seek Usually, you create a formula to calculate a result based upon existing values. By contrast, using Tools > Goal Seek you can discover what values will produce the result that you want.
that are already signed. For the fourth quarter, however, no definite income is available. So how much must the company earn in Q4 to reach its goal? The CFO can enter the projected earnings for each of the other three quarters along with a formula that totals all four quarters. Then she runs a goal seek on the empty cell for Q4 sales, and receives her answer. Other uses of goal seek may be more complicated, but the method remains the same. Only one argument can be altered in a single goal seek.
Figure 234: Example setup for goal seek 5) Click OK. A dialog appears informing you that the Goal Seek was successful. Click Yes to enter the goal value into the variable cell. The result is shown below. Figure 235: Result of goal seek operation Using the Solver Tools > Solver amounts to a more elaborate form of Goal Seek. The difference is that the Solver deals with equations with multiple unknown variables.
also define the constraint that one or more variables must be integers (values without decimals), or binary values (where only 0 and 1 are allowed). Once you have finished setting up the rules, click the Solve button to begin the automatic process of adjusting values and calculating results. Depending on the complexity of the task, this may take some time. Solver example Let’s say you have $10,000 that you want to invest in two mutual funds for one year.
4) Choose Tools > Solver. The Solver dialog opens. Figure 237: The Solver dialog 5) Click in the Target cell field. In the sheet, click in the cell that contains the target value. In this example it is cell B4 containing total interest value. 6) Select Value of and enter 1000 in the field next to it. In this example, the target cell value is 1000 because your target is a total interest earned of $1000. Select Maximum or Minimum if the target cell value needs to be one of those extremes.
Figure 238: Result of Solver operation Note The default solver supports only linear equations. For nonlinear programming requirements, try the EuroOffice Solver or Sun’s Solver for Nonlinear Programming [Beta]. Both are available from the OpenOffice.org extensions repository. (For more about extensions, see Chapter 14 (Setting up and Customizing Calc.
10 Chapter Linking Calc Data Sharing data in and out of Calc
Why use multiple sheets? Chapter 1 introduced the concept of multiple sheets in a spreadsheet. Multiple sheets help keep information organized; once you link those sheets together, you unleash the full power of Calc. Consider this case. John is having trouble keeping track of his personal finances. He has several bank accounts and the information is scattered and disorganized. He can’t get a good grasp on his finances until he can see everything at once.
Inserting new sheets There are several ways to insert a new sheet. The first step, in all cases, is to select the sheet that will be next to the new sheet. Then do any of the following: • Select Insert > Sheet from the menu bar, or • Right-click on the tab and select Insert Sheet, or • Click in an empty space at the end of the line of sheet tabs. Click here to create a new sheet Figure 240: Creating a new sheet Each method opens the Insert Sheet dialog.
We need 6 sheets, one for each of the 5 accounts and one as a summary sheet so we will add 3 more. We also want to name each of these sheets for the account they represent: Summary, Checking Account, Savings Account, Credit Card 1, Credit Card 2, and Car Loan. We have two choices: insert 3 new sheets and rename all 6 sheets afterwards; or rename the existing sheets, then insert the 3 new sheets one at a time, renaming each new sheet during the insert step.
Tip For a shortcut to inserting a sheet from another file, choose Insert > Sheet from file from the menu bar. The Insert Sheet dialog opens with the From file option preselected, and then the Insert dialog opens on top of it. If you prefer, select the Link option to insert the external sheet as a link instead as a copy. This is one of several ways to include “live” data from another spreadsheet. (See also “Linking to external data” on page 307.
Figure 243: Checking ledger Referencing other sheets On the Summary sheet we display the balance from each of the other sheets. If you copy the example in Figure 243 onto each account, the current balances will be in cell F3 of each sheet. There are two ways to reference cells in other sheets: by entering the formula directly using the keyboard or by using the mouse. We will look at the mouse method first.
Figure 245: Equal sign in input line 2) Now, click on the sheet tab for the sheet containing the cell to be referenced. In this case, that is the Checking Account sheet as shown in Figure 246. Figure 246: Click on the checking account tab 3) Click on cell F3 (where the balance is) in the Checking Account sheet. The phrase ‘Checking Account’.F3 should appear in the input line as in Figure 247. Figure 247: Cell reference selected 4) Click the green checkmark in the input line to finish.
Creating the reference with the keyboard From Figure 248, you can deduce how the cell reference is constructed. The reference has two parts: the sheet name (‘Checking Account’) and the cell reference (F3). Notice that they are separated by a period. Note The sheet name is in single quotes because it contains a space, and the mandatory period (.) always falls outside any quotes. So, you can fill in the Savings Account cell reference by just typing it in.
2) Switch to the other spreadsheet (the process to do this will vary depending on which operating system you are using). 3) Select the sheet (Savings Account) and then the reference cell (F3). Figure 250: Selecting the savings account reference cell 4) Switch back to the original spreadsheet. 5) Click on the green check mark on the input line. Your spreadsheet should now resemble Figure 251.
Note The reference for a file has three forward slashes /// and the reference for a hyperlink has two forward slashes //. Hyperlinks and URLs Hyperlinks can be used in Calc to jump to a different location from within a spreadsheet and can lead to other parts of the current file, to different files or even to web sites. Relative and absolute hyperlinks Hyperlinks can be stored within your file as either relative or absolute.
Creating hyperlinks When you type text that can be used as a hyperlink (such as a website address or URL), Calc formats it automatically, creating the hyperlink and applying to the text a color and background shading. If this does not happen, you can enable this feature using Tools > AutoCorrect Options > Options and selecting URL Recognition. Tips To change the color of hyperlinks, go to Tools > Options > OpenOffice.
The top right part of the dialog changes according to the choice made for the hyperlink category from the left panel. A full description of all the choices, and their interactions, is beyond the scope of this chapter. Here is a summary of the most common choices used in spreadsheets. For an Internet hyperlink, choose the type of hyperlink (Web, FTP, or Telnet), and enter the required web address (URL).
• Name is applicable to HTML documents. It specifies text that will be added as a NAME attribute in the HTML code behind the hyperlink. • Events button: this button will be activated to allow Calc to react to events for which the user has written some code (macro). This function is not covered in this chapter. Note A hyperlink button is a type of form control. As with all form controls, it can be anchored or positioned by right-clicking on the button in design mode.
Linking to external data You can insert tables from HTML documents, and data located within named ranges from an OpenOffice.org Calc or Microsoft Excel spreadsheet, into a Calc spreadsheet. (To use other data sources, including database files in OOo Base, see “Linking to registered data sources” on page 312.) You can do this in two ways: using the External Data dialog or using the Navigator.
Figure 254: Selecting a table or range in a source document from the Web Using the Navigator 1) Open the OpenOffice.org Calc spreadsheet in which the external data is to be inserted (target document). 2) Open the document from which the external data is to be taken (source document). If the source document is a Web page, choose Web Page Query (OpenOffice.org Calc) as the file type. Figure 255: Opening a file using the Web Page Query filter 308 OpenOffice.org 3.
3) In the target document, press F5 to open the Navigator. 4) At the bottom of the Navigator, select the source document. (In Figure 256, the source is named actives.) The Navigator now shows the range names or the tables contained in the source document (the example contains range names; other documents have a list of tables). Click on the + next to Range names to display the list.
Figure 257: Linked areas in target spreadsheet How to find the required data range or table The examples above show that the import filter gave names to the data rangers (tables) in the sample web page starting from HTML_1.
Figure 258: Using the Navigator to find a data range name Figure 259: Using the Name box to find a data range name Chapter 10 Linking Calc Data 311
Linking to registered data sources You can access a variety of databases and other data sources and link them into Calc documents. First you need to register the data source with OpenOffice.org. (To register means to tell OOo what type of data source it is and where the file is located.) The way to do this depends on whether or not the data source is a database in *.odb format. To register a data source that is in *.odb format: 1) Choose Tools > Options > OpenOffice.org Base > Databases.
Microsoft Access and other Microsoft products are not among the choices if you are using Linux. In our example, we chose dBASE. Figure 261: Registering a database using the Database Wizard 3) Click Next. Type the path to the database file or click Browse and use the Open dialog to navigate to and select the database file before clicking Open. 4) Click Next. Select Yes, register the database for me, but clear the checkbox marked Open the database for editing. 5) Click Finish.
Viewing data sources Open a document in Calc. To view the data sources available, press F4 or select View > Data Sources from the menu bar. The Data Source View pane opens above the spreadsheet. A list of registered databases is in the Data Explorer area on the left. (The built-in Bibliography database is included in the list.) To view each database, click on the + to the left of the name of the database. (This has been done for the Automobile database in Figure 262.
Launching Base to work on data sources You can launch OOo Base at any time from the Data Source View pane. Right-click on a database or the Tables or Queries icons and select Edit Database File. Once in Base, you can edit, add, and delete tables, queries, forms, and reports. For more about using Base, see Chapter 8 (Getting Started with Base) in the Getting Started guide.
2) Drag and drop the gray box to where you want the record to appear in the spreadsheet. 3) Repeat until you have moved all of the fields you need to where you want them. 4) Close the Data Source window: press F4. 5) Save the spreadsheet and click the Edit File button on the Standard toolbar, to make the spreadsheet read-only. All of the fields will show the value for the data of the first record you selected. 6) Add the Form Navigation toolbar: View > Toolbars > Form Navigation.
it. You can also insert a link to the object that will appear as an icon rather than an area showing the contents itself. OLE objects can be linked to a target document or be embedded in the target document. Linking inserts information which will be updated with any subsequent changes to the original file, while embedding inserts a static copy of the data. If you want to edit the embedded spreadsheet, double-click on the object.
To insert an existing object: 1) To create from a file, select Create from file. The dialog changes to look like Figure 265. 2) To insert the object as a link, select the Link to file option. Otherwise, the object will be embedded. 3) Click Search, select the required file in the Open dialog, then click Open. A section of the inserted file is shown in the document.
2) Select Create New to insert a new object of the type selected in the Object Type list, or select Create from File to create a new object from a file. 3) If you choose Create from File, the dialog shown below opens. Click Browse and choose the file to insert. The inserted file object is editable by the Windows program that created it. If instead of inserting an object, you want to insert a link to an object, select the Display As Icon option.
Dynamic Data Exchange (DDE) DDE is an acronym for Dynamic Data Exchange, a mechanism whereby selected data in document A can be pasted into document B as a linked, ‘live’ copy of the original. It would be used, for example, in a report written in Writer containing time-varying data, such as sales results sourced from a Calc spreadsheet.
DDE link in Writer The process for creating a DDE link from Calc to Writer is similar to creating a link within Calc. 1) In Calc, select the cells to make the DDE link to. Copy them. 2) Go to the place in your Writer document where you want the DDE link. Select Edit > Paste Special. 3) Select DDE Link (Figure 269). Click OK. Now the link has been created in Writer. When the Calc spreadsheet is updated, the table in Writer is automatically updated.
11 Chapter Sharing and Reviewing Documents
Introduction This chapter covers methods for editing shared documents: sharing (collaboration), recording changes, adding comments, reviewing changes, merging and comparing documents, and saving and using document versions. Basic editing techniques are discussed in Chapter 2 (Entering, Editing, and Formatting Data). Sharing documents (collaboration) In OpenOffice.org Writer, Impress, and Draw, only one user at a time can open any document for editing.
To enable sharing, select the box at the top of the dialog, and then click OK. A message appears stating that you must save the document to activate shared mode. Click Yes to continue. The word (shared) is then shown on the title bar after the document’s title. The Tools > Share Document command can be used to switch the mode for a document from unshared to shared. However, if you want to use a shared document in unshared mode, you need to save the shared document using another name or path.
Saving a shared spreadsheet When you save a shared spreadsheet, one of several situations may occur: • If the document was not modified and saved by another user since you opened it, the document is saved. • If the document was modified and saved by another user since you opened it, one of the following events will occur: – If the changes do not conflict, the document is saved, the dialog below appears, and any cells modified by the other user are shown with a red border.
locked due to a merge-in in progress. You can choose to cancel the Save command for now, or retry saving later. When you successfully save a shared spreadsheet, the document shows the latest version of all changes saved by all users. Recording changes You can use several methods to record changes that you or others make to a document. • You can use change marks to show added material, deleted material, and changes to formatting.
Identifying copies of spreadsheets When not using the document sharing feature, it is important to keep track of the different copies of the document. This can be done either in the file name or in the file title. If you have not provided a file title in the spreadsheet’s properties, the spreadsheet’s file name is displayed in the title bar. To set the title of the spreadsheet, select File > Properties > Description.
• Buy cheaper baseballs. • Only buy 2 spare gloves. To make these changes, use the record changes feature in Calc. To start recording changes: 1) Open the Budget Spreadsheet. 2) Select Edit > Changes > Record from the menu bar. 3) Begin editing the document. A colored border, with a dot in the upper left-hand corner, appears around a cell where changes were made (Figure 274). Other reviewers then quickly know which cells were edited. A deleted column or row is marked by a heavy colored bar.
Adding comments to changes Calc automatically adds to any recorded change a comment that describes what was changed (for example, Cell B4 changed from ‘9’ to ‘4’). Reviewers and authors can add their own comments to explain their reasons for the changes. To add a comment to a change: 1) Make the change to the spreadsheet. 2) Select the cell with the change. 3) Choose Edit > Changes > Comments. The dialog shown in Figure 275 appears.
Figure 276: Comment added to cell B3 The comment also appears in the dialog when you are accepting and rejecting changes, as shown in the first line of Figure 281 on page 335. Editing change comments 1) Select the cell with the change comment that you want to edit. 2) Select Edit > Changes > Comments. 3) Edit the comment and click OK. Tip You can view your comments one at a time using the left and right arrows located on the right hand side of the Comment dialog.
Figure 277: Inserting a comment Now the cell to which you added the comment has a colored dot in the upper right-hand corner, as shown in Figure 278. It does not have a colored border, unless the cell was also changed. Figure 278: Colored dot in cell containing a comment Tip You can change the colors Calc uses for notes by selecting Tools > Options > OpenOffice.org > Appearance.
3) When done, click outside the comment to deselect it. To hide the comment again, right-click on the cell and deselect Show Comment on the pop-up menu. Formatting comments You can change the background color, border style, transparency, and other attributes of a comment. 1) Right-click on the cell containing the comment marker, and choose Show comment from the pop-up menu. 2) Click on the comment itself. The Formatting toolbar changes to show many of the comment formatting options.
You are the coach of a youth baseball team and you submitted a potential budget created in Calc to your team sponsor. Your sponsor has reviewed the document using the record changes feature of Calc. Now, you want to review those changes and accept or reject the counter proposal. Because the sponsor recorded changes in Calc, you can easily see what changes were made and decide how to act. Viewing changes Calc gives you tremendous control over what changes you see when reviewing a document.
• Comment – Searches the content of the comments and only displays changes which have comments that match the search criteria. • Show accepted changes – Only changes you accepted are displayed. • Show rejected changes – Only changes you rejected are displayed. Note You can also access the filter control in the Accept or Reject Changes dialog shown in Figure 281. Click the Filter tab to get a set of options similar to those shown in Figure 280.
Figure 281: Accept or Reject changes dialog On the Filter tab of this dialog (not shown here), you can choose how to filter the list of changes: by date, author, cell range, or comments containing specific terms. After selecting the filter criteria, switch back to the List tab to see the results. Merging documents You submitted your budget proposal to your sponsor, but you sent it to one of your assistant coaches as well. Both of them returned their revised budget to you at the same time.
To merge documents, all of the edited documents need to have recorded changes in them. 1) Open the original document. 2) Select Edit > Changes > Merge Document. 3) A file selection dialog opens. Select a file you want to merge and click OK. 4) After the documents merge, the Accept or Reject Changes dialog opens, as in Figure 282, showing changes by more than one reviewer. If you want to merge more documents, close the dialog and then repeat steps 2 and 3.
Comparing documents Sometimes when sharing documents, reviewers may forget to record the changes they make. This is not a problem with Calc, because Calc can find the changes by comparing documents. In order to compare documents you need to have the original document and the one that is edited. To compare them: 1) Open the edited document that you want to compare with the original document. 2) Select Edit > Compare Document. 3) An open document dialog appears. Select the original document and click Insert.
Figure 284: Version management dialog 2) Click the Save New Version button to save a new version. 3) A dialog opens where you can enter comments about this version. Figure 285: Version comment dialog 4) After you enter your comment and click OK, the new version is included in the version list. Figure 286: Updated version list 338 OpenOffice.org 3.
Now when you save the file, both versions are saved in the same file. From this point you can: • Open an old version – Select the version and click the Open button and a read-only version of a previous version opens. • Compare all versions – Clicking the Compare button performs an action similar to merging documents together. An Accept or Reject Changes dialog opens showing all of the changes through the different versions.
12 Chapter Calc Macros Automating repetitive tasks
Introduction A macro is a saved sequence of commands or keystrokes that are stored for later use. An example of a simple macro is one that “types” your address. The OpenOffice.org (OOo) macro language is very flexible, allowing automation of both simple and complex tasks. Macros are especially useful to repeat a task the same way over and over again. This chapter briefly discusses common problems related to macro programming using Calc.
6) Use Edit > Paste Special to open the Paste Special dialog. Figure 289: Paste Special dialog 7) Set the operation to Multiply and click OK. The cells are now multiplied by 3. Figure 290: Cells multiplied by 3 8) Click Stop Recording to stop the macro recorder. The OpenOffice.org Basic Macros dialog opens. 9) Select the current document (see Figure 291). For this example, the current Calc document is Untitled 1. Existing documents show a library named Standard.
1 My Macros 5 Create new module in library 2 OpenOffice.org Macros 6 Macros in selected library 3 Open documents 7 Current document 4 Create new library 8 Expand/collapse list Figure 291: Parts of the OpenOffice.org Basic Macros dialog 10) Click New Module. If no libraries exist, then the Standard library is automatically created and used. In the New Module dialog, type a name for the new module or leave the name as Module1. 11) Click OK to create a new module named Module1.
Figure 292: Select the module and name the macro The created macro is saved in Module1 of the Standard library in the Untitled 1 document. Listing 1 shows the contents of the macro. Listing 1. Paste special with multiply. sub PasteMultiply rem -------------------------------------------------------------rem define variables dim document as object dim dispatcher as object rem -------------------------------------------------------------rem get access to the document document = ThisComponent.
More detail on recording macros is provided in Chapter 13 (Getting Started with Macros) in the Getting Started guide; we recommend you read it if you have not already done so. More detail is also provided in the following sections, but not as related to recording macros. Write your own functions Calc can call macros as Calc functions. Use the following steps to create a simple macro: 1) Create a new Calc document named CalcTestMacros.ods. 2) Use Tools > Macros > Organize Macros > OpenOffice.
Figure 294: OpenOffice.org Basic Macro Organizer 4) Click New to open the New Library dialog. Figure 295: New Library dialog 5) Enter a descriptive library name (such as AuthorsCalcMacros) and click OK to create the library. The new library name is shown the library list, but the dialog may show only a portion of the name. Figure 296: The library is shown in the organizer 346 OpenOffice.org 3.
6) Select AuthorsCalcMacros and click Edit to edit the library. Calc automatically creates a module named Module1 and a macro named Main. Figure 297: Basic Integrated Development Environment (IDE) 7) Modify the code so that it is the same as that shown in Listing 2.The important addition is the creation of the NumberFive function, which returns the number five. The Option Explicit statement forces all variables to be declared before they are used.
Using a macro as a function Using the newly created Calc document CalcTestMacros.ods, enter the formula =NumberFive() (see Figure 298). Calc finds the macro and calls it. Figure 298: Use the NumberFive() Macro as a Calc function Tip Function names are not case sensitive. In Figure 298, you can enter =NumberFive() and Calc clearly shows =NUMBERFIVE(). Save the Calc document, close it, and open it again. Depending on your settings in Tools > Options > OpenOffice.
Figure 300: Warning if macros are disabled If you choose to disable macros, then when the document loads, Calc can no longer find the function. Figure 301: The function is gone When a document is created and saved, it automatically contains a library named Standard. The Standard library is automatically loaded when the document is opened. No other library is automatically loaded. Calc does not contain a function named NumberFive(), so it checks all opened and visible macro libraries for the function.
a stub with the desired name is stored in the Standard library. The stub macro loads the library containing the implementation and then calls the implementation. 1) Use Tools > Macros > Organize Macros > OpenOffice.org Basic to open the OpenOffice.org Basic Macros dialog. Select the NumberFive macro and click Edit to open the macro for editing. Figure 302: Select a macro and click Edit 2) Change the name of NumberFive to NumberFive_Implementation (see Listing 3). Listing 3.
6) Save, close, and reopen the Calc document. This time, the NumberFive() function works. Listing 4. Change the name of NumberFive to NumberFive_Implementation. Function NumberFive() If NOT BasicLibraries.isLibraryLoaded("AuthorsCalcMacros") Then BasicLibraries.
3) IsArray checks to see if the argument is a single value, or an array. For example, =PositiveSum(7) or =PositiveSum(A4). In the first case, the number 7 is passed as an argument, and in the second case, the value of cell A4 is passed to the function. 4) If a range is passed to the function, it is passed as a twodimensional array of values; for example, =PositiveSum(A2:B5). LBound and UBound are used to determine the array bounds that are used.
Writing macros that act like built-in functions Although Calc finds and calls macros as normal functions, they do not really behave as built-in functions. For example, macros do not appear in the function lists. It is possible to write functions that behave as regular functions by writing an Add-In. However, this is an advanced topic that is not covered here; see http://wiki.services.openoffice.org/wiki/SimpleCalcAddIn.
which is more cumbersome than treating it as an array with two dimensions as is done in Listing 5. Listing 8. Add cell A2:C5 in every sheet Function SumCellsAllSheets() Dim TheSum As Double Dim iRow As Integer, iCol As Integer, i As Integer Dim oSheets, oSheet, oCells Dim oRow(), oRows() oSheets = ThisComponent.getSheets() For i = 0 To oSheets.getCount() - 1 oSheet = oSheets.getByIndex(i) oCells = oSheet.
The example in Listing 9, however, demonstrates how to sort on two columns. Listing 9. Sort cells A1:C5 on Sheet 1. Sub SortRange Dim oSheet Dim oCellRange REM REM REM REM Dim ' Calc sheet containing data to sort. ' Data range to sort. An array of sort fields determines the columns that are sorted. This is an array with two elements, 0 and 1. To sort on only one column, use: Dim oSortFields(0) As New com.sun.star.util.SortField oSortFields(1) As New com.sun.star.util.
Conclusion This chapter provides a brief overview on how to create libraries and modules, using the macro recorder, using macros as Calc functions, and writing your own macros without the macro recorder. Each topic deserves at least one chapter, and writing your own macros for Calc could easily fill an entire book. In other words, this is just the beginning of what you can learn! 356 OpenOffice.org 3.
13 Chapter Calc as a Simple Database A guide for users and macro programmers
Introduction A Calc document is a very capable database, providing sufficient functionality to satisfy the needs of many users. This chapter presents the capabilities of a Calc document that make it suitable as a database tool. Where applicable, the functionality is explained using both the GUI (Graphical User Interface) and macros. Note Although this document was initially created for macro programmers, the content should be accessible to all users.
A B C D E F 8 Ilsub 70 85 97 79 82.75 9 James 45 65 97 85 73 10 Lisa 100 97 100 93 97.5 11 Michelle 100 97 100 65 90.5 12 Ravi 87 92 86 93 89.5 13 Sal 45 65 100 92 75.5 14 Ted 100 97 100 85 95.5 15 Tom 70 85 93 65 78.25 16 Whil 70 85 93 97 86.25 Tip G Although the choice to associate a row to a record rather than a column is arbitrary, it is almost universal.
a range named Scores, and then I used the following equation: =SUM(Scores). To create a named range, select the range to define. Use Insert > Names > Define to open the Define Names dialog. Use the Define Names dialog to add and modify one named range at a time. Figure 304. Define a named range. In a macro, a named range is accessed, created, and deleted using the NamedRanges property of a Calc document. Use the methods hasByName(name) and getByName(name) to verify and retrieve a named range.
If NOT oRanges.hasByName(sName$) Then REM I can obtain the cell address by obtaining the cell REM and then extracting the address from the cell. Dim oCellAddress As new com.sun.star.table.CellAddress oCellAddress.Sheet = 0 'The first sheet. oCellAddress.Column = 1 'Column B. oCellAddress.Row = 2 'Row 3. REM The first argument is the range name. REM The second argument is formula or expression to REM use. The second argument is usually a string that REM defines a range.
The third argument, a cell address, acts as the base address for cells referenced in a relative way. If the cell range is not specified as an absolute address, the referenced range will be different based on where in the spreadsheet the range is used. The relative behavior is illustrated in Listing 11, which also illustrates another usage of a named range—defining an equation. The macro in Listing 11 creates the named range AddLeft, which refers to the equation A3+B3 with C3 as the reference cell.
Figure 305. Define a named range. The macro in Listing 12 creates three named ranges based on the top row of a named range. Listing 12. Create many named ranges. Sub AddManyNamedRanges() Dim oSheet 'Sheet that contains the named range. Dim oAddress 'Range address. Dim oRanges 'The NamedRanges property. Dim oRange 'Single cell range. oRanges = ThisComponent.NamedRanges oSheet = ThisComponent.getSheets().getByIndex(0) oRange = oSheet.getCellRangeByName("A1:C20") oAddress = oRange.getRangeAddress() oRanges.
Database range Although a database range can be used as a regular named range, a database range also defines a range of cells in a spreadsheet to be used as a database. Each row in a range corresponds to a record and each cell corresponds to a field. You can sort, group, search, and perform calculations on the range as if it were a database. A database range provides behavior that is useful when performing database related activities. For example, you can mark the first row as headings.
Listing 13. Create a database range and an auto filter. Sub AddNewDatabaseRange() Dim oRange 'DatabaseRange object. Dim oAddr 'Cell address range for the database range. Dim oSheet 'First sheet, which will contain the range. Dim oDoc 'Reference ThisComponent with a shorter name. oDoc = ThisComponent If NOT oDoc.DatabaseRanges.hasByName("MyName") Then oSheet = ThisComponent.getSheets().getByIndex(0) oRange = oSheet.getCellRangeByName("A1:F10") oAddr = oRange.getRangeAddress() oDoc.DatabaseRanges.
Click on the Options tab (see Figure 308) to set the sort options. Check the Range contains column labels checkbox to prevent column headers from being sorted with the rest of the data. The Sort by list box in Figure 307 displays the columns using the column headers if the Range contains column labels checkbox in Figure 308 is checked. If the Range contains column labels checkbox is not checked, however, then the columns are identified by their column name; Column A, for example.
Caution When a cell is moved during a sort operation, external references to that cell are not updated. If a cell that contains a relative reference to another cell is moved, the reference is relative to the new position when sorting is finished. Know the behavior of references during sorting and do not be alarmed; this is almost always what you want—because the reference is to the right or left in the same row.
To create an auto filter, first select the columns to filter. For example, using the data in Table 12, select data in columns B and C. If you do not select the title rows, Calc asks if the title row or the current row should be used. Although you can place the auto filter in any row, only the rows below the auto filter are filtered. Use Data > Filter > AutoFilter to insert the auto filter combo box in the appropriate cell.
Figure 310: Use the standard filter The macro in Listing 14 creates a simple filter for the first sheet. Listing 14. Create a simple sheet filter. Sub SimpleSheetFilter() Dim oSheet ' Sheet that will contain the filter. Dim oFilterDesc ' Filter descriptor. Dim oFields(0) As New com.sun.star.sheet.TableFilterField oSheet = ThisComponent.getSheets().getByIndex(0) REM If argument is True, creates an empty filter REM descriptor. If argument is False, create a REM descriptor with the previous settings.
.Field = 5 REM Compare using a numeric or a string? .IsNumeric = True REM The NumericValue property is used REM because .IsNumeric = True from above. .NumericValue = 80 REM If IsNumeric was False, then the REM StringValue property would be used. REM .StringValue = "what ever" REM Valid operators include EMPTY, NOT_EMPTY, EQUAL, REM NOT_EQUAL, GREATER, GREATER_EQUAL, LESS, REM LESS_EQUAL, TOP_VALUES, TOP_PERCENT, REM BOTTOM_VALUES, and BOTTOM_PERCENT .Operator = com.sun.star.sheet.FilterOperator.
Listing 16 demonstrates a more advanced filter that filters two columns and uses regular expressions. I noticed some unexpected behavior while working with Listing 16. Although you can create a filter descriptor using any sheet cell range, the filter applies to the entire sheet. Listing 16. A simple sheet filter using two columns. Sub SimpleSheetFilter_2() Dim oSheet ' Sheet to filter. Dim oRange ' Range to be filtered. Dim oFilterDesc ' Filter descriptor. Dim oFields(1) As New com.sun.star.sheet.
Advanced filters An advanced filter supports up to eight filter conditions, as opposed to the three supported by the simple filter. The criteria for an advanced filter is stored in a sheet. The first step in creating an advanced filter is entering the filter criteria into the spreadsheet. 1) Select an empty space in the Calc document. The empty space may reside in any sheet in any location in the Calc document.
Figure 311. Apply an advanced filter using a previously defined named range. Listing 17. Use an advanced filter. Sub UseAnAdvancedFilter() Dim oSheet 'A sheet from the Calc document. Dim oRanges 'The NamedRanges property. Dim oCritRange 'Range that contains the filter criteria. Dim oDataRange 'Range that contains the data to filter. Dim oFiltDesc 'Filter descriptor. REM Range that contains the filter criteria oSheet = ThisComponent.getSheets().getByIndex(1) oCritRange = oSheet.
The filter created in Listing 17 filters the data in place. Modify the OutputPosition property to specify a different output position (see Listing 18). The filter descriptor must be modified before the filter is applied. Table 16. Advanced filter properties. Property Comment ContainsHeader Boolean (true or false) that specifies if the first row (or column) contains headers which should not be filtered.
= 2 does not work (because you set the Row on the copy to 2, but do not change the original). Manipulating filtered data Filtered data copied to a new location may be selected, modified, and deleted at will. Data that is not copied, however, requires special attention because rows that do not match the filter criteria are simply hidden. OpenOffice.org behaves differently depending on how the cells became hidden and what operation is done.
Function Description HLOOKUP Search for a specific value across the columns in the first row of an array. Return the value from a different row in the same column. INDEX Return the content of a cell, specified by row and column number or an optional range name. INDIRECT Return the reference specified by a text string. LOOKUP Return the contents of a cell either from a one-row or one-column range or from an array. MATCH Search an array and return the relative position of the found item.
Function Description SUMIF Calculate the sum for the cells that meet the search criteria. VAR Estimate the variance based on a sample. VARA Estimate the variance based on a sample. The value of text is 0. VARP Estimate the variance based on the entire population. VARPA Estimate the variance based on the entire population. The value of a text is 0. VLOOKUP Search for a specific value across the rows in the first column of an array. Returns the value from a different column in the same row.
Table 18. Examples of search criteria for the COUNTIF and SUMIF functions. Criteria Type Function Result Description Number =COUNTIF(B1:C16; 95) 3 Finds numeric values of 95. Text =COUNTIF(B1:C16; "95") 3 Finds numeric or text values of 95. Expression =COUNTIF(B1:C16; ">95") 6 Finds numeric values greater than 95. Expression =COUNTIF(B1:C16; 2*45+5) 3 Finds only numeric values of 95. Regular expression =COUNTIF(B1:C16; "9.*") 12 Finds numbers or text that start with 9.
Function index Function 8 STDEVP 9 SUM 10 VAR 11 VARP Tip Do not forget that the SUBTOTAL function ignores cells that use the SUBTOTAL function. Say you have a spreadsheet that tracks investments. The retirement investments are grouped together with a subtotal. The same is true of regular investments. You can use a single subtotal that includes the entire range without worrying about the subtotal cells. Using formulas to find data Calc offers numerous methods to find data in a sheet.
The return_column_index identifies the column to return; a value of 1 returns the first column in the range. The statement =VLOOKUP("Bob"; A1:G9; 1) finds the first row in A1:G9 containing the text Bob, and returns the value in the first column. The first column is the searched column, so the text Bob is returned. If the column index is 2, then the value in the cell to the right of Bob is returned: column B. The final column, sort_order, is optional.
Search a row or column using LOOKUP LOOKUP is similar to HLOOKUP and VLOOKUP. The search range for the LOOKUP function is a single sorted row or column. LOOKUP has two forms: LOOKUP(search_value; search_range) LOOKUP(search_value; search_range; return_range) The search value is the same as HLOOKUP and VLOOKUP. The search range, however, must be a single row or a single column; for example, A7:A12 (values in column A) or C5:Q5 (values in row 5). If the return_range is omitted, the matched value is returned.
• The search data is in descending order and the data is large enough that the data must be searched assuming that it is sorted; because it is faster to sort a sorted list. Examples Consider the data in Table 23. Each student’s information is stored in a single row. Write a formula to return the average grade for Fred. The problem can be restated as Search column A in the range A1:G16 for Fred and return the value in column F (column F is the sixth column).
The formula =VLOOKUP(83; $Sheet2.$A$2:$B$7; 2) is an obvious solution. Dollar signs are used so that the formula can be copied and pasted to a different location and it will still reference the same values in Table 20. ADDRESS returns a string with a cell’s address Use ADDRESS to return a text representation of a cell address based on the row, column, and sheet; ADDRESS is frequently used with MATCH.
INDIRECT converts a string to a cell or range Use INDIRECT to convert a string representation of a cell or range address to a reference to the cell or range. Table 22 contains examples accessing data as shown in Table 20. Table 22. Examples using INDIRECT. Example Comment INDIRECT("A2") Returns cell A2, which contains Bob. INDIRECT(G1) If Cell G1 contains the text A2, then this returns Bob. SUM(INDIRECT("B1:B5")) Returns the sum of the range B1:B5, which is 194.
Table 23. Breakdown of Listing 19. Function Description MATCH("Bob";A1:A16; 0) Return 4 because Bob is the fourth entry in column A. ADDRESS(4; 4) Return $D$4. INDIRECT("$D$4") Convert $D$4 into a reference to the cell D4. OFFSET($D$4; 0; 0; 1; 2) Return the range D4:E4. SUM(D4:E4) Return the sum of Bob’s quiz scores. Although Listing 19 works as intended, it breaks easily and unexpectedly. Consider, for example, what happens if the range is changed to A2:A16.
returns the cell B2. Table 25 lists shows the syntax for using the INDEX function. Table 25. Syntax for INDEX. Syntax Description INDEX(reference) Return the entire range. INDEX(reference; row) Return the specified row in the range. INDEX(reference; row; column) Return the cell specified by row and column. A row and column of 1 returns the cell in the upper left corner of the range. INDEX(reference; row; column; range) A reference range can contain multiple ranges.
Table 27. Using INDEX with a multi-range. Function Returns =INDEX(B2:G2; 1; 2) 93 =INDEX(B5:G5; 1; 2) 65 =INDEX((B2:G2;B5:G5); 1; 2) 93 =INDEX((B2:G2;B5:G5); 1; 2; 1) 93 =INDEX((B2:G2;B5:G5); 1; 2; 2) 65 Database-specific functions Although every Calc function can be used for database manipulation, the functions in Table 28 are specifically designed for use as a database.
Function Description DVAR Calculate the variance using the fields that match the search criteria. The fields are treated as a sample. DVARP Calculatesthe variance using the fields that match the search criteria. The fields are treated as the entire population. The syntax for the database functions are identical. DCOUNT(database; database field; search criteria) The database argument is the cell range that defines the database. The cell range should contain the column labels (see Listing 22).
14 Chapter Setting up and Customizing Calc
Introduction This chapter describes some common customizations that you may wish to do. In addition to selecting options from those provided, you can customize menus, toolbars, and keyboard shortcuts, add new menus and toolbars, and assign macros to events. However, you cannot customize context (right-click) menus. Other customizations are made easy by extensions that you can install from the OpenOffice.org website or from other providers.
User Data options Calc uses the first and last name stored in the OpenOffice.org – User Data page to fill in the Created and Modified fields in the document properties, and the optional Author field often used in the footer of a printed spreadsheet. Fill in the form on this page. Print options Set the print options to suit your default printer and your most common printing method.
To modify a color: 1) Select the color to modify from the list or the color table. 2) Enter the new values that define the color. If necessary, change the settings from RGB (Red, Green, Blue) to CMYK (Cyan, Magenta, Yellow, Black) or vice versa. The changed color appears in the lower of the two color preview boxes at the top. 3) Modify the Name as required. 4) Click the Modify button. The newly defined color is now listed in the Color table.
Another way to define or alter colors is through the Colors page of the Area dialog, where you can also save and load palettes, a feature that is not possible here. In Calc, draw a temporary draw object and use the context menu of this object to open the Area dialog. If you load a palette in one component of OOo, it is only active in that component; the other components keep their own palettes. Security options Use the OpenOffice.
Click the Options button to open a separate dialog with specific choices (Figure 315). Remove personal information on saving. Select this option to always remove user data from the file properties when saving the file. To manually remove personal information from specific documents, deselect this option and then use the Delete button under File > Properties > General. Ctrl-click required to follow hyperlinks. In older versions of OOo, clicking on a hyperlink in a document opened the linked document.
Macro security Click the Macro Security button to open the Macro Security dialog (not shown here), where you can adjust the security level for executing macros and specify trusted sources. File sharing options for this document Select the Open this document in read-only mode option to restrict this document to be opened in read-only mode only. This option protects the document against accidental changes.
Choosing options for loading and saving documents You can set the Load/Save options to suit the way you work. This chapter describes only a few of the options, those more relevant to working with Calc. See Chapter 2 (Setting Up OpenOffice.org) in the Getting Started guide for a description of the other options. If the Options dialog is not already open, click Tools > Options. Click the + sign to the left of Load/Save to display the list of load/save options pages.
ODF format version OpenOffice.org by default saves documents in OpenDocument Format (ODF) version 1.2. While this allows for improved functionality, there may be backwards compatibility issues. When a file saved in ODF 1.2 is opened in an earlier version of OpenOffice.org (using ODF 1.0/1.1), some of the advanced features may be lost. Two notable examples are the functions ADDRESS and INDIRECT and the formatting of numbered lists.
• If you choose Save original Basic code, the macros will not work in OpenOffice.org but are retained if you save the file into Microsoft Office format. • If you choose Load Basic code to edit, the changed code is saved in an OpenOffice.org document but is not retained if you save into an Microsoft Office format. • If you are importing a Microsoft Excel file containing VBA code, you can select the option Executable code.
HTML compatibility Load/Save options Choices made on the Load/Save – HTML Compatibility page (Figure 321) affect HTML pages imported into OpenOffice.org and those exported from OOo. See HTML documents; importing/exporting in the Help for more information. The main items of interest for Calc users are in the Export section: OpenOffice.org Basic and Display warning. Export - OpenOffice.org Basic Select this option to include OOo Basic macros (scripts) when exporting to HTML format.
Choosing options for Calc In the Options dialog, click the + sign to the left of OpenOffice.org Calc on the left-hand side. A list of subsections drops down. General options for Calc In the Options dialog, choose OpenOffice.org Calc > General. Figure 322: Selecting general options for Calc Metrics section Choose the unit of measurement used in spreadsheets and the default tab stops distance. 400 OpenOffice.org 3.
Updating section Choose whether to update links when opening a document always, only on request, or never. Regardless of this setting, you can manually update links at any time. You might want to avoid updating links when opening documents if they often contain many charts or linked graphics that would slow down loading. Input settings section Press Enter to move selection Specifies that pressing Enter moves the cursor to another cell.
Use printer metrics for text formatting Specifies that printer metrics are applied for printing and also for formatting the display on the screen. If this option is not selected, a printer-independent layout is used for screen display and printing. Show overwrite warning when pasting data Specifies that, when you paste cells from the clipboard to a cell range that is not empty, a warning appears. View options for Calc In the Options dialog, choose OpenOffice.org Calc > View.
For printing, choose Format > Page > Sheet and mark the Grid option. Page breaks Specifies whether to view the page breaks within a defined print area. Guides while moving Specifies whether to view guides when moving drawings, frames, graphics and other objects. These guides help you align objects. Simple handles Specifies whether to display the handles (the eight points on a selection box) as simple squares without a 3D effect.
Text overflow If a cell contains text that is wider than the width of the cell, the text is displayed over empty neighboring cells in the same row. If there is no empty neighboring cell, a small triangle at the cell border indicates that the text continues. Show references in color Specifies that each reference is highlighted in color in the formula. The cell range is also enclosed by a colored border as soon as the cell containing the reference is selected for editing.
Figure 324: Calc calculation options Iterative references section Iterative references are formulas that are continuously repeated until the problem is solved. In this section you can choose the number of approximation steps carried out during iterative calculations and the degree of precision of the answer. Iterations Select this option to enable iterations. If this options is not selected, an iterative reference causes an error message. Steps Sets the maximum number of iteration steps.
01/01/1904 Sets 1/1/1904 as day zero. Use this setting for spreadsheets that are imported in a foreign format. Other options Specify a variety of options relevant to spreadsheet calculation. Case sensitive Specifies whether to distinguish between upper and lower case in texts when comparing cell contents. The EXACT text function is always case-sensitive, independent of the settings in this dialog. Decimal places Defines the number of decimals to be displayed for numbers with the Standard number format.
Automatically find column and row labels Specifies that you can use the text in any cell as a label for the column below the text or the row to the right of the text. The text must consist of at least one word and must not contain any operators. Example: Cell E5 contains the text Europe. Below, in cell E6, is the value 100 and in cell E7 the value 200. If the Automatically find column and row labels option is selected, you can write the following formula in cell A1: =SUM(Europe).
Changes options In the Options dialog, choose OpenOffice.org Calc > Changes. Figure 326: Calc options for highlighting changes On this page you can specify options for highlighting recorded changes in spreadsheets. You can assign specific colors for insertions, deletions, and other changes, or you can let Calc assign colors based on the author of the change; in the latter case, one color will apply to all changes made by that author. Grid options The Grid page defines the grid settings for spreadsheets.
Figure 327: Calc grid options Grid section Snap to grid activates the snap function. Visible grid displays grid points on the screen. These points are not printed. Resolution section Here you can set the unit of distance for the spacing between horizontal and vertical grid points and subdivisions (intermediate points) of the grid. Synchronize axes changes the current grid settings symmetrically. Print options In the Options dialog, choose OpenOffice.org Calc > Print.
In Calc, this dialog has four tabs, as shown below. Options are described in the Help; many will be familiar to users of other office suites. Figure 328: The AutoCorrect dialog in Calc Customizing the user interface Customizing the menu font If you want to change the menu font from that supplied by OOo to the system font for your operating system, do this: 1) Choose Tools > Options > OpenOffice.org > View. 2) Check Use system font for user interface and click OK.
menus that are contained under another menu. For example, in addition to File, Edit, View, and so on, there is File | Send and File |Templates. The commands available for the selected menu are shown in the central part of the dialog. 5) To customize the selected menu, click on the Menu or Modify buttons. You can also add commands to a menu by clicking on the Add button. These actions are described in the following sections.
Creating a new menu In the Customize dialog, click New to display the dialog shown in Figure 330. 1) Type a name for your new menu in the Menu name box. 2) Use the up and down arrow buttons to move the new menu into the required position on the menu bar. Click OK to save. The new menu now appears on the list of menus in the Customize dialog. (It will appear on the menu bar itself after you save your customizations.
To move submenus (such as File | Send), select the main menu (File) in the Menu list and then, in the Menu Content section of the dialog, select the submenu (Send) in the Entries list and use the arrow keys to move it up or down in the sequence. Submenus are easily identified in the Entries list by a small black triangle on the right hand side of the name.
On the Add Commands dialog, select a category and then the command, and click Add. The dialog remains open, so you can select several commands. When you have finished adding commands, click Close. Back on the Customize dialog, you can use the up and down arrow buttons to arrange the commands in your preferred sequence. Modifying menu entries In addition to changing the sequence of entries on a menu or submenu, you can add submenus, rename or delete the entries, and add group separators.
4) When you have finished making all your changes, click OK to save them. Figure 332: The Toolbars page of the Customize dialog Creating a new toolbar To create a new toolbar: 1) Choose Tools > Customize > Toolbars from the menu bar. 2) Click New. On the Name dialog, type the new toolbar's name and choose from the Save In drop-down list where to save this changed menu: for Calc or for a selected document.
The new toolbar now appears on the list of toolbars in the Customize dialog. After creating a new toolbar, you need to add some commands to it, as described below. Adding a command to a toolbar If the list of available buttons for a toolbar does not include all the commands you want on that toolbar, you can add commands. When you create a new toolbar, you need to add commands to it.
Figure 333: Change Icon dialog Caution Be careful when reassigning your operating system’s or OOo’s predefined shortcut keys. Many key assignments are universally understood shortcuts, such as F1 for Help, and are always expected to provide certain results. Although you can easily reset the shortcut key assignments to the OOo defaults, changing some common shortcut keys can cause confusion, frustration and possible data loss or corruption, especially if other users share your computer.
Note All existing shortcut keys for the currently selected Function are listed in the Keys selection box. If the Keys list is empty, it indicates that the chosen key combination is free for use. If it were not, and you wanted to reassign a shortcut key combination that is already in use, you must first delete the existing key. Shortcut keys that are greyed-out in the listing on the Customize dialog, such as F1 and F10, are not available for reassignment.
2) In the Functions section at the bottom of the dialog, scroll down in the Category list to Styles. Click the + sign to expand the list of styles. 3) Choose the category of style. (This example uses a cell style, but you can also choose page styles.) The Function list now displays the names of the available styles for the selected category. The example shows OOo’s predefined cell styles.
feature with care as no confirmation dialog will be displayed; the defaults will be set without any further notice or user input. Running macros from key combinations You can also define shortcut key combinations that will run macros. These shortcut keys are strictly user-defined; none are built in. For more information on macros, see Chapter 12 (Calc Macros). Adding functionality with extensions An extension is a package that can be installed into OpenOffice.org to add new functionality.
Figure 335: Installing an extension Using extensions This section describes a few of the extensions to Calc. In each case, you need to install the extension as described in the previous section. rofessional Template Pack II Provides more than 120 templates for Writer, Calc, and Impress. Available in several languages. After you have installed this extension, you will find the templates under File > New > Templates and Documents.
A Appendix Keyboard Shortcuts
Introduction You can use Calc without a pointing device such as a mouse or trackball, by using its built-in keyboard shortcuts. OOo has a general set of keyboard shortcuts, available in all components, and a component-specific set directly related to the work of that component. For help with OOo’s keyboard shortcuts or using OOo with a keyboard only, search the OOo online help using the “shortcut” or “accessibility” keywords.
Shortcut Keys Effect Ctrl+Down Arrow Moves the cursor to the bottom edge of the current data range. If the row below the cell that contains the cursor is empty, the cursor moves down to the next row that contains data. Ctrl+Shift+Arrow Selects all cells containing data from the current cell to the end of the continuous range of data cells, in the direction of the arrow pressed. If used to select rows and columns together, a rectangular cell range is selected. Ctrl+Page Up Moves one sheet to the left.
Function and arrow key shortcuts Table 30: Function key shortcuts Shortcut Keys Effect F1 Displays the OOo help browser. When the help browser is already open F1 jumps to the main help page. Shift+F1 Displays context help. Ctrl+F1 Displays the note that is attached to the current cell. F2 Switches to Edit mode and places the cursor at the end of the contents of the current cell.
Shortcut Keys Effect Shift+Ctrl+F11 Updates the templates. F12 Groups the selected data range. Ctrl+F12 Ungroups the selected data range. Table 31. Arrow key shortcuts Shortcut Keys Effect Alt+Down Arrow Increases the height of current row. Alt+Up Arrow Decreases the height of current row. Alt+Right Arrow Increases the width of the current column. Alt+Left Arrow Decreases the width of the current column.
DataPilot shortcuts Table 33: DataPilot shortcut keys Shortcut Keys Effect Tab Changes the focus by moving forward through the areas and buttons of the dialog. Shift+Tab Changes the focus by moving backward through the areas and buttons of the dialog. Up arrow Moves the focus up one item in the current dialog area. Down arrow Moves the focus down one item in the current dialog area. Left arrow Moves the focus one item to the left in the current dialog area.
B Appendix Description of Functions
Functions available in Calc Calc provides all of the commonly used functions found in modern spreadsheet applications. Since many of Calc’s functions require very specific and carefully calculated input arguments, the descriptions in this appendix should not be considered complete references for each function. Refer to the application Help or the OOo wiki for details and examples of all functions. On the wiki, start with http://wiki.services.openoffice.
Mathematical functions Table 34: Mathematical functions Syntax Description ABS(number) Returns the absolute value of the given number. ACOS(number) Returns the inverse cosine of the given number in radians. ACOSH(number) Returns the inverse hyperbolic cosine of the given number in radians. ACOT(number) Returns the inverse cotangent of the given number in radians. ACOTH(number) Returns the inverse hyperbolic cotangent of the given number in radians.
Syntax Description COMBINA(count_1; count_2) Returns the number of combinations for a given number of objects (repetition included). Count_1 is the total number of elements. Count_2 is the selected count from the elements. CONVERT(value; "text"; "text") Converts a currency value of a European currency into Euros. Value is the amount in the currency to be converted. Text is the official abbreviation for the currency in question (for example, "EUR").
Syntax Description and if the number and significance are negative, rounding up is carried out based on that value. GCD(numbers) Returns the greatest common divisor of one or more integers. Numbers is a list of up to 30 numbers whose greatest common divisor is to be calculated, separated by semi-colons. GCD_ADD(numbers) Returns the greatest common divisor of a list of numbers. Numbers is a list of up to 30 numbers separated by semi-colons.
Syntax Description MULTINOMIAL (number(s)) Returns the factorial of the sum of the arguments divided by the product of the factorials of the arguments. Number(s) is a list of up to 30 numbers separated by semi-colons. ODD(number) Rounds the given number up to the nearest odd integer. PI() Returns the value of PI to fourteen decimal places. POWER(base; power) Returns the result of a number raised to a power. Base is the number that is to be raised to the given power.
Syntax Description ROUNDUP(number; count) Rounds the given number up. Count (optional) is the number of digits to which rounding up is to be done. If the count parameter is negative, only the whole number portion is rounded. It is rounded to the place indicated by the count. SERIESSUM(x; n; m; coefficients) Returns a sum of powers of the number x in accordance with the following formula: SERIESSUM(x;n;m;coefficients) = coefficient_1*x^n + coefficient_2*x^(n+m) + coefficient_3*x^(n+2m) +...
Syntax Description the range to which the criteria are to be applied. Criteria is the cell in which the search criterion is shown, or the search criterion itself. Sum_range is the range from which values are summed; if it has not been indicated, the values found in the Range are summed. SUMSQ(number_1; Calculates the sum of the squares of numbers (totaling up of the squares of the arguments) Number_1; number_2;... number_30 are up to 30 arguments, the sum of whose squares is to be calculated.
• As a percentage. To enter an interest rate as a percentage, type in the interest rate followed by the % key. For example, to compute a loan with a 3.25% interest rate, enter 3.25% into the function. If you enter it as 3.25, the function will treat it as a 325% interest rate. Accounting systems vary in the number of days in a month or a year used in calculations. The following table gives the integers used for the basis parameter used in some of the financial analysis functions.
Syntax Description period; rate; basis) that is independent of the depreciable life is used here. Cost is the acquisition cost. Date_purchased is the date of acquisition. First_period is the end date of the first settlement period. Salvage is the salvage value of the capital asset at the end of the depreciable life. Period is the settlement period to be considered. Rate is the rate of depreciation. Basis indicates how the year is to be calculated.
Syntax Description number of interest payments per year (1, 2 or 4). Basis indicates how the year is to be calculated. COUPNCD(settlement; maturity; frequency; basis) Returns the date of the first interest date after the settlement date, and formats the result as a date. Settlement is the date of purchase of the security. Maturity is the date on which the security matures (expires). Frequency is the number of interest payments per year (1, 2 or 4). Basis indicates how the year is to be calculated.
Syntax Description same unit, and thus both must be calculated annually or monthly. PV is the current value. Start_period the first payment period for the calculation. End_period the last payment period for the calculation. Type is the due date of the payment at the beginning (1) or end (0) of each period. CUMPRINC(rate; NPER; PV; S; E; type) Returns the cumulative interest paid for an investment period with a constant interest rate. Rate is the periodic interest rate.
Syntax Description method. Note that the book value will never reach zero under this calculation type. Cost fixes the initial cost of an asset. Salvage fixes the value of an asset at the end of its life. Life is the number of periods defining how long the asset is to be used. Period defines the length of the period. The period must be entered in the same time unit as the life. Factor (optional) is the factor by which depreciation decreases.
Syntax Description (settlement; maturity; coupon; yield; frequency; basis) security in years. Settlement is the date of purchase of the security. Maturity is the date on which the security matures (expires). Coupon is the annual coupon interest rate (nominal rate of interest). Yield is the annual yield of the security. Frequency is the number of interest payments per year (1, 2 or 4). Basis indicates how the year is to be calculated.
Syntax Description Maturity is the date on which the security is sold. Investment is the purchase price. Redemption is the selling price. Basis indicates how the year is to be calculated. IPMT(rate; period; NPER; PV; FV; type) Calculates the periodic amortization for an investment with regular payments and a constant interest rate. Rate is the periodic interest rate. Period is the period for which the compound interest is calculated. NPER is the total number of periods during which annuity is paid.
Syntax Description MIRR(values; investment; reinvest_rate) Calculates the modified internal rate of return of a series of investments. Values corresponds to the array or the cell reference for cells whose content corresponds to the payments. Investment is the rate of interest of the investments (the negative values of the array) Reinvest_rate is the rate of interest of the reinvestment (the positive values of the array).
Syntax Description Redemption is the redemption value per 100 currency units of par value. Frequency is the number of interest payments per year (1, 2 or 4). Basis indicates how the year is to be calculated. ODDFYIELD(settlement; maturity; issue; first_coupon; rate; price; redemption; frequency; basis) Calculates the yield of a security if the first interest date falls irregularly. Settlement is the date of purchase of the security. Maturity is the date on which the security matures (expires).
Syntax Description type) with constant interest rates. Rate is the periodic interest rate. NPER is the number of periods in which annuity is paid. PV is the present value (cash value) in a sequence of payments. FV (optional) is the desired value (future value) to be reached at the end of the periodic payments. Type (optional) defines whether the payment is due at the beginning (1) or the end (0) of a period.
Syntax Description purchase of the security. Maturity is the date on which the security matures (expires). Issue is the date of issue of the security. Rate is the interest rate of the security on the issue date. Yield is the annual yield of the security. Basis indicates how the year is to be calculated. PV(rate; NPER; PMT; FV; type) Returns the present value of an investment resulting from a series of regular payments. Rate defines the interest rate per period.
Syntax Description SLN(cost; salvage; life) Returns the straight-line depreciation of an asset for one period. The amount of the depreciation is constant during the depreciation period. Cost is the initial cost of an asset. Salvage is the value of an asset at the end of the depreciation. Life is the depreciation period determining the number of periods in the depreciation of the asset. SYD(cost; salvage; life; period) Returns the arithmetic-declining depreciation rate.
Syntax Description asset at the end of the depreciation. Life is the depreciation duration of the asset. Start is the start of the depreciation entered in the same date unit as the life. End is the end of the depreciation. Factor (optional) is the depreciation factor. FA=2 is double rate depreciation. Type (optional) defines whether the payment is due at the beginning (1) or the end (0) of a period.
Syntax Description is the price (purchase price) of the security per 100 currency units of par value. Redemption is the redemption value per 100 currency units of par value. Basis indicates how the year is to be calculated. YIELDMAT(settlement; maturity; issue; rate; price; basis) Calculates the annual yield of a security, the interest of which is paid on the date of maturity. Settlement is the date of purchase of the security. Maturity is the date on which the security matures (expires).
Syntax Description success on each trial. T_1 defines the lower limit for the number of trials. T_2 (optional) defines the upper limit for the number of trials. BETADIST(number; alpha; beta; start; end) Returns the cumulative beta probability density function. Number is the value between Start and End at which to evaluate the function. Alpha is a parameter to the distribution. Beta is a parameter to the distribution. Start (optional) is the lower bound for number.
Syntax Description be determined with CHIDIST, in which case the chi square of the random sample must then be passed as a parameter instead of the data row. Data_B is the array of the observations. Data_E is the range of the expected values. CONFIDENCE(alpha; STDEV; size) Returns the (1-alpha) confidence interval for a normal distribution. Alpha is the level of the confidence interval. STDEV is the standard deviation for the total population. Size is the size of the total population.
Syntax Description calculates the density function, and C = 1 calculates the distribution. FDIST(number; degrees_freedom_1; degrees_freedom_2) Calculates the values of an F probability distribution. Number is the value for which the F distribution is to be calculated. Degrees_freedom_1 is the degrees of freedom in the numerator in the F distribution. Degrees_freedom_2 is the degrees of freedom in the denominator in the F distribution.
Syntax Description distribution. GAMMAINV(number; alpha; beta) Returns the inverse of the Gamma cumulative distribution. This function allows you to search for variables with different distribution. Number is the probability value for which the inverse Gamma distribution is to be calculated. Alpha is the parameter Alpha of the Gamma distribution. Beta is the parameter Beta of the Gamma distribution. GAMMALN(number) Returns the natural logarithm of the Gamma function, G(x), for the given number.
Syntax Description set. Data is the cell range of data. Rank_c is the ranking of the value (2nd largest, 3rd largest, etc.) written as an integer. LOGINV(number; mean; STDEV) Returns the inverse of the lognormal distribution for the given Number, a probability value. Mean is the arithmetic mean of the standard logarithmic distribution. STDEV is the standard deviation of the standard logarithmic distribution.
Syntax Description SP) the value returned for unsuccessful tests. R is the value returned for successful tests. SP is the probability of the success of an attempt. NORMDIST(number; mean; STDEV; C) Returns the normal distribution for the given Number in the distribution. Mean is the mean value of the distribution. STDEV is the standard deviation of the distribution. C = 0 calculates the density function, and C = 1 calculates the distribution.
Syntax Description POISSON(number; mean; C) Returns the Poisson distribution for the given Number. Mean is the middle value of the Poisson distribution. C = 0 calculates the density function, and C = 1 calculates the distribution. PROB(data; probability: start; end) Returns the probability that values in a range are between two limits. Data is the array or range of data in the sample. Probability is the array or range of the corresponding probabilities.
Syntax Description STDEV(number_1; number_2; ... number_30) Estimates the standard deviation based on a sample. Number_1; number_2; ... number_30 are numerical values or ranges representing a sample based on an entire population. STDEVA(value_1; value_2; ... value_30) Calculates the standard deviation of an estimation based on a sample. Value_1; value_2; ... value_30 are values or ranges representing a sample derived from an entire population. Text has the value 0. STDEVP(number_1; number_2; ...
Syntax Description the dependent array or range of data for the second record. Mode = 1 calculates the onetailed test, Mode = 2 the two- tailed test. Type of t-test to perform: paired (1), equal variance (homoscedastic) (2), or unequal variance (heteroscedastic) (3). VAR(number_1; number_2; ... number_30) Estimates the variance based on a sample. Number_1; number_2; ... number_30 are numerical values or ranges representing a sample based on an entire population. VARA(value_1; value_2; ...
time value, it is displayed as a number. For example, 01/01/2000 12:00 PM, converts to 36526.5. This is just a matter of formatting; the actual value is always stored and manipulated as a number. To see the date or time displayed in a standard format, change the number format (date or time) accordingly. To set the default date format used by Calc. go to Tools > Options > OpenOffice.org Calc > Calculate.
Syntax Description DAYSINYEAR(date) Calculates the number of days in the year of the given date. EASTERSUNDAY(integer) Returns the date of Easter Sunday for the entered year. Year is an integer between 1583 and 9956 or 0 and 99. EDATE(start_date; months) The result is a date a number of Months away from the given Start_date. Only months are considered; days are not used for calculation. Months is the number of months.
Syntax Description NOW() Returns the computer system date and time. The value is updated when your document recalculates. NOW is a function without arguments. SECOND(number) Returns the second, as an integer, for the given time value. Number is a time value. TIME(hour; minute; second) Returns the current time value from values for hours, minutes and seconds. This function can be used to convert a time based on these three elements to a decimal time value. Hour, minute and second must all be integers.
Syntax Description WEEKSINYEAR(date) Calculates the number of weeks in a year until a certain date. A week that spans two years is added to the year in which most days of that week occur. WORKDAY(start_date; days; holidays) Returns a date number that can be formatted as a date. You then see the date of a day that is a certain number of Workdays away from the start_date. Holidays (optional) is a list of holidays. Enter a cell range in which the holidays are listed individually.
Syntax Description FALSE() Set the logical value to FALSE. The FALSE() function does not require any arguments. IF(test; then_value; otherwise_value) Specifies a logical test to be performed. Test is any value or expression that can be TRUE or FALSE. Then_value (optional) is the value that is returned if the logical test is TRUE. Otherwise_value (optional) is the value that is returned if the logical test is FALSE. NOT(logical_value) Reverses the logical value.
Syntax Description CURRENT() Calculates the current value of a formula at the actual position. FORMULA(reference) Displays the formula of a formula cell at any position. The formula will be returned as a string in the Reference position. If no formula cell can be found, or if the presented argument is not a reference, returns the error value #N/A. ISBLANK(value) Returns TRUE if the reference to a cell is blank. This function is used to determine if the content of a cell is empty.
Syntax Description ISNA(value) Returns TRUE if a cell contains the #N/A (value not available) error value. If an error occurs, the function returns a logical or numerical value. Value is the value or expression to be tested. ISNONTEXT(value) Tests if the cell contents are text or numbers, and returns FALSE if the contents are text. If an error occurs, the function returns a logical or numerical value.
Database functions This section deals with functions used with data organized as one row of data for one record. The Database category should not be confused with the Base database component in OpenOffice.org. A Calc database is simple a range of cells that comprises a block of related data where each row contains a separate record. There is no connection between a database in OpenOffice.org and the Database category in OOo Calc.
Syntax Description DCOUNTA(database; database_field; search_criteria) Counts the number of rows (records) in a database that match the specified search_criteria and contain numeric or alphanumeric values. The search supports regular expressions. DGET(database; database_field; search_criteria) Returns the contents of the referenced cell in a database which matches the specified search_criteria.
Syntax Description DVARP(database; database_field; search_criteria) Calculates the variance of all cell values in a database field in all records that match the specified search_criteria. The records are from the example are treated as an entire population. Array functions Table 42: Array functions Syntax Description FREQUENCY(data; classes) Calculates the frequency distribution in a onecolumn-array.
Syntax Description MDETERM(array) Returns the array determinant of an array. This function returns a value in the current cell; it is not necessary to define a range for the results. Array is a square array in which the determinants are defined. MINVERSE(array) Returns the inverse array. Array is a square array that is to be inverted. MMULT(array; array) Calculates the array product of two arrays. The number of columns for array 1 must match the number of rows for array 2.
Syntax Description TRANSPOSE(array) Transposes the rows and columns of an array. Array is the array in the spreadsheet that is to be transposed. TREND(data_Y; data_X; new_data_X; linear_Type) Returns values along a linear trend. Data_Y is the Y Data array. Data_X (optional) is the X Data array. New_data_X (optional) is the array of the X data, which are used for recalculating values. Linear_type is optional. If linear_type = 0, then lines will be calculated through the zero point.
Syntax Description value30 is the list of values entered as a reference to a cell or as individual values. COLUMN(reference) Returns the column number of a cell reference. If the reference is a cell, the column number of the cell is returned; if the parameter is a cell area, the corresponding column numbers are returned in a single-row array if the formula is entered as an array formula.
Syntax Description function returns a logical or numerical value. Reference contains the address of the cell in which the error occurs. HLOOKUP(search_criteria; array; index; sorted) Searches for a value and reference to the cells below the selected area. This function verifies if the first row of an array contains a certain value. The function returns the value in a row of the array, named in the index, in the same column. The search supports regular expressions.
Syntax Description search will not return any usable results. The search supports regular expressions. Search_criterion is the value to be searched for; entered either directly or as a reference. Search_vector is the single-row or singlecolumn area to be searched. Result_vector is another single-row or single-column range from which the result of the function is taken. The result is the cell of the result vector with the same index as the instance found in the search vector.
Syntax Description used in an array formula, only the row number of the first range cell will be returned. Reference is a cell, an area, or the name of an area. If a reference is not indicated, Calc automatically sets the reference to the current cell. ROWS(array) Returns the number of rows in a reference or array. Array is the reference or named area whose total number of rows is to be determined. SHEET(reference) Returns the sheet number of a reference or a string representing a sheet name.
Syntax Description Sort_order (optional) indicates whether the first column in the array is sorted in ascending order. Text functions Use Calc’s text functions to search and manipulate text strings or character codes. Table 44: Text functions Syntax Description ARABIC(text) Calculates the value of a Roman number. The value range must be between 0 and 3999. Text is the text that represents a Roman number.
Syntax Description DECIMAL(text; radix) Converts text with characters from a number system to a positive integer in the base radix given. The radix must be in the range 2 to 36. Spaces and tabs are ignored. The text field is not case-sensitive. Text is the text to be converted. To differentiate between a hexadecimal number, such as A1 and the reference to cell A1, place the number in quotation marks; for example, "A1" or "FACE”. Radix is the base of the number system.
Syntax Description equal to 0, the thousands separator is suppressed. If the parameter is equal to 0 or if it is missing altogether, the thousands separators of the current locale setting are displayed. LEFT(text; number) Returns the first character or characters in a text string. Text is the text where the initial partial words are to be determined. Number (optional) is the number of characters for the start text. If this parameter is not defined, one character is returned.
Syntax Description repeated. Number is the number of repetitions. The result can be a maximum of 255 characters. RIGHT(text; number) Defines the last character or characters in a text string. Text is the text of which the right part is to be determined. Number (optional) is the number of characters from the right part of the text. ROMAN(number; mode) Converts a number into a Roman numeral. The value range must be between 0 and 3999; the modes can be integers from 0 to 4.
Syntax Description T(value) Converts a number to a blank text string. Value is the value to be converted. Also, a reference can be used as a parameter. If the referenced cell includes a number or a formula containing a numerical result, the result will be an empty string. TEXT(number; format) Converts a number into text according to a given format. Number is the numerical value to be converted. Format is the text which defines the format.
Syntax Description function. x is the value on which the function will be calculated. n is the order of the Bessel function. BIN2DEC(number) Returns the decimal number for the binary number entered. Number is the binary number. BIN2HEX(number; places) Returns the hexadecimal number for the binary number entered. Number is the binary number. Places is the number of places to be output. BIN2OCT(number; places) Returns the octal number for the binary number entered. Number is the binary number.
Syntax Description ERF(lower_limit; upper_limit) Returns values of the Gaussian error integral. Lower_limit is the lower limit of integral. Upper_limit (optional) is the upper limit of the integral. If this value is missing, the calculation takes places between 0 and the lower limit. ERFC(lower_limit) Returns complementary values of the Gaussian error integral between x and infinity. Lower limit is the lower limit of integral (x).
Syntax Description IMCOS(complex_number) Returns the cosine of a complex_number. The complex number is entered in the form "x + yi" or "x + yj" IMDIV(numerator; denominator) Returns the division of two complex numbers. Numerator, Denominator are entered in the form "x + yi" or "x + yj" IMEXP(complex_number) Returns the power of e (the Eulerian number) and the complex number.
Syntax Description entered in the form "x + yi" or "x + yj" OCT2BIN(number; places) Returns the binary number for the octal number entered. Number is the octal number. Places is the number of places to be output. OCT2DEC(number) Returns the decimal number for the octal number entered. Number is the octal number. OCT2HEX(number; places) Returns the hexadecimal number for the octal number entered. Number is the octal number. Places is the number of places to be output.
C Appendix Calc Error Codes
Introduction to Calc error codes Calc provides feedback for errors of miscalculation, incorrect use of functions, invalid cell references and values, and other user initiated mistakes. The feedback may be displayed within the cell that contains the error (Figure 336) or on the status bar (Figure 337) or in both, depending on the type of error. Generally speaking, if the error occurs in the cell that is selected (or contains the cursor), the error message is displayed on the status bar.
Error codes displayed within cells Cell error Code Explanation of the error ### N/A The column is too narrow to display the complete formatted contents of the cell. This is not really an error value, so there is no corresponding numerical error code. The solutions to this problem are to increase the width of the column. or select Format > Cells > Alignment and click either Wrap text automatically or Shrink to fit cell size to make the text match the current column width.
General error codes The following table is an overview of the most common error messages for OOo Calc. Note Errors described as Internal errors should not be encountered by users under normal conditions. Errors listed as Not used are not currently assigned to any error condition and will not occur. Code Message Explanation of the error 501 Invalid character Character in a formula is not valid. This error is the same as the Invalid Name error (525) except that it occurs within a formula.
Code Message Explanation of the error 512 Formula overflow The total number of internal tokens (that is, operators, variables, brackets) in the formula exceeds 512, or the total number of matrices the formula creates exceeds 150. This includes basic functions that receive too large an array as a parameter. 513 String overflow An identifier in the formula exceeds 64 KB in size, or a result of a string operation exceeds 64 KB in size.
Code Message Explanation of the error 525 Invalid names (cell displays #NAME?) An identifier could not be evaluated; for example, no valid reference, no valid domain name, no column/row label, no macro, incorrect decimal divider, add-in not found. 526 Internal syntax error Obsolete, no longer used, but could come from old documents if the result is a formula from a domain. 527 Internal overflow References, such as when a cell references a cell, are too encapsulated or deeply nested.
Index 3 C 3D chart appearance 89 area chart 98 bar chart 96 elements 79 formatting 87 illumination 89 line chart 99 moving elements 86 pie chart 96 rotation and perspective 88 rotation, interactive 90 types 75 Calc options calculate 404 case sensitive 406 changes 408 dates 405 grid 408 grid lines, viewing 402 guides while moving 403 handles 403 input settings 401 iterative references 405 page breaks, visible 403 screen display 403 sort lists 407 unit of measurement 400 updating links 401 Zoom 404 calcula
options 408 recording 327 reviewing 332 viewing 333 chart appearance of 3D 89 area 79 area background 86 colors 87 creating 73 data labels 80, 92 editing 78 formatting 84 formatting 3D 87 graphic background 87 grids 78 illumination of 3D 89 interactive 3D rotation 90 mean value lines 83 moving elements 86 perspective 88 Position and Size dialog 94 resizing 93 rotation 88 titles 78 trend lines 82 types 95 wall 79 Y error bars 83 chart elements 79 chart type 75, 79 Chart Wizard 74 collaboration 323 color codi
dialog 244 examples of use 219 filter 260 GETPIVOTDATA 267 group date or time values 255 group rows or columns 253 group scalar values 254 group without intervals 256 multiple data fields 262 page fields 253 preconditions 241 refresh 261 row or column fields 250 shortcuts 266 sort results 257 starting 242 DataPilot Tools 421 date conversion, automatic 45 date functions 458 dates and times, entering 43 DDE link description 320 in Calc 320 in Writer 321 default template 124 deleting columns and rows 31 sheets
wildcards 69 fixed values 178 focus 23 formatting chart 84 data labels 92 symbols 92 formatting autoformatting cells and sheets 59 cell background 59 cell borders 58 conditional 61 themes 60 formatting data font 56 font effects 56 manual line breaks 54 multiple lines of text 53 numbers 55 shrinking text 54 wrapping 53 Formatting toolbar 12 formula bar 13 formulas absolute reference 190 breaking into parts 204 calculation examples 180 concatenation operator 188 definition 178 intersection operator 188 operat
grids in charts 78 guides while moving 403 function keys 425 introduction 423 loading from a file 419 navigation 423 resetting to default values 419 saving to a file 419 selection 423 H headers and footers 115 Hide/Show button 135 hiding and showing data 63 HTML compatibility 399 hyperlinks absolute 303 creating 304 Ctrl-click to activate 394 editing 306 relative 303 removing 306 I illumination of 3D chart 89 image map 151 images adding 130 context menu 142 cropping 140 embedding 133 inserting from file
Microsoft Office file conversion 398 moving chart elements 86 moving from sheet to sheet 26 Multiple Operations tool 281 multiple sheets introduction 295 setting up 295 N named range 359 navigating within spreadsheets cell reference 23 focus 23 keyboard 24 methods 23 Navigator 23, 38 scenarios 280 nested functions 198 net charts 101 nonlinear programming solver 293 numbers as text, entering 42 numbers, entering 42 O Object Linking and Embedding (OLE) 316 ODF plugin (Sun) 20 OLE object linked 319 non-linke
relative reference 189 relief options 56 remove personal information on saving 394 removing data from a cell 51 renaming sheets 33 replacing cell data 52 resolution options 409 rofessional Template Pack 421 rotating images 142 rotation chart 88 rounding methods 213 rounding off numbers 213 rows deleting 31 freezing 34 hiding and showing 63 inserting 30 selecting 29 S save original Basic code 398 saving AutoRecovery info 20 saving spreadsheets as CSV file 22 as HTML 23 methods 19 to Microsoft Excel format 2
strikethrough 56 Style dialog 116 style organizer 111 styles accessing 108 assign to shortcut keys 111 assigning to shortcut keys 418 cell 107 copy 117 create by drag and drop 117 create from selection 116 create using Style dialog 116 delete 119 description 106 linking 116 modifying 111 move 117 organizer 111 page 108, 111 types supported 106 Styles and Formatting window 109 subtotals 275 synchronize axes 409 system font 410 T template associate spreadsheet with different template 125 create spreadsheet f
