The Gnumeric Manual, version 1.12

If you are a GNOME user, you should have a ‘panel’ somewhere on your desktop. This panel contains icons and at least two menus. One of these menus is called Applications and has an icon which looks like the outline of a foot. If you click on this menu name, a menu will appear. Drag the cursor down to the Office sub-menu name, and a sub-menu will appear. Drag the cursor into the sub-menu and then release the mouse button when the cursor is on the entry which reads "Gnumeric Spreadsheet." This will start the program and the main window of Gnumeric will appear as shown in Figure 3-1.

If you run a UNIX-like operating system (called *NIX in this manual) such as GNU/Linux, GNU on some other kernel, or a commercial UNIX (TM) system, or if you use a commercially distributed version of GNOME, KDE (K Desktop Environment), or a similar desktop system, you will have to find a way to launch Gnumeric yourself. Hunt around the menus until you find something named "Gnumeric", possibly with the icon, and then click on that menu entry.

You can also start Gnumeric from the command line in an xterm window or equivalent terminal emulator. Open a terminal. At the shell prompt type:

gnumeric &

Gnumeric, starting with the 1.4 series, can be run as a native application on the Microsoft Windows operating systems. On those operating systems, The Start menu should contain an entry which will launch Gnumeric. The actual location of the menu item depends on the choices made during installation.

gnumeric

c:\Program Files\Gnome-Office\gnumeric

Gnumeric can also be opened using a spreadsheet file directly. If there is a spreadsheet file on the desktop or in a file manager like Nautilus, it may be possible to click or double-click with the mouse pointer on the file and have Gnumeric open the file automatically. Alternatively, you may be able to right click on the file and get a pop-up menu that will allow you to select Gnumeric as the application to use to open the file.

If you are still stuck, ask a friend or someone who knows your machine. Unfortunately, getting started is often the hardest part of learning to use a new program but it is also the one place a manual such as this one cannot really help.

The menus provide the simplest way for you to get to all of the commands provided by Gnumeric. These menus work like those in any GNOME application: you click on the menu to open it, you drag the mouse cursor onto the menu and then release the mouse button (or click again) while the cursor is above a menu entry to execute that command. For further information, see Section 4.2 ― Menus.

The buttons on the toolbars are quite simple to use. You simply place the mouse cursor above one of the buttons and press the left mouse button to perform the command and it will either execute immediately or open a dialog window to obtain further information first. For further information on the toolbar button commands, see Section 4.4 ― Toolbars.

In many situations, Gnumeric provides a menu right under the mouse cursor if the right hand mouse button is clicked. This menu contains different entries depending on where the mouse cursor is when you click the right hand mouse button. For further information, see Section 4.3 ― Context Menus.

You can trigger certain common commands by using a combination of keys. The menu entries are often followed by a combination of keys which you can use to trigger that command. For instance, to save the file which you are currently using, you can jointly type the control key and the s key (i.e. Ctrl+S). For further information, see Appendix B ― Keybinding Reference.

Spreadsheets like Gnumeric treat information by separating the data into separate cells and considering the data in each cell to be separate elements. Each cell in the spreadsheet has both a value, which is what Gnumeric manipulates, and a representation, which is what is actually shown. Understanding this distinction is complicated and make take some time if you are new to spreadsheets. This distinction between value and representation is one of the reasons spreadsheets are so useful.

The cells of the spreadsheet are contained in the cell grid area. The cell grid area is the area with a white background and grey grid lines. The grid lines separate this area into separate cells. Each cell has a unique reference name which is the combination of the letters of the name of the column and the number of the row. For instance, the top, leftmost cell is the cell named "A1" and the cell two over to the right and four rows down is named "C4" because it is in the column labelled "C" and in the fourth row. Each of these cells can contain only one single datum.

The datum contained in any cell will have one of five types: a text string type, a number type, a formula type, a boolean type or an error type. These five types of data values can then have various display formats so that, for instance, a number value can be displayed as a number, a monetary amount, a date or a time. Text strings are sequences of characters and punctuation marks and could, for example, contain textual information such as people's names. Number values are simply numbers but may be input and displayed in various formats including decimal numbers, dates, times, and numbers in scientific notation. Formulas are instructions to Gnumeric to calculate a result. The power of spreadsheets comes from these formulas because the results of the calculation can depend on the contents of other cells. Boolean values are either TRUE or FALSE and can be used in logical statements. Error values are usually the result of mistakes or impossible calculations.

For more advanced information on the types of data usable in Gnumeric, see Section 5.2 ― The Types of Cell Elements.

In order to enter data into the spreadsheet, you must first select a cell in which to place the information and then actually type the information on the keyboard. Once you have entered the information, Gnumeric attempts to figure out both the appropriate data value type to assign to the cell and the appropriate data format in which to display this data value. Because this process is quite complex, you may occasionally need to actively select these parameters of the cells, which we explain in Section 3.6 ― Cell Formats below. The next two sections explain how to get data into a cell, by first moving the selection box to a desired cell and then typing the data.

=3+4-1

Simple changes to the format of a cell include changing the alignment of the characters, changing the font type or colour, changing the border, and changing the colour or pattern of the background.

The Alignment, Font, Border, and Background tabs are simple to understand simply by playing around with the settings and looking at the effect on a cell which contains text.

The Protection and Validation tabs are advanced functionality which you can ignore at the beginning. For explanations of these tabs, see the advanced description in Section 5.10.3.2 ― Validation Tab.

Cell formats are most difficult to understand when they address the type of data stored and the visual display of that data. This only arises with the options selected in the Number tab of the Format Cells dialog. While these ideas are complex, you need to understand them early on as they are fundamental to spreadsheet use.

When you enter data into Gnumeric, the spreadsheet interprets the entry based on the input format of the cell. The default format of empty cells is the General format which instructs Gnumeric to guess both the type of the data being entered and a suitable display format for that data type. However, you can change the General format to a specific format in order to alter both the way Gnumeric interprets any future data input to the cell and the way data in the cell are displayed.

For example, if you enter "12/25/2000" (without the quotes), Gnumeric guesses that this is a date and stores the value (serial number) 36885. (Usually, the value Gnumeric uses for dates is the number of days since January 1st, 1900.) ¹ At the same time, Gnumeric changes the display format to display this number as a date, with a numeric month, day and year, separated by slashes.

It sometimes becomes necessary to override the "General" type if Gnumeric is making an incorrect assessment of the data being entered. Postal Zip Codes in the United States, for instance, are incorrectly interpreted to be numbers. Some of these Zip Codes start with a leading zero which the "General" format type drops so the user must intervene to keep that zero displayed. In order to input these Zip Codes, the following steps must be performed. First, the cell must be selected. Next, the cell must be formatted to hold a "Text" value. This formatting changes both the interpretation of any future data entry into this cell and alters the display formatting of the cell. Finally, the Zip Code can be entered. Following these steps, the data value will be considered to be a "Text" value, any leading zeros will be retained and the data will be left justified since this is the default display format for "Text" values.

The simplest way to move cell contents around a spreadsheet involves selecting a block of cells containing the contents to be moved, either "cutting" or "copying" those cells, selecting the location where these contents are to be moved and then pasting the data.

Moving data can only be performed with a single selection of cells which means that only continuous rectangular blocks of cells can be moved. This does mean, however, that columns or rows can be moved as a unit. By default, Gnumeric moves the entire contents of the cells including both the data values and the formatting of the cells.

Once you have selected a group of cells, they can be "cut" or "copied" either using the Edit menu, the toolbar buttons (a pair of scissors or two pieces of paper, respectively), the right mouse button context menu or keyboard shortcuts (Ctrl+X or Ctrl+C respectively). If cells are "cut" the contents will be removed from the current location. If cells are "copied", the contents will be duplicated in the new location. These two operations treat cell references in formulas slightly differently. If cells are "cut", any references in the cells in the new location will remain pointed at the original cells. If cells are "copied", the references in the cells in the new location will point to cells in the same relative position.

You can select the new location for the cells in two ways. The simplest is to select the top, left cell of the new location. Alternatively, you can select the whole new range of cells but the shape of this new range must match exactly the dimensions of the original range which is more difficult.

Finally you can "paste" the cell contents in the new location using either the Edit menu Paste menu entry, the toolbar button with a clipboard, the context menu Paste menu entry or the Ctrl+V keyboard shortcut.

An alternative way to move cells in a current worksheet involves dragging and dropping the original selection. You select the cells to be moved as above. You then place the mouse cursor on the thick white selection border. If you click and hold the left hand button, you can drag the selected cells to a new location resulting in the same operation as a "cut" and a "paste". If you hold down the Ctrl key during the click and drag of the mouse, the result is the same as a "copy" and "paste" operation and can be repeated several times.

