# Chapter 1

# Using BeatWare Sum-It

# Introduction to Sum-It

BeatWare Sum-It is a full-featured spreadsheet application that performs calculations with exceptional speed. To allow you to take advantage of this powerful tool as quickly as possible, we have designed Sum-It to resemble other familiar spreadsheet programs in both appearance and functionality.

This brief guide introduces you to the application. It includes a tour of Sum-It features and functionality, and a reference section summarizing menu commands and keyboard shortcuts.

## Text Conventions

Convention | Appears as
Keyboard shortcut | Cmd+A, where Cmd is Command key on Macintosh-type keyboard and A or Alt key on PC-type; + sign indicates that you hold down a key or keys while pressing another.
Menu path | Menu commands are written starting with the main menu heading, followed by the menu item, followed by the submenu item or dialog checkbox: Text.Font.Courier.
Emphasis | boldface

# Installing Sum-It

To install Sum-It you must be running BeOS Release 4 or better on either a Power PC 603 or 604 or Intel Pentium machine. For BeOS installation instructions, see the BeOS User's Guide.

## Launching Sum-It

To launch the application double-click on the Sum-It icon. This brings up a new, untitled worksheet. You can also double-click on a Sum-It file to open the file and launch Sum-It simultaneously.

To open another worksheet while in the application, select File.Open (Cmd+O) and choose a file from the selection window that appears.

The files you can see in this window are Sum-It files, text files, and SYLK files (the format used by Microsoft Excel(TM) to export to other applications). For more on file types you can open in Sum-It, see the section "Importing Files."

To open a new worksheet, select File.New or press Cmd+N.

# The Sum-It Window

The Sum-It window represents a worksheet -- a grid composed of rows and columns. The windows features are summarized in the following table:

Feature | Does this
File tab | Refers to the yellow tab at the upper-left of the window. To reposition the window, click on the tab, hold down the mouse button, and drag. Clicking on the double-box icon of the File tab alternates the window size between full screen and last customized size. To close the window, click in the single box at the left end of the tab. The name you give the file appears in the tab.
Menu bar | Displays the Sum-It main menu: File, Edit, Format, Data, Chart, View and Help. Click on a main menu heading to open it and display menu items. A menu remains open until you click outside of it or make a selection from it. Important menu features are discussed in this chapter; all menu items are summarized in "Appendix A: Menus and Keyboard Shortcuts."
Formula bar | The formula bar (immediately below the menu bar) is where you edit cell contents: click in the bar and edit your data. You can leave edit mode by entering the contents of the cell or by exiting without recording your edits. To enter the contents, press Return or click on the green checkmark icon at the left end of the formula bar. To exit without entering, click the red X or press the Escape key.
Icons | Green checkmark icon: Enter data in a cell. Red X icon: Escape formula bar without entering data. Sigma icon: Sum a range of values.
Blank box below icons | Click here to highlight entire worksheet.
Column and row heads | Cell address components. Click in column head to select entire column, in row head to select entire row.
Address box | Lower-left corner. Shows address of current active cell or of a range of selected cells. Click in address box to bring up Go To dialog.
Horizontal and vertical scroll bars | Click on arrows or drag sliders to scroll worksheet horizontally or vertically.
Dotted area in lower-right corner. | Click, hold down mouse button, and drag to resize window

# Navigating a Worksheet

The table below summarizes worksheet navigation. Navigation in edit mode (when you are entering or changing data) is covered in the section "Entering and Editing Data."

To | Do this
Advance active cell indicator. | Use arrow keys to move by one cell in any direction.
Highlight range of cells in direction of arrow key | Press Shift + arrow key.
Advance active cell indicator by one page in direction of arrow. | Press Cmd + arrow key.
Jump active cell indicator to the last cell in a row or column. | Press Control + arrow key.
Deselect highlighted range | Press the Escape key.
Go to a specific cell. | Open the Go To dialog box, available either by clicking on the left corner of the Sum-It window or by selecting Go To from the Data menu. Enter cell or range of cells and select OK.

# Entering and Editing Data

In Sum-It you enter data into an empty cell. You edit when there is already data in the cell; new data overwrites the existing data. Data can be a numeric value, a date, or text. Data is a constant value; once you enter it into a cell, it doesn't change unless you edit or update the value. A formula, however, results in a dependent value, and can change when other values in the worksheet change (see the section "Working With Formulas" for more information).

