|Help Topics||Commands||Index||Custom Apps||Save & Print|
A spreadsheet allows one to display and compute data stored in the cells of a grid, and relate rows and columns by formulas.
Formulas in a spreadsheet allow cells to be related so that the entry of one cell can be calculated from a value or values in different cells.
See also Insert Function, Examples.
Some general tips on using formulas are listed below:
=B1^2+5 If cell B1 has a value of 2, the cell containing this formula will get the value of the square of cell B1 plus 5 (i.e., 2^2+5=9).
=$B1^2+5 Fixes the column reference (column B) and allows the row reference (row 1, 2, 3, etc.) to vary.
=B$1^2+5 Fixes the row reference (row 1) and allows the column reference (column B, C, D, etc.) to vary.
=$B$1^2+5 Fixes both the column (column B) and row (row 1) references.
See also Examples.
Fill Down, Column Formula
Both the Fill Down and Column Formula entries of the Edit menu allow you to apply formulas to entire regions of cells at a time. This saves time in entering formulas.
See also Examples.
See also Getting Started.
Choose Insert | Function for a listing of common functions. Click on a function name below for a full description of its use:
See also Algebra & Functions Commands.
|Basic Statistics||Trigonometric||Combinatorics||Other Functions|
If inserted into an empty cell, simply type between the parentheses (i.e., "=functionname()"). If the selected cell is not empty, the function name will be inserted at the end of those contents. To be executed as a formula, you must use the equals sign "=" immediately preceding the function name.
Shown below are two examples of using the Fill Down option with formulas. Example 2 shows the use of fixed cell references.
Example 1. Using Fill Down with variable cell references.
|Suppose that you have entered a value into a spreadsheet, as shown in cell A1 at right. Next, a formula will be used to relate each cell in column A to the cell above it.||Type a formula into cell A2 so that its value is 5 more than the value in A1 ( =A1+5). Then select cells A2 through A4.||Choose Edit | Fill Down . The cells to the right display the formulas contained in each cell. NOTE: If a cell contains a formula, the numerical value of the formula is shown unless the individual cell is selected.||The numerical values corresponding to each of the formulas described above are shown here.|
Example 2. Using Fill Down with fixed and variable cell references.
|Suppose you have data entered in column A. Next, a formula will be used to relate each cell in column B to a fixed cell and a variable cell in column A.||Enter a formula into cell B1 so that its value is the square of cell A1 plus the corresponding column A cell value. Then select cells B1 through B4.||Choose Edit | Fill Down to obtain the desired formulas in cells B2 through B4, as shown to the right. Notice the fixed and variable references used.||The numerical values corresponding to each of the formulas as described above are shown here.|
Edit and Insert Options
The Edit and Insert menus offer functionality to move and shift cells, delete and insert rows and columns, and to modify settings and styles of spreadsheet cells.
Move and Shift Cells
Use the Edit menu, toolbar icons, or keyboard shortcuts for quick cut, copy, and paste options for selected regions of cells. Also shift cells or regions of cells at a time.
See also Delete Column(s)/Row(s) and Insert Column(s)/Row(s).
Note: The temporary storage will be replaced when anything else is cut or copied.
Note: Any cells that were to the right of/below the selected cells also get shifted to the right/down to make room for the selected cells. Cell formulas are updated where necessary as cells move.
Delete and Insert Row(s)/Column(s)
Use these options to remove and shift entire row(s) and column(s) of a spreadsheet at a time:
See also Shift Cells right or down.
Settings and Styles
Customize the display of cells including the vertical alignment, the number of digits displayed (including increase and decrease by one), and the mode. Also choose to edit the column name from the default alphabetical name.
See also Sort data.
Note: When you manually type a number in a cell that is not a formula it is assumed that the desired # of digits (decimal places) is already set. The Set Digits options only apply to formulas.
Note: If a column is renamed, you may use the new column name within a formula (as long as the first characters in the name are not numerical) by typing the first characters of the name until the first space. For example, suppose column A is renamed to "Test." In column B type =A1*3 OR =Test1*3.
Most tools available in spreadsheet are also available within the Statistics | Data Analysis tool. Options to Sort, Graph, or view Summary Statistics, activate a Solver, or a Chi-Square Test are explained below.
The two Tool menu options for sorting data are Sort All Rows by and Sort All Columns by. Two different methods for using these features are suggested below:
Note: This method is desirable if the spreadsheet cells are related to one other (e.g., bivariate data with pairings by rows).
Note: This method is not desirable if the spreadsheet cells are related to one other (e.g., bivariate data that include specific pairings).
Spreadsheet graph options are organized by type below. The links on each graph type redirect to additional help topics within Data Analysis (or Advanced Apps for Contour plots):
|Use a single column, or Control+Click to select more than one column of data for a "stacked" graph.||Two (or more) columns must be selected. Scatterplots require one independent and one dependent column.||May involve more than one column of data (multi-column graphs will be color-coded).||Uses the number of rows and columns to determine the size of the grid and the entries of these cells as the "height" of the contour plot.|
Choose Tools | Summary Statistics to compute Descriptive statistics (n, mean, minimum, q1, median, q3, maximum, sample standard deviation, and sample variance) and analyze Regression based on a selected model (linear, quadratic, cubic, quartic, power, exponential, logarithmic, polynomial, sinusoidal).
Note: For Regression analysis first choose the independent and dependent variables, then click OK. A separate Regression Analysis Frame will appear with Results, Graph, and Residuals in tabs across the top.
See also Statistics & Probability for additional help topics.
To use the Solver tool, first enter the following information into a blank Data Sheet:
To use the Chi-Square Test:
Choose a data set from the Data menu for quick access to pre-loaded data examples.
See also Data Sets for more information.