Both the Edit menu and the context menu have an extra menu entry called Paste Special... which can be used during a cut and paste operation to selectively transfer some of the original cell contents or to alter the contents in specific ways. This option allows the transfer of only the cell contents, only the cell formats or only the calculated values of the cells. The transferred contents can also be mathematically combined with the current contents of cells in the new location. Alternatively, the selection can be transposed. See Section 5.7.3 ― Paste Special for more information on the Paste Special... command.

A worksheet can also be altered by inserting or by deleting cells. These operations actually alter the locations of cells in a workbook.

Inserting and deleting columns and rows are easy to understand. If you select a group of columns or rows, selecting the Column or Row menu entries in the Insert menu will add the same number of columns to the left of the selected columns or of rows above the selected rows. You can also use the context menu for the insert operation. The context menu can be used to delete the currently selected columns or rows.

Individual cells or contiguous rectangular blocks of cells can also be inserted and deleted. During this operation, you are asked which way to shift the current cells to allow the insertion or deletion of the selected cells. The movement can be along the rows or along the columns and will result in the relative movement of cells which were previously contiguous. This shift is the fundamental difference between insert and delete operations compared to cut or copy and paste operations.

This section will introduce the process of creating a graph by presenting an example of a side-by-side column plot.

3.10.1.1. Data for the examples.

Because a graph requires data, it is first necessary to create some simple data to use in these examples. First we have to input these data into a worksheet. The data used are shown in Figure 3-3. For clarity in this discussion, the word "Interval" should be in cell A1.

Figure 3-3 Gnumeric with the data used in this example.

3.10.1.2. Making the Column Plot

A column plot presents a series of data points as columns whose height depends on the value of each datum. This is a useful type of plot to show the number of eggs produced in each interval.

Making a Column Plot

1. The quickest way to make a plot starts with the selection of the data. Using the mouse, first select the range A1:C5 which includes the data both for the number of Eggs and for the number of Females.

2. Next, click on the graphing toolbar button which looks like three colored pillars. This launches a new window called the graph guru.

3. Next, click on the word "Column" next to the icon with vertical colored bars which will move the selection to that row.

4. Click on the "Insert" button. This will make the druid disappear and leave the mouse cursor as a thin cross hair.

5. Finally, we will place and size the graph on a sheet. Click on the sheet and drag downward and to the right. As the mouse is dragged, a rectangle will expand. When the mouse button is released, a simple column chart should appear.

The simple graph should look like Figure 3-4.

Figure 3-4 The Simple Graph with a Column Plot.

3.10.1.3. Modifying the Simple Graph

The graph can be customized with titles, extra charts, overlaid plots, label boxes and lots of extra information. To customize the graph, right click on the plot to open the graph custom menu. The graph context menu will appear as shown in Figure 3-5.

Figure 3-5 The context menu which appears on a graph object.

This menu provides access to several functions. Users can customize the appearance of graphs by selecting the Properties menu item, can save the graph into PNG or SVG formats using the Save as image menu item, can reorder the various graphical elements displayed in the worksheet using the Top, Up, Down, and Bottom menu items or can delete graphical elements with the Delete menu item.

If we wanted to add a title and a legend to the graph, we could use the Properties menu item to open the graph editor and customize the graph as follows:

Adding a title and legend to the simple graph

1. Right click on the graph to open the context menu and select the Properties menu item. This will open the graph editor.

2. The graph editor opens with the top-level "Graph" entry selected in the element tree displayed in the top left pane of the editor. The top right pane of the editor displays a preview of the eventual graph. The bottom pane of the editor has a single or several tabs presenting the elements which can be modified for the particular item selected in the element tree. Click and hold on the Add button to open the menu of elements addable to a graph. Note that this menu changes depending on the element selected in the element tree when the Add is clicked. Drag the mouse cursor down until the selection highlights the "Title" entry and release the mouse button. This will add a "Title" node in the graph element tree and change the selection to this "Title" node. The bottom pane of the graph editor will also change to display the modifiable characteristics of the "Title" element.

3. In the text field of the tab displayed in the bottom pane of the graph editor, add a title such as "Egg Production and Female Productivity" and type the Enter key. Note that the title appears at the top of the graph in the preview pane.

4. In the graph tree, select the node labelled "Chart1". Next, click and hold on the add button, drag down to the Legend menu item and release. Note that this adds a legend on the right of the graph in the preview pane.

5. Click on the "Apply" button. The plot should now have a title and a legend. Note that Gnumeric has used the words in the column headers automatically to label the two data series in the legend.

The modified graph should look like Figure 3-6.

Figure 3-6 The Modified Column Graph.

A menu is a graphical element within a program which appears with a list of options. For instance, almost all applications have a File menu through which the user can access the computer's filesystem to open or save their work. The main menus are on the menubar. The use of these menus is discussed in Section 4.2.2 ― Menubar.

Gnumeric also uses context menus to give users a quick way to access certain commands. The context menu will open up right under the mouse pointer when one of the secondary mouse buttons, usually the rightmost, is clicked. This menu is called a context menu because the entries in the menu are different depending on the location of the mouse pointer. The context menus are discussed in Section 4.3 ― Context Menus.

Both the main menus, on the menubar, and context menus may have submenus. A submenu is indicated by a small right-pointing arrow. To access a submenu, move the pointer down to the submenu entry. When the submenu opens, move the pointer directly across into the submenu. When there is not enough room to the right of the currently open menu, submenus may open to the left. Note that the submenu will close if the mouse pointer moves into any other menu entry.

You can also use the keyboard to navigate menus and submenus. See Section 4.2.2 ― Menubar to access the main menus using the keyboard. Once a menu is displayed, menu entries can be highlighted by pressing the down and up arrow keys. When a submenu opens, pressing the right arrow key moves the highlight to the first entry of the submenu. When a submenu entry is highlighted, pressing the left arrow key removes the highlight from the submenu. Pressing the space bar or the Enter key activates the highlighted menu entry.

Menu entries ending with an ellipsis (three dots) open a dialog window which asks for more choices.

The default location of the menubar is at the top of the application window. The menus provide quick and organized access to all major commands such as opening files, saving files, printing and quitting the application.

Figure 4-2 The Gnumeric menubar.

To open a Gnumeric menu, click on the name of the menu in the menu bar. Once clicked, the menu will stay open. If the mouse pointer is moved to the name of another menu on the menubar, the first menu will close and the new menu open up. This is a useful way to look in each menu to hunt for a commands. Menus can also be opened through the keyboard. Pressing and holding the Alt key causes one of the letters in each menu name to be underlined. Press-and-hold Alt and press the underlined letter to open the associated menu. Using the keyboard for menu activation or navigation causes a letter of each menu or submenu item label to be underlined. Press that letter key to activate the menu item. Once a menu is open, the arrow keys can be used to move between menus or select an entry in a particular menu. To close an open menu, click over any other area of the application or of the desktop or press the Escape key, Esc.

Many menu entries are followed by a series of key names. These keys can be used to perform the menu action without having to open the menu. These are often combinations of keys involving the control key which is labeled as Ctrl, the shift key which is labeled Shift and the function keys which are labeled with an F and then a number. For example, to quickly cut a selection (accessible through the Edit menu), the user can make a selection and then type the control key and the "x" key at the same time.

The File menu is the most important menu in Gnumeric because it gives the user the ability to interact with the computer operating system. This menu allows the user to create files containing all the work they have done. It also enables users to print the results of their work. Finally, the File menu is the best way to close Gnumeric.

Figure 4-3 The File menu.

The menu choices are grouped into the following groups:

The Edit menu is mostly used for operations on a worksheet or between worksheets. This menu gives users powerful editing operations such as the ability to undo recent changes, the ability to cut and paste selections of cells and the ability to search for specific cell contents.

Figure 4-5 The Edit menu.

The menu choices are grouped into the following groups:

The View menu is

Figure 4-9 The View menu.

The menu choices are grouped into the following groups:

The Insert menu is

Figure 4-12 The Insert menu.

The menu choices are grouped into the following groups:

The Format menu allows users to control the formats of cells, columns, rows, worksheets and the workbook. This menu also gives users access to templates of standard formats.

Figure 4-14 The Format Menu and Its Cells Submenu.

The Tools menu is

Figure 4-17 The Tools menu.

The menu choices are grouped into the following groups:

The Statistics menu is

Figure 4-18 The Statistics menu.

The Statistics menu and its submenus contain all available statistical analysis tools. These tools are explained in Chapter 8 ― Statistical Analysis.

The Data menu is

Figure 4-19 The Data menu.

The menu choices are grouped into the following groups:

The Help menu is quite simple.

Figure 4-22 The Help menu.