To enter data into a cell, activate it by clicking on it, type your entry and press the Return key. You may also click on the green checkmark to register the entry. To clear data from a cell before entering, click the red X icon or press the Escape key. To delete an entry, activate cell and press the delete key.

Arrow keys may also be used to enter data and move the cursor one cell in the direction of the arrow. To enable the arrow keys to enter data, select the "Arrow key enters/data moves cursor'" option under View.Settings.Misc.

Entries that are too large to fit within the boundaries of a cell flow over into adjacent empty cells. If adjacent cells are not empty, an entry appears truncated. To see truncated text or numbers displayed in full, enlarge the cell by moving the column (see "Resizing Rows or Columns").

## Data Entry Tips

Text:

- Enter text as you do other data: type it in the formula bar and press Return. Text aligns left by default. Text that is too long to fit in the cell spills into adjacent cells if they are empty, or appears truncated if they are not.
- Text which begins with a number or mathematical operator such as * or - requires a single or double quote at the beginning to indicate entry is text. Example, to have the label (as of December 31, 1997) appear in parentheses, type `(as of December 31, 1997).

Numerical Values:

- Enter numbers without commas or other formatting symbols. To display $10,000, enter 10000, then format number using the options under the Format menu.
- Enter negative numbers by placing a minus sign (-) in front of the entry; i.e., -100.
- Enter fractions and percentages as decimals. Example, 50% is entered as 0.5. To display as 50%, use the Percent option under the Format menu.

Dates:

Entering dates is dependent upon the user preferences selected under View.Settings.Dates. Sum-It will only recognize dates entered in accordance with the preferences set. All dates must contain a day, month and year. Example, Date Separator is chosen as '/' and Date Order is set at MDY. To enter the date January 1, 1998, type 01/01/98. (Leading zeroes are not required).

TIP: To display only the month and year as January 1998, enter 01/01/98, then use Format/Date and enable the month and year only.

# Working With Formulas

A formula is a sequence of values, cell references, names, functions, or operators that produces a new value from existing values (i.e., data you've entered). A value that results from a formula is a dependent value and may change when other values in your worksheet change.

## Components of a Formula

A formula combines constant values and operators (such as + or -) to produce new values.

### Operators

Operators and the order of precedence are shown in the following table. Arithmetic operators represent the basic mathematical operations; Comparison operators are used to indicate the relative relationship of different values. The Order of operators column shows the order in which the mathematical operations are performed within a formula:

Arithmetic operators | Comparison operators
Addition: + | Equal: =
Subtraction or negative value: - | Greater than: >
Division: / | Less than: <
Multiplication: * | Greater than or equal to: >=
Exponentiation: ^ | Less than or equal to: <=
| Not equal to: <>

Order of operators: Negation (-), Exponentiation (^), Multiplication and division (* and /), Addition and subtraction (+ and -), Text joining (&), Comparison (=, < , >, <=, >=, <>)

### Using Parentheses to Alter the Order of Operation

To alter the order of operation, use parentheses to group operations in your formula. For example:

This formula | Produces this value
3+2*5 | 13
(3+2)*5 | 25

## Displaying Formulas in Your Worksheet

To display formulas in the worksheet instead of resulting values, select Data.Display Formulas.

# Understanding Cell References

Every cell has a unique address, derived from its coordinates within the worksheet grid. A cell which lays at the intersection of column B and row 10 has the unique cell address B10.

Note that the address of the active cell or range of cells is always displayed in the lower-left corner of the Sum-It window.

## The Role of Cell Addresses in Formulas

Cell addresses are used much the same way that the x is used as a variable in basic algebra. The formula 3+2=5 can also be written as 3+x=5 where x=2, or 3+B10=5 where the value in cell B10=2

By creating formulas that use cell addresses rather than actual numbers, you can write the formula once and have it automatically recalculate a value every time you update your data.

To enter a cell address into a formula simply type the address as you would any other number. For example, to add the contents of cells A3 and A5, type A3+A5 and press the enter key.

The same may be accomplished using your mouse and the formula bar. To add the contents of A3 and A5 in cell A10, begin by clicking on cell A10 to activate it and then, click on the formula bar, Now click on cell A3. You will see `A3' in the formula bar. Press the + key on the keyboard and click on cell A5. You will now see `A3+A5' in the formula bar. Click on the green checkmark icon to enter the formula.

