OpenOffice.org 3.3 Calc Guide Using Spreadsheets in OpenOffice.org 3.
Copyright This document is Copyright © 2005–2011 by its contributors as listed below. You may distribute it and/or modify it under the terms of either the GNU General Public License (http://www.gnu.org/licenses/gpl.html), version 3 or later, or the Creative Commons Attribution License (http://creativecommons.org/licenses/by/3.0/), 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.
Contents Copyright................................................................................................................... 2 Note for Mac users.................................................................................................... 8 Chapter 1 Introducing Calc.......................................................................................................... 9 What is Calc?...........................................................................................................
Adding drawing objects to charts............................................................................ 87 Resizing and moving the chart................................................................................ 88 Gallery of chart types............................................................................................... 89 Chapter 4 Using Styles and Templates in Calc...........................................................................98 What is a template?.........................
Removing personal data........................................................................................ 158 Chapter 7 Using Formulas and Functions................................................................................159 Introduction........................................................................................................... 160 Setting up a spreadsheet....................................................................................... 160 Creating formulas..................
Reviewing changes................................................................................................ 291 Merging documents............................................................................................... 294 Comparing documents........................................................................................... 295 Saving versions...................................................................................................... 295 Chapter 12 Calc Macros..................
Logical functions.................................................................................................... 404 Informational functions.......................................................................................... 405 Database functions................................................................................................ 407 Array functions...................................................................................................... 408 Spreadsheet functions..........
Note for Mac users Some keystrokes and menu items are different on a Mac from those used in Windows and Linux. The table below gives some common substitutions for the instructions in this chapter. For a more detailed list, see the application Help. 8 Windows/Linux Mac equivalent Effect Tools > Options menu selection OpenOffice.
1 Chapter Introducing Calc
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.
Figure 1: Parts of the Calc window Title bar The Title bar, located at the top, shows the name of the current spreadsheet. When the spreadsheet is newly created, its name is Untitled X, where X is a number. When you save a spreadsheet for the first time, you are prompted to enter a name of your choice. Menu bar Under the Title bar is the Menu bar. When you choose one of the menus, a submenu appears with other options.
• Window contains commands for the display window such as New Window, Split, and Freeze. • Help contains links to the Help file bundled with the software, What's This?, Support, Registration, and Check for Updates. Toolbars Calc has several types of toolbars: docked (fixed in place), floating, and tear-off. Docked toolbars can be moved to different locations or made to float, and floating toolbars can be docked.
Figure 3: Example of a tear-off toolbar Moving toolbars To move a docked toolbar, place the mouse pointer over the toolbar handle, hold down the left mouse button, drag the toolbar to the new location, and then release the mouse button. Figure 4: Moving a docked toolbar To move a floating toolbar, click on its title bar and drag it to a new location, as shown in Figure 3.
Figure 5: Control+double-click to dock or undock Customizing toolbars You can customize toolbars in several ways, including choosing which icons are visible and locking the position of a docked toolbar. To access a toolbar’s customization options, use the down-arrow at the end of the toolbar or on its title bar (Figure 6). Figure 6: Customizing toolbars To show or hide icons defined for the selected toolbar, choose Visible Buttons from the drop-down menu.
Figure 8: Apply Style, Font Name and Font Size lists Note If any of the icons (buttons) in Figure 8 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 drop-down 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.
the Input line area, click in the area, then type your changes. To edit within the current cell, just double-click the cell. Right-click (context) menus Right-click on a cell, graphic, or other object to open a context menu. Often the context menu is the fastest and easiest way to reach a function. If you’re not sure where in the menus or toolbars a function is located, you may be able to find it by right-clicking.
Status bar The Calc status bar provides information about the spreadsheet and convenient ways to quickly change some of its features. Figure 11: Left end of Calc status bar Figure 12: 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.
Cell or object information ( ) Displays information about the selected items. When a group of cells is selected, the sum of the contents is displayed by default; you can right-click on this field and select other functions, such as the average value, maximum value, minimum value, or count (number of items selected). 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.
• From the Start Center. When OOo is open but no document is open (for example, if you close all the open documents but leave the program running), the Start Center is shown. Click one of the icons to open a new document of that type, or click the Templates icon to start a new document using a template. If a document is already open in OOo, the new document opens in a new window. Figure 14: OpenOffice.org Start Center When OOo is open, you can also start a new document in one of the following ways.
Figure 15: Starting a new spreadsheet from a template Opening existing spreadsheets When no document is open, the Start Center (Figure 14) provides an icon for opening an existing document or choosing from a list of recently-edited documents. You can also open an existing document in one of the following ways. If a document is already open in OOo, the second document opens in a new window. • • • • Choose File > Open.... Click the Open button on the main toolbar. Press Control+O on the keyboard.
Opening CSV files Comma-separated-values (CSV) files are text files that contain the cell contents of a single sheet. Each line in a CSV file represents a row in a spreadsheet. Commas, semicolons, or other characters are used to separate the cells. Text is entered in quotation marks; numbers are entered without quotation marks. To open a CSV file in Calc: 1) Choose File > Open. 2) Locate the CSV file that you want to open. 3) If the file has a *.csv extension, select the file and click Open.
6) In OOo 3.3, two new options are available when importing CSV files that contain data separated by specific characters. These options determine whether quoted data will always be imported as text, and whether Calc will automatically detect all number formats, including special number formats such as dates, time, and scientific notation. The detection depends on the language settings. 7) Click OK to open the file.
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.
Saving as a CSV file To save a spreadsheet as a comma separated 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;*.xls) and click Save. You may see the message box shown below. Click Keep Current Format. 4) In the Export of text files dialog, select the options you want and then click OK.
1) Use File > Save As when saving the document. (You can also use File > Save the first time you save a new document.) 2) On the Save As dialog, type the file name, select the Save with password option, and then click Save. 3) The Set Password dialog opens. Figure 19: Two levels of password protection Here you have several choices: • To read-protect the document, type a password in the two fields at the top of the dialog box.
• To write-protect the document but allow selected people to edit it, select the Open file read-only checkbox and type a password in the two boxes at the bottom of the dialog box. 4) Click OK to save the file. If either pair of passwords do not match, you receive an error message. Close the message box to return to the Set Password dialog box and enter the password again.
Figure 20. (left) One selected cell and (right) a group of selected cells Using the Tab and Enter keys • Pressing Enter or Shift+Enter moves the focus down or up, respectively. • Pressing Tab or Shift+Tab moves the focus to the right or to the left, respectively. Using the arrow keys Pressing the arrow keys on the keyboard moves the focus in the direction of the arrows. Using Home, End, Page Up and Page Down • Home moves the focus to the start of a row.
Key Combination Movement Control+↑ To the next row above containing data in that column or to Row 1 Control+↓ To the next row below containing data in that column or to Row 65536 Control+Home To Cell A1 Control+End To lower right-hand corner of the rectangular area containing data Alt+Page Downn One screen to the right (if possible) Alt+Page Up One screen to the left (if possible) Control+Page Down One sheet to the right (in sheet tabs) Control+Page Up One sheet to the left (in sheet tabs)
Using the keyboard Pressing Control+Page Down moves one sheet to the right and pressing Control+Page Up moves one sheet to the left. Using the mouse Clicking on one of the sheet tabs at the bottom of the spreadsheet selects that sheet. If you have a lot of sheets, then some of the sheet tabs may be hidden behind the horizontal scroll bar at the bottom of the screen. If this is the case, then the four buttons at the left of the sheet tabs can move the tabs into view. Figure 22 shows how to do this.
3) Move the mouse around the screen. 4) Once the desired block of cells is highlighted, release the left mouse button. To select a range of cells without dragging the mouse: 1) Click in the cell which is to be one corner of the range of cells. 2) Move the mouse to the opposite corner of the range of cells. 3) Hold down the Shift key and click.
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. To select multiple columns or rows that are not contiguous: 1) Click on the first column or row in the group. 2) Hold down the Control key. 3) Click on all of the subsequent columns or rows while holding down the Control key.
All sheets Right-click any one of the sheet tabs and choose Select All Sheets from the pop-up menu. Working with columns and rows Inserting columns and rows Columns and rows can be inserted individually or in groups. Note When you insert a single new column, it is inserted to the left of the highlighted column. When you insert a single new row, it is inserted above the highlighted row.
Multiple columns or rows Multiple columns or rows can be deleted at once rather than deleting them one at a time. 1) Highlight the required columns or rows by holding down the left mouse button on the first one and then dragging across the required number of identifiers. 2) Proceed as for deleting a single column or row above. Tip Instead of deleting a row or column, you may wish to delete the contents of the cells but keep the empty row or column.
Figure 25: Insert Sheet dialog Renaming sheets The default name for the a new sheet is SheetX, where X is a number. While this works for a small spreadsheet with only a few sheets, it becomes awkward when there are many sheets. To give a sheet a more meaningful name, you can: • Enter the name in the Name box when you create the sheet, or • Right-click on a sheet tab and choose Rename Sheet from the pop-up menu; replace the existing name with a different one. • (New in OOo3.
Figure 26. 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. Fit Width Displays the complete width of the document page. The top and bottom edges of the page may not be visible. 100% Displays the document at its actual size. Variable Enter a zoom percentage of your choice.
Figure 27. Frozen rows and columns Freezing a row and a column 1) Click into the cell that is immediately below the row you want frozen and immediately to the right of the column you want frozen. 2) Choose Window > Freeze. Two lines appear on the screen, a horizontal line above this cell and a vertical line to the left of this cell. Now as you scroll around the screen, everything above and to the left of these lines will remain in view. Unfreezing To unfreeze rows or columns, choose Window > Freeze.
Splitting the screen horizontally To split the screen horizontally: 1) Move the mouse pointer into the vertical scroll bar, on the right-hand side of the screen, and place it over the small button at the top with the black triangle. Split screen bar Figure 29. Split screen bar on vertical scroll bar 2) Immediately above this button, you will see a thick black line (Figure 29). Move the mouse pointer over this line, and it turns into a line with two arrows (Figure 30). Figure 30.
Split screen bar Figure 31: Split bar on horizontal scroll bar 2) Immediately to the right of this button is a thick black line (Figure 31). Move the mouse pointer over this line and it turns into a line with two arrows. 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.
Figure 32: 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.
• Use the Start and End icons to jump to the first or last cell in the selected data range. Tip Ranges, scenarios, pictures, and other objects are much easier to find if you have given them informative names when creating them, instead of keeping Calc’s default Graphics 1, Graphics 2, Object 1, and so on, which may not correspond to the position of the object in the document. Choosing a drag mode Sets the drag and drop options for inserting items into a document using the Navigator.
Figure 33: The Description page of the document’s Properties dialog Use the Custom Properties page (Figure 34) to store information that does not fit into the fields supplied on the other pages of this dialog box. Figure 34: Custom Properties page, showing drop-down lists of names and types When the Custom Properties page is first opened in a new document, it may be blank. However, if the new document is based on a template, this page may contain fields.
• In the Value column, type or select what you want to appear in the document where this field is used. Choices may be limited to specific data types depending on the selection in the Type column; for example, if the Type selection is Date, the Value for that property is limited to a date. To remove a custom property, click the button at the end of the row. Tip 42 To change the format of the Date value, go to Tools > Options > Languages and change the Locale setting.
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.
Caution When a number is formatted as text, take care that the cell containing the number is not used in a formula because Calc will ignore the value. Entering dates and times Select the cell and type the date or time. You can separate the date elements with a slash (/) or a hyphen (–) or use text such as 10 Oct 03. Calc recognizes a variety of date formats. You can separate time elements with colons such as 10:43:45.
Text that you type: Result A - B (A, space, minus, space, B) A – B (A, space, en-dash, space, B) A -- B (A, space, minus, minus, space, B) A – B (A, space, en-dash, space, B) A--B (A, minus, minus, B) A—B (A, em-dash, B) A-B (A, minus, B) A-B (unchanged) A -B (A, space, minus, B) A -B (unchanged) A --B (A, space, minus, minus, B) A –B (A, space, en-dash, B) Deactivating automatic changes Calc automatically applies many changes during data input, unless you deactivate those changes.
Caution Tip Choices that are not available are grayed out, but you can still choose the opposite direction from what you intend, which could cause you to overwrite cells accidentally. A shortcut way to fill cells is to grab the “handle” in the lower righthand corner of the cell and drag it in the direction you want to fill. If the cell contains a number, the number will fill in series. If the cell contains text, the same text will fill in the direction you chose.
Figure 38: Result of fill series selection shown in Figure 37 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 40: 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 on 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 42: Validity choices for a cell range To provide input help for a cell, use the Input Help page of the Validity dialog (Figure 43). To show an error message when an invalid value is entered, use the Error Alert page (Figure 44). Be sure to write something helpful, explaining what a valid entry should contain—not just “Invalid data—try again” or something similar.
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. Click in the cell to select it, and then press the Backspace key. Removing data and formatting The data and the formatting can be removed from a cell at the same time.
Using the keyboard After selecting the appropriate cell, press the F2 key and the cursor is placed at the end of the cell. Then use the keyboard arrow keys to move the cursor through the text in the cell. Using the mouse Using the mouse, either double-click on the appropriate cell (to select it and place the cursor in it for editing), or single-click to select the cell and then move the mouse pointer up to the input line and click into it to place the cursor for editing.
Figure 47: Format Cells > Alignment dialog 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 48 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 50: Number format icons. Left to right: currency, percentage, date, exponential, standard, add decimal place, delete decimal place. In OOo 3.
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).
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. Figure 53: Cell alignment and orientation Some of the alignment and orientation icons are available only if you have Asian or CTL (Complex Text Layout) languages enabled (in Tools > Options > Language Settings > Languages).
Figure 55: Types of vertical stacking Formatting the cell borders To quickly choose a line style and color for the borders of a cell, click the small arrows next to the Line Style and Line Color icons on the Formatting toolbar. If the Line Style and Line Color icons are not displayed in the formatting toolbar, select the down arrow on the right side of the bar, then select Visible Buttons. In each case, a palette of choices is displayed.
Autoformatting cells and sheets You can use the AutoFormat feature to quickly apply a set of cell formats to a sheet or a selected cell range. 1) Select the cells that you want to format, including the column and row headers. 2) Choose Format > AutoFormat. 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. Click OK.
Formatting spreadsheets using themes Calc comes with a predefined set of formatting themes that you can apply to your spreadsheets. It is not possible to add themes to Calc, and they cannot be modified. However, you can modify their styles after you apply them to a spreadsheet. To apply a theme to a spreadsheet: 1) Click the Choose Themes icon in the Tools toolbar. If this toolbar is not visible, you can show it using View > Toolbars > Tools. The Theme Selection dialog appears.
Parameter field Enter a reference, value, or formula in the parameter field, or in both parameter fields if you have selected a condition that requires two parameters. You can also enter formulas containing relative references. Figure 57: 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 hide or show sheets, rows, and columns, use the options on the Format menu or the right-click (context) menu. For example, to hide a row, first select the row, and then choose Format > Row > Hide (or right-click and choose Hide). To hide or show selected cells, choose Format > Cells from the menu bar (or rightclick and choose Format Cells). On the Format Cells dialog, go to the Cell Protection tab.
Figure 59: Outline group controls Filtering which cells are visible A filter is a list of conditions that each entry has to meet in order to be displayed. You can set three types of filters from the Data > Filter sub-menu. Automatic filters add a drop-down list to the top row of a column that contains commonly used filters. They are quick and convenient and almost as useful with text as with numbers, because the list includes every unique entry in the selected cells.
toolbar buttons. Using the dialog, you can sort the selected cells using up to three columns, in either ascending (A-Z, 1-9) or descending (Z-A, 9-1) order. Tip You can define a custom sort order if the supplied alphanumeric ones do not fit your requirements. See “Defining a fill series” on page 48 for instructions. Figure 60: Choosing the criteria and order of sorting On the Options tab of the Sort dialog (Figure 61), you can choose the following options. Figure 61: Options for sorting 64 OpenOffice.
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. Range contains column labels Does not include the column heading in the sort. Include formats A cell's formatting is moved with its contents.
Text and numbers in cells may have been entered directly or may be the result of a calculation. The search method you use depends on the type of data you are searching for. Tip Cell contents can be formatted in different ways. For example, a number can be formatted as a currency, to be displayed with a currency symbol. You see the currency symbol in the cell, but you cannot search for it. By default, Calc searches the current sheet.
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. 1) To open the Find & Replace dialog, use the keyboard shortcut Control+F or select Edit > Find & Replace. 2) Click More Options to expand the dialog. 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.
3) Click Find, Find All, Replace, or Replace All (not recommended). Tip The online help describes many of the regular expressions and their uses. The following points may be of interest to Calc users: • In Calc, regular expressions are applied separately to each cell. This means that a search for r.d will match red in cell A1 but will not match r in cell A2 and d (or ed) in cell A3. (The regular expression r.d means the system will try to match r followed by any other character followed by d.
3 Chapter Creating Charts and Graphs Presenting information visually
Introduction Charts and graphs are often powerful ways to convey information to the reader. OpenOffice.org Calc offers a variety of 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.
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 65: 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 66. Tip Before choosing the Chart Wizard, place the cursor anywhere in the area of the data. The Chart Wizard will then do a fairly good job of guessing the range of the data.
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. Calc offers a choice of 10 basic chart types, with a few options for each type of chart.
Selecting data series Figure 68: 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.
You can leave out the legend or include it and place it to the left, right, top or bottom. To confirm your selections and complete the chart, click Finish. Figure 69: Titles, legend and grids Editing charts After you have created a chart, you may find things you would like to change. Calc provides tools for changing the chart type, chart elements, data ranges, fonts, colors and many other options, through the Insert and Format menus, the right-click (context) menu and the Chart toolbar.
The default 3D chart also has the chart floor, which is not available in 2D charts. Chart title Chart wall Legend Chart area Axis labels Figure 70: Elements of 2D chart Chart floor Figure 71: 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.
Data labels Data labels put information about each data point on the chart. They can be very useful for presenting detailed information, but you need to be careful to not create a chart that is too cluttered to read. Select the graph as described above and choose Insert > Data Labels. The options are as follows. Figure 72: Data Labels dialog Show value as number Displays the numeric values of the data points. When selected, this option activates the Number format... button. Number format...
Placement Selects the placement of data labels relative to the objects. Figure 77 on page 83 shows examples of values as text (neither Show value as number nor Show value as percentage selected) and values as percentages, as well as when data values are used as substitutes for legends or in conjunction with them. 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.
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. To show the trend line equation, select the trend line in the chart, right-click to open the context menu, and choose Insert Trend Line Equation. When the chart is in edit mode, OpenOffice.org gives you the equation of the trend line and the correlation coefficient.
• In the drop-down list: – Standard Error – calculates the error based on the numerical data you provide in the chart – Variance – shows error calculated on the size of the biggest and smallest data points – – 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.
Data Ranges Explained on page 72 (Figure 67 and Figure 68). 3D View Formats 3D charts (see page 82). Note Chart Floor and 3D View are only available for a 3D chart. These options are unavailable (grayed out) if a 2D chart is selected. In most cases you need to select the exact element you want to format. Sometimes this can be tricky to do with the mouse, if the chart has many elements, especially if some of them are small or overlapping. If you have Tooltips turned on (in Tools > Options > OpenOffice.
Note Tip If your chart graphic is 3D, round red handles appear which control the three-dimensional angle of the graphic. You cannot resize or reposition the graphic while the round red handles are showing. With the round red handles showing, Shift+Click to get the green resizing handles. You can now resize and reposition your 3D chart graphic. See the following tip. You can resize the chart graphic using its green resizing handles (Shift+Click, then drag a corner handle to maintain the proportions).
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.
• An x value of 90, with y and z set to 0, provides a view from the top of the chart. With x set to –90, the view is from the bottom of the chart. • The rotations are applied in the following order: x first, then y, and z last. • When shading is enabled and you rotate a chart, the lights are rotated as if they are fixed to the chart. • The rotation axes always relate to the page, not to the chart’s axes. This is different from some other chart programs.
Illumination Use the Illumination page (Figure 78) to set the light sources for the 3D view. Refer to the Draw Guide for more details on setting the illumination. Click any of the eight buttons to switch a directed light source on or off. By default, the second light source is switched on. It is the first of seven normal, uniform light sources. The first light source projects a specular light with highlights.
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 80: 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. Choosing the command Format Data Labels 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 Data Label tab is shown in Figure 72.
Choosing and formatting symbols In line and scatter charts the symbols representing the points can be changed to a different symbol shape or color through the object properties dialog. Select the data series you wish to change, right-click, and choose Format Data Series from the context menu. You can reach this dialog by double-clicking the area of data you would like to change. On the Line tab of the Data Series dialog, in the Icon section, choose from the dropdown list Select > Symbols.
To format the drawing objects, right-click and choose your changes from the context menu. 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 changes, then the dialog for precise sizing and positioning. To resize a chart interactively: 1) Click once on the chart to select it.
Position is defined as a X,Y coordinate relative to a fixed point (the base point), typically located at the upper left of the document. You can temporarily change this base point to make positioning or dimensioning simpler (click on the spot corresponding to the location of the base point in either of the two selection windows on the right side of the dialog—upper for positioning or lower for dimensioning).
Bar charts are excellent for giving an immediate visual impact for data comparison in cases when time is not an important factor, for example when comparing the popularity of a few products in a marketplace. • The first chart in Figure 83 is achieved quite simply by using the chart wizard with Insert > Grids, deselecting y-axis, and using Insert > Mean Value Lines. • The second chart in the figure is the 3D option in the chart wizard with a simple border and the 3D chart area twisted around.
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. • The second example is a 3D pie chart with realistic schema and illumination. With a completed 2D pie chart, choose Format > 3D view > Illumination where you can change the direction of the light, the color of the ambient light, and the depth of the shade. We also adjusted the 3D angle of the disc in the Perspective dialog on the same set of tabs.
Figure 86: Examples of donut charts Area charts An area chart is a version of a line or column graph. It may be useful where you wish to emphasize volume of change. Area charts have a greater visual impact than a line chart, but the data you use will make a difference. Figure 87: Area charts—the good, the bad, and the ugly As shown in Figure 87, an area chart is sometimes tricky to use. This may be one good reason to use transparency values in an area chart.
a picture of the sky. As you can see, the legend turns into labels on the z-axis. But overall, though it is visually more appealing, it is more difficult to see the point you are trying to make with the data. Other ways of visualizing the same data series are represented by the stacked area chart or the percentage stacked area chart.
compare other data. Examples of good scatter charts might include weather data, reactions under different acidity levels, conditions at altitude or any data which matches two series of numeric data. In contrast to line charts, the x-axis is to the left of the right labels, which usually indicates a time series. Scatter charts may surprise those unfamiliar with how they work. While constructing the chart, if you choose Data Range > Data series in rows, the first row of data represents the x-axis.
Figure 91: Bubble chart showing three data series Net charts A net chart is similar to a polar or radar chart. It is useful for comparing data that is not a time series but shows 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.
Other varieties of a net chart can be made to show the data series as stacked numbers or stacked percentages. The series can also be filled with a color (Figure 93). Partial transparency is often best for showing all the series all at once. Figure 93: 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.
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 like sales over time (column) with a profit margin trends (line). You can choose the number of columns and lines in the Chart Wizard. So for example you might have two columns with two lines to represent two product lines with the sales figures and profit margins of both.
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.
Cell styles Similar to paragraph styles in OOo Writer, cell styles are the most basic type of style in Calc. You can apply a cell style to a cell and that cell will follow the formatting rules of the style. Five cell styles are supplied with OOo: Default, Heading, Heading1, Result, and Result2. Initially, the styles are configured so that if you change the font family of Default, then all of the other styles will change to match.
• Menu: Choose Format > Styles and Formatting. • 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, , is for cell styles and the second, , is for page styles.
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. Caution When this mode is active, a right-click anywhere in the document undoes the last Fill Format action. Be careful not to accidentally rightclick and thus undo actions you want to keep.
To find out which page style is in use for a selected sheet, look in status bar. Figure 99: Status bar showing location of page style information below the sheet tabs. Modifying styles To modify a style, right-click on its name in the Styles and Formatting window and choose Modify. Make the changes in the Style dialog and click OK to save the changes.
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. If you link cell styles, then when you change the base style (for example, by changing the font from Times to Helvetica), all the linked styles will change as well. Sometimes this is exactly what you want; other times you do not want the changes to apply to all the linked styles.
Cell protection Use the Cell Protection options to protect cells against certain types of editing. Page style options Several of the page style options are described in more detail in Chapter 6 (Printing, Exporting, and E-mailing), because manually formatting a sheet at print time (using Format > Page) actually modifies the page style. Page Use the Page tab to edit the overall appearance of the page and its layout. The available options are shown in Figure 101.
Mirrored formats the pages as if you want to bind the printed pages like a book. The first page of a document is assumed to be an odd page. 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.
Creating a new style using the Style dialog To create a new style using the Style dialog, right-click in the Styles and Formatting window and choose New from the pop-up menu. (Cell styles only) If you want your new style to be linked with an existing style, first select that style and then right-click and choose New. If you link styles, then when you change the base style (for example, by changing the font from Times to Helvetica), all the linked styles will change as well.
2) In the Template Management dialog (Figure 103), set the lists at the bottom to either Templates or Documents, as needed. The default is Templates on the left and Documents on the right. Figure 103: Choosing to copy styles from a document, not a template. Tip To copy styles from a file that is not open, click the File button. When you return to this dialog, both lists show the selected file as well as all the currently open documents.
4) To copy a style, hold down the Ctrl key and drag the name of the style from one list to the other. 5) Repeat for each style you want to copy. If the receiving template or document has many styles, you may not see any change unless you scroll down in the list. When you are finished, click Close. Deleting styles You cannot remove (delete) any of Calc’s predefined styles, even if you are not using them.
4) Select the template that you want to use. You can preview the selected template or view the template’s properties: • To preview the template, click the Preview icon. A preview of the template appears in the box on the right. • To view the template’s properties, click the Document Properties icon. The template’s properties appear in the box on the right. 5) Click Open. The Templates and Documents dialog closes and a new document based on the selected template opens in Calc.
Templates can also contain predefined text, saving you from having to type it every time you create a new document. For example, an invoice template might contain your company’s name, address and logo. You can also save menu and toolbar customizations in templates; see Chapter 14 (Setting up and Customizing Calc) for more information.
Updating a spreadsheet from a changed template The next time you open a spreadsheet that was created from the changed template, the following message appears. Figure 108: Update styles message Click Update Styles to apply the template’s changed styles to the spreadsheet. Click Keep Old Styles if you do not want to apply the template’s changed styles to the spreadsheet (but see Caution notice below).
Adding templates using the Extension Manager The Extension Manager provides an easy way to install collections of templates, graphics, macros, or other add-ins that have been “packaged” into files with a .OXT extension. See Chapter 14 (Setting Up and Customizing Calc) for more about the Extension Manager. This Web page lists many of the available extensions: http://extensions.services.openoffice.org/.
Setting a custom template as the default You can set any template to be the default, as long as it is in one of the folders displayed in the Template Management dialog. 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.
Organizing templates OOo can only use templates that are in OOo template folders. You can create new OOo template folders and use them to organize your templates, and import templates into those folders. For example, you might have one template folder for report templates and another for letter templates. You can also export templates. To begin, choose File > Templates > Organize from the main menu. The Template Management dialog opens.
To delete a template: 1) In the Template Management dialog, double-click the folder that contains the template you want to delete. A list of the templates contained in that folder appears underneath the folder name. 2) Click the template that you want to delete. 3) Click the Commands button and choose Delete from the drop-down menu. A message box appears and asks you to confirm the deletion. Click Yes.
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.
2) Choose Insert > Picture > From File from the menu bar, or click the Insert Picture icon on the Picture toolbar). 3) On the Insert Picture dialog, navigate to the file to be inserted, select it, and click Open. Note The picture is inserted into Calc floating above the cells and anchored to the cell in which the cursor was placed. See “Positioning graphics” on page 133 for more about positioning and anchoring graphics.
Linking an image file To create a link to the file containing the image instead of saving a copy of the image in the Calc document, use the Insert picture dialog and select the Link option. The image is then displayed in the document, but when the document is saved, it contains only a reference to the image file—not the image itself. The document and the image remain as two separate files, which are merged temporarily only when you open the document again.
Figure 111: The Edit Links dialog Note Going the other way, from embedded to linked, is not so easy—you must delete and reinsert each image, one at a time, selecting the Link option when you do so. 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.
The Gallery is available in all components of OOo. It does not come with many graphics, but you can add your own pictures or find extensions containing more graphics. The Gallery is explained in more detail in Chapter 11 (Graphics, the Gallery, and Fontwork) in the Getting Started guide. For more about extensions, see Chapter 14 (Setting Up and Customizing Calc) in this book. This section explains the basics of inserting a Gallery image into a Calc document.
To close the Gallery, choose Tools > Gallery to uncheck the Gallery entry, or click on the Gallery icon again. 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.
Table 3: Picture toolbar functions (from left to right) Icon 124 Name Behavior From File Use of this icon is described in “Inserting an image file” on page 118. Filter Displays the Graphic Filter toolbar. See page 125. Graphics Mode Provides several color modes in the dropdown list. See page 125. Color Opens the Color toolbar, described on page 126. Transparency Sets the transparency of the selected image. See page 126. 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 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. Caution Applying OOo picture filters to any image consecutively will progressively degrade the quality of the image. The picture filters used in Calc utilize what is known as a Destructive Editing algorithm, whereby each filter is applied to the image immediately, changing the original data of the image.
Figure 114: The Crop dialog On the Crop dialog, you can control the following parameters: Keep scale / Keep image size When Keep scale is selected (default), cropping the image does not change the scale of the picture. When Keep image size is selected, cropping enlarges (for positive cropping values), shrinks (for negative cropping values), or distorts of the image so that the image size remains constant. Left, Right, Top, and Bottom The image is cropped by the amount entered in these boxes.
Resizing an image To resize an image. 1) Click the picture, if necessary, to show the green resizing handles. 2) Position the pointer over one of the green resizing handles. The pointer changes shape giving a graphical representation of the direction of the resizing. 3) Click and drag to resize the picture. 4) Release the mouse button when satisfied with the new size.
Text Opens a dialog where you can set the options for text that goes over a picture. To write text over a graphic, click on the graphic to select it, and then press Enter. There should be a cursor inside the graphic. Any 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.
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. Macros are introduced in Chapter 12 (Calc Macros).
Table 6: Drawing toolbar functions (from left to right) Icon Name Behavior Select Selects objects. Line Draws a line. Rectangle Draws a rectangle. To draw a square, hold down Shift while you drag. Ellipse Draws an ellipse. To draw a circle, hold down Shift while you drag. Freeform Line Draws a freeform line. Text Draws a text box with no border. Callouts Draws a line that ends in a rectangular callout. Basic Shapes Opens the Basic Shapes toolbar.
Icon Name Behavior Polygon Draws a line composed of a series of straight line segments. Hold down the Shift key to position new points at 45 degree angles. Curve Draws a smooth Bézier curve. Arc Draws an arc. Ellipse Pie Draws a filled shape that is defined by the arc of an oval and two radius lines in the current document. To draw a circle pie, hold down Shift while you drag. Circle Segment Draws a filled shape that is defined by the arc of a circle and a diameter line in the current document.
These default properties are applied only to the current document and session. They are is not retained when you close the document, and they do not apply to any other document. The defaults apply to all the drawing objects except text objects. To change the properties for an existing drawing object, select the object and continue as described above. Other tools and methods for modifying and positioning graphics are described in “Positioning graphics” below.
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 118: Layering effect Calc provides four basic options to re-arrange the order of images.
Anchoring graphics Anchors tell a graphic where to stay in relation to other items. Anchor to page Anchoring a graphic to the page allows it to be positioned in a specific place on the page. The graphic does not move when cells are added or deleted. This is equivalent to an absolute reference. The graphic will always stay by cell B10 if that is where it is placed. Anchor to cell Anchoring a graphic to a cell ensures that the graphic always stays with the content it is originally anchored to.
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 below) 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. 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.
6 Chapter Printing, Exporting, and E-mailing
Quick printing Click the Print File Directly icon printer defined for your computer. Note to send the entire document to the default 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 select the Load printer settings with the document option. Controlling printing For more control over printing, use the Print dialog (File > Print or Ctrl+P).
Selecting general printing options On the General tab of the Print dialog (Figure 122), you can choose: • The printer (from the printers available) • Which sheets and pages to print, the number of copies to print, and whether to collate multiple copies (Range and copies section) Select the Properties button to display a dialog where you can choose portrait or landscape orientation, which paper tray to use, and the paper size to print on.
When printing more than 2 pages per sheet, you can choose the order in which they are printing across and down the paper. The pictures above and below show the difference. 3) In the Page sides section, select whether to print all pages or only some pages. 4) Click the Print button. Selecting sheets to print In addition to printing a full document, you can choose to print individual sheets, ranges of sheets, or a selection of a document.
Printing a selection of cells: 1) In the document, select the section of cells to print. a) Select the first cell. b) Hold down the Control key. c) Click on the additional desired cells. d) Release the Control key when all required cells are selected. 2) Choose File > Print from the menu. 3) In the Ranges and copies section of the Print dialog, select the Selected cells option. 4) Click the Print button. Caution After printing, be sure to deselect the extra sheets.
Removing a print range It may become necessary to remove a defined print range, for example if the whole sheet needs to be printed later. Choose Format > Print Ranges > Remove. This removes all defined print ranges on the sheet. After the print range is removed, the default page break lines will appear on the screen. Editing a print range At any time, you can directly edit the print range, for example to remove or resize part of the print range. Choose Format > Print Ranges > Edit.
Printing rows or columns on every page If a sheet is printed on multiple pages, you can set up certain rows or columns to repeat on each printed page. 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.
are equivalent) after the initial selection. Make sure that each group of cells is separated with a semicolon. 5) Click OK. Figure 129: Define Names dialog To print this range: 1) Choose Format > Print Ranges > Edit (Figure 126). The previously defined area now appears in the drop-down box under Print range. 2) Select the defined print range and click OK. This method can be useful to quickly change the print range without highlighting a large area of cells every time.
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. 3) Select Row Break or Column Break depending on your need. The break is now set. Row break Selecting Row Break creates a page break above the selected cell. For example, if the active cell is H15, then the break is created between rows 14 and 15. Column break Selecting Column Break creates a page break to the left of the selected cell.
The Sheet tab of the Page Style dialog, Format > Page > Sheet (Figure 130), provides the following options. Page Order You can set the order in which pages print. This is especially useful in a large document; for example, controlling the print order can save time if you have to collate the document a certain way.
Scale Use the scale features to control the number of pages the data will print on. • Reduce/Enlarge printout—scales the data in the printout either larger or smaller. For example, if a sheet would normally print out as four pages (two high and two wide), a scaling of 50% would print as one page (both width and height are halved). • Fit print range(s) on number of pages—defines exactly how many pages the printout will take up. This option will only reduce a printout, it will not enlarge it.
From here you can also set the margins, the spacing, and height for the header or footer. You can check the AutoFit height box to automatically adjust the height of the header or footer. Margin Changing the size of the left or right margin adjusts how far the header or footer is from that side of the page. Spacing Spacing affects how far above or below the sheet the header or footer will print. So, if spacing is set to 1.00", then there will be 1 inch between the header or footer and the sheet.
Figure 133: Edit contents of header or footer Areas Each area in the header or footer is independent and can have different information in it. Header You can select from several preset choices in the Header drop-down list, or specify a custom header using the buttons below the area boxes. (To format a footer, the choices are the same.) Custom header Click in the area (Left, Center, Right) that you want to customize, then use the buttons to add elements or change text attributes.
Exporting to PDF Calc can export documents to PDF (Portable Document Format). This industrystandard 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. You are asked to enter the file name and location for the PDF file, but you do not get a chance to choose a page range, the image compression, or other options.
Figure 134: General page of PDF Options dialog General section • PDF/A-1a: PDF/A is an ISO standard for long-term preservation of documents, by embedding all the information necessary for faithful reproduction (such as fonts) while forbidding other elements (including forms, security, and encryption). PDF tags are written. If you select PDF/A-1a, the forbidden elements are greyed-out (not available). • Tagged PDF: Tagged PDF contains information about the structure of the document’s contents.
• Create PDF form - Submit format: Choose the format for submitting forms from within the PDF file. This setting overrides the control’s URL property that you set in the document. There is only one common setting valid for the whole PDF document: PDF (sends the whole document), FDF (sends the control contents), HTML, and XML. Most often you will choose the PDF format.
User Interface page of PDF Options dialog On the User Interface page (Figure 136), you can choose more settings to control how a PDF viewer displays the file. Some of these choices are particularly useful when you are creating a PDF to be used as a presentation or a kiosk-type display. Figure 136: 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.
Links page of PDF Options dialog On this page you can choose how links are exported to PDF. Figure 137: Links page of PDF Options dialog Export bookmarks as named destinations If you have defined Writer bookmarks, Impress or Draw slide names, or Calc sheet names, this option exports them as “named destinations” to which Web pages and PDF documents can link. Convert document references to PDF targets If you have defined links to other documents with OpenDocument extensions (such as .ODT, .ODS, and .
Figure 138: Security page of PDF Options dialog. Figure 139: Setting a password to encrypt a PDF 156 OpenOffice.org 3.
Exporting to XHTML Calc can export spreadsheets to XHTML. Choose File > Export. On the Export dialog, specify a file name for the exported document, then select the XHTML in the File format list and click the Export button. 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.
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. When you apply a digital signature to a document, a checksum is computed from the document’s content plus your personal key. The checksum and your public key are stored together with the document. When someone later opens the document on any computer with a recent version of OpenOffice.
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.
Error-checking formulas Adding up columns of data or selections of cells from a worksheet often results in errors due to omitting cells, wrongly specifying a range, or double-counting cells. It is useful to institute checks in your spreadsheets. For example, set up a spreadsheet to calculate columns of figures, and use SUM to calculate the individual column totals. You can check the result by including (in a non-printing column) a set of row totals and adding these together.
formula was shown as =B3+B4. The plus sign indicates that the contents of cells B3 and B4 are to be added together and then have the result in the cell holding the formula. All formulas build upon this concept. Other ways of entering formulas are shown in Table 7. These cell references allow formulas to use data from anywhere in the worksheet being worked on or from any other worksheet in the workbook that is opened.
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. =B8-SUM(B10:B14) Calculates B8 minus the sum of the cells B10 to B14.
Arithmetic operators The addition, subtraction, multiplication and division operators return numerical results. The Negation and Percent operators identify a 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 23 = 2*2*2.
Figure 142: Text concatenation In this example, specific pieces of the text were found in three different cells. To join these segments together, the formula also adds required spaces and punctuation enclosed within quotation marks, resulting in a formula of =B2 & " " & C2 & ", " & D2. The result is the concatenation into a date formatted in a particular sequence. Calc has a CONCATENATE function which performs the same operation.
Figure 144: Naming a cell or range of cells for inclusion in a formula Figure 145: 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. 166 OpenOffice.org 3.
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. On spreadsheets read from left to right, the upper left cell is A1. Range operator The range operator is written as colon.
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'.
Relative and absolute references References are the way that we refer to the location of a particular cell in Calc and can be either relative (to the current cell) or absolute (a fixed amount). Relative referencing An example of a relative reference will illustrate the difference between a relative reference and absolute reference using the spreadsheet from Figure 147. 1) Type the numbers 4 and 11 into cells C3 and C4 respectively of that spreadsheet. 2) Copy the formula in cell B5 to cell C5.
Absolute referencing You may want to multiply a column of numbers by a fixed amount. A column of figures might show amounts in US Dollars. To convert these amounts to Euros it is necessary to multiply each dollar amount by the exchange rate. $US10.00 would be multiplied by 0.75 to convert to Euros, in this case Eur7.50. The following example shows how to input an exchange rate and use that rate to convert amounts in a column form USD to Euros. 1) Input the exchange rate Eur:USD (0.75) in cell D1.
Cell references can be shown in four ways. Reference Explanation D1 Relative, from cell E3 it is the cell one column to the left and two rows above $D$1 Absolute, from cell E3 it is the cell D1 $D1 Partially absolute, from cell E3 it is the cell in column D and two rows above D$1 Partially absolute, from cell E3 it is the cell one column to the left and in row 1 Hint To change references in formulas highlight the cell and press Shift-F4 to cycle through the four different types of references.
Calculations linking sheets Another powerful feature of Calc is the ability to link data through several worksheets. The naming of worksheets can be helpful to 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.
Sheet containing data for Branch 3. Sheet containing combined data for all branches. Figure 149: 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. Right-click on the tab again and select Move/Copy Sheet.
Figure 150: 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.
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 nonformatted rows. Figure 153: 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. The Function Wizard can also be used to accomplish the linking.
Each function has a number of arguments used in the calculations. These arguments may or may not have their own name. Your task is to enter the arguments needed to run the function. In some cases, the arguments have predefined choices, and you may need to refer to the online help or Appendix B (Description of Functions) in this book to understand them. More often, however, an argument is a value that you enter manually, or one already entered in a cell or range of cells on the spreadsheet.
Nested functions Functions can also be used as arguments within other functions. These are called nested functions. =SUM(2;PRODUCT(5;7)) To get an idea of what nested functions can do, imagine that you are designing a selfdirected 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).
Figure 154: Function List docked to right side of Calc window Function Wizard The most commonly used input method is the Function Wizard (Figure 155). 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 156: 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 157: 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 158: 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.
For ease of presentation, it is good practice to set up a spreadsheet in a manner similar to that shown in Figure 159. In this example, the individual variables are input into cells on the sheet and no editing of the formula (in cell B9) is required. Figure 159: Setting up a formula with arguments You can take several broad approaches when creating a formula.
Break formulas into parts and combine the parts The second strategy is similar to the first, but instead you break down longer formulas into smaller parts and then combine the parts into the whole. Many examples of this type exist in complex scientific and engineering calculations where interim results are used in a number of places in the worksheet.
NAME? (525) No valid reference exists for the argument. REF (525) The column, row, or sheet for the referenced cell is missing. VALUE (519) The value for one of the arguments is not the type that the argument requires. The value may be entered incorrectly; for example, double-quotation marks may be missing around the value. At other times, a cell or range used may have the wrong format, such as text instead of numbers. 509 An operator such as an equals sign is missing from the formula.
Figure 161: 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.
To use the Detective, select a cell with a formula, then start the Detective. On the spreadsheet, you will see lines ending in circles to indicate precedents, and lines ending in arrows for dependents. The lines show the flow of information. Use the Detective to assist in following the precedents referred to in a formula in a cell. By tracing these precedents, you frequently can find the source of the 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 186 OpenOffice.org 3.
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.
format so that everyone who handles a spreadsheet becomes accustomed to a standard input. Simple statistics Another common use for spreadsheet functions is to pull useful information out of a list, such as a series of test scores in a class, or a summary of earnings per quarter for a company. You can, of course, scan a list of figures if you want basic information such as the highest or lowest entry or the average.
Some of these functions overlap; for example, MIN and MAX are both covered by QUARTILE. In other cases, a custom sort or filter might give much the same result. Which you use depends on your temperament and your needs. Some might prefer to use MIN and MAX because they are easy to remember, while others might prefer QUARTILE because it is more versatile. 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.
All three of these functions require the single argument of number—the cell or number to be rounded. Used with only this argument, all three functions round to the nearest whole number, so that 46.5 would round to 47 with ROUND or ROUNDUP and 46 with ROUNDDOWN. However, if you use the optional count argument, you can specify the number of decimal places to include. For instance, if number was set to 1, then 48.65 would round to 48.7 with ROUND or ROUNDUP and to 48.6 with ROUNDDOWN.
to 2. Change the number of decimal places, and, if necessary, uncheck the Precision as shown box on the same page, and the spreadsheet will display as expected. Using regular expressions in functions A number of functions in Calc allow the use of regular expressions: SUMIF, COUNTIF, MATCH, SEARCH, LOOKUP, HLOOKUP, VLOOKUP, DCOUNT, DCOUNTA, DSUM, DPRODUCT, DMAX, DMIN, DAVERAGE, DSTDEV, DSTDEVP, DVAR, DVARP, DGET. Whether or not regular expressions are used is selected on the Tools > Options > OpenOffice.
Regular expression searches within functions are always case insensitive, irrespective of the setting of the Case sensitive checkbox on the dialog in Figure 163—so red and ROD will always be matched in the above example. This caseinsensitivity also applies to the regular expression structures ([:lower:]) and ([:upper:]), which match characters irrespective of case. Regular expressions will not work in simple comparisons. For example: A1="r.
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 the result of using complicated formulas and solutions to solve simple day to day problems. More efficient and effective solutions use the DataPilot, a tool for combining, comparing, and analyzing large amounts of data easily. 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, an intermediate or advanced user.
Practical problems and questions • To display additional values for May, June, July, and so on, you need to add extra columns; that is, you have to change the structure of the calculation sheet. This is not only somewhat inefficient from a workflow point of view, but it also raises some practical questions: How do references react if you add more columns or rows to the sum formulas? • The layout, where the timeline is displayed horizontally, is less convenient if you want to add more months.
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, choose Current selection and click OK. 3) The DataPilot dialog (Figure 167) 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 (drag and drop with the mouse) the date field into the Column Fields area.
Figure 168: 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 169), make sure Intervals and Months are selected in the Group by section, and click OK. The result is now grouped for months (Figure 170). Figure 169: Grouping on months Figure 170: DataPilot result grouped for months In this result you will recognize the beginners’ example.
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 horizontal and categories vertical or vice versa, in two mouse clicks. 3) Additional differentiating factors are immediately available. 4) Many types of evaluation are possible; for example, number or average instead of sum, accumulated values, comparisons, and so on. We will now demonstrate some of these advantages.
To transpose the table completely, just drag the Category field above the area of the displayed values, to cell C3 (see Figure 173). The result of this action is shown in Figure 174. Figure 173: Drag Category field to right of Data field Figure 174: Transposed layout of Figure 172 In contrast to the beginners’ example in Figure 165, it is now very simple to view or add different aspects of the underlying data.
Figure 175: 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 176.
Figure 176: 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. Note In the real world timekeeping requires tracking working time on individual projects.
Figure 177: One month of timekeeping for one employee Figure 178: Yearly sums for one employee 202 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 179).
Figure 180: Part of DataPilot dialog The result appears on a new sheet (Figure 181). Figure 181: The evaluation, done within seconds with DataPilot 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). 204 OpenOffice.org 3.
2) Choose Data > Group and Outline > Group . On the Grouping dialog, leave Start and End as Automatically; in the Group by section, choose Intervals and Months. Click OK. The result is now grouped into months. Figure 182: Monthly sums If you need a result with a percentage, start the DataPilot from this page. 1) Select the cell A3 (or any other single 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 (Figure 183).
Figure 184: Result with percentages To get a comparison between employees, start the DataPilot again from the output sheet: 1) 2) 3) 4) Select the cell A3 (or any other cell that contains a result of the DataPilot). Choose Data > DataPilot > Start. Double-click on Sum – hours to open the Data Field dialog. 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.
Figure 186: The DataPilot now shows accumulated values Differences and advantages These examples show an important aspect of the DataPilot. Normally you have to collect your data according to the way you want the result to be represented. This means you have to use a specific structure and you are stuck with it. The DataPilot works more like a real database. The source data are collected in a simple spreadsheet that contains all data.
Figure 187: Raw data for the frequency of the messages To import the data into a table in Calc, choose Insert > Sheet From File and select the text file users.txt containing the raw data. The Text Import dialog (Figure 188) 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 208 1: 2: 3: 4: Hide Date (DMY) Standard Hide OpenOffice.org 3.
Figure 188: Import settings Figure 189 shows the imported raw data with a row added at the top containing titles for each column.
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 190: FREQUENCY function in a matrix formula 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.
Figure 191: Part of DataPilot dialog 1) Select the cell A1 (or any other cell within the list). 2) Choose Data > DataPilot > Start and click OK. 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.
Note This may be a very time-consuming process because of the large number of items. The time does not depend that much on the number of lines but rather on the number of rows needed for the table that contains the results. 8) For grouping the rows, select the cell A4 or any other cell that contains a time. 9) Choose Data > Group and Outline > Group, select for the interval Hours and click OK. The result is now grouped according to hours as shown in Figure 195.
Figure 194: Data Field settings for relative values Figure 195: Frequency distribution with the DataPilot Chapter 8 Using the DataPilot Figure 196: Relative occurrence 213
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.
2) In the Sales list, instead of only one column for the amount, you made a column for the amounts for each employee. The amounts then had to be entered into the appropriate column. An analysis with the DataPilot would not be possible any more. In contrast, one result of the DataPilot is that you can get results for each employee if you have entered everything in one column. 3) You entered the amounts in chronological order. At the end of each month you made a sum total.
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. For example, a huge list can be copied from a different application and pasted into Calc. The behavior of Calc while inserting the data depends on the format of the data. If the data is in a common spreadsheet format, it is copied directly into Calc.
Figure 198: 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.
Selection from Shows the range of cells used in 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 202: 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. Total columns / total rows With this option you decide if the DataPilot will show an extra row with the sums of each column or if it adds on the very right a column with the sums of each row.
Figure 203: Expanded dialog for a data field In the Displayed value section, you can choose other possibilities for analysis by using the aggregate function. Depending on the setting for Type, you may have to choose definitions for Base field and Base element. The table below lists the possible types of displayed value and associated base field and element, together with a note on usage.
Type Base field Base element Analysis % 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.
Figure 205: Division of the regions for employees (two row fields) without partial sums Figure 206: Division of the regions for employees with partial sums (by region) 222 OpenOffice.org 3.
Choose the option Automatically to use the aggregate function for the partial results that can also be used for the data fields (see above). To set up the aggregate function for the partial results independently from the overall settings of the DataPilot, choose User-defined. Figure 207: 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.
Figure 209: Setting “Show Items with no data” Page fields The Options dialog for page fields is the same as for row and column fields, even though it appears to be useless to have the same settings as described for the row and column fields. With the flexibility of the DataPilot you can switch the different fields between pages, columns or rows. The fields keep the settings that you made for them. 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.
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 to be grouped. This was demonstrated in all three examples in the section “Examples with step by step descriptions” starting on page 194. With the menu entry Data > Group and Outline > Group or by pressing F12, you get the following dialog.
Figure 214: Database with nonscalar categories (departments) Figure 215: DataPilot with nonscalar categories For grouping of non scalar categories, select in the result of the DataPilot all the individual field values that you want to put in the one group. Tip You can select several non-contiguous cells in one step by pressing and holding the Control key while left-clicking with the mouse.
Figure 219: Reduced to the new groups Figure 218: Renamed groups and partial results Note A well-structured database makes manual sorting within the DataPilot obsolete. In the example shown, you could add another column with the name Department, that has the correct entry for each person based on whether the employee’s department belongs to the group Office or Technical. The mapping for this (1:n relationship) can be done easily with the VLOOKUP function from Calc.
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. 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.
Drilling (showing details) Drill allows you to show the related detailed data for a single, compressed value in the DataPilot result. To activate a drill, double-click on the cell or choose Data > Group and Outline > Show Details. You have to distinguish two cases: 1) The active cell is the category of a row or column field. In this case drill means an additional breakdown into the categories of another field. For example, double-click on the cell with the value golfing in the row field region.
To hide the details again, double-click on the cell golfing or choose Data > Group and Outline > Hide Details. The DataPilot remembers your selection (in our example the field employee), so that for the next drill down for a category in the field region the dialog does not appear. To remove the selection employee, open the DataPilot dialog by right-clicking and choosing Start, then delete the unwanted selection in the row or column field. 2) The active cell is a value of the data field.
Figure 227: Dialog for defining the filter Note Even if they are not called a filter, page fields are a practical way to filter the results. The advantage is that the filtering criteria used are clearly visible. Updating (refreshing) changed values After you have created the DataPilot, changes in the source data do not cause an automatic update in the resulting table. You have to always update (refresh) the DataPilot manually after changing any of the underlying data values.
You could, for example, list all the sales values per day and additionally give the number of entries per day. To do this, put both the sales field and the date field into the Data Fields area. For the date field, choose the Count option for the aggregate function (see Figure 228). Since every entry has a specific date, this field will give you the number of entries for each date.
When using multiple data fields, the DataPilot result area contains a field called Data to allow for manipulating the existing data fields. You can move this field just like any other row or column field by using drag and drop. This is an easy way to achieve different structures for the results (see Figures 230 and 231; drag and drop the Data field onto the date field label, or onto the category field label).
Figure 233: 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 234: Multiple analyses for the same data field Unfortunately, Calc does not allow you to use the same data field multiple times.
Figure 235: 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.
Figure 236: Formula reference to a cell of the DataPilot If the underlying data or the layout of the DataPilot changes, then you must take into account that the sales value for Hans might appear in a different cell. Your formula still references the cell C6 and therefore uses a wrong value. The correct value is in a different location. For example, in Figure 237, the location is now C7. Figure 237: The value that you really want to use can be found now in a different location.
First syntax variation The target field to specify which data field of the DataPilot is used within the function. If your DataPilot has only one data field, this entry is ignored, but you must enter it anyway. If your DataPilot has more than one data field, then you have to enter the field name from the underlying data source (for example “sales value”) or the field name of the data field itself (for example “sum – sales value”). The argument DataPilot specifies the DataPilot that you want to use.
Figure 239: Second syntax variation Chapter 8 Using the DataPilot 239
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.
Figure 240: Defining the data to be consolidated 7) Specify where you want to display the result by selecting a target range from the Copy results to drop-down list. If the target range is not named, click in the field next to Copy results to and enter the reference of the target range or select the range using the mouse or position the cursor in the top left cell of the target range.
• Under Consolidate by, select either Row labels or Column labels if the cells of the source data range are not to be consolidated corresponding to the identical position of the cell in the range, but instead according to a matching row label or column label. To consolidate by row labels or column labels, the label must be contained in the selected source ranges. The text in the labels must be identical, so that rows or columns can be accurately matched.
Figure 241: Setting up subtotals Figure 242: Choosing options for subtotals 244 OpenOffice.org 3.
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.
4) Optionally add some information to the Comment box. The example shows the default comment. This information is displayed in the Navigator when you click the Scenarios icon and select the desired scenario. 5) Optionally select or deselect the options in the Settings section. See page 246 for more information about these options. 6) Click OK to close the dialog. The new scenario is automatically activated. You can create several scenarios for any given range of cells.
The extent to which either of these aspects can be changed is dependent upon both the existing properties of the scenario and the current protection state of the sheet and cells. Changing scenario properties If the sheet is protected (Tools > Protect Document > Sheet), and Prevent changes is selected then scenario properties cannot be changed.
Working with scenarios using the Navigator After scenarios are added to a spreadsheet, you can jump to a particular scenario by selecting it from the list in the Navigator. Click the Scenarios icon in the Navigator. The defined scenarios are listed, along with the comments that were entered when the scenarios were created. To apply a scenario to the current sheet, double-click the scenario name in the Navigator. To delete a scenario, right-click the name in the Navigator and choose Delete.
Using other “what if” tools Like scenarios, Data > Multiple Operations is a planning tool for “what if” questions. Unlike a scenario, the Multiple Operations tool does not present the alternate versions in the same cells or with a drop-down list. Instead, the Multiple Operations tool creates a formula array: a separate set of cells showing the results of applying the formula to a list of alternative values for the variables used by the formula.
Note Before you choose the Data > Multiple Operations option, be sure to select not only your list of alternative values but also the adjacent cells into which the results should be placed. In the Formulas field of the Multiple Operations dialog, enter the cell reference to the formula that you wish to use. The arrangement of your alternative values dictates how you should complete the rest of the dialog. If you have listed them in a single column, you should complete the field for Column input cell.
Figure 245: Sheet and Multiple operations dialog showing input Figure 246: 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. 6) Set the cursor in the Column input cell field and click cell B4. Figure 247 shows the worksheet and the Multiple operations dialog. Figure 247: 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 248: Results of multiple operations calculations 252 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.
4) Set the cursor in the Row input cell field and click cell B1. This means that B1, the selling price, is the horizontally entered variable (with the values 8, 10, 15 and 20). 5) Set the cursor in the Column input cell field and click cell B4. This means that B4, the quantity, is the vertically entered variable. 6) Click OK. The profits for the different selling prices are now shown in the range E2:H11.
Enter each of the values mentioned above into adjacent cells (for Capital, C, an arbitrary value like $100,000 or it can be left blank; for number of years, n, 1; for interest rate, i, 7.5%). Enter the formula to calculate the interest, I, in another cell. Instead of C, n, and i, use the reference to the cell with the corresponding value. In our example (Figure 249), this would be =B1*B2*B3. 1) Place the cursor in the formula cell (B4), and choose Tools > Goal Seek.
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. It is specifically designed to minimize or maximize the result according to a set of rules that you define. Each of these rules defines whether an argument in the formula should be greater than, less than, or equal to the figure you enter.
Figure 253: Example setup for Solver 4) Choose Tools > Solver. The Solver dialog opens. Figure 254: 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.
9) Click OK. A dialog appears informing you that the Solving successfully finished. Click Keep Result to enter the result in the cell with the variable value. The result is shown in Figure 255. Figure 255: Result of Solver operation Note 258 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.
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.
Note If you want to save the spreadsheet to Microsoft Excel format, the following characters are not allowed in sheet names: \ / ? * [ ] : and ' as the first or last character of the name. 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.
To insert just one sheet, choose whether before or after then currently selected sheet, give it a new name if desired, and click OK. The new sheet will be selected and visible in the line of sheet tabs. In our example, 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.
Figure 260: Checking ledger Inserting sheets from a different spreadsheet On the Insert Sheet dialog, you can also add a sheet from a different spreadsheet file (for example, another Calc or Excel spreadsheet), by choosing the From file option. Click Browse and select the file; a list of the available sheets appears in the box. Select the sheet to import. If, after you select the file, no sheets appear you probably selected an invalid file type (not a spreadsheet, for example).
Balance column. We want to place the reference for the checking account balance in cell B3. Figure 261: Blank summary To make the cell reference in cell B3, select the cell and follow these steps: 1) Click on the = icon next to the input line. The icons change and an equals sign appears in the input line as in Figure 262. Figure 262: Equal sign in input line 2) Now, click on the sheet tab for the sheet containing the cell to be referenced.
Figure 265: Finished checking account reference Creating the reference with the keyboard From Figure 265, 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.
Referencing other documents: links to sheets in other spreadsheets John decides to keep his family account information in a different spreadsheet file from his own summary. Fortunately Calc can link different files together. The process is the same as described for different sheets in a single spreadsheet, but we add one more step to indicate which file the sheet is in. For this example we use two different spreadsheet files.
Creating the reference with the keyboard Typing the reference is simple once you know the format the reference takes. The reference has three parts: • Path and file name • Sheet name • Cell Looking at Figure 268 you can see the the general format for the reference is =’file:///Path & File Name’#$SheetName.CellName Note The reference for a file has three forward slashes /// and the reference for a hyperlink has two forward slashes //.
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.
For a Mail and News hyperlink, specify whether it is a mail or news link, the receiver address and for email, also the subject. For a Document hyperlink, specify the document path (the Open File button opens a file browser); leave this blank if you want to link to a target in the same spreadsheet. Optionally specify the target in the document (for example a specific sheet).
Editing hyperlinks To edit an existing link, place the cursor anywhere in the link and click the Hyperlink icon on the Standard toolbar or select Edit > Hyperlink from the menu bar. The Hyperlink dialog (Figure 269) opens. If the Hyperlink is in button form, the spreadsheet must have Design Mode on in order to edit the Hyperlink. Make your changes and click Apply. If you need to edit several hyperlinks, you can leave the Hyperlink dialog open until you have edited all of them.
Figure 271: Selecting a table or range in a source document from the Web 1) The Available tables/ranges list remains empty until you press Enter after typing the URL of the source. If you select the source document using the […] button, then pressing Enter is not required. Notes 2) The OK button remains unavailable (grayed out) until you select one or more tables/ranges in the list. You can hold down the Ctrl key while clicking on tables/ranges to select more than one. 3) No images are imported.
Figure 272: Opening a file using the Web Page Query filter 6) In the Navigator, select the Insert as Link drag mode, as shown in Figure 273. 7) Select the required range or table and drag it from the Navigator into the target document, to the cell where you want the upper left-hand cell of the data range to be. Figure 273: Selecting a data range in a source document, to be inserted as a link 8) In the target document, check the Navigator.
Figure 274: 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 ranges (tables) in the sample web page starting from HTML_1.
Figure 276: Using the Navigator to find a data range name 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 *.
Figure 277: Registering databases To register a data source that is not in *.odb format: 1) Choose File > New > Database to open the Database Wizard. 2) Select Connect to an existing database. The choice of database type depends on your operating system. For example, Microsoft Access and other Microsoft products are not available choices if you are using Linux. In our example, we chose dBASE. See Figure 278.
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. Name and save the database in the location of your choice. Note: changes made to the *.odb do not affect the original dBASE file.
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. Using data sources in Calc spreadsheets Data from tables in the data source pane can be placed into Calc documents in a variety of ways.
7) Click the arrows on the Form Navigation toolbar to view the different records of the table. The number in the Record box changes as you move through the records. The data in the fields changes to correspond to the data for that particular record number. You can also search for a specific record, sort and filter records, and do other tasks using this toolbar. Embedding spreadsheets Spreadsheets can be embedded in other OOo files.
To create a new object: 1) Select Create new and select the object type from the available options. Note “Further objects” is only available if you are using the Windows operating system. 2) Click OK. An empty container is placed in the sheet. 3) Double-click on the OLE object to enter the edit mode of the object. The application devoted to handling that type of file will open the object. Note If the object inserted is handled by OpenOffice.
Figure 283: Inserting an OLE object under Windows 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.
Linked OLE object When the spreadsheet OLE object is linked, if you change it in Writer it will change in Calc; if you change it in Calc, it will change in Writer. This can be a very powerful tool if you create reports in Writer using Calc data, and want to make a quick change without opening Calc. Note You can only edit one copy of a spreadsheet at a time.
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 286). 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.
in unshared mode, you need to save the shared document using another name or path. This creates a copy of the spreadsheet that is not shared. Opening a shared spreadsheet When you open a spreadsheet document that is in shared mode, a message appears stating that the document is in shared mode and that some features are not available in this mode. After clicking OK, the document is opened in shared mode.
– If the changes conflict, the Resolve Conflicts dialog is shown. You must decide for each conflict which version to keep, yours or the other person’s. When all conflicts are resolved, the document is saved. While you are resolving the conflicts, no other user can save the shared document. Figure 289: Resolve Conflicts dialog • If another user is trying to save the shared document and resolve conflicts, you see a message that the shared spreadsheet file is locked due to a merge-in in progress.
Preparing a document for review (optional) When you send a document to someone else to review or edit, you may want to protect it first so that the editor or reviewer does not have to remember to turn on the revision marks. After you have protected the document, any user must enter the correct password in order to turn off protection and accept or reject changes.
Looking over the figures, you see a few places where money could be saved: • • • • Post-game snacks can be bought by parents. New uniforms can wait; only buy 10 to replace damaged ones. 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.
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 292 appears. The automatically-added comment provided by Calc appears in the title bar of this dialog and cannot be edited. 4) Type your own 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. You do not need to click OK after editing each comment; you can save them all at once when you’re done editing. Adding other comments Calc provides another type of comment (formerly called “note”), which authors and reviewers often use to exchange ideas, ask for suggestions, or brainstorm in the document. To add a comment: 1) Select the cell that the comment applies to.
Figure 296: Viewing a comment Editing comments You can edit and format the text of a comment, just as you do for any other text. 1) Right-click on the cell containing the comment marker, and choose Show comment from the pop-up menu. 2) Select the comment, then double-click on it. The cursor changes to the usual blinking text-entry cursor, and the Formatting toolbar changes to show text attributes. 3) When done, click outside the comment to deselect it.
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.
Accepting or rejecting changes When you receive a document back with changes, the beauty of the recording changes system becomes evident. Now, as the original author, you can step through each change and decide how to proceed. To begin this process: 1) Open the edited document. 2) Select Edit > Changes > Accept or Reject. The dialog shown in Figure 289 appears. 3) Click Accept or Reject to step through the changes one at a time. You can also choose to accept or reject all changes in one step.
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. You could review each document and the changes separately, but to save time, you want to see both of their revisions simultaneously. The processes discussed to this point are effective when you have one reviewer at a time.
Figure 300: Merged documents with different author colors 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.
To use version management in Calc: 1) Choose File > Versions. The Versions dialog opens. Figure 301: 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 302: Version comment dialog 4) After you enter your comment and click OK, the new version is included in the version list. Figure 303: Updated version list 296 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 readonly copy 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.
Figure 306: Paste Special dialog Figure 307: Cells multiplied by 3 9) Select the current document (see Figure 308). For this example, the current Calc document is Untitled 1. Existing documents show a library named Standard. This library is not created until the document is saved or the library is needed, so at this point your new document does not contain a library. You can create a new library to contain the macro, but this is not necessary. 300 OpenOffice.org 3.
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 308: 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 309: 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 311: OpenOffice.org Basic Macro Organizer 4) Click New to open the New Library dialog. Figure 312: 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 313: The library is shown in the organizer 6) Select AuthorsCalcMacros and click Edit to edit the library.
Figure 314: 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. If Option Explicit is omitted, variables are automatically defined at first use as type Variant. 8) Save the modified Module1. Listing 2. Function that returns five.
Figure 315: Use the NumberFive() Macro as a Calc function Tip Function names are not case sensitive. In Figure 315, 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.org > Security > Macro Security, Calc will display the warning shown in Figure 316 or the one shown in Figure 317.
If you choose to disable macros, then when the document loads, Calc can no longer find the function. Figure 318: 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. Libraries in OpenOffice.
2) Change the name of NumberFive to NumberFive_Implementation (Listing 3). Listing 3. Change the name of NumberFive to NumberFive_Implementation Function NumberFive_Implementation() NumberFive_Implementation() = 5 End Function 3) In the Basic IDE (see Figure 312), hover the mouse cursor over the toolbar buttons to display the tool tips. Click the Select Macro button to open the OpenOffice.org Basic Macros dialog (see Figure 308).
The macro in Listing 5 demonstrates some important techniques: 1) The argument x is optional. When an argument is not optional and the function is called without it, OOo prints a warning message every time the macro is called. If Calc calls the function many times, then the error is displayed many times. 2) IsMissing checks that an argument was passed before the argument is used. 3) IsArray checks to see if the argument is a single value, or an array. For example, =PositiveSum(7) or =PositiveSum(A4).
Accessing cells directly You can access the OOo internal objects directly to manipulate a Calc document. For example, the macro in Listing 7 adds the values in cell A2 from every sheet in the current document. ThisComponent is set by StarBasic when the macro starts to reference the current document. A Calc document contains sheets: ThisComponent.getSheets(). Use getCellByPosition(col, row) to return a cell at a specific row and column. Listing 7. Add cell A2 in every sheet.
For iRow = LBound(oRows()) To UBound(oRows()) oRow() = oRows(iRow) For iCol = LBound(oRow()) To UBound(oRow()) TheSum = TheSum + oRow(iCol) Next Next Next SumCellsAllSheets = TheSum End Function When a macro is called as a Calc function, the macro cannot modify any value in the sheet from which the macro was called. Tip Sorting Consider sorting the data in Figure 320. First, sort on column B descending and then column A ascending.
REM Select the range to sort. REM The only purpose would be to emphasize the sorted data. 'ThisComponent.getCurrentController.select(oCellRange) REM The columns are numbered starting with 0, so REM column A is 0, column B is 1, etc. REM Sort column B (column 1) descending. oSortFields(0).Field = 1 oSortFields(0).SortAscending = FALSE REM If column B has two cells with the same value, REM then use column A ascending to decide the order. oSortFields(1).Field = 0 oSortFields(1).
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.
Tip Although the choice to associate a row to a record rather than a column is arbitrary, it is almost universal. In other words, you are not likely to hear someone refer to a column of data as a single database record. Associating a range with a name In a Calc document, a range refers to a contiguous group of cells containing at least one cell. You can associate a meaningful name to a range, which allows you to refer to the range using the meaningful name.
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. The method getElementNames() returns an array containing the names of all named ranges. The NamedRanges object supports the method addNewByname, which accepts four arguments; the name, content, position, and type.
The method addNewByname() accepts four arguments; the name, content, position, and type. The fourth argument to the method addNewByName() is a combination of flags that specify how the named range will be used (see Table 13). The most common value is 0, which is not a defined constant value. Table 13. com.sun.star.sheet.NamedRangeFlag constants. Value Name Description 1 FILTER_CRITERIA The range contains filter criteria. 2 PRINT_AREA The range can be used as a print range.
Select the range containing the headers and the data and then use Insert > Names > Create to open the Create Names dialog (see Figure 322), which allows you to simultaneously create multiple named ranges based on the top row, bottom row, right column or left column. If you choose to create ranges based on the top row, one named range is created for each column header—the header is not included in the named range.
Caution It is possible to create multiple named ranges with the same name. Creating multiple ranges with a single command increases the likelihood that multiple ranges will be created with the same name— avoid this if possible. 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.
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 325) 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 324 displays the columns using the column headers if the Range contains column labels checkbox in Figure 325 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.
Filters Use filters to limit the visible rows in a spreadsheet. Generic filters, common to all sorts of data manipulations, are automatically provided by the auto filter capability. You can also define your own filters. Caution After applying a filter, some rows are visible and some rows are not. If you select multiple rows in one operation, you will also select the invisible rows contained between the selected visible rows. Operations, such as delete, act on all of the selected rows.
Figure 326: Use an auto filter with column C Remove an auto filter by repeating the steps to create the auto filter—in other words, the menu option acts as a toggle to turn the auto filter on and off. When an auto filter is removed, the combo box is removed from the cell. The macro in Listing 13 demonstrates creating an auto filter for a range. Standard filters Use Data > Filter > Standard Filter to open Standard Filter dialog (see Figure 327) and limit the view based on 1 to 3 filter conditions.
Figure 327: 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. 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.TableFilterField oSheet = ThisComponent.getSheets().getByIndex(0) oRange = oSheet.getCellRangeByName("E12:G19") REM If argument is True, creates an REM empty filter descriptor. oFilterDesc = oRange.
2) Duplicate the column headings from the area to be filtered into the area that will contain the filter criteria. 3) Enter the filter criteria underneath the column headings (see Table 24). The criterion in each column of a row is connected with AND. The criteria from each row are connected with OR. Table 15.
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.getCellRangeByName("A1:G3") REM REM REM REM REM You can also obtain the range containing the filter criteria from a named range. oRanges = ThisComponent.NamedRanges oRange = oRanges.getByName("AverageLess80") oCritRange = oRange.
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. CopyOutputData Boolean that specifies if the filtered data should be copied to another position in the document. IsCaseSensitive Boolean that specifies if the case of letters is important when comparing entries. Orientation Specifies if columns (com.sun.star.table.TableOrientation.COLUMNS) or rows (com.sun.star.
Calc functions similar to database functions Although every Calc function can be used for database manipulation, the functions in Table 17 are more commonly used as such. Some functions’ names differ only by the letter appended at the end; AVERAGE and AVERAGEA, for example. Functions that do not end with the letter A operate only on numeric values and cells that contain text or are empty are ignored.
Function Description STDEV Estimate the standard deviation based on a sample. STDEVA Estimate the standard deviation based on a sample. The value of text is 0. STDEVP Calculate the standard deviation based on the entire population. STDEVPA Calculate the standard deviation based on the entire population. SUBTOTAL Calculate a specified function based on a subset created using AutoFilters. SUM Return the sum of the cells. SUMIF Calculate the sum for the cells that meet the search criteria.
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.
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.
Use VLOOKUP when: • The data is arranged in rows and you want to return data from the same row. For example, student names with test and quiz scores to the right of the student’s name. • Searching the first column of a range of data. Search a block of data using HLOOKUP Use HLOOKUP to search the first row (rows are horizontal) of a block of data and return the value from a row in the same column.
The search value and search range are the same as for LOOKUP. The final argument, search type, controls how the search is performed. A search type of 1, sorted in ascending order, is the default. A search type of -1 indicates that the list is sorted in descending order. A search type of 0 indicates that the list is not sorted. Regular expressions can only be used on an unsorted list. Use MATCH when: • You need an index into the range rather than the value.
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. The supported forms for ADDRESS are as follows: ADDRESS(row; column) ADDRESS(row; column; abs) ADDRESS(row; column; abs; sheet) The row and column are integer values where ADDRESS(1; 1) returns $A$1.
point. The second and third arguments specify the number of rows and columns to move from the reference point; in other words, where the new range starts. The OFFSET function has the following syntax: OFFSET(reference; rows; columns) OFFSET(reference; rows; columns; height) OFFSET(reference; rows; columns; height; width) Tip If the width or height is included, the OFFSET function returns a range. If both the width and height are missing, a cell reference is returned.
Table 24. Breakdown of Listing 20. Function Description MATCH("Bob";A1:A16; 0)-1 Return 3 because Bob is the fourth entry in column A. OFFSET(A1; 3; 3; 1; 2) Return the range D4:E4. SUM(D4:E4) Return the sum of Bob’s quiz scores. Tip The first argument to OFFSET can be a range so you can use a defined range name. INDEX returns cells inside a specified range INDEX returns the cells specified by a row and column number.
Tip A simple range contains one contiguous rectangular region of cells. It is possible to define a multi-range that contains multiple simple ranges. If the reference consists of multiple ranges, you must enclose the reference or range name in parentheses. If reference argument to the INDEX function is a multi-range, then the range argument specifies which simple range to use (see Table 27). Table 27. Using INDEX with a multi-range.
Function Description 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.
3) Modify the Name as required. 4) Click the Modify button. The newly defined color is now listed in the Color table. Alternatively, click the Edit button to open the Color dialog, shown in Figure 330. Here you can select a color from one of the color windows in the upper area, or you can enter values in the lower area using your choice of RGB, CMYK, or HSB (Hue, Saturation and Brightness) values.
Figure 331: Choosing security options for opening and saving documents Security options and warnings If you record changes, save multiple versions, or include hidden information or notes in your documents, and you do not want some of the recipients to see that information, you can set warnings to remind you to remove this information, or you can have OOo remove some information automatically. Note that (unless removed) much of this information is retained in a file whether the file is in OpenOffice.
Figure 332: Security options and warnings dialog Appearance options On the OpenOffice.org – Appearance page, you can specify which items are visible and the colors used to display various items. Figure 333: Changing the color of grid points in Calc Scroll down in the page until you find Spreadsheet. To change the default color for grid points, click the down-arrow by the color and select a new color from the pop-up box.
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.
features may be lost. Two notable examples are cross-references to headings and the formatting of numbered lists. If you plan to share documents with people who are still using older versions of OpenOffice.org, save the document using ODF version 1.0/1.1. Size optimization for ODF format OpenOffice.org documents are XML files. When you select this option, OOo writes the XML data without indents and line breaks.
Microsoft Office Load/Save options On the Load/Save – Microsoft Office page, you can choose what to do when importing and exporting Microsoft Office OLE objects (linked or embedded objects or documents such as spreadsheets or equations). Select the [L] options to convert Microsoft OLE objects into the corresponding OpenOffice.org OLE objects when a Microsoft document is loaded into OOo (mnemonic: “L” for “load”). Select the [S] options to convert OpenOffice.
If you want the macro to run automatically when the HTML document is opened, choose Tools > Customize > Events. See Chapter 12 (Calc Macros) for more information. Export - Display warning When the OpenOffice.org Basic option (see above) is not selected, the Display warning option becomes available. If the Display warning option is selected, then when exporting to HTML a warning is shown that OpenOffice.org Basic macros will be lost.
General options for Calc In the Options dialog, choose OpenOffice.org Calc > General. Figure 339: Selecting general options for Calc Metrics section Choose the unit of measurement used in spreadsheets and the default tab stops distance. 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.
Expand references when new columns/rows are inserted Specifies whether to expand references when inserting columns or rows adjacent to the reference range. This is only possible if the reference range, where the column or row is inserted, originally spanned at least two cells in the desired direction. Example: If the range A1:B1 is referenced in a formula and you insert a new column after column B, the reference is expanded to A1:C1.
Visual aids section Specifies which lines are displayed. Grid lines Specifies whether to display grid lines between the cells when viewed onscreen. If this option is selected, you can also specify the color for the grid lines in the current document. The color choice overrides the selection made in Tools > Options > OpenOffice.org > Appearance > Spreadsheet > Grid lines. For printing, choose Format > Page > Sheet and mark the Grid option.
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.
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.
This search: Has this result: win Finds win, but not win95, os2win, or upwind win.* Finds win and win95, but not os2win or upwind .*win Finds win and os2win, but not win95 or upwind .*win.* Finds win, win95, os2win, and upwind If this option is not selected, the win search pattern acts like .*win.* —the search pattern can be at any position within the cell when searching with the Calc database functions.
Changes options In the Options dialog, choose OpenOffice.org Calc > Changes. Figure 343: 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.
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.
Customizing menu content In addition to changing the menu font, you can add and rearrange items on the menu bar, add items to menus, and make other changes. To customize menus: 1) Choose Tools > Customize. 2) On the Customize dialog, pick the Menus page (Figure 346). Figure 346: The Menus page of the Customize dialog 3) In the Save In drop-down list, choose whether to save this changed menu for Calc or for a selected document. 4) In the section OpenOffice.
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. Use the up and down arrows next to the Entries list to move the selected menu item to a different position. 6) When you have finished making all your changes, click OK to save them. Creating a new menu In the Customize dialog, click New to display the dialog shown in Figure 347.
are easily identified in the Entries list by a small black triangle on the right hand side of the name. In addition to renaming, you can specify a keyboard shortcut that allows you to select a menu command when you press Alt+ an underlined letter in a menu command. 1) Select a menu or menu entry. 2) Click the Menu button and select Rename. 3) Add a tilde (~) in front of the letter that you want to use as an accelerator. For example, to select the Save All command by pressing Alt+V, enter Sa~ve All.
To begin, select the menu or submenu in the Menu list near the top of the Customize page, then select the entry in the Entries list under Menu Content. Click the Modify button and choose the required action from the drop-down list of actions. Most of the actions should be self-explanatory. Begin a group adds a separator line after the highlighted entry.
To customize toolbars: 1) In the Save In drop-down list, choose whether to save this changed toolbar for Calc or for a selected document. 2) In the section OpenOffice.org Calc Toolbars, select from the Toolbar dropdown list the toolbar that you want to customize. 3) You can create a new toolbar by clicking on the New button, or customize existing toolbars by clicking on the Toolbar or Modify buttons, and add commands to a toolbar by clicking on the Add button. These actions are described below.
To choose an icon for a command, select the command and click Modify > Change icon. On the Change Icon dialog (Figure 350), scroll through the available icons, select one, and click OK to assign it to the command. To use a custom icon, create it in a graphics program and import it into OOo by clicking the Import button on the Change Icon dialog. Custom icons must be 16 x 16 or 26 x 26 pixels in size and cannot contain more than 256 colors.
4) Now select the desired shortcut keys in the Shortcut keys list and click the Modify button at the upper right. 5) Click OK to accept the change. Now the chosen shortcut keys will execute the function chosen in step 3 above whenever they are pressed. 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.
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. 4) To assign Ctrl+3 to be the shortcut key combination for the Heading style, select Heading in the Function list, and then click Modify. Ctrl+3 now appears in the Keys list on the right, and Heading appears next to Ctrl+3 in the Shortcut keys box at the top.
Some extensions are free of charge; others are available for a fee. Check the descriptions to see what licenses and fees apply to the ones that interest you. Installing extensions To install an extension, follow these steps: 1) Download an extension and save it anywhere on your computer. 2) In OOo, select Tools > Extension Manager from the menu bar. In the Extension Manager dialog (Figure 352), click Add. 3) A file browser window opens. Find and select the extension you want to install and click Open.
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. Professional 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. Solver for Nonlinear Programming Calc ships with a solver engine for linear programming only.
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+Page Down Moves one sheet to the right. In the page preview it moves to the next print page. Page Up Moves the viewable rows up one screen. Page Down Moves the viewable rows down one screen. Alt+Page Up Moves the viewable columns one screen to the left. Alt+Page Down Moves the viewable columns one screen to the right. Shift+Ctrl+Page Up Adds the previous sheet to the current selection of sheets.
Shortcut Keys Effect F4 Shows or hides the Database Sources menu. Shift+F4 Rearranges the relative or absolute references (for example, A1, $A$1, $A1, A$1) in the input field. F5 Shows or hides the Navigator. Shift+F5 Traces dependents. Shift+Ctrl+F5 Moves the cursor to the Name box. F7 Checks spelling in the current sheet. Ctrl+F7 Opens the Thesaurus if the current cell contains text. Shift+F7 Traces precedents. F8 Turns additional selection mode on or off.
Cell formatting shortcuts Ctrl+1 open the Format Cells dialog. Note The shortcuts listed in Table 29 do not work under any Linux desktop tested. If you would like to have the functionality described in this table, you must create custom key sequences as explained in Chapter 14 (Setting Up and Customizing Calc). Note The shortcut keys shown in Table 29 do not use the number keys on the number pad. They use the number keys above the letter keys on the main keyboard.
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.
Syntax Description 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. ASIN(number) Returns the inverse sine of the given number in radians. ASINH(number) Returns the inverse hyperbolic sine of the given number in radians. ATAN(number) Returns the inverse tangent of the given number in radians. ATAN2(number_x; number_y) Returns the inverse tangent of the specified x and y coordinates.
Syntax Description COUNTBLANK(range) Returns the number of empty cells. Range is the cell range in which the empty cells are counted. COUNTIF(range; criteria) Returns the number of elements that meet certain criteria within a cell range. Range is the range to which the criteria are to be applied. Criteria indicates the criteria in the form of a number, a regular expression, or a character string by which the cells are counted. DEGREES(number) Converts the given number in radians to degrees.
Syntax Description LOG(number; base) Returns the logarithm of the given number to the specified base. Base is the base for the logarithm calculation. LOG10(number) Returns the base-10 logarithm of the given number. MOD(dividend; divisor) Returns the remainder after a number is divided by a divisor. Dividend is the number which will be divided by the divisor. Divisor is the number by which to divide the dividend. MROUND(number; multiple) The result is the nearest integer multiple of the number.
Syntax Description ROUNDDOWN(number; count) Rounds the given number. Count (optional) is the number of digits to be rounded down to. If the count parameter is negative, only the whole number portion is rounded. It is rounded to the place indicated by the count. 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.
Syntax Description SUMSQ(number_1; number_2; ... number_30) 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. TAN(number) Returns the tangent of the given number (angle in radians). TANH(number) Returns the hyperbolic tangent of the given number (angle in radians).
Table 35: Basis calculation types Basis Calculation 0 or missing US method (NASD), 12 months of 30 days each. 1 Exact number of days in months, exact number of days in year. 2 Exact number of days in month, year has 360 days. 3 Exact number of days in month, year has 365 days. 4 European method, 12 months of 30 days each.
Syntax Description AMORLINC(cost; date_purchased; first_period; salvage; period; rate; basis) Calculates the amount of depreciation for a settlement period as linear amortization. If the capital asset is purchased during the settlement period, the proportional amount of depreciation is considered. Cost is the acquisition cost. Date_purchased is the date of acquisition. First_period is the end date of the first settlement period.
Syntax Description COUPPCD(settlement; maturity; frequency; basis) Returns the date of the interest date prior to 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 DB(cost; salvage; life; period; month) Returns the depreciation of an asset for a specified period using the double-declining balance method. Cost is the initial cost of an asset. Salvage is the value of an asset at the end of the depreciation. Life defines the period over which an asset is depreciated. Period is the length of each period. The life must be entered in the same date unit as the depreciation period.
Syntax Description DURATION_ADD (settlement; maturity; coupon; yield; frequency; basis) Calculates the duration of a fixed interest 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 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. Period=NPER, if compound interest for the last period is calculated. PV is the present cash value in sequence of payments.
Syntax Description NOMINAL_ADD(effective_rate ; Npery) Calculates the yearly nominal rate of interest, given the effective rate and the number of compounding periods per year. Effective_rate is the effective annual rate of interest. Npery is the number of interest payments per year. NPER(rate; PMT; PV; FV; type) Returns the number of periods for an investment based on periodic, constant payments and a constant interest rate. Rate is the periodic interest rate.
Syntax Description ODDLYIELD(settlement; maturity; last_interest; rate; price; redemption; frequency; basis) Calculates the yield of a security if the last interest date falls irregularly. Settlement is the date of purchase of the security. Maturity is the date on which the security matures (expires). Last_interest is the last interest date of the security. Rate is the annual rate of interest. Price is the price of the security. Redemption is the redemption value per 100 currency units of par value.
Syntax Description PRICEMAT(settlement; maturity; issue; rate; yield; basis) Calculates the price per 100 currency units of par value of a security, that pays interest on the maturity date. Settlement is the date of 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.
Syntax Description SYD(cost; salvage; life; period) Returns the arithmetic-declining depreciation rate. Use this function to calculate the depreciation amount for one period of the total depreciation span of an object. Arithmetic declining depreciation reduces the depreciation amount from period to period by a fixed sum. Cost is the initial cost of an asset. Salvage is the value of an asset after depreciation. Life is the period fixing the time span over which an asset is depreciated.
Syntax Description XNPV(rate; values; dates) Calculates the capital value (net present value) for a list of payments which take place on different dates. The calculation is based on a 365 days per year basis, ignoring leap years. If the payments take place at regular intervals, use the NPV function. Rate is the internal rate of return for the payments. Values and dates are a series of payments and the series of associated date values entered as cell references.
Statistical analysis functions Calc includes over 70 statistical functions which enable the evaluation of data from simple arithmetic calculations, such as averaging, to advanced distribution and probability computations. Several other statistics-based functions are available through the Add-ins which are noted at the end of this appendix. Table 37: Statistical analysis functions Syntax Description AVEDEV(number1; number2; ...
Syntax Description CHIDIST(number; degrees_freedom) Returns the probability value that a hypothesis will be confirmed from the indicated chi square. The probability determined by CHIDIST can also be determined by CHITEST. Number is the chi-square value of the random sample used to determine the error probability. Degrees_freedom is the degrees of freedom of the experiment. CHIINV(number; degrees_freedom) Returns the inverse of the one-tailed probability of the chi-squared distribution.
Syntax Description DEVSQ(number_1; number_2; ... number_30) Returns the sum of squares of deviations based on a sample mean. Number_1; number_2; ... number_30 are numerical values or ranges representing a sample. EXPONDIST(number; lambda; C) Returns the exponential distribution. Number is the value of the function. Lambda is the parameter value. C is a logical value that determines the form of the function. C = 0 calculates the density function, and C = 1 calculates the distribution.
Syntax Description 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 LOGNORMDIST(number; mean; STDEV) Returns the cumulative lognormal distribution for the given Number, a probability value. Mean is the mean value of the standard logarithmic distribution. STDEV is the standard deviation of the standard logarithmic distribution. MAX(number_1; number_2; ... number_30) Returns the maximum value in a list of arguments. Number_1; number_2; ... number_30 are numerical values or ranges. MAXA(value_1; value_2; ...
Syntax Description PEARSON(data_1; data_2) Returns the Pearson product moment correlation coefficient r. Data_1 is the array of the first data set. Data_2 is the array of the second data set. PERCENTILE(data; alpha) Returns the alpha-percentile of data values in an array. Data is the array of data. Alpha is the percentage of the scale between 0 and 1. PERCENTRANK(data; value) Returns the percentage rank (percentile) of the given value in a sample. Data is the array of data in the sample.
Syntax Description SLOPE(data_Y; data_X) Returns the slope of the linear regression line. Data_Y is the array or matrix of Y data. Data_X is the array or matrix of X data. SMALL(data; rank_c) Returns the Rank_c-th smallest value in a data set. Data is the cell range of data. Rank_c is the rank of the value (2nd smallest, 3rd smallest, etc.) written as an integer. STANDARDIZE(number; mean; STDEV) Converts a random variable to a normalized value. Number is the value to be standardized.
Syntax Description TTEST(data_1; data_2; mode; type) Returns the probability associated with a Student’s tTest. Data_1 is the dependent array or range of data for the first record. Data_2 is the dependent array or range of data for the second record. Mode = 1 calculates the one-tailed test, Mode = 2 the twotailed test. Type of t-test to perform: paired (1), equal variance (homoscedastic) (2), or unequal variance (heteroscedastic) (3). VAR(number_1; number_2; ...
Date and time functions Use these functions for inserting, editing, and manipulating dates and times. OpenOffice.org handles and computes a date/time value as a number. When you assign the number format “Number” to a date or 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.
Syntax Description 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. EOMONTH(start_date; months) Returns the date of the last day of a month which falls Months away from the given Start_date. Months is the number of months before (negative) or after (positive) the start date. HOUR(number) Returns the hour, as an integer, for the given time value.
Syntax Description TODAY() Returns the current computer system date. The value is updated when your document recalculates. TODAY is a function without arguments. WEEKDAY(number; type) Returns the day of the week for the given number (date value). The day is returned as an integer based on the type.
Logical functions Use the logical functions to test values and produce results based on the result of the test. These functions are conditional and provide the ability to write longer formulas based on input or output. Table 39: Logical functions Syntax Description AND(logical_value_1; logical_value_2; ...logical_value_30) Returns TRUE if all arguments are TRUE. If any element is FALSE, this function returns the FALSE value. Logical_value_1; logical_value_2; ...
Informational functions These functions provide information (or feedback) regarding the results of a test for a specific condition, or a test for the type of data or content a cell contains. Table 40: Informational functions Syntax Description CELL(info_type; reference) Returns information on a cell such as its address, formatting or contents of a cell based on the value of the info_type argument. Info_type specifies the type of information to be returned and comes from a predefined list of arguments.
Syntax Description ISLOGICAL(value) Returns TRUE if the cell contains a logical number format. The function is used in order to check for both TRUE and FALSE values in certain cells. If an error occurs, the function returns a logical or numerical value. Value is the value to be tested for logical number format. 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.
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 DMAX(database; database_field; search_criteria) Returns the maximum content of a cell (field) in a database (all records) that matches the specified search_criteria. The search supports regular expressions. DMIN(database; database_field; search_criteria) Returns the minimum content of a cell (field) in a database that matches the specified search_criteria. The search supports regular expressions.
Syntax Description GROWTH(data_Y; data_X; new_data_X; function_type) Calculates the points of an exponential trend in an array. Data_Y is the Y Data array. Data_X (optional) is the X Data array. New_Data_X (optional) is the X data array, in which the values are recalculated. Function_type is optional. If function_type = 0, functions in the form y = m^x are calculated. Otherwise, y = b*m^x functions are calculated. LINEST(data_Y; data_X; linear_type; stats) Returns the parameters of a linear trend.
Syntax Description SUMX2PY2(array_X; array_Y) Returns the sum of the sum of squares of corresponding values in two arrays. Array_X is the first array whose arguments are to be squared and added. Array_Y is the second array, whose elements are to be added and squared. SUMXMY2(array_X; array_Y) Adds the squares of the variance between corresponding values in two arrays. Array_X is the first array whose elements are to be subtracted and squared.
Syntax Description 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. If the COLUMN function with an area reference parameter is not used for an array formula, only the column number of the first cell within the area is determined.
Syntax Description INDEX(reference; row; column; range) Returns the content of a cell, specified by row and column number or an optional range name. Reference is a cell reference, entered either directly or by specifying a range name. If the reference consists of multiple ranges, the reference or range name must be enclosed in parentheses. Row (optional) is the row number of the reference range, for which to return a value.
Syntax Description OFFSET(reference; rows; columns; height; width) Returns the value of a cell offset by a certain number of rows and columns from a given reference point. Reference is the cell from which the function searches for the new reference. Rows is the number of cells by which the reference was corrected up (negative value) or down. Columns is the number of columns by which the reference was corrected to the left (negative value) or to the right.
Syntax Description VLOOKUP(search_criterion; array; index; sort_order) Searches vertically with reference to adjacent cells to the right. If a specific value is contained in the first column of an array, returns the value to the same line of a specific array column named by index. The search supports regular expressions. Search_criterion is the value searched for in the first column of the array. Array is the reference, which must include at least two columns.
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 LOWER(text) Converts all uppercase letters in a text string to lowercase. Text is the text to be converted. MID(text; start; number) Returns a text segment of a character string. The parameters specify the starting position and the number of characters. Text is the text containing the characters to extract. Start is the position of the first character in the text to extract. Number is the number of characters in the part of the text.
Syntax Description SEARCH(find_text; text; position) Returns the position of a text segment within a character string. The start of the search can be set as an option. The search text can be a number or any sequence of characters. The search is not casesensitive. The search supports regular expressions. Find_text is the text to be searched for. Text is the text where the search will take place. Position (optional) is the position in the text where the search is to start.
Syntax Description BESSELJ(x; n) Calculates the Bessel function Jn(x) (cylinder function). x is the value on which the function will be calculated. n is the order of the Bessel function. BESSELK(x; n) Calculates the modified Bessel function Kn(x). x is the value on which the function will be calculated. n is the order of the Bessel function. BESSELY(x; n) Calculates the modified Bessel function Yn(x), also known as the Weber or Neumann function.
Syntax Description ERFC(lower_limit) Returns complementary values of the Gaussian error integral between x and infinity. Lower limit is the lower limit of integral (x). FACTDOUBLE(number) Returns the factorial of the number with increments of 2. If the number is even, the following factorial is calculated: n*(N-2)*(n4)*...*4*2. If the number is uneven, the following factorial is calculated: n*(N-2)*(n-4)*...*3*1. GESTEP(number; step) Returns 1 if number is greater than or equal to step.
Syntax Description IMLOG10(complex _number) Returns the common logarithm of a complex_number. The complex number is entered in the form "x + yi" or "x + yj" IMLOG2(complex _number) Returns the binary logarithm of a complex_number. The complex number is entered in the form "x + yi" or "x + yj" IMPOWER(complex _number; number) Returns the integer power of a complex_number. The complex number is entered in the form "x + yi" or "x + yj". Number is the exponent.
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 353) or on the status bar (Figure 354) 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 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. 514 Internal overflow Sort operation attempted on too much numerical data (max. 100000) or a calculation stack overflow. 515 Internal syntax error Not used. 516 Internal syntax error Matrix is expected on the calculation stack, but is not available.
Code Message Explanation of the error 528– 531 — Not used. 532 Division by zero Division operator / if the denominator is 0. Some more functions return this error; for example: VARP with less than 1 argument STDEVP with less than 1 argument VAR with less than 2 arguments STDEV with less than 2 arguments STANDARDIZE with stdev=0 NORMDIST with stdev=0 426 OpenOffice.org 3.
Index 3 3D chart appearance 83 area chart 92 bar chart 90 choosing type 72 elements 74 formatting 82 illumination 84 pie chart 90 resizing 81 rotating interactively 84 rotation and perspective 82 A absolute reference 170 advanced functions 192 anchor, visible 353 anchoring of objects 135 appearance of chart 83 appearance options 346 Apply Style list 14, 102 area chart 92 arguments 376 arguments in functions 176 arithmetic operators 164 array functions 408 arrow keys navigating within spreadsheets 27 assign
data labels 86 data labels 76 editing 74 elements 74 formatting 79, 82 graphic background 82 grids 73 mean value lines 78 moving elements 80 perspective 82 Position and Size dialog 88 resizing and moving 88 rotating interactively 84 rotation 82 titles 73 trend lines 77 types 89 Y error bars 78 chart elements 74 chart type 72, 74 chart wall 74 Chart Wizard 71 collaboration 284 color coding for input 184 color options 343 Color toolbar 126 column and line chart 97 column chart 89 columns deleting 32 freezing
deleting columns and rows 32 sheets 33 Detective 184 digital rights management (DRM) 155 digital signature 158 digital signature status bar indicator 17 docking/floating windows 13 documents comparing 295 merging 294 drag and drop 119 drag mode 40 drawing object properties 132 Drawing Object Properties toolbar 132 drawing objects grouping 133 properties 132 resizing 133 Drawing toolbar 132 drawing tools 130 Dynamic Data Exchange (DDE) 281 E e-mailing several recipients 157 Writer document as attachment 157
functions add-in 417 ADDRESS 336 arguments 176, 309, 376 array 408 basic arithmetic 187 CEILING 190 CONCATENATE 165 COUNTIF 331 database 330, 407 date and time 401 date formats 381 definition 160 description 160 entering 161 examples 187 financial analysis 381 FLOOR 190 HLOOKUP 334 INDEX 338 INDIRECT 336 informational 405 logical 404 LOOKUP 334 macros 303 MATCH 334 mathematical 376 MROUND 190 nested 177 OFFSET 336 overview 175 ROUND 189 ROUNDDOWN 189 ROUNDUP 189 simple statistics 188 spreadsheet 410 statist
navigation 370 resetting to default values 366 saving to a file 366 selection 370 L layout settings format 106 page layout 105 table alignment 106 line chart 93 linking to external data External Data dialog 270 find required range 273 Navigator 271 load Basic code to edit 348 load/save options 347 logical functions 404 M macro recorder 299 macro security options 345 macros 366 macros accessing cells directly 310 arguments 308 arguments in functions 309 as Calc functions 303 introduction 299 organizing 303
pictures linking 120 resizing 128 transparency 126 pie chart 90 Position and Size dialog 88 Position and Size of graphics 129 precision 355 prepare document for review 287 print file directly 139 print options 343 printer metrics 352 printing choosing what to print 139 general options 140 multiple pages per sheet 140 page orientation 140 selecting what to print 141 Professional Template Pack 368 Properties dialog 41 protect a document 287 selecting 30 S save original Basic code 348 saving AutoRecovery in
update from changed template 112 spreadsheet functions 410 spreadsheets comparing 295 creating new 18 description 10 documentation, internal 160 embedding 278 identifying copies 287 merging 294 opening 20 opening shared spreadsheet 285 saving 22 saving as CSV 24 saving as Microsoft Excel 22 saving shared 285 setting up sharing 284 sharing 284 statistical analysis functions 393 status bar 17 stock chart 96 strikethrough 56 Style dialog 107 style organizer 103 styles accessing 100 assign to shortcut keys 102
# 434 #REF! Incorrect references 184 #VALUE Non-existent value 184 OpenOffice.org 3.