The Help menu connects users to this manual, to the list of functions available for use in Gnumeric and to the list of people who created this wonderful application.

The context menu in the cell grid area appears when the pointer is over the cell grid area. This menu applies to the cells that have been selected, not necessarily the cell underneath the mouse pointer.

Figure 4-23 The Context Menu for the Cell Grid Area.

The grid context menu merely provides an extra way to access Gnumeric commands. The Cut, Copy, Paste, Paste Special, Delete Cells..., Clear Contents, Remove Comment, Edit Hyperlink, and Remove Link commands are taken from the Edit menu and its submenus and are explained in Section 4.2.4 ― Edit Menu. The Insert Cells... and Add Comment menu items are explained in the section on the Insert menu in Section 4.2.6 ― Insert Menu. The Format Cells..., Conditional Formatting... items as well as the items on the Cell, Column, and Row submenus are explained in the manual section on the Format menu in Section 4.2.7 ― Format Menu.

The Context Menu for Column and Row Headers

Figure 4-24 The Context Menu of a Row Header

The context menu which relates to column or row headers is similar to the context menu for the grid area. Both insert and delete operations are modified to operate explicitly on rows or on columns.

The context menu for worksheet tabs provides access to functions that manipulate worksheets as a whole, rather than their contents.

Figure 4-25 The Context Menu for Worksheet Tabs

The context menu for the worksheet tabs provides the same functions as the Sheet submenu of the Edit menu. These functions are explained in Section 11.6.2 ― Worksheet Tab Context Menu. There are also two submenus where you can select one of the existing sheets, whether its tab is currently visible or not. The first submenu lists the sheets in their current order, the second submenu lists them alphabetically.

The Context Menu for Embedded Objects and Components.

Figure 4-26 The Context Menu for Graphical Objects

All embedded objects, such as drawing elements, graphs and images, have a context menu which can be invoked by placing the mouse pointer over the element and clicking with one of the secondary mouse buttons.

The first menu entry, labeled Properties, will open a dialog specific to the type of element selected in which the user can configure the properties of the element.

The menu entry labeled Size & Position, will open a dialog that permits the user to adjust the size and position of the embedded object.

The menu entry labeled Snap to Grid, enlarges the object such that all of its corners are located at cell corners.

The menu entry labeled Order, opens a submenu which allows the user to change the visual order in which the graphical elements are placed. This order will affect the way in which the graphical elements obscure each other.

Figure 4-27 The Context Submenu for Graphical Object Order

The menu entries labeled Cut, Copy, and Delete, allow the user to operate on the whole object at once. The Cut menu item allows the user to remove the element from its current position and then paste the object in a different location on the same sheet, in a different worksheet, in a different Gnumeric file, or in a file from a different program entirely. The Copy menu item provides the same functionality while leaving the original item in place. The Delete allows the user to remove the graphical element entirely.

The remaining menu item, Print, allows just the selected object to be printed.

For certain types of objects, a menu entry labeled Save As Image... allows the object to be saved as an image file. This menu item is not present in Figure 4-26 but is shown in Figure 4-28 .

Figure 4-28 The Context Menu for Graphs and Images

Graphs have a context menu that also contains menu entries labeled Open in New Window and Copy to New Graph Sheet. The Open in New Window menu item shows the graph by itself in a window. The Copy to New Graph Sheet copies the graph to a special sheet in the current workbook that contains only a single graph.

The Context Menu for Toolbars

Figure 4-29 The Context Menu for Toolbars.

The context menu for toolbars allows the user to configure toolbars. Each toolbar can be displayed above the sheets, to the left of the sheets, or to the right of the sheets. The context menu can also be used to hide a toolbar. The toolbar can be made visible again via the Toolbars submenu of the View menu. See Section 4.2.5 ― View Menu.

The toolbar context menu described in Section 4.3.5 ― The Context Menu for Toolbars can be used to determine where each toolbar (above the sheets, to the left of the sheets, or to the right of the sheets) is displayed. The Toolbars submenu of the View menu (see Section 4.2.5 ― View Menu) can be used to determine which toolbars are visible.

If the Gnumeric window is too small to fit all of the toolbar buttons, Gnumeric displays an arrow to indicate that there are more options. If the user clicks on this arrow, a window will open with the remaining buttons as shown in Figure 4-31.

Figure 4-31 The Toolbar Extension Menu

Certain toolbar options, such as the zoom box, are combinations of a button or text entry area and a downward pointing arrow. If the user clicks on the arrow, a list of available options appears.

The standard toolbar is shown in Figure 4-32.

Figure 4-32 The Standard Toolbar

The Standard Toolbar gives the user access to file operations, printing, movement of data blocks, the undo system, and to some of the powerful tools like the function creator and the graphing system.

The Format Toolbar

Figure 4-33 The Format Toolbar

The Long Format Toolbar

Figure 4-34 The Long Format Toolbar

The Long Format Toolbar contains all buttons that the Format Toolbar (described in Section 4.4.3 ― The Format Toolbar) contains, except in a different order.

In addition, the Long Format Toolbar also contains the following buttons:

Figure 4-35 The Object Toolbar

The Object Toolbar.

On the far left of the Data Entry area is the current cell indicator area. This area is shown in Figure 4-37

Figure 4-37 The Current Cell Indicator.

The cell indicator will show the address for the cell at the top left of the selected region. This address is listed in the standard column:row notation. The alphabetic part indicates the column of this top leftmost cell and the numeric part indicates the row of this top left cell. For instance, the cell which is over three columns and down two rows is designated:

This designation matches the column and row headers for this cell.

While a region is being selected, the current cell indicator will change to show the size of the region which is being selected. This information is presented in a row number by column number format. For instance, this designation:

The Cancel button can be used to cancel the current edit and to restore the cell contents to the previous state. If a user decides in the middle of an edit that the data being entered into a cell is not what they want, the user can push this cancel button to cancel the current data and return the cell to the state it was at before.

The most common use of this button is when overwriting the contents of a cell with new data. If the user decided to revert the change before confirming it, the cancel button is the answer. Note that this button works just like typing the escape key on the keyboard.

The confirm button . can be used to finish the edit of a cell and enter the edit into the workbook. Note that this button works in the same way as the enter key.

The equals button can be used to start a formula in the currently selected cell. If a user wanted to make cell D10 equal to cell B4, the user could simply click on cell D10, click this equals button, click on cell B4 and type the enter key. Note that this button works the same way as the equals key.

The sheet is bordered by column headers on the top and row headers to the left. Columns are labeled alphabetically running horizontally across the top of the sheet as shown in Figure 4-39.

Figure 4-39 The column labels at the top of the worksheet.

Figure 4-40 The row labels at the left side of the worksheet.

The cell grid area's scrollbars work like other scrollbars. In this case they let the user work on a worksheet which is much bigger than what could be shown at any given moment.

At the bottom of Figure 4-38, Gnumeric has a tabbed sheet indicator. This is the boxes with the labels "Guppies", "Turtles", "Mermaids", "seahorses" and so on. In workbooks where there are more than one sheet, extra tabs are used to indicate how many sheets are there, and also allows the user to move to another sheet by clicking the proper tab. Each tab is a link to a separate worksheet within Gnumeric. In this figure, the "Turtles" tab has been selected. Gnumeric shows this by making the "Turtles" since the tab is a little bigger and making it overlap the two neighbouring tabs.

On the far right, the two arrows indicate that there are more sheets in the workbook and more tabs for those sheets. Unfortunately, there is not enough space to show them all so Gnumeric displays these arrows. If all the sheets are visible, these arrows will not be displayed. These arrows can be used to scroll the tabs and bring tabs that were hidden into view. This may be a little difficult to understand and work with but becomes easier quickly.

The leftmost portion of the information display area, the part which reads: "Sort the selected cells" is a hint explaining what a menu does. In this case, the Data menu was opened and the pointer is hovering above the "sort" menu entry. Gnumeric is responding by giving a quick explanation of what that menu entry does.

Whenever Gnumeric opens or saves a file, the file status indicator appears with a progress bar which grows as Gnumeric progresses. In Figure 4-41, Gnumeric was just over halfway done opening a file.

In the bottom right hand corner, is an info area that shows some constantly calculated values. The default set is to show the Sum of all the currently selected cells.

There is a right click option that includes a few more options. This can include Sum, Min, Max, Average, Count.

Figure 4-42 The running calculation area.