# Using Pre-Defined Functions to Simplify Formulas

Sum-It includes over 80 pre-defined mathematical, financial, and logical functions. A list of these functions is available in the Edit.Paste Function menu. A brief description of each function appears when the function name is highlighted.

An example of a pre-defined function is Sum. The sum of cells A1 through A5 plus B15 plus 100 may either be accomplished by manually typing A1+A2+A3+A4+A5+B15+100 or more quickly by using the pre-defined Sum function. Every function has a syntax associated with it, or a very specific way in which you must enter the data so the function will produce accurate results. All function syntax begins with the function name, followed by an open parentheses. Within the parentheses the function calls for arguments which can either be entered as specific values (i.e., 100) or as cell addresses like A5. Arguments must be separated by commas, and closed with a closing parentheses. Example: Sum(list):

The argument (list) calls for a listing of the numbers or cells to be summed. Any or all of the following values can be included in a list: 100, A1:A3, B15.

Example If(condition, trueval, falseval)

An If Function checks a condition. If the condition is true, it returns the value specified as the second argument, trueval. If the condition is false, the function returns the value specified in the third argument.

If the value in cell A10 is greater than the value of cell A9, then the value of A10 will be displayed. If not, the cell will display a value of zero.

Functions may be selected from the Edit.Paste Function menu. Functions are organized by group. To change the group, click on the group button at the top of the dialog box. Select function, open parentheses, input arguments according to specified syntax, close parentheses and press Enter.

> TIP: Give your cells names to find your data more quickly. For example, when doing a number of home mortgage calculations, you can simplify by naming the cell containing the current interest rate, "interest'". Then, instead of having to remember that the interest rate is in cell D11, you can simply use "interest'" in place of the cell address in all your formulas. To name cells or ranges of cells, highlight the cells and select Data.Names from the menu. Enter a name and press OK.

# Copying and Moving Cells

You can copy the contents of a cell to another cell by activating the original cell and selecting File.Copy, then activating the destination cell and selecting File.Paste.

> TIP: The menu items Data.Fill can be used instead of Copy.Paste for creating long rows or columns of identical values (an expense which is constant each month, i.e., rent). Enter the value once, then highlight the row or column to be filled, including the starting value. Select Data.Fill Right (for rows) or Data.Fill Down (for columns). The row or column automatically fills with the value in the first cell.

When a cell containing a formula is copied and pasted elsewhere, all cell addresses are updated to reflect entry at the new destination. For example, if the contents of cell A6, Sum(A1:A5), is copied and pasted in cell B6, Sum-It automatically updates the cell addresses to reflect placement in column B. The pasted formula would read Sum(B1:B5) and display the total of the values in the cells directly above it.

You can move cells from one location to another by selecting File/Cut and File/Paste. Once again, cell references update automatically to reflect their new location on the worksheet.

> TIP: You can move cells with the mouse by highlighting the cell or range of cells, and holding down the mouse button. When the hand disappears, drag cells to their new location. Release mouse button. Holding the CTRL key when releasing the mouse button brings up a menu of move options.

If cells containing constant numeric values are relocated on the worksheet, all formulas dependent on that content will be automatically updated to reflect the move.

# Using the Paste Special Feature

The Paste Special dialog is a useful Sum-It feature that lets you select which specific attributes you want to copy from a cell. You access Paste Special through the Edit menu, or by pressing Control+Cmd+V. The table below summarizes the operation of the Paste Special dialog:

Dialog item | Does this
Paste Format | Pastes only the format from one cell to another or to a range of cells.
Paste Content | Pastes one of the following: Values-pastes the displayed value only. Formulas-pastes the formula, which is updated to its new address; Same as Paste. References-Pastes the copied cell's address into the new cell.
Original Cell References | Duplicates the formula of the copied cell exactly, retaining the original cell references.
To Selection Only | Limits the paste to the range of highlighted cells if the number of copied cells exceeds the number of cells in the paste range.
Transpose | Transposes a row of cells into a column of cells and vice versa.
Overwrite | Paste overwrites existing cell entry
Add | Adds the value of the cell you cut/copied to the current value of the paste cell.
Subtract | Subtracts the value of the cell you cut/copied from the current value of the paste cell.
Multiply | Multiplies the value of the paste cell by the value in the copied cell.
Divide | Divides the value of the paste cell by the value in the copied cell.

# Formatting the Worksheet