Text elements consist of an arbitrary sequence of characters or numbers entered into a cell. Because Gnumeric automatically recognizes certain sequences as numbers or formulas, certain sequences of characters (such as sequences containing only digits or a text element which starts with an equals sign) must be treated specially to have them considered text. In order to force any sequence to be considered text, the sequence can be started with an apostrophe symbol ('). Alternatively, the 'number' format of the cell can be specified to be 'text' before entering the characters, as explained in Section 5.10.1 ― Number Formatting Tab. Text elements are the simplest elements to enter into spreadsheet cells.

An example of a spreadsheet cell grid with cells containing text is given in Figure 5-1.

Figure 5-1 A Text Data Element in a Cell.

Valid text entries include simple words, whole sentences and even paragraphs.

Number data elements include a variety of data all of which are stored and manipulated by Gnumeric as numbers. This includes integers, decimal fractions, general fractions, numbers in scientific notation, dates, times, and currency values.

Data are recognized as numbers when they are entered, dependent on the format of the sequence of characters entered. Gnumeric attempts to intelligently guess the subtype of the data and match the data to an existing format for numbered data. If it matches a data format, Gnumeric will automatically assign the datum to a data type and associate an appropriate display format with the cell. The format recognition of Gnumeric includes a wide variety of data formats which are discussed in detail in Section 5.10.1 ― Number Formatting Tab.

Because Gnumeric automatically guesses the data type of a number being entered into a cell, this process may have to be over-ridden for certain types of data. For example, postal codes in the United States consist of a sequence of numbers which Gnumeric interprets as an integer. However, U.S. postal codes can start with a leading zero which Gnumeric discards by default. In order to override the default format, the number format of the cell must be specified before the entry of the data. This is explained in Section 5.10.1 ― Number Formatting Tab, below.

Cells can contain boolean data elements. These elements arise from Boolean logic which is a branch of mathematics. These elements are useful for manipulation of formulas.

Boolean values can be either "TRUE" or "FALSE". If these strings are entered into a cell, Gnumeric will recognize these as boolean values. These values can then be used in formulas. Certain formulas will also return boolean values.

Formulas are the key to making a powerful spreadsheet. A formula instructs Gnumeric to perform calculations and display the results. These calculations are defined as a formula data elements. The power of these formulas arises because these formulas can include the contents of other cells and the results of the formulas are updated automatically when the contents of any cell included in the formula change. The contents of other cells are included using "cell references" which are explained below.

Any formula entered into a cell must follow a specific syntax so that Gnumeric can interpret the formula correctly. This syntax closely follows mathematical notation but also includes spreadsheet formulas, object names and cell references.

=5+5            returns 10.
	
=5-4            returns 1.
	
=-5             returns -5.
	
=5*5            returns 25.

=(5*5)+11       returns 36.
	
=(5*5)+(49/7)   returns 32.
        

FUNCTIONNAME(ARGUMENTS)
        

=SUM(A1,A2,A4,B5)
	    
=AVERAGE(A1:A16)
	    
=EXP(1)
	    
=PI()
	    
=3+4*MIN(A1,A2,B6)
	  

5.2.4.3. Cell Referencing

Formulas can include the displayed data from other cells. These contents are described as `cell references' which are names indicating that the contents of other cells should be used in the calculation.

Each cell in a spreadsheet is named by its column and row labels. By default, the column labels are letters and the row labels are numbers. The first cell, therefore, is called A1. One column over and two rows down from cell A1 is the cell B3. In a worksheet of the default size, the right most and bottom most cell is cell IV65536 which is the cell in column IV and in row 65536. An alternative cell reference notation uses numbers for both row and column identification. See Section 5.2.4.3.2 ― References using R1C1 Notation below for details.

The value of a cell can be used in a formula simply by entering its name where a number value would otherwise occur. For example, to have the data in cell B1 appear in another cell, enter =B1 into that cell. Other more complex examples include:

Example 5-4 Some examples of simple cell reference syntax

=A1+EXP(B1)-(C3/C4)
      
=COS(A2)*SIN(A2)
        

• 5.2.4.3.1. Absolute cell referencing
• 5.2.4.3.2. References using R1C1 Notation
• 5.2.4.3.3. Referencing multiple cells
• 5.2.4.3.4. Referencing cells on other sheets
• 5.2.4.3.5. Referencing cells on other files

5.2.4.3.1. Absolute cell referencing

Cells can be referenced in the default way (relative referencing), or by using absolute referencing. Absolute referencing means that when the cell is copied, the cell reference does not change. Normally, auto-filling a cell range or moving a cell will change its cell reference so that it maintains a relation to the original cell. Absolute referencing prevents these changes.

When Does Relative Referencing Make a Difference?

The difference between absolute and relative cell references only matters if you are copying or moving cells that contain cell references. For cells that are going to remain in place, both the relative and absolute references have the same result.

Example 5-5 Relative References

For example, if =A1 is the formula entered into cell B2, cell B2 will display the data in cell A1, which is one row up and one column left. Then, if you copy the contents of B2 to cell F6, cell F6 will contain the value from E5, which is also one row up and one column left.

For the copied cell to still refer to A1, specify absolute references using the $ character: $A$1 refers to cell A1, no matter where it is copied.

The format for absolute cell referencing is to use a '$' in front of the cell coordinate that you want to stay constant. The column, the row, or both can be held constant.

Example 5-6 Cell referencing examples

What happens when a given formula is entered into cell B2, then copied to other cells?

=A1

=A1 is a normal, or relative, cell reference function. When =A1 is entered into cell B2, it refers to the value of data one cell up and one cell left from the cell with the reference. Therefore, if this formula were copied from cell B2 to cell C2, the value displayed in cell C2 will be the value of data in cell B1. Copied to cell R19, the formula will display the data in cell Q18.

=$A1

In this case, the column value is absolute, but the row value is relative. Therefore, if =$A1 is entered into cell B2, the formula refers to the data in column A that is one row up from the current location. Copied to cell C2, the formula will refer to the data in cell A1. Copied to cell R19, it will refer to the data in A18.

=A$1

This formula uses a relative column value and an absolute row value. In cell B2, it refers to cell A1 as the data in the cell one column left and in row 1. Copied to cell C3, the formula will display the data in cell B1.

=$A$1

No matter where this formula is copied, it will always refer to the data in cell A1.

5.2.4.3.2. References using R1C1 Notation

From the Format ▶ Sheet submenu you can select R1C1 notation for a worksheet. This causes all cell references on the sheet to be shown as “RrCc”, where r is the row number and c is the column number. When R1C1 notation is selected, the column headers show numbers rather than letters.

When r and c are positive integers, as in “R1C1”, the reference is absolute. To produce a relative reference, enclose a number in square brackets; if the number is zero, it can be omitted along with the brackets. For example, “RC[-2]” refers to the cell two columns to the left in the current row, while “R[1]C1” refers to the cell in the first column of the next row down from the referencing cell. The second example combines a relative row reference with an absolute column reference.

5.2.4.3.3. Referencing multiple cells

Many functions can take multiple cells as arguments. This can either be a comma separated list, an array, or any combination thereof.

• 5.2.4.3.3.1. Multiple individual cells
• 5.2.4.3.3.2. Referencing a continuous region of cells
• 5.2.4.3.3.3. Referencing non-continuous regions

5.2.4.3.3.1. Multiple individual cells

A comma separated list of cell references can be used to indicate cells that are discontinuous.

Example 5-7 Some examples of function syntax

=SUM(A1,B2,C4)
	  
=MIN(A1,B2,C4,C5,D6)
	  

5.2.4.3.3.2. Referencing a continuous region of cells

For functions that take more than one argument, it is often easier to reference the cells as a group. This can include cells in sets horizontally, vertically, or in arrays.

The ':' operator is used to indicate a range of cells. The basic syntax is upper left corner:bottom right corner.

Example 5-8 Referencing blocks of cells

=SUM(A1:E1)
	  
=AVERAGE(B4:E7)
	  
=MIN(A1:A5)            
	  

5.2.4.3.3.3. Referencing non-continuous regions

For referencing cells that are in non-continuous regions, you can use any combination of the above methods to get the needed cells.

Example 5-9 Referencing blocks of cells

=SUM(A1:E1, B19, L14:L17)
	  
=AVERAGE(A1,A3, A5:C5)
	  

5.2.4.3.4. Referencing cells on other sheets

It is possible to reference cells which are not part of the current sheet. This is done using the SHEETNAME!CELLLIST syntax, where SHEETNAME is an identifier (usually a sheet name) and CELLLIST is a reference to a cell or range of cells as described in the previous sections. If SHEETNAME contains spaces or other special characters, you must quote the whole name to allow Gnumeric to recognize it as a single name. See the examples below.

When the reference is to a range of cells, the worksheet name only needs to be given with the first cell reference. The ending cell of the range is assumed to be on the same worksheet if an explicit sheet name is not specified. Note, however, that “Sheet1!A1:Sheet3!C5” is a legitimate cell range description. It identifies a range three columns wide and five rows deep on each of the worksheets from Sheet1 through Sheet3. The preferred form of such a reference is “Sheet1:Sheet3!A1:C5”, which is the form Gnumeric will display if you subsequently edit the contents of a cell containing such a reference.

Example 5-10 Referencing cells in other sheets

='Sheet 0'!A1+'Sheet 3'!A5

=SUM(Sheet1!A1:A5)
        

5.2.4.3.5. Referencing cells on other files

It is possible to reference cells in other files. The canonical form for these references is [filename]SHEETNAME!CELLLIST. The square brackets serve to quote filename, so you should use quotation marks only if they are actually part of the file name. Note that the sheet name must be present in references of this form.

Example 5-11 Referencing cells in other files

=[Name of the file]'Sheet 0'!A1

=CEIL( [First Version.gnumeric]'Sheet 1'!E20 )
        

=VLOOKUP(C1, "gnu", myCellRange, 2, 0)

=LN(my_E_Constant)

=SUM(myCellRange, my_E_Constant)
      

={FUNCTION(ARGUMENTS)}(num_rows, num_cols)[row][column]
      

5.2.4.6. Database Formulas

Solely for compatibility with Excel and ODF files, Gnumeric supports various database functions: DAVERAGE , DCOUNT , DCOUNTA , DGET , DMAX , DMIN , DPRODUCT , DSTDEV , DSTDEVP , DSUM , DVAR and DVARP .

Since these functions are quite restrictive on the criteria that can be used, it is often easier to use array functions as described in Section 5.2.4.5 ― Array Formulas. Array functions are also useful in the case that a specific database function does not exist:

Example 5-14 Simulating DMEDIAN with an Array Function

As shown in Figure 5-2, instead of using (the non-existing) function DMEDIAN one can use the alternative expression median(if(A1:A20="AA",B1:B20)) entered as an array function as described in Section 5.2.4.5 ― Array Formulas. Multiple conditions can be combined using multiplication to obtain AND and addition to obtain OR as in median(if((A1:A20="AA")+(C1:C20="BB"),B1:B20)). Using defined names as introduced in Section 5.2.4.4 ― Names for A1:A20 and B1:B20 can make this code very flexible and readable.

In this case we cannot use if(OR(A1:A20="AA",C1:C20="BB"),...) since the OR function would be applied to all 40 equality tests rather than each of the 20 pairs of equality tests.

Figure 5-2 Calculating the MEDIAN of Some Data Values

Cells can display error values if the formula contained in the cell cannot be solved or if other anomalous conditions occur.

In Gnumeric all error values have names that start with #. 8 error values are standardized:

The simplest method to place data in a spreadsheet involves selecting the location for the data, typing the data on a keyboard and then typing the Enter key to finish the input.

Text can be entered by selecting a cell, typing the text and then typing the Enter key. Anything that is not interpreted to be a number, boolean, formula or error will be treated as a text field. To prevent Gnumeric from interpreting an entry as one of these other elements, a leading apostrophe can be added to force the entry to be text. Any entry, no matter what the contents, which starts with a leading apostrophe (') will be considered to be text.

Numbers can be entered like other items. First the cell in which the number should be entered must be selected, then a valid number must be typed in and finally the entry must be inserted using the Enter key. The valid formats for numbers are presented in Section 5.2.2 ― Number Data Elements.

A boolean can be entered by selecting the cell, typing the boolean (either TRUE or FALSE) and then typing the Enter key.

Formulas can be entered simply by typing a syntactically correct formula in a cell. The correct syntax for formulas is explained in detail in Section 5.2.4 ― Formula Elements. Formulas begin with an equals sign (=) and contain arithmetic symbols, functions with their arguments and cell references.

To enter a formula, first the cell must be selected, then the correct formula must be typed, and finally the Enter key must be typed.

5.3.5.1. Shortcuts for including cell references and ranges in formulas

Formulas often include cell references or references to ranges of cells as arguments to functions. These references and ranges can be entered into a formula simply by typing in the proper syntax (see Section 5.2.4.3 ― Cell Referencing for more details). But manually entering in cell ranges is slow and cumbersome. In order to speed up the entry of these cell ranges, the mouse and the keyboard arrow keys can be used to select these cell ranges quickly.

When editing a formula, if the cursor is at a point where a cell reference or range would be appropriate, the reference or range can be selected using the mouse. If the mouse is used to click on a cell, the reference of that cell will be entered into a formula. Alternatively, if the mouse is used to drag a selection over a range of cells, that cell range will become part of the formula. The selected range will be highlighted by a border of moving black dashes, commonly called the "marching ants" border.

Figure 5-3 The highlighted selection

Using the mouse to enter cell references

1. Begin entering a formula as you would normally. Stop at the point where a cell reference is appropriate. For example, example, type =exp(

2. Click on the cell you want to reference. Its reference will be entered into the formula.

3. To finish the formula, just type in the closing parenthesis. This will also "unselect" the region.

The mouse can be used to select a single cell, a continuous range of cells or several disjoint ranges of cells just like regular selections.

If entering lots of formulas or even just entering a few formulas, it is often quickest to use the keyboard to select cells and ranges of cells for use in formulas. Just as reaching a input point allows the user to select cells with the mouse, it is possible to use the keyboard to select cells. Just use the cursor keys and selection modifiers to create a selection.

Using the keyboard to enter cell references

1. Select a cell to enter a formula into. For example A1.

2. Enter a formula, but stop the cursor at a point where a cell reference is appropriate. For this example, =SUM(

3. Move the selection around with the cursor keys. Move the selection cursor to cell B1 to start the selection.

4. Hold down Shift and move the selection one cell over and one cell down. Cells B1,B2,C1,C2 should now be selected and the formula should show =sum(B1:C2

5. To finish the function, close the parenthesis. The cell should now show =sum(B1:C2). Press enter and the formula is entered.

To select a range of cell, hold Shift and move over the desired area.

To stop entering a selection or to start over, press Shift+Backspace

Errors are almost never entered directly into a spreadsheet but usually arise from problems which Gnumeric encounters during calculations. A list of errors with their meanings is presented in Section 5.2.5 ― Error Elements.

In an unusual case where it is needed, an error can be entered by hand like the entry of other elements. First the cell must be selected, then the error must be entered (e.g. #DIV/0!) and finally the Enter key must be pressed.

It is sometimes necessary to enter large amounts of data by hand into a spreadsheet. To facilitate this work, Gnumeric provides several techniques to facilitate the entry of large amounts of data.

If data are to be entered into a series of rows or columns, this region can be selected ahead of time thereby modifying the behaviour of the data entry keys (the Enter, Tab and arrow keys).

It is often necessary to enter a regular sequence of numbers or a repeated sequence of text. Gnumeric provides several ways to input series and sequences of this kind.

The simplest way to fill a series with the same element repeated involves entering the element once and dragging the selection box to fill that element repeatedly. For example, the text "employee:" could be input into cell C2. That cell could then be selected. The selection box is a thick white rectangle which surrounds the cell. This selection box has a small white square at the bottom right hand corner. If the mouse cursor is placed above this square box, it changes to a thin cross. If the left hand mouse button is clicked and held, and the mouse dragged to cell C10, Gnumeric will automatically fill all of the cells with the identical string.

An alternative way to enter data into a region involves first selecting the region, then typing the value and finally typing the Ctrl+Enter key combination. This will fill the whole region with the identical value which was originally entered.

A similar method is available to fill sequences of integers. If the example just given was altered so that cell C2 had the number 14 and the Ctrl key was held during the dragging of the selection, Gnumeric will automatically fill the cells C2 to C10 with the series 14,15,16,...,22.

More complex series and sequences of data can be entered with a similar mechanism.

To do an autofill:

An alternative to the last step involves using the menus. Once the first two values have been input, the whole range to be filled can be selected using the mouse and then the Autofill selection can be made from the Edit and Fill. This will automatically complete the series in the selected region.

Gnumeric is able to increment several types of data beyond simple integers. The procedure is the same as described above but involves different starting values. Gnumeric can increment:

Gnumeric can be explicitly told the cells to autofill as in the examples above, but it can also guess the number of cells to fill based on the length of an adjacent column or row. For example, if the cells B2 to B10 have information and cell C2 has the integer value "1", then selecting cell C2 and double clicking on the bottom rightmost box of the selection rectangle will fill the value "1" from cell C3 to cell C10.

The entry of large amounts of data into a spreadsheet is tedious work which is prone to repeated mistakes. Gnumeric provides a tool to automatically correct commonly made simple mistakes. The corrections are configured and activated using the `AutoCorrect' dialog, available via Auto Correct in the Tools menu.

Figure 5-4 The Auto Correct dialog.

This section has not yet been written.

Figure 5-5 Random Number Generation Tool Dialog

Use the random number generation tool to generate random numbers. This tool can generate random numbers from various probability distributions.

Specify the random distribution by selecting one of the items from the random distribution list. The following random distributions are supported: Discrete, Normal, Poisson, Exponential, Binomial, Negative Binomial, Bernoulli, and Uniform.

Specify the parameters of the selected distribution:

Specify the number of variables in the “ Number of Variables:” entry on the `Options' Page. This determines the number of columns of random values to be produced.

Specify the number of random numbers for each variable in the “Size of Sample: ” entry on the same page. This determines the number of rows of random values to be produced.

Figure 5-6 Some Example Data for the Random Number Generation Tool

Figure 5-7 Random Number Generation Tool Output

Data which already exist in an external file can be imported into a workbook first by opening the file into a separate instance of Gnumeric and then by copying the relevant data into the desired worksheet.

Data contained in external files in the formats of several other spreadsheet applications can be opened by Gnumeric. Gnumeric can open files in Applix (TM) format, GNU Oleo format, Lotus 123 (TM) format, Microsoft Excel (TM) format, Multiplan (TM) format, OpenOffice Calc or StarOffice Calc (TM) format, Plan Perfect (TM) format, Quattro Pro (TM) format, sc and XSpread format or in the Xbase format.

Data contained in external text files which are formatted in well defined ways can also be opened by Gnumeric. Gnumeric can open files formatted in Data Interchange Format, formatted as HTML tables, or formatted in text formats such as comma separated values or tab delimited records. The opening of text format files is configurable by the user so that text files which contain data formatted in a wide variety of schemes can be imported correctly into Gnumeric. Obtaining data from these files is explained in detail in the section titled Section 14.3 ― Opening Files.

Data which are opened into a separate instance of Gnumeric can be copied and pasted into the current worksheet with the copy and paste commands as explained in Section 5.7.2 ― Cut, Copy and Paste.

Data can be obtained from other Gnumeric workbooks or other applications using the mouse to copy data and paste them into a worksheet. This is explained below in the section titled Section 5.7.4 ― Cut and Paste Between Gnumeric and Other Applications.

This has not yet been written.

Gnumeric has been connected to external data sources such as stock quotation service data streams using an experimental plugin. This requires the ability to write programming code. Information on extending Gnumeric by developing plugins can be found in the section called Section 18.4 ― Writing New Plugins and the actual computer source code can be found in the directory gnumeric/plugins/sample_datasource/ of either a distribution of Gnumeric or in a code checkout from the revision control repository.

The simplest selection involves a single cell. In most cases, simply clicking with the left hand mouse button while the mouse cursor is over a cell will cause that cell to become selected.

The selected cell is indicated by a dark double line with a small square in the bottom right corner. The selected cell is the one that is currently in focus and will take any input for the keyboard.

Figure 5-8 A selected cell.

To make a cell become the selected cell, simply move the white-cross cursor over the cell and press the left mouse button. There is one exception: When on a cell that contains a hyperlink, the mouse cursor changes from the white cross to a hand and clicking the left mouse button activates the link. In that case the cell can be selected by clicking the middle mouse button.

Figure 5-9 Selecting a cell with the mouse cursor.

Several cells can be selected at once. When multiple cells are selected, the selected cells are indicated with a light, "baby" blue colour. When a single block of cells is selected, a double black line will surround the selection, and all cells except the first one to be selected will be shown in light blue.

Figure 5-10 A multi-cell selection

5.6.2.1. Continuous selections

To make a continuous selection of cells, move the white cross mouse cursor pointer to the cell at one corner of the block of continuous cells, click and hold the primary mouse pointer on that cell, drag the pointer to the opposite corner of the block of cells and then release the mouse button. The block of cells will now be selected.

The arrow keys can also be used to select a continuous block of cells. To add cells in this manner, first select the cell in one corner of the cell block with the primary mouse button, then hold the Shift key and use the arrow keys to expand the selection block.

Figure 5-11 Multiple cells being selected with the cursor.

5.6.2.2. Discontinuous selections

A discontinuous group of cells can also be selected, by selecting cells or block of cells while holding down the Ctrl key. To select a discontinuous group of cells, first select a cell or a continuous group of cells as explained above, then click and hold down the Ctrl while selecting more cells. All of the cells which are selected while the Ctrl key is held down will be added to the selection. All selected cells will be displayed with a light blue colour. Cells can be added individually or as continuous blocks. This selection process is additive with each selected cell added only once so selecting cells twice or more simply adds those cells once to the selection.

Figure 5-12 A discontinuous selection of cells.

There are several ways to make selections using the keyboard keys directly. These may be quicker than using the mouse pointer. As explained above, the simplest of these uses the Shift and arrow keys to make a new selection.

To select an entire row of cells, press the combination of Shift+space keys. This is equivalent to pressing the row label button on the left side of the sheet.

Figure 5-13 An entire row being selected.

To select an entire column of cells, press the combination of Ctrl+Space keys. This is equivalent to pressing the column label button on the top of the sheet.

Figure 5-14 An entire column being selected.

To select the entire sheet, press the combination of Ctrl+A keys. This is equivalent to pressing the button in the top left corner of the sheet.

Figure 5-15 The entire worksheet being selected.

The following list summaries keyboard shortcuts which can be used to select large cell blocks.

The easiest way to move or copy a cell or a group of cells is by selecting the cell or cells to be moved or copied, then using the mouse to drag the selection box to a new location.

The selected cells will be copied or moved to the new location. This will move both cells' content and formatting. When copying, relative cell references in all formulas will change as described in Section 5.2.4.3 ― Cell Referencing; when moving, relative cell references will remain unchanged.

Another, more flexible, way to copy or move a selection is to use cut, copy, and paste operations. These operations allow the user to copy or move selected cells to the clipboard buffer and then paste the contents of the clipboard buffer to a different location or a different workbook.

All of the methods described above to move or copy data create identical copies of the original cells in the new location. This means that both the contents and the formatting of the original selection are copied to the new location and also means that any data present in the new location are deleted. It is frequently important either to alter the data before it is pasted or to merge the data in the new location with the data being pasted. The Paste Special... command enables this.

The Paste Special... command can act exactly like the Paste command or may selectively paste the cell contents, the cell formats, the calculated values of the original cells rather than their contents. described in the previous section copies both contents and formatting of the original selection. If you need more options, use Paste Special... command.

The Paste Special... command can be accessed, after a selection has been cut or copied as explained above, in one of two ways. First, the location where the pasting should happen must be selected. The easiest way to do this is to select the single cell which will be at the top left hand corner of the region of pasted cells. Alternatively, the exact region into which the cells will be copied can be selected. After the selection is made, the Paste Special... command can be chosen from the Edit menu or the context menu obtained by clicking with the right mouse button in the cell area of the spreadsheet.

The Paste Special... menu item opens a dialog with three categories. By default, Paste Special... acts as if it were the Paste menu item.

The first set of choices allow the user to control the data pasted.The user can choose to limit the pasting to only the cell contents (no cell formatting is copied) or the opposite only cell formats copied (no contents). Furthermore, the user can insert the selection while transforming all the contents into values only. Formulas will be replaced by their values.

A second set of choices allows the user to perform simple mathematical operations during the paste. These operations use the current contents of the cells in the paste range and the contents of the clipboard. For example, if you are pasting a cell containing number 5 to a cell that currently contains number 10 and choose option Divide, the result will be 10/5=2. Choosing option None will just replace the previous contents of the cells by the contents of the clipboard buffer (this is the default behavior).

The final choices contains the following options:

Cut and paste commands described above allow copying and moving selections from one location in a spreadsheet to another, or between different spreadsheets. However, you can also use cut and paste to exchange information between Gnumeric and other applications, using so-called X clipboard which is shared by all graphical applications.

Whenever you cut or copy a selection in Gnumeric, it is placed both in Gnumeric clipboard and in X clipboard. In X clipboard, it is placed as text, with formulas replaced by their values and contents of different cells separated by spaces.

To paste the selection from X clipboard to an application, click with middle mouse button (if you have two button mouse, you can emulate the middle mouse button by pressing left and right buttons simultaneously). Most applications also allow pasting from X clipboard by using keyboard shortcut (usually Ctrl+V) or by choosing Edit ▸ Paste.

To paste a selection from another application to Gnumeric, place this selection in the X clipboard. Usually it is done by just selecting it with the mouse; some applications also allow you to use keyboard shortcut Ctrl+C, or choose Edit ▸ Copy. After this, you can paste it in Gnumeric by using Paste command described above. This command will automatically paste the contents of X clipboard if Gnumeric's own clipboard is empty.

Pasting X clipboard in Gnumeric will automatically launch the Text Import druid which will assist you in importing the plain text contents of X clipboard into the spreadsheet. The Text Import druid is described in Section 14.4 ― Importing Text Files.

This tab allows you to select the format for the cell's contents. You can select one of the many preset formatting styles which should be more than adequate for the vast majority of cases. If none of these meet the needs of the user, it is possible to create your own formats.

To use one of the preset formats, select the format category (such as Number or Date) by clicking on the corresponding radiobutton in the left side of the dialog. The right side of the dialog will show you how the selected cell would look with this format and give more options for the selected format.

The following is a list of all available format categories:

These two tabs are used to control user's access to cells and restrict values of data allowed in a cell. Unlike other formatting options, these two tabs have no effect on a cells appearance. These options are mostly used for writing templates and forms to be filled by others.

5.10.3.1. Protection Tab

Figure 5-21 Protection Tab

This tab allows you to see and change cell protection in imported Excel workbooks. Cell protection has no effect in Gnumeric: you can edit cells whether or not they are marked as protected. However, Gnumeric keeps the protection setting of imported Excel workbooks. If you later save your workbook in Excel format, Gnumeric will save the protection information too. For more information about cell protection in Excel, please refer to Excel documentation.

5.10.3.2. Validation Tab

This tab allows you to set restrictions on allowed values of data in the cells. If you (or someone else) attempts to enter a data that does not meet the set criteria, a warning (or an error message, depending on the options set in this tab) will be shown.

Figure 5-22 Validation Tab

This tab consists of two part. The first part, Criteria is used to set the criteria for the cell values. The second part, Error Alert, is used to choose the action when data entered does not meet the criteria.

To set the criteria for cell values, follow these steps:

1. Choose the type of data contained in the cells, using the Allow drop-down list.

2. Choose a condition that must be satisfied by the cells value, using Condition drop-down list. In these conditions, val stands for the cells value (for text, val stands for the length of text string) and min, max, and bound are constants that you need to specify.

3. Enter the values of constants used in condition. For example, if you chose condition min<=val<=max , you need to enter values of constants min and max.

After specifying the criteria, you need to specify how Gnumeric should respond to incorrect cell value. You can choose one of four possible actions from Action drop-down list:

None

Accept invalid value without any warning. Equivalent to having no validation.

Stop

Do not accept the invalid value. Show the user an error message which you need to specify (see below).

Warning

Show the user a warning dialog, giving him a choice whether to accept or reject the invalid value. You need to specify the message to use in the warning dialog (see below).

Information

Accept invalid values but show the user a warning dialog. You need to specify the message to use in the warning dialog (see below).

If you choose one of the options Stop, Warning, or Information, you must enter the message that will be show to the user in the error or warning dialog. Otherwise, the dialog will be empty so it will be completely useless. You need to enter the title (which will be used as the window title for the dialog window) and the message itself. For example, the values shown in Section 5.10.3.2 ― Validation Tab will produce the dialog shown in Figure 5-23.

Figure 5-23 Warning dialog in response to invalid input

This section has not yet been written.

To use advanced filter, you should have a few blank rows available in your worksheet to be used as a criteria range. These rows should not overlap with the rows in the table to be filtered.

Now copy all the column labels from the table you want to filter into the first blank row in the criteria range. Below the criteria labels, you can now type the conditions you want to match for the particular label. For example, under a label `Profit' you could type `>=1000'. The tool selects only rows that match all the criteria.

Figure 5-24 Worksheet containing the table to be filtered and a simple criteria

It is possible to have many conditions for a single label. For example, you can select the departments whose profit is either very high or very low. To do this, type, for example, `<=0' below the `>=40000' condition.

Figure 5-25 Criteria for selecting rows whose `Profit' column is between 0 and 40000.

To start the tool, select ``Advanced Filter'' from the ``Data'' menu. It brings you the advanced filter dialog. In the dialog, select the action you want to take.

Figure 5-26 The Advanced Filter dialog.

``Filter in-place'' writes the new table in-place. Note that you will lose all the rows in the table that will not match the criteria. ``Copy to a new location'' copies the selected rows into the same sheet but into the specified cell range. Type the cell range into the ``Copy to'' entry if you want this action to happen. The other options let you to copy the selected rows into a new sheet or a new workbook.

You should then specify the cell range containing the table to be filtered in the ``List range'' entry. The cell range containing the criteria is specified in the ``Criteria range'' entry. If the original table contains duplicate rows, you may also want to specify the ``Unique records only''. If it is checked on, the filter removes all duplicates.

To start the tool, you can then click the ``OK'' button and you will get the new table.

This section has not yet been written...

This section has not yet been written...

This section has not yet been written...

With data shuffling tool you can shuffle data in a given cell range. The tool can be started by selecting ``Shuffle'' menu item in ``Data'' menu. In order to use the tool give the input range in which the data to be shuffled is stored. The tool is able to shuffle the contents of the whole cell range, or, also shuffle data according to rows or columns. The shuffling method is selected under the ``Input Range'' entry. For example, if your data is grouped by rows then select ``Shuffle Method'' ``Rows''.

The default output method is to shuffle in-place. The shuffled data can also be written into a new sheet, new workbook, or into an existing sheet by giving the output range. If you select the ``Autofit Columns'' option, the width of each output column is automatically fixed according to the size of data in it.

This section has not yet been written...

This section has not yet been written...

This section has not yet been written...

First, select the ``Goal Seek...'' tool item from ``Tools'' menu. Specify the output variable cell (``Set Cell'') by typing the cell reference into the entry or by clicking the worksheet cell. If you are searching for the break-even point, for example, you should specify the cell reference of the profit calculation here.

Specify the desired result for the output variable cell into the ``To Value'' cell. In the search for the break-even, specify this to be zero.

Specify the input variable cell (``By changing cell'') by typing the cell reference into the entry or by clicking the worksheet cell. In the search for the break-even, specify the cell reference of the sales here. When you have done this, you may want to press the ``Apply'' button to start the tool.

Gnumeric will systematically iterate the model by changing the input value to achieve the desired result, if possible. If goal seek was successful the tool displays the message ``Goal Seeking with cell __ found a solution''.

It is possible that Gnumeric does not find a solution that generates the desired result. There may not be such a solution for the model, or, it may be too difficult to find. For example, the mathematical function behind the calculation may have many non-continuous points.

If Gnumeric did not find a solution that generates the desired result, you may want to try to specify a range (minimum and maximum) in which the value of the output variable should be. To do this, specify the ``Minimum'' and ``Maximum'' entries.

A simulation is the imitation of the operation of a real-world process or system.  The behavior of a system is studied by generating an artificial history of the system through the use of random numbers.  These numbers are used in the context of a simulation model, which is the mathematical, logical and symbolic representation of the relationships between the objects of interest of the system.  After the model has been validated, the effects of changes in the environment on the system, or the effects of changes in the system on system performance can be predicted using the simulation model. ²

Gnumeric includes a facility for performing Monte Carlo Simulation.  Monte Carlo simulation involves the sampling of random numbers to solve a problem where the passage of time plays no substantive role. ³  In other words, each sample is not effected by prior samples.  This is in contrast to discrete event simulation or continuous simulation where the results from earlier in the simulation can effect successive samples within a simulation experiment.  The Monte Carlo simulation will be enabled through the use of the Random Number functions as described in ??? and the results presented along with statistics for use in analysis. ⁴

The remainder of this chapter will illustrate use of the simulation tool using an example from Banks et. al. ⁵  A classic inventory problem is the newsvendor problem.  A newsvendor buys papers for 33 cents each and sells for 50 cents.  Newspapers not sold are sold as scrap (recycled) for 5 cents.  Newspapers are purchased by the paper seller in bundles of 10.  Demand for newspapers can be categorized as “good,” “fair,” or “poor” with probability 0.35, 0.45 and 0.20 respectively, with each day's demand being independent of prior days.  The problem for the newsvendor is to determine the optimal number of papers to purchase when the day's demand is not yet known.

The daily profit equation for the newsvendor is:

To set up the model, this example will use two tabs in Gnumeric, a tab labeled 'Profit' to calculate profit, and a tab labeled 'Demand Tables' to store the various tables needed to calculate the demand for any given sampling.

For the Profit tab, set up the profit tab as in Figure 6-1.

At the top of the Profit' tab, the Profit table will be entered .  There are three variables: Sale revenue, Cost and Scrap value, and they take the per unit coefficients of 0.5, 0.33 and 0.05 respectively.  Enter the coefficients in cells B13 through D13.  In cells B12 through D12, enter the equations for sale revenue, cost and Scrap value that are in the list below. In cell E12, enter the equation for Profit

Next, we add the values for the decision variable, which is the amount purchased, and the amount sold.  

Figure 6-1 Profit table for newsvendor example

Next, create the demand tables from which the demand will be generated.  In the tab 'Demand Tables' enter the values of the probability in cells B4 through B6 (B4: 0.35; B5: 0.45; B6: 0.2).  In cells C4, C5 and C6 enter the cumulative probability values (C4: 0.35; C5: 0.8; C6: 1) as shown in Figure 6-2.

Figure 6-2 Probability distribution for type of newsday

The next table is the daily demand for newspapers based on the type of news day.  The table Distribution of Newspapers Demanded is in cells A11 through D18 of the Demand Tables worksheet as shown in Table 6-1 and contains the daily demand distribution values.  The cumulative distribution tables in cells A21 through G29, shown in Table 6-2 are derived values from the Distribution of Newspapers Demanded using values in the top Distribution of Newspapers demanded table.

When these values are entered, the final results will look like Figure 6-3.

Figure 6-3 Completed probability distribution tables in Demand Tables worksheet

Finally, back in the Profit tab, the demand data will be filled in through the use of references to the Demand Tables tab as shown in Figure 6-4.

In the following cells, enter the equations below in the 'Profit' tab:

Figure 6-4 Profit table for newsvendor example

When done, the Profit spreadsheet will be setup with a profit equation, decision variables, and random events as shown in Figure 6-4.  The rand() functions in cells C17 and C18 return a random value between 0 and 1, which are used by the lookup() functions in cells B20, B24, C24 and D24 to calculate a randomly determined daily demand.  Next, this sheet will be used for analysis through the use of simulation.

To run the simulation, from the Gnumeric toolbar, select Tools → Simulation.  In the Risk Simulation dialog box that appears, the first tab is the Variables tab.  There are two entries in the Variables tab:  Input variables and Output variables (Figure 6-5).

Figure 6-5 Variables tab in simulation dialog box

Input variables are the cells which hold the functions based on random numbers of the type described in Section A.14.  In this case, they are the cells B17 and B18 in the Profit worksheet, which hold the rand() function.  Later, when the quantity purchased is a parameter set by the SIMTABLE function, cell B16 which holds the purchase quantity will be added to the range of input variables.

Output variables are the results of interest, or the dependent variable.  In this case, the dependent variables are the demand and the profit, which are in cells B20 and B21.

The next tab is the Options tab .  There are four settings in the options as shown in Figure 6-6.

Figure 6-6 Options tab in Simulation dialog box for newsvendor simulation example

The second pair of options are the number of iterations and the Max time.  In a simulation, each iteration is the equivalent of a sample.  A sample from a random distribution is taken for each of the input values (as specified in the Variables tab) and the resulting output value(s).  The more iterations, the better the estimate of the output value.  However, this also takes more time to run.  A Max time value is specified in seconds where the simulation will end without output if an individual simulation takes longer than the Max time allotted.  If this occurs (see Figure 6-7), the options are to either increase the Max time value, or decrease the number of iterations.  A more drastic option is to change the model so that fewer calculations or samples of random numbers need to be made.

Figure 6-7 Maximum time for simulation exceeded message box

The next tab is the Summary.  There are two boxes in this tab, the Simulation Summary and the Summary of results (see Figure 6-8).  In simulation summary, there is a description of the simulation parameters.

Figure 6-8 Summary tab for simulation tool

In the summary of results window, there are summary statistics for each round of the simulation.  If multiple rounds were done, the results of each round can be browsed by using the 'Prev. Sim.' and 'Next Sim.' buttons below the Summary of results box.  For each output and input variable, the summary shows the Min,  Average and the Max value across the iterations for that round of the simulation.  Note that for the input variables, this shows the random number that is the average, max and min.  If the statistics on intermediate values, such as a cost distribution, was desired, these intermediate values should be added to the list of output variables.

The last tab is labeled 'Output'.  This tab identifies the location where the output table will be generated.  There are two sets of options, first the Output Placement then Output Formatting as shown in Figure 6-9.

Figure 6-9 Output options tab for simulation

The default output placement is 'New sheet'.  This will create a new sheet in the Gnumeric workbook labeled 'Simulation Report (1)', where '1' can be replaced with another number if a tab labeled 'Simulation Report (1)' already exists.  The option 'New workbook' creates a Gnumeric workbook named 'Book2.gnumeric' with a tab labeled 'Simulation Report.'

The third option is to embed the output table into an existing worksheet.  This is done by specifying the 'Output range'.  Note that the output range must be large enough to include the entire table, including heading information.  For a single round this requires 11 rows and 16 columns.  For example, the range Profit!A24:P35 would contain the statistics for one round with the three input variables and two output variables.  As input and output variables change, or the number of rounds of the simulation change, the number of rows required will change.

For output formatting, their are four options.

The simulation output provides statistics on the output and input variables for each round.  The statistics are calculated over the iterations in a single round of the simulation.  These statistics for each variable are:

Figure 6-10 Simulation output example

The output will include a heading, then a table for each round of the simulation.  Judicious choice of output variables will also include any intermediate values of interest in the simulation in this table.  Each row of the output table has statistics of the values of a variable over the iterations of the simulation as shown in Figure 6-10.

The output will be of the input variables and the output variables that were variables tab of the Simulation window .  For the input variables, the output will be the statistics of the random variable used in modeling the input variables.  For the output variables, the statistics will be of the output variable.  These statistics, in particular the standard deviation and confidence interval, should be examined to ensure the simulation was at a precision adequate for the purpose.  Some notes on how to use these statistics for refining the simulation design can be found in Section 6.4.6 ― Determining the number of iterations.

The SIMTABLE function is intended to change a variable in the simulation so that each round of the simulation can be used to evaluate a different scenario.  This automates the use of simulation for what-if questions or to create a set of possible outcomes to a situation.

In this example, we will use the SIMTABLE function to find the optimal quantity of newspapers to buy.  For the purchase quantity in our spreadsheet, we will replace '50' with the following formula in Profit!B16:

    Profit!B16 = SIMTABLE(50,60,70,80,90)
    

Each entry in the list of the SIMTABLE arguments is a value that will be used for the purchased quantity.  Each entry corresponds to one round of simulation, as used in Figure 6-6.  In this example there are 5 entries to the SIMTABLE list, so '5' will be entered into the 'Last Round #' option in the Options tab of the Simulation dialog.

Figure 6-11 Simulation output example using SIMTABLE and several rounds

When this simulation is run with 5 rounds, the summary of results will have one entry for each round, with each round using a different entry from the SIMTABLE function for the purchase quantity. The results for the various rounds can be previewed using the 'Prev. Sim.' and 'Next Sim.' buttons.  The output also has one table for each round of the simulation.

As seen in Figure 6-11, each value in the original SIMTABLE statement corresponds to a simulation round, with the Purchase Quantity taking on the value from the SIMTABLE list.  The analyst can then record the Profit statistics (mean, variance, skewness, kurtosis, 95% confidence intervals) and determine if the simulation results are of sufficient resolution for the analysts purposes.

The use of SIMTABLE to change parameters within the simulation provides a convenient method to do what-if analysis, and analyze the results as a whole.

In simulation, one major question is how many iterations are needed to reach a chosen level of precision in the results.  Simulation as a tool provides an approximation of the actual relationship between the input and output variables.  The precision of the approximation is based on the number of iterations of the simulation done.   More iterations in the sample lead to greater precision.  But the relationship between iterations and precision depends on the relationship between the variables in the precision.  In addition, the analyst must decide which output variable is the variable of interest, and what degree of precision is required.  The next step is to determine a sufficiently large number of iterations R be used to satisfy:

Where Θ-hat is the estimate of the mean, Θ is the actual mean, ε is the specified error, and (1-α) is the probability that the estimate is within ε of the actual value (i.e. the (1-α) confidence interval). Common values of (1-α) are 95% and 99%. The Simulation Report from Gnumeric includes values for the 95% confidence interval as shown in Figure 6-10.

The general procedure is as follows:⁶

In this example, to estimate the profit to within ε=0.05 , first run the simulation with 1000 iterations and a purchase quantity of 50 results in the following

Taking the variance of the table, and setting ε=0.05 and α=0.05 , lookup z_α/2 from a standard normal table. z_α/2=1.96 so we have

Therefore, the minimum number of iterations is 3857.  The simulation can then be re-run with 3857 iterations to create a 95% c.i for profit where ε <=0.05 In this example with 3857 iterations, we get the following Simulation Report table:

As expected, the 95% Confidence interval for Profit is less than 0.05. For the newsvendor example, the next step would be to look at the confidence intervals of the profit for all values of purchase quantity, and verify that this confidence interval is adequate for the decision to be made.