## Changing Font Attributes

You can change the font, font size, and font style of every cell by first highlighting the cells to be formatted and then selecting the desired format from the Format menu.

## Changing Display Colors

The font color and background cell color may be changed similarly by highlighting a cell or selection of cells and selecting Format.Cell Color from the menu. Under Format.Cell Color.Other, there is a color panel which provides you with the opportunity to customize a color by dragging the red, green and blue sliders.

> TIP: Using colored type and cells is a good way to highlight information or to designate categories (e.g., make all expenses data red) on your worksheet. This will make your worksheet easier to read.

## Aligning Entries

Align General aligns text to the left, numbers to the right of the cell. Align Left, Align Right, and Align Center align all data in highlighted cells to the left, right, and center respectively.

## Formatting Numerical Values

Format numerical values after entering them. General (the default) displays the minimum number of digits necessary to approximate the value of the number which fits the cell. If the entire number can't be displayed at the cells current width, Sum-It converts number to Scientific notation.

Currency displays number as a monetary value. The number of decimal places displayed can either be controlled by selecting Format/Digits or recorded as a default setting under View.Settings.Numbers. For a number to appear in a cell with the % sign, enter it as a decimal, such as 0.05 and format with Percentage. Fixed formatting rounds the number to the nearest whole number. Scientific formats in scientific notation.

## Formatting Dates

Use the Date/Time Format dialog to set preferences for these entries. See the "Dates'" section in "Data Entry Tips'" for more information.

## Resizing Rows or Columns

To resize a column, move the cursor into the row of letters at the top so that it displays the pointing finger. With the finger directly over one of the lines separating the columns, click and hold the mouse button while dragging the column boundary to the left or right.When the column is at desired width release the mouse button. Use the same procedure to resize the rows in a worksheet, clicking and holding the mouse button while the pointing finger is directly over the line between two numbers.

> TIP: You can resize multiple adjacent columns or rows at once by highlighting a range of columns/rows and dragging the dividing gridline of any one column/row in the range. All selected columns/row will resize to the same width/height.

# Saving Your Work

To save a file a new file or to change the name or path of a previously saved file, select Save As from the file menu. In this window you name the file and set a path to the place on your hard drive where you want to save it. The BeOS also gives you a set of menus with the file saving window which allow considerably expanded options for handling the file within this window. See the BeOS User's Guide for more information.

For previously named files, select Save (Cmd+S).

# Printing Your Worksheet

Choose File.Page Setup to set paper type and document orientation, then use the Print command (Cmd+P) to print your work.

# Advanced Features

## Importing Files

If you have the correct translator (datatype or data handler) installed in the Sum-It Filters folder, you can open a file from another application as if it were a Sum-It file (by using Edit.Open, Cmd+O, or dragging and dropping the file onto the Sum-It icon).

The Excel 5.0 datatype handler included with Sum-It can convert Excel 5.0/95 to Sum-It. Sum-It also automatically converts SYLK files (an Excel export format). With SYLK files Sum-It warns you, if necessary, if the file contains formulas Sum-It cannot read; Sum-It enters this formula as a string that you can correct later.

## Sorting Data

To sort rows or columns of data, highlight the range of values (do not include rows or columns containing headings or labels). From the Main Menu, select Data.Sort. Indicate whether the data is entered in rows or columns. In first NoKey box, select row/column which contains the criteria for the primary sort. Indicate whether sort is to be listed in ascending or descending order. The second row can be used to set a secondary sort criteria and similarly with the third row. Only the primary sort criteria is required. Press OK

## Charting

To create a chart, enter the data in a tabular form. Enter X-axis labels in the cell directly above the data, enter Y-axis labels in the cell directly to the left of the data. Highlight range of values. Do not include labels in range. Sum-It creates the chart and places it in the file containing the data. To modify the appearance of the chart, add titles, etc., click on the chart border to activate it and select Chart Options from the main Chart menu.

Charts can be easily resized by holding the bottom-left corner with the mouse and dragging inward or outward.

> TIP: Drag and drop Sum-It charts into BeatWare Writer for compelling reports. Highlight the block of cells behind the chart with the mouse. Then, while holding down the CTRL key, grab the gray, highlighted area with the mouse and drag the frame into Writer. If you are inadvertently picking up Sum-It's gridlines when you drag, turn the gridlines off, View.Settings.Worksheet.Display Grid.