ELF 101

The Introductory Statistical Package





Requires Windows 3.1 or Windows '95, 5 megabytes of hard disk space and 4 megabytes of RAM.






The Winchendon Group, Inc. help@winchendon.com

(703) 960-2587 www.winchendon.com

ELF 101 The Introductory Statistics Package, the manual and the accompanying disks are Copyright © 1996 The Winchendon Group, Inc. All rights reserved.

ELF is a registered trademark of The Winchendon Group, Inc. Windows 3.1, Windows '95 and Excel are trademarks of Microsoft, Inc. Lotus 1-2-3 is a trademark of Lotus, Inc. Quattro Pro is a trademark of Corel, Inc.

Table of Contents

  1. Chapter 1--Introduction 5
  2. Chapter 2--Preferences 7
  3. Chapter 3--Spreadsheet 11
  4. Chapter 4--Graphics 47
  5. Chapter 5--One-Way Analysis of Variance 57
  6. Chapter 6--Two-Way Analysis of Variance 59
  7. Chapter 7--Correlation 61
  8. Chapter 8--Simple Statistics 63
  9. Chapter 9--Significance Levels 65
  10. Chapter 10--Stepwise Regression 67
  11. Chapter 11--Paired t Test on Means 69
  12. Chapter 12--Unpaired t Test on Means 71
  13. Chapter 13--Databases 75
  14. Chapter 14--References 79




Introduction

Chapter 1--Introduction





ELF 101 is part of the ELF Statistical System. ELF 101 is designed for students taking an introductory course in statistics, econometrics, psychometric, sociometrics, or biometrics. It contains all the statistical procedures that the major text books in these fields use. In some cases, ELF 101 has more features than will be needed in the typical introductory course. Most colleges provide access to statistical packages such as SAS or SPSS either on mainframes or through a campus Local Area Network (LAN), but many do not have dial-in access from home or dorm. Also SAS and SPSS are overkill for the introductory class. Many students find the range of statistical procedures confusing ("Correlation? Is that Pearson or Spearman?"). The choice of options within a procedure can be puzzling.

Many statistical programs with a mainframe heritage are not user friendly: they demand precise commands and do not make effective use of modern graphical user interfaces available today. Error messages are frequently cryptic and intimidating.

These programs are expensive, difficult to install, and require large amounts of memory and hard disk space.

ELF 101 meets all of these problems. It is inexpensive enough for a student to purchase as a course supplement. It has a modern graphical user interface with friendly, informative error messages.

ELF 101 make limited demands on a computer system. It will run on virtually any PC with Windows 3.1 or Windows '95. More precisely, it takes up no more than 5 megabytes of disk space and requires 4 megabytes of RAM.

Specifically, ELF 101 offers the student one-way analysis of variance, two-way analysis of variance, correlation coefficients, crosstabs, sophisticated graphics, an Excel compatible spreadsheet, regression, paired t test on means, unpaired t test on means and the significance level of normal, binomial, chi square, t, F and Poisson distributions.

This book is a reference manual on how to use ELF 101 and provides a basic information about each of the statistical procedures involved. For each procedure, this book summarizes



While ELF 101 is designed for a student in an introductory class, it is a serious statistical package. For example, a database can hold more than 250 variables and over 18,000 observations. The regression procedure can analyze over 100 independent variables.

Technical support for ELF 101 can be reached at:







For information on special offers for ELF 101 users who want to upgrade to a more powerful version of the ELF system:





Chapter 2--Preferences





General

Show Variable Labels If this box is checked, ELF will show variable labels along with variable names on the Input Window
Default Database Type These three radio buttons determine what the default database type will be.
Excel Use an Excel4 spreadsheet file (.XLS) with the first row a list of variable names and labels. Each column will be headed by the variable name and label. Columns without variable names are not allowed.
TWG Use a TWG database. There are actually two files in a TWG database. The DCT file has one line for each variable. Each line has the variable name. The TWG file has the data with one line for each data point. Variable labels are not saved.
TWG2 Use a TWG2 database. In a TWG2 database, the first line has a list of the variable names and labels. Later lines are one per observation.
Directory Default drive and directory to search for a database.




Graphics

2D or 3D These radio buttons determine if the default graph is two- or three-dimensional.
Line If this radio button is selected, the default is a line (tape in three dimensions) graph.
Vert Bar If this radio button is selected, the default is a vertical bar graph.
Horiz Bar If this radio button is selected, the default is an horizontal bar graph.
Step If this radio button is selected, the default is a step graph.
XY Scatter If this radio button is selected, the default is a XY scatter graph and is valid only in two dimensions..






Spreadsheet

Auto Recalc If this box is checked, ELF will automatically recalculate the spreadsheet database every time a change is made. This can be time consuming for large databases so there are advantages to requesting manual recalculation.
Move Down If this box is checked, ELF will automatically move down to the next observation when entering data.


Spreadsheet

Chapter 3--Spreadsheet





Introduction

A spreadsheet is a computerized way to enter numbers into a table and to automatically update calculated values. A spreadsheet looks very much like the tables of data presented in statistics books which makes it ideal for data entry and (1) manipulation .

Data entry and manipulation is done with the ELF spreadsheet. This spreadsheet is similar to commercial spreadsheets such as Microsoft's Excel®, Corel's Quattro Pro®, and Lotus 1-2-3®. It is most similar to Excel®, and can read Excel 4 spreadsheets directly. Data can be imported from Quattro Pro® and 1-2-3® if it is first converted to an ASCII text file.

ELF contains many useful capabilities including:



Entering Data

This section shows how to create a database in an ELF spreadsheet, navigate an ELF spreadsheet, and enter data.



The Basics



The Basics

To Do This... Do This...
An ELF spreadsheet must follow these rules:
  1. The top row (across) must contain the variable names.
  2. Each column (down) of data must have a unique variable name in its first row.
  3. Each observation must occupy one and only one row.


For example in the database HARMAN.XLS, the first row has the variable name for the five columns. The variables are POP, EMP, PROF, SCHOOL, and HVAL. Also notice that there are 13 rows, but only 12 observations because the first row is used for variable names.

ELF is not case sensitive So you can refer to POP as pop, Pop, or PoP.
ELF does not require variables to be numeric Text strings can (and should) be used where they are more meaningful. Of course, it is impossible to take the average of "cat" and "dog" so there are instances where the analysis requires numeric data.




Getting Started

Getting Started

To Do This... Do This...
Start a Spreadsheet Use the menu sequence Windows | Spreadsheet. You will be presented with a blank spreadsheet.
Clear a Spreadsheet Click on the Clear Button.
Create a Variable Name Move the mouse arrow ("go to") to the top cell in a column such as cell A1. Click on the cell, and the box cursor should outline A1. Type the variable name such as POP and press the [ENTER] key. (As soon as you press a key, the cursor will change from to .
Enter a Number Position the mouse cursor on the desired box. Type the number and press the [ENTER] key. You can enter a decimal point if appropriate.
Commas If a comma is entered the spreadsheet will not show it.
Dollar amounts To enter a collar amount, type the dollar sign ($) and optionally the appropriate commas. In this case the spreadsheet will display the comma (and the dollar sign).
Percentages Type the amount and the the percent sign.

For example, to enter a percentage such as 10 percent, just type 10%.[ENTER]. The percentage sign will show, but the number will actually be stored in the decimal representation of 10% (0.10).

Tip: Entering Large Amounts of Data Use the menu sequence: File | Preferences | Move down in the spreadsheet menu. After the [ENTER] key is pressed, the spreadsheet will automatically move the cursor down one cell. If the [TAB]key is pressed to end entering the number, the cell cursor will move horizontally one cell.


Using Existing Data

ELF can use data that has previously been entered into a computer file if it the data is formatted in one of three ways.

Using Existing Data

If The Data Is Formatted Like This... Do This...
One ASCII text file. The first line has variable names separated by commas. If there are labels, variable names and labels are in double quotes separated from the next variable name and label by a comma. Data is entered with values within an observation separated by a comma. Each observation is a separate line. File | Open | TWG2

Save TWG2 Button

Two ASCII text files. The first one ends with .DCT and has variable names, one variable to a line. The second one ends with .TWG and has one data point (one number) on each line. File | Open | TWG

Save TWG Button

An Excel 4 spreadsheet with the first row of data listing variable names one to each column. An optional variable label is in the same cell as the variable name separated from the variable name by a carriage return (technically, chr(10)). Data follows below this first row. File | Open | Excel

Save Excel Button



Saving Data

After all this work, it is a good idea to save the modified spreadsheet. (In fact, it is a always a good idea to save all work on your computer every few minutes just in case.) ELF lets you save data three ways.

Saving a database in an Excel4 spreadsheet preserves all of the formatting information. The other two formats save the database as an ASCII text files (to import an ELF spreadsheet into another program that cannot read an Excel 4 spreadsheet) and lose different amounts of information. These two formats available are TWG and TWG2.

The TWG format saves pair of files. The first is a dictionary file that has the suffix DCT and contains the names of the variables, one to a line. The second file in the pair is the data file and has the suffix TWG. It has each data item as alphanumeric characters, also one to a line. The default file name is the same prefix as the spreadsheet. To select a different prefix, use the menu sequence: File | Save As | Database | TWG The TWG format does not save labels.

The alternative ASCII format is the TWG2 format. This format uses one file. The first line of the file is a list of the variable names and labels. Each name and label pair is surrounded by quotation marks (" "). If there is no label, the variable name is surrounded by quotation marks. Each variable is seperated from the next by a comma (,).Each observation of data is on a separate line. Each data point is separated from the next by a comma.



Saving Data

To Do This... Do This...
Save In Excel4 Format Use the Save Excel Button or the menu sequence File | Save | Excel
Save In TWG Format Use the Save TWG Button or the menu sequence File | Save | Database | TWG
Save in TWG2 Format The Save TWG2 Button or the menu sequence File | Save | Database | TWG2


Note: To save a file with a new name, use the menu sequence File Save As instead of File | Save

Moving Around A Spreadsheet



There are several ways to move around a spreadsheet.

Moving Around A Spreadsheet

To Do This... Do This...
Using the Mouse To move to part of the spreadsheet the is not visible, click on a cell with the left mouse button. Hold the button down and continue in the direction where you want to go.
Using the Arrow Keys Hold the up, down, left or right arrow to move the spreadsheet in that direction.
[HOME] Key Moves to the A column (column one) in the current row.
[END] Key Moves to the last occupied column in the current row.
[CTRL]-[HOME] Pressing the [HOME] key and the [CTRL] key at the same time moves the cursor to cell A1. (This is usually written [CTRL]-[HOME].)
[CTRL]-[END] Pressing the [END] key and the [CTRL] key at the same time will move you to the lower left hand corner of the spreadsheet (not the lowest and left most possible, the lowest and left most used cell).
[PAGE UP]

[PAGE DOWN]

 Moves up or down one screen at a time, respectively.
[CTRL]-[PAGE UP] and [CTRL]-[PAGE DOWN] Moves left or right one screen at a time, respectively.
[CTRL]-[LEFT ARROW], [CTRL]-[RIGHT ARROW], [CTRL]-[UP ARROW], and [CTRL]-[DOWN ARROW] Moves in the indicated direction to the next occupied cell.
[SCROLL LOCK] Enabling the [SCROLL LOCK] will cause the other movements to scroll the spreadsheet window without changing the cell cursor's position.
Scrollbars The horizontal and vertical scrollbars will move the spreadsheet.




Selecting a Range

Selecting a Range

General To select a range of cells, move to one corner of the range. Hold down a key and move to the diagonal corner. You can move by using the mouse or an arrow key. Note: Holding down a [Shift] key will toggle most numeric keypads between numeric and arrow status.
Mouse To select a range of cells, go to the first cell in the range, hold down the left mouse button and move to the last cell in the range.
Arrow Keys A range of cells can be selected by going to the first cell in the range, pressing the [SHIFT] key (either one), and then using an arrow to move to the last cell in the range.

Correcting Data

Inevitably, it will be necessary to revise data because of errors in data entry or because of updated information.

Correcting Data

To Do This... Do This...
Select a Cell Select the cell using the mouse or arrow keys, then press the [F2] key. The cell will change color and can be edited in place.

Alternatively, after selecting the cell to edit, click on the edit bar with the mouse. The edit bar is the long horizontal bar between the buttons the spreadsheet.

Moving Without Deleting The [LEFT ARROW] and [RIGHT ARROW] keys move the editing cursor without deleting information.
Deleting To delete information, use the [BACK SPACE] key or the [DEL] key.




Cut, Copy and Paste



ELF follows the Windows conventions for cutting, copying and pasting information. Cut, Copy and Paste can be used within ELF or between applications. For example, it can be used to copy a graph to the spreadsheet or to copy a spreadsheet to a word processor.

Cut, Copy and Paste

To Do This... Do This...
Cut (Copy and Delete from the Screen) Select the range of the spreadsheet desired and press CTRL-X or use the Copy Button
Copy (Copy and Leave the Original) Select the range of the spreadsheet desired and press [CTRL]-[C] or use the Cut Button.
Paste the Material That Was Cut or Copied Position the cursor at the target location and press [CTRL]-[V] or use the Paste Button.


Writing An Equation

One powerful feature of a spreadsheet is the ability to create new numbers by writing an equation. By using the copy and paste features, a new variable can be quickly created. ELF stores both the equation and the numeric result so there is a record of how the variable was created. ELF can also print out the equations using variable names (File | Print Equations | Variable Names. In an ELF spreadsheet, an equation must start with an equals sign (=). Any other first character will cause ELF to consider the cell to be a text string.

For example, in the Harman spreadsheet perhaps there is a need to create a variable that is the square of the population. To do this:

= a2*a2 [Enter]



Relative and Absolute Cell References

If you look at one of cells, you will notice that the equation =a2*a2 has changed. For example, in cell F10 the equation is =a10*a10. This is because in ELF a cell reference is normally a relative cell reference. If you copy a cell with an equation, the references to cells are adjusted. Both the row and the column change automatically. Copying cell F2 to G2 will change the equation to =b2*b2. If for some reason this should be prevented, the $, is used. This is known as an absolute reference. For example, to keep all references to cell a2, write the equation as =$a$2*$a$2. To allow the row to change, but not the column, write the equation as =$a2*$a2. To allow the column to change, but not the row, write the equation as =a$2*a$2.

Why would someone want to use this? One common use is if the first observation is a base number of some sort such as the consumer price index. By referring to this cell as an absolute number, later numbers can be standardized by the initial value. Or one might want to calculate the spread between certain interest rates using the 30-year Treasury bond rate as the reference.

Relative and Absolute Cell References

To Do This... Do This...
Relative Reference a1
Absolute Column Reference $a1
Absolute Row Reference a$1
Absolute Reference $a$1

Arithmetic Operations

ELF offers the standard arithmetic operators: addition (+), subtraction (-), multiplication (*) and division (/). Parenthesis can be used to control the order of operations.

To raise a number to a power (exponentiation) use the (^) operator.

The Table Spreadsheet Functions lists the mathematical, logical and other functions that can be used with ELF.



Formatting the Appearance of a Spreadsheet

The appearance of a cell or range of cells can be customized in several ways:

Numeric Formats

Numeric formatting can be initiated by the menu sequence Format | Numeric or with the Fmt Range Button.

If the Fmt Range Button is used, push the Numeric Format radio button and then the Change Button. When done with formatting, push the Done Button.

Formats cover the areas of currency, fixed, percent, fraction, scientific, date and time numbers.

Format Symbol Summary

Symbol Description Sample Format Cell Data Appearance
0 Digit, pad with zero 0000.00 123.456

123

 0123.46

123

# Digit, no padding #.## 123.456

0.2

 123.46

.2

? Digit, pad with blanks ????.0?

????.00

 123.145

123

 123.4

0123.00

. [period] Decimal point #.## 123.456

0.123

 123.46

.12

% Display number as percentage ##.##% 1.00

0.1234

 95%

12.34%

, [comma] Thousands separator ###,### 100

123456

 100

123,456

E+,E-,e+,e- Use scientific notation, e+ and E+ always include a plus sign, E- ane e- do not. 00.00E+00 3

123.45

 30.00E-01

12.35E+01



Notes: The ?? format leaves a blank space to the right. Other formats align numbers to the right edge of the column.

The E+ formats are usually specified 0.00E+00, but using a 00.00E+00 format shifts the displayed decimal point with the exponent adjusted accordingly. E+ or E- result in capital letter E in the format while e+ or e- result in a small letter e. E+ and e+ force a plus sign on the exponent. With E- and e-, a sign positive sign on the exponent is not explicitly display.

Currency format can be created by placing the currency symbol ($, etc.) as the first character of the format string.

Printing

ELF offer several ways to print a spreadsheet. First, you print the data in a table. Second, you can print the formulas behind the numbers. In this second case, you can either print cell coordinates or variable names.

Printing

To Do This... Do This...
Printing A Spreadsheet To print a table you can either click on the Printer Button () or use the menu sequence File |Print Spreadsheet. Either way, the print dialog box pops up. Click on the OK Button.
Custom Header or Footer Use the menu sequence File | Page Setup
Printer Setup If you have more than one printer attached to your computer or if you want to change the orientation of the paper (say, from portrait to landscape), you need to get to the printer setup dialog. To do this proceed as above in Printing a Table. Instead of clicking the OK Button, click the Setup Button. You will then get the option to change printer and paper source and size. If you click the Options Button you will get printer-specific options.

You can also reach the printer setup dialog with the menu sequence: File | Printer Setup.

Printing the Equations To print the underlying equations, ELF must be in the spreadsheet window. Use the menu sequence File | Print Equations. At this point there are the choices of using Cell Coordinates or Variable Names




Special Header and Footer Commands

If these commands are included in a spreadsheet header or footer, they will have the special effects indicated. These are set in the File | Page Setup menu sequence. If the following commands are used, they must appear before the second group (below).

Header and Footer Font Format Commands

Effect Command
Bold &B
Italic &I
Underline &U
Strikeout &S
Use specified font &"fontname"
Use specified font size--must be two digits &nn




These commands create special text in the header or footer:

Header and Footer Text Commands

Text Command
Left align the text that follows &L
Center the text that follows &C
Right align the following text &R
Current date &D
Current time &T
Spreadsheet name &F
Page number plus optional number &P+number
Page number minus optional number &P-number
Ampersand &&
Total number of pages in spreadsheet &N


Error Values

Sometimes a spreadsheet is not able to calculate a value. Division by zero is an example of this. The spreadsheet has seven different error values to help the user to find the problem. These error values are:

Error Codes

Code Meaning
#DIV/0! Division by zero. The denominator of a fraction might be zero or a cell in the denominator might be empty. Sometimes parentheses can be used to force the spreadsheet to calculate in the specified order and to eliminate the error.
#N/A No value is available. This can be caused by inappropriate values in a formula or a reference to a cell with the #N/A value.
#NAME? Name not recognized. Can be caused by attempting to use a name range that has not been defined.
#NULL! Two ranges have a null intersection. Check that the ranges in the cell overlap (if they are supposed to).
#NUM! Inappropriate number. Also can be caused by failure to be able to solve some financial functions (IRR, etc.) within 30 iterations. Try a different starting guess. Also check for suspiciously large or small numbers.
#REF! Reference error. Can be caused by referring to a cell or range that has been deleted.
#VALUE! Wrong argument type. Can be cause by entering a text string where a number is expected, etc. Can also be caused by entering a range of cells where a value is required.


Buttons



To make common tasks easier, ELF has buttons for frequent actions.

Open a TWG database. A TWG database is a pair of ASCII files. The first is a dictionary file that has one line for each variable in the database. That line is the name of one variable. This file has the suffix .DCT. The second file is the data file and has one line for each data point. The number of data points is the product of the number of variables and the number of observations. This file has the suffix .TWG.
Open a TWG2 database. A TWG2 database is an ASCII file. The first line of this file has the names of the variable separated by a comma. If a variable has a label, it follows the variable name separated by a blank. Variables are surrounded by quotation marks (""). The remaining lines are each observation in the database. Each data point is separated from the next with a comma except for the last one in an observation which is indicated by the ASCII CR-LF combination. This can be created by the [ENTER] key in most text editors including NotePad and WordPad which come with Windows 3.1 or Windows '95.
Open an Excel spreadsheet as the current database. The spreadsheet has the first row consisting of the variable names heading each column of data. The succeeding rows have the data (which may or may not be numeric). A variable label is separated from the variable name by an ASCII LF. The easiest way to enter an ASCII LF is to use the Var Name Button in ELF.
Add a variable to the end of the list of variables. Optionally, add a variable label.
Create a new variable at the cursor location by creating a new column and shifting the existing variables/columns to the right.
Delete the current variable (the column) from the spreadsheet removing the column and shifting columns to the right of the deleted one to the left.
Print the current spreadsheet.
Copy the selected range to the Windows clipboard. The range can be pasted to another part of the spreadsheet or to another application.
Format a range of the spreadsheet. Formats that can be applied are: numeric, font, alignment, background pattern, border, column width, row height, sorting and page breaks. Page breaks can also be cleared.
Change or add a variable name and/or label.
Save the current spreadsheet as a TWG database.
Save the current spreadsheet as a TWG2 database.
Save the current spreadsheet as an Excel 4 spreadsheet.
Add an observation at the end of the existing observations.
Create a new observation at the cursor location by creating a new row and shifting all existing rows below the new one down one.
Delete the current observation(row) from the spreadsheet removing the row and shifting the remaining rows up.
Paste the Windows clipboard to the current cell(s).
Cut (copy and remove) the selected cell(s) to the Windows clipboard.
Clear the spreadsheet be removing data, formats, etc. from all cells.
Move to the cell selected. Note: The destination must be specified in row/cell (letter/number) format.
Recalculate a spreadsheet when auto recalc has been turned off in File | Preferences. If auto recalc is on, the Recalc button will be inactive.


Spreadsheet Functions



Key points are:





Function Description Syntax Comments See Also Examples (Rounded)
ABS Absolute value of a number ABS(number) An absolute value does not display a plus or minus sign. SIGN abs(-2) -> 2

abs(2) -> 2

ACOS Arc (inverse) cosine of a number ACOS(number)

Result is in radians

 A cosine is in the range -1 to +1. To convert radians to degrees, multiply acos(x) by 180 / pi() COS, PI, ASIN, SIN acos(.5) -> 1.05

acos(-.2) -> 1.77

ACOSH Returns the inverse hyperbolic cosine ACOSH(number)

number is in radians

 Limited use in statistics ASINH, ATANH, COSH acosh (3)->1.76
ADDRESS creates a cell address as text ADDRESS ( row, column, ref_type) Used for programming.

For ref_type:

1 Absolute

2 Absolute row,

relative column

3 Relative row, absolute column

4 Relative

 COLUMN, OFFSET, ROW address(5, 6, 1) -> $F$5
AND Logical and. Returns true if and only if all arguments are true AND(list) list can contain formulas or cell references or both OR, NOT and(3>2, -4<-2) -> TRUE

and(1<0, 2>1, 3>1) -> FALSE

ASIN Arc (inverse) sine ASIN(number)

Result is in radians

 Returns the angle whose sine is number.

-1 number 1

To convert radians to degrees, multiply acos(x) by 180 / pi()

 COS, PI asin(1) -> 1.57

asin(-1) -> -1.57

ASINH Arc hyperbolic sine ASINH(number) Result is in radians Returns the number whose hyperbolic sine is number.

To convert radians to degrees, multiply acos(x) by 180 / pi()

ACOSH, SINH, ASINH ASINH(4) -> 2.09
ATAN Arc (inverse) tangent ATAN(number) Assumes that the angle is in the range

-/p to /2

 ASIN, ACOS ATAN(1) -> 0.79
ATAN2 Arc (inverse) tangent of the specified coordinates ATAN2(x, y) The arctangent is the angle from the x axis to a line with end points at the origin (0, 0) and a point with the given coordinates (x, y). The angle is returned in radians,

- < angle <

ASIN, ACOS, ATAN ATAN(3, 6) -> 1.11
ATANH Arc (inverse) hyperbolic tangent ATANH(number)

1 < number < 1

 ACOSH, ASINH ATANH(0.5) -> 0.55
AVERAGE Average of a list of numbers (or cells) AVERAGE(number list) AVERAGE(1, 2, 3) -> 2

AVERAGE(A2:A10) -> average of cells A2 to A10

CEILING Round up CEILING ( number, significance ) Rounds number up (away from zero) to the next largest significance. Returns #VALUE! if number, or significance are non-numeric. Returns #NUM! if number, or significance have opposite signs INT, ROUND, TRUNC, FLOOR CEILING(1.235, 0.5) -> 1.5
CHAR Convert a number to the ASCII code it represents CHAR(number) 1 number 255 CHAR(65) -> 'A'
CHOOSE Choose an item from a list CHOOSE (index, item_list ) item_list can have at most 29 items. Returns #VALUE! If index is invalid. Truncates index if necessary INDEX CHOOSE(1, 2, 4, 6, 8)-> 2

CHOOSE(2.1, A1, A2, A3) returns whatever is in A2

CLEAN Remove nonprinting characters from a string CLEAN(string) Removes control characters from string CHAR, TRIM CLEAN(Test & CHAR(10) & String) removes the unprintable CHAR(10)
CODE Return the ASCII code of the first character of a string CODE(string) String can be a cell reference or a string. Strings are surrounded by double quotes ("). CHAR CODE("Alphabet") -> 65
COLUMN The column number of a cell COLUMN(cell) If Cell is blank, the returned value is the column that COLUMN() is in COLUMNS, ROW COLUMN(C15) -> 3
COLUMNS The number of columns in a range of cells COLUMNS(cell_range) COLUMNS(A1:D99) -> 4
COS The cosine of an angle COS(number)

number is in radians

 To convert number to degrees, multiply by pi()/180 SIN, TAN, ACOS COS(1) -> 0.54

COS(-0.5) -> 0.88

COSH Hyperbolic cosine of a number COSH(number) ACOSH, SINH, TANH COSH(2.1) -> 4.14
COUNT The number of valid numerical values in a list COUNT(list) COUNT ignores 'bad' numerical values such as empty cells, logical values, and text COUNTA COUNT(03/06/96, 06/21/96, 10/19/96) -> 3

COUNT(1, -3, "X") -> 2

COUNTA The number of non-blank cells in a range COUNTA(range) Counts null strings (""), but not empty cells COUNT COUNTA(1, 2, 3, "") -> 4
DATE A serial number for a date DATE(year, month, day) If year is two digits, it is assumed to be in the range 1920 to 2019. If month or day is too large, it is converted to the appropriate year or month respectively. DATEVAUE, TODAY DATE(94, 6, 21) -> 34506
DATEVALUE A serial number for a date string. DATEVALUE(string) If year is omitted, the current year is assumed DATE, TODAY DATEVALUE(6/21/94) -> 34506
DAY The day of the month for a serial number DAY(serial_number) Will also work with a date string (e.g., "6/21/94")

Returns #VALUE! for non-existent dates.

 DATE, DATEVALUE DAY(34506) -> 21

DAY("6/21/94") -> 21

DB Depreciation of a capital asset using the fixed declining balance method DB(cost, salvage, life, period[ , months]) cost initial cost of the asset

salvage final value of the asset

life life of the asset

period period for which depreciation is calculated

months number of months in first year. Default is 12.

 DDB, SLN, SYD, VDB DB(100000, 10000, 6, 1) -> 31900
DDB Depreciation using the double declining balance method xxx
DOLLAR A string of a currency DOLLAR(number, [precision]) If precision is not specified, 2 is used. FIXED, TEXT, VALUE DOLLAR(1.234, 1) -> "$1.23"
ERROR.TYPE A string representing the error code ERROR.TYPE(cell) 1 #NULL!

2 #DIV/0!

3 #VALUE!

4 #REF!

5 #NAME?

6 #NUM!

7 #N/A

#N/A Other

 ISERR, ISERROR If cell A1 attempts division by zero, ERROR.TYPE(A1) -> 2
EVEN Round up to the nearest even number EVEN(number) Can use either a number or a cell CEILING, FLOOR, ROUND, TRUNC, ODD EVEN(2.3) -> 4
EXACT Case sensitive matching of strings EXACT(string1, string2) Returns true or false LEN, SEARCH EXACT("Dog", "dog") -> false
EXP Raise e (2.71828182845904) to the number power exp(number) LOG

LN

 EXP(2) -> 7.389
FACT Factorials FACT(number) number is truncated if necessary. FACTORIAL(n) = n! = (n)(n-1)(n-2)...1 PRODUCT FACT(3.3) -> 6
FALSE Returns the logical value FALSE FALSE() Requires the empty and trailing parentheses TRUE FALSE() -> false
FIND Find the starting position of a substring of text in a string FIND(substring, string [, start_pos]) substring is the string to look for. string is the string to be searched. The default start_pos is 1. If substring is "", FIND() returns 1. Wildcards cannot be used EXACT, LEN, MID, SEARCH FIND("target", "a target") -> 3
FIXED Round a number to the specified precision and convert to a string (with or without commas) FIXED ( number [, precision][, no_commas] ) number is the number to be converted to a string. precision is the number of decimal places with a default of 2. no_commas is 1 (true) or 0 (false) and determines if the string includes commas DOLLAR, ROUND, TEXT, VALUE FIXED(123.4, 2, 1) -> "1,23.40"
FLOOR Round down towards zero to the specified number precision FLOOR(number , precision) If number or precision is non-numeric, #NAME? is returned. If the arguments have opposite signs, #NUM! is returned. CEILING, INT, EVEN, ODD, ROUND, TRUNC FLOOR(2.39, 0.25) -> 2.25
FV Calculate the future value of an annuity FV(int, nper, pmt, [pv], [type}) int is the interest (discount) rate, nper is the number of periods or payments, pmt is the amount of each payment (fixed), pv is the amount the annuity is currently worth (default is 0), and type is a code where 0 means the payments are at the end of the period and 1 means the payments are due at the start of the period (default is 0 or end). IPMT, NPER, PMT, PPMT, PV, RATE FV(10%/12, 240, 700, 1) -> 531,550.86

HLOOKUP Searches the top row of a table for a value and returns the contents of the cell in(another) row that is in the column with the match HLOOKUP ( search_item, search_range, row_index ) search_item is a number, string or cell reference to (case insensitive) match a cell in the top row of search_range. The first row of search_range must be sorted into ascending order. row_index is the row number to use (e.g., 3).

If search_item cannot be found in the top row of search_range, the largest value that is less than search_item is used. When search_item is less than the smallest value in the first row of the search_range, the error #REF! is returned.

 INDEX, LOOKUP, MATCH, VLOOKUP Example is at end of the chapter.
HOUR The hour of a time serial number HOUR(time_serial_number) Returns

0 hour 23

 MINUTE, NOW, SECOND HOUR(34259.4) -> 9
IF Test if condition is true or false IF(condition, true_value, false_value) Returns true_value if condition is true or false_value if condition is false IF(2.0 > pi(), 1, 2) -> 2

since pi() or 3.14 is not less than 2.0

INDEX The contents of a cell specified by row and column INDEX(reference [, row] [,column] [,range_number]) reference is one or more ranges of cells

row is the row number of the cell to return

cell is the column number of the cell to return

If only one row or column is specified, row or column can be omitted, respectively.

If reference has more than one range of cells, then range_number specifies which one to use (the first is 1). If row and column are omitted, returns the range in reference indicated by range_number.

Returns #REF! if row, column, and range_number do not point to a cell in reference.

 HLOOKUP, LOOKUP Example is at end of the chapter.
INDIRECT The contents of a cell referenced by the cell specified. INDIRECT(ref_cell) Returns #REF! if ref_cell is not a valid reference. If cell B2 has the string C3, and C3 contains 123.45, then INDEX(B2) -> 123.45
INT Round down to the nearest integer INT(number) CEIL, ROUND, TRUNC, ODD, EVEN INT(123.45) -> 123

INT(-123.45) -> -124

IPMT The interest payment of an annuity IPMT(interest_rate, period, nperiods, pv[,fv] [,type]) interest_rate is the periodic interest rate

period is the period number in question (1-nperiods)

nperiods the number of periods or payments

pv is the present value of the annuity

fv is the future value of the annuity. If omitted, fv is 0.

type when payments are made. 0 for the end of the period or 1 for the start of the period. If omitted, type is 0.

 FV, PPMT, PMT, RATE IPMT(8%/12, 2, 48, 18000) -> -117.87

IRR Internal rate of return for a series of periodic cash flows IRR(cash_flow [, guess]) cash_flow is a series of payments of which at least one is negative and one is positive. If guess is not specified, 10 percent is assumed.

Note that there can be multiple answers to the IRR calculation and different initial guesses may give different answers. See any finance book for details.

 MIRR, NPV, RATE IRR(-60000, 9590, 10580, 12790, 15830, 18930) -> 3.72%
ISBLANK Is the specified cell blank? ISBLANK(cell) Returns true or false ISERR, ISERROR, ISLOGICAL, ISNA, ISNONTEXT, ISNUMBER, ISTEXT, ISREF ISBLANK(B2) -> true

if cell B2 is empty (blank).

ISERR Is true if the specified cell or expression returns any error value except #N/A! ISERR(expression) expression can be a cell reference ISERROR, ISBLANK, ISLOGICAL, ISNA, ISNONTEXT, ISNUMBER, ISREF, ISTEXT ISERROR(1/0) -> true

ISERROR() -> false

ISERROR Is true if the specified cell or expression returns any error value including #N/A! ISERROR(expression) expression can be a cell reference ISERR, ISBLANK, ISLOGICAL, ISNA, ISNONTEXT, ISNUMBER, ISREF, ISTEXT ISERROR(1/0) -> true

ISERROR() -> false

ISLOGICAL Is true if the expression returns a logical value (true or false) ISLOGICAL(expression) expression can be a cell reference ISERR, ISERROR, ISBLANK, ISNA, ISNONTEXT, ISNUMBER, ISREF, ISTEXT ISLOGICAL(5) -> false

ISLOGICAL(ISNUMBER(5)) -> true

ISNA Is true if the expression returns the value not available (#N/A!). ISNA(expression) expression can be a cell reference ISERR, ISERROR, ISBLANK, ISLOGICAL, ISNONTEXT, ISNUMBER, ISREF, ISTEXT ISNA(NA()) -> true

ISNA(B2) -> false if B2 is a number

ISNONTEXT Is true if expression is not text (a blank is not text). ISNONTEXT(expression) expression can be a cell reference ISERR, ISERROR, ISBLANK, ISLOGICAL, ISNA, ISNUMBER, ISREF, ISTEXT ISNONTEXT(5) -> true

ISNONTEXT("a") -> false

ISNUMBER Is true if expression is a number. ISNUMBER(expression) expression can be a cell reference ISERR, ISERROR, ISBLANK, ISLOGICAL, ISNA, ISNONTEXT, ISREF, ISTEXT ISNUMBER(5) -> true

ISNUMBER("a") -> false

ISREF Is true if expression is a range reference. ISREF(expression) expression can be a cell reference ISERR, ISERROR, ISBLANK, ISLOGICAL, ISNA, ISNONTEXT, ISNUMBER, ISTEXT ISREF(B2) -> true

ISREF(B2:C3-> true

ISTEXT Is true if expression is text. ISTEXT(expression) expression can be a cell reference ISERR, ISERROR, ISBLANK, ISLOGICAL, ISNA, ISNONTEXT, ISNUMBER, ISREF ISTEXT(5) -> false

ISTEXT("a") -> true

LEFT The left characters of a text string LEFT(text [,num_char]) If num_char is not specified, it defaults to 1. num_char must be 0 MID, RIGHT LEFT("abcde", 2) -> "ab"
LEN The length in characters of a text string LEN(text) Spaces are counted. EXACT, SEARCH LEN("A B") ->3

LEN("") -> 0

LN Natural logarithm (base e) LN(number) number > 0

if number 0, LN returns #NUM!

EXP() is the inverse of LN()

 EXP, LOG, LOG10 LN(1) -> 0

LN(10 -> 2.31

LOG Logarithm to a user- specified base LOG(number [, base]) Default base is 10.

number > 0

if number 0, LOG returns #NUM!

base > 1

if base < 1, LOG returns #NUM!

if base = 1, LOG returns #DIV/0!

 EXP, LN, LOG10 LOG(2, 2) -> 1

LOG(2, 1.5) -> 1.71

LOG10 Base 10 logarithm LOG10(number) if number 0, LOG10 returns #NUM! EXP, LN, LOG LOG10(1) -> 0
LOOKUP Search for a value in one range and return the contents indicated in a second range LOOKUP(lookup_value, lookup_range, result_range) lookup_value is the value to look up in lookup_range and return the value in result-range.

lookup_range must be in ascending order.

If there is not an exact match, LOOKUP returns the result_range value corresponding to the largest value less than or equal to lookup_value.

 HLOOKUP, VLOOKUP, INDEX Example is at end of the chapter.
LOWER Lower case a text string LOWER(text) Numbers are not changed. UPPER, PROPER LOWER("A2") -> "a2"
MATCH Match lookup against range and return the position number MATCH(lookup, range, type) type specifies how to handle cases when there is not an exact match. The default is 1.

0--match the first case of an exact match. The data in range need not be ordered. When using text, wild card matches (? for any one character and * for any number of characters) can be used.

1--match the smallest value greater than or equal to lookup. Range must be in ascending order.

HLOOKUP, INDEX, LOOKUP, VLOOKUP Example is at end of the chapter.
MAX The largest value in a series of numbers. MAX(number_list) number_list can contain as many as 30 numbers. It can also contain a cell range. In a cell range, text and blank cells are ignored. MIN MAX(-1, 1, 2, 3, 99,-99) -> 99
MID The characters from the middle of a text string. MID(text, start_pos, num_char) Returns num_char from string text starting at start_pos.

If start_pos is greater than the length of text, an empty string ("") is returned.

If start_pos is less than 1 or if num_char is negative, #VALUE! is returned

 CODE, FIND, LEFT, RIGHT, SEARCH MID("Example", 3, 5) -> "ample"
MIN The smallest value in a series of numbers MIN(number_list) number_list can contain as many as 30 numbers. It can also contain a cell range. In a cell range, text and blank cells are ignored. MAX MIN(-1, 1, 2, 3, 99,-99) -> -99
MINUTE The minute in the date serial number. MINUTE(serial_number) The returned value is in the range 0 to 59 DAY, HOUR, MONTH, NOW, SECOND, WEEKDAY, YEAR MINUTE(34399.826) -> 49
MIRR Modified internal rate of return (IRR). MIRR(cash_flow, finance_rate, reinvest_rate) The modified internal rate of return considers both the cost of capital (finance_rate) and the reinvestment of cash received (reinvest_rate).

cash_flow must refer to a range of cells.

 IRR, NPV, RATE MIRR(A1:A4, 10%,12%) -> MIRR(A1:A4, 10%,12%) -> 19%
MOD The modulus (remainder) of a division MOD(numerator, denominator) If denominator is 0, returns #DIV/0! INT, ROUND, TRUNC MOD(5, 4) -> 1

MOD(8, 2) -> 0

MONTH The month in a date serial number or a date. MONTH(serial_number)

MONTH(date_string)

 Returns #VALUE! for non-existent dates. DAY, NOW, HOUR, MINUTE, SECOND, TODAY, WEEKDAY, YEAR MONTH(34626) -> 10

MONTH("10-4-99") -> 10

N Translate cell contents according to fixed rules. N(value) Number are returned as number.

Serial numbers formatted as dates are returned as serial numbers.

True() is returned as 1.

All other expressions are returned as 1.

 T, VALUE N(123) -> 123

N(TRUE()) -> 1

NA Not Available NA() The parenthesis must be empty. Returns #NA! to indicate that the data point was not available. ISNA NA() -> #NA!
NOT Negates a logical value. If it was TRUE, it now is FALSE. If it was FALSE, it now is TRUE NOT(logical_expression) AND

IF

OR

TRUE

FALSE

 NOT(FALSE()) -> TRUE
NOW The current date and time as a serial number NOW() The parenthesis must be empty.

Numbers to the left of the decimal point represent the date.

Numbers to the right of the decimal point represent the time.

Updated only when the spreadsheet is recalculated

 DATE, DAY, HOUR, MINUTE, MONTH, SECOND, TODAY, WEEKDAY, YEAR NOW() -> 35345.862 (on October 7, 1995 at approximately 8:41 PM)
NPER The number of periods in an investment with constant payments. NPER(interest, pmt, pv, fv, type) interest interest rate

pmt periodic payment

pv present value of the stream of payments

fv future value or payment at the end of the stream of periodic payments

type 0 if at end of period, 1 if at start of period. Default is 0 (end of period).

 PV, FV, IPMT, PMT, PPMT, RATE, IRR, MIRR NPER(8%/12,-100000,1000000,0) -> 10.38
NPV Net present value NPV(discount_rate, pmts) Calculate the net present value of up to 29 payments pmts using discount_rate. FV, PV, IRR NPV(8%/12,100,100,100) -> 296.04
ODD Round up to the nearest odd integer. ODD(number) CEILING, EVEN, FLOOR, INT, ROUND, TRUNC ODD(4.5) -> 5

ODD(3.5) -> 5

OFFSET The contents of a range that is offset from a specified starting point OFFSET(reference, rows, columns [,height] [,width]) reference the base cell for the calculations

No default.

#VALUE! is returned if a range is specified

rows the number of rows offset from reference

Negative numbers are above the reference cell, positive numbers below. If outside the spreadsheet, #REF! is returned.

columns the number of columns offset from reference. Negative numbers are to the left of the reference cell, positive to the right. If outside the spreadsheet. #REF! is returned.

height the number of rows to include in the offset range. Default is one row.

width the number of columns to include in the offset range. Default is one column.

 HLOOKUP, VLOOKUP, MATCH SUM(OFFSET(C1, 3, 2, 2,2) ) -> returns the sum of cells F3-H5
OR Logical or, true if any of the conditions is true OR(condition_list) AND

IF

NOR

 OR(1 + 1 = 3, 5 / 5 = 1) -> true
PI PI () PI() The empty parenthesis are required. PI() -> 3. 1415962535...
PMT The periodic payment necessary to equate the present and future values for a fixed interest rate. . PMT(int_rate, nper, pv, [, fv] [, type]) For example, an annuity or a mortgage.

Divide an annual interest rate by the number of periods. int_rate periodic interest rate (fixed)

nper number of periods

pv present value of the annuity or mortgage

fv final value of the annuity or mortgage (balloon payment)

type 0 if payments are at the end of the period, 1 if payments are at the start of the period. Default is 0 (end of period).

 IPMT, PV, FV, NPER, IRR, MIRR, PPMT PMT(9%/12,48,18000,0,0) -> - 447.93

(The negative sign shows that the payment flows in the opposite direction from the $18,000.)

PPMT Principal in a periodic an annuity or mortgage payment. PPMT(int_rate, period, , [, fv] [, type]) For example, an annuity or a mortgage.

Divide an annual interest rate by the number of periods.

int_rate periodic inters t rate (fixed)

period the period in the study

nper number of periods

pv present value of the annuity or mortgage

fv final value of the annuity or mortgage (balloon payment)

type 0 if payments are at the end of the period, 1 if payments are at the start of the period. Default is 0 (end of period).

 IPMT, PV, FV, NPER, IRR, MIRR, PMT PMT(9%/12,1,48,18000,0,0) ->-312.93
PRODUCT The product of a series of numbers. PRODUCT(series) All numerical values are used in the calculations.

Error values result in #ERR!

SUM, FACT PRODUCT(1, 2, 3) -> 6
PROPER Properly capitalize a text string PROPER(text) Capitalize only:

The first alphabetic character of a word,

the first character following a number, punctuation mark or space.

Numbers are not affected.

 LOWER UPPER PROPER("PAUL SAMUELSON") ->

"Paul Samuelson"

PROPER("1st quarter") ->"1st Quarter"

PV Present value of a stream of constant payments (annuity or mortgage). PV(int_rate, nper, pmt, [, fv] [, type]) int_rate periodic interest rate (remember to convert annual to monthly if necessary)

nper number of payments

pmt amount of payment

fv future value (terminal value, or balloon payment)

Default is 0.

type 0 if payments are at end of period, 1 if payments are at start of period. Default is 0 (end of period).

Cash paid out should be entered as a negative number.

 FV, PMT, RATE, IPMT, PPMT, NPER PV(8%/12,48,500) -> -20480.96
RAND Random number x such that 0 x < 1 RAND() The empty parenthesis are required.

The random number changes every time the spreadsheet is recalculated

 RAND() * 10 +5 -> a random number less than or equal to 5 and less than 15
RATE The interest rate to equate payments with the price of an annuity (or mortgage) RATE(nper, pmt, pv, [, fv] [, type] [, guess]) nper number of periods

pmt regular payment of the annuity (or mortgage)

pv present value (cost of the annuity, initial amount of the mortgage)

fv future value (final value of the annuity or final mortgage balance or balloon payment) Default is 0

type when payments are made. 0 for end of period, 1 for start of period. Default is 0 (end of period)

guess initial starting guess. Default is 10%. If ELF cannot calculate the interest rate, try a different starting guess.

Rate terminates after 20 iterations or when estimated rate is accurate to 0.0001 percent.

 FV, IPMT, NPER, PMT, PPMT, PV
REPLACE Replace part of a text string with another text string. REPLACE(original_text, start_position, num_char, replace_text) original_text original text string

start_position position to start replacing. If less than 1, #VALUE! is returned.

num_char number of characters to remove from the original string. Not necessarily the number of characters in replace_text. Returns #VALUE! if num_char is less than zero.

replace_text the replacement string. The entire string is used.

 MID, SEARCH, TRIM, LEFT, RIGHT REPLACE("abcdefghij",2,2,"ABCDE") -> "abABCDEdefghij"
REPT Repeat a text string. REPT(text, times) text is the text to repeat

times is the number of times to repeat the string. If 0, an empty string ("") is returned.

The maximum length of the string returned by REPT is 255 characters.

 REPT("xX", 3) -> "xXxXxX"
RIGHT The characters on the right of a text string. RIGHT(text

[, num_char])

 text the text string

num_char the number of characters to return. num_char 0. Default is 1

 LEFT, MID RIGHT("First Week" , 4) -> "Week"
ROUND Round a number to user-specified precision. ROUND(number, n_dec) number the number to be rounded

n_dec the number of decimal places to round to. A negative number rounds to places to the left of the decimal point.

 CEILING, FLOOR, INT, MOD ROUND(123.456, 1) -> 123.5

ROUND(89.111, -1) -> 90

ROW The row number in a cell reference ROW(cell_reference) cell_reference is a cell reference COLUMN, ROWS ROW(C1) -> 1
ROWS The number of rows in a range reference. ROWS(range_reference) range_reference is a range of cells COLUMNS, ROW ROWS(A1:C99) -> 99
SEARCH Locate the position of a substring in a string. SEARCH(substring, string, start_pos) substring the substring to be matched

string the string to search in

start_pos the position in string to start the search. Default is 1. If start_pos is less than 0 or greater than the length of string, #VALUE! is returned.

SEARCH is case sensitive.

? and * are allowed. Use ~? and ~* to search for ? or *

 FIND, MID, REPLACE, SUBSTITUTE SEARCH("Error", "This is not an error") -> 0 (because of case sensitivity)
SECOND The second in a date-time serial number SECOND(serial_number) The decimal part of the serial_number contains the time. DAY, HOUR, MINUTE, MONTH, YEAR, NOW, WEEKDAY SECOND(0.123) -> 7
SIGN The sign of a number SIGN(number) Returns +1, -1 or 0 ABS SIGN(123) -> 1

SIGN(-123) -> -1

SIGN(0) -> 0

SIN The sine of an angle SIN(number)

number is in radians

 To convert number to degrees, multiply by pi()/180 ASIN, COS, TAN, PI SIN(45 * PI()/ 180) -> 0.7071068
SINH The hyperbolic sine of a number. SINH(number) ASINH, COSH, TANH SINH(1) -> 1.18
SLN Straight-line depreciation SLN(cost, salvage, life) Divides the total depreciation (cost - salvage) by life. DDB, SYD, VDB SLN(10000, 5000, 5) ->1000
SQRT Square root SQRT(number) If number is negative, SQRT returns #NUM! SUMSQ SQRT(4) -> 2
STDEV Standard deviation (sample) STDEV(number_list) number_list is a list of up to 30 numbers or cell references.

STDEV uses the n - 1 formula appropriate for samples. Use STDEVP for the n formula for populations.

 STDEVP, VAR, VARP STDEV(2, 3) -. 0.7071068
STDEVP Standard deviation (population) STDEVP(number_list) number_list is a list of up to 30 numbers or cell references.

STDEVP uses the n formula for populations.

Use STDEV for the n - 1 formula appropriate for samples.

STDEV, VAR, VARP STDEVP(2, 3) -> 0.5
SUBSTITUTE Substitute part one string for another. SUBSTITUTE(text, old_text, new_text [, occurance_num]) text the string to work on

old_text the substring to replace

new_text the new substring

occurance_num which occurrence of old_text to replace. Default is all occurrences.

REPLACE, TRIM, SUBSTITUTE("January 1, 1997", "1", "2") -> "January 2, 2997"

SUBSTITUTE("January 1, 1997", "1", "2", 1) -> "January 2, 1997"

SUM Sum a list of numbers SUM(number_list) Sum (total) up to 30 numbers or cell references. AVERAGE, COUNT, COUNTA, PRODUCT, SUMSQ SUM(1, 2, 3) -> 6
SUMSQ Sum the squares of a series of numbers SUMSQ(number_list) Square and sum up to 30 numbers or cell references.

Entering error values or text results in an error. A cell range reference will ignore text, logical expressions and empty cells.

 SUM, SUMSQ(1, 2, 3) -> 14
SYD Sum of years depreciation SYD(cost, salvage, life, period) cost initial cost of the asset

salvage salvage value of the asset

life lift of the asset

period period for which depreciation is to be calculated.

Using SYD if an asset has a life of 3 years, depreciation in the first year is 3 / (1 + 2 + 3), in the second year is 2 / (1 + 2 + 3) and in the third year is 1 / (1 + 2 + 3)

 DDB, SLN, VDB SYD(10000,1000,7,4) -> 1285.7143
T Test for text T(value) Returns the text if value is text. Otherwise, returns an empty string (""). T("test") -> "test"

T(123) -> ""

TAN Tangent of a number TAN(number)

number is in radians

 To convert number to degrees, multiply by pi()/180 ASIN, COS, ATAN, PI TAN(45 * PI()/ 180) -> 1
TANH Hyperbolic tangent TANH(number) ATANH, SINH, COSH TANH(1) -> 0.7615942
TEXT Format a number as text using a user-specified format TEXT(number, format) number is a number or cell reference to a number or a formula

format any valid ELF spreadsheet format such as "General" or "#,##0". The double quotation marks are required. Asterisks (*) are not allowed.

 DOLLAR, FIXED, T, VALUE TEXT(1234.567, "#,###.00") -> 1,234.57
TIME Date-time serial number of hour, minute and second. TIME(hour, minute, second) hour hour in the range 0-23

minute minutes in the range 0-59

second seconds in the range 0-59

The date part (to the left of the decimal point) is zero.

 HOUR, MINUTE, SECOND, NOW, TIMEVALUE, DATE, DATEVALUE TIME(12, 11, 11) -> 0.5077662
TIMEVALUE Date-time serial number of a text string. TIMEVALUE(text) text is the time HOUR, MINUTE, SECOND, NOW, TIME, DATE, DATEVALUE TIMEVALUE("12:11:11 am") -> 0.5077662
TODAY Date-time serial number for the current date. TODAY() The empty parenthesis are required.

Updated with the spreadsheet is recalculated

 DATE, DAY, NOW TODAY() -> 35346
TRIM Remove all spaces from a text string except for single spaces between words TRIM(text) Useful when importing data. CLEAN, MID, REPLACE, SUBSTITUTE TRIM(" Now is the time") -> "Now is the time"
TRUE The logical value true TRUE() The empty parentheses are required. FALSE TRUE() -> TRUE
TRUNC Truncate a number to user-specified precision TRUNC(number [, n_dec]) number number to truncate

n_dec number of decimal places to retain. Default is 0 (integer).

 CEILING, FLOOR, INT, ROUND, ODD, EVEN TRUNC(123.456, 1) -> 123.4

TRUNC(123.456, -1) -> 120

TYPE A code for the type of expression TYPE(expression) Return values:

1 number

2 text

4 logical

16 error

 ISBLANK, ISERR, ISERROR, ISLOGICAL, ISNA, ISNONTEXT, ISNUMBER, ISREF, ISTEXT TYPE("A") -. 2

TYPE(1) -> 1

UPPER Upper case a text string. UPPER(text) Nonalphabetic characters (numbers, blank, asterisk, etc.) are not affected. LOWER, PROPER UPPER("abc") -> "ABC"
VALUE The value of a text string VALUE(text) text is the text to convert to a numerical value. Can be in any standard format such as $123.45 or 1/1/96.

Returns #VALUE! if it cannot convert text to a numerical value

 DOLLAR, FIXED, TEXT VALUE("$1,234.56") -> 1234.56

VALUE("3:00 pm") -> 0.625

VAR Variance of a list of numbers using the n-1 formula which is appropriate for a sample VAR(number_list) number_list can contain up to 30 numbers.

Use VARP for populations and the n formula.

 STDEV, STDEVP, VARP VAR(1, 2, 3) -> 1
VARP Variance of a list of number using the n formula which is appropriate for a population. VAR(number_list) number_list can contain up to 30 numbers.

USE VAR for samples and the n-1 formula.

 STDEV, STDEVP, VAR VARP(1,2,3) -> 0.6666667
VDB Variable declining balance depreciation such a double declining balance with option switch over to straight line. For one or more time periods. VDB(cost, salvage, life, start_period, end_period [, factor] [, method]) cost initial cost

salvage salvage value

life the depreciable life of the asset

start_period starting period for depreciation calculations

end_period ending period for depreciation calculations

factor rate at which balance declines. Default is 2 for double-declining balance.

method TRUE for continuous declining balance, FALSE for optimal crossover to straight line. Default is FALSE.

 DDB, SLN, SYD VDB(10000, 1000, 9, 3, 4) -> 1045.5723

VDB(10000, 1000, 9, 3, 3) -> 0

VLOOKUP Search the first column of a table for a match and return a value based on this match VLOOKUP(search_item, search_range, column_index) search_item a value, string or cell reference

range the table. The first column is searched an must be in ascending order. (-1, 0, 1, "A", "Z", False, True).

column_index the column offset

Returns #VALUE! if column_index < 1

Returns #REF! if column_index is outside of range.

 HLOOKUP, LOOKUP, INDEX, MATCH Example is at end of the chapter.
WEEKDAY Day of the week for a date-time serial number or date string. WEEKDAY(serial_number) serial_number is either a date-time serial number or a date string ("1-1-90", "1-Jan-90").

1 Sunday

7 Saturday

Returns #VALUE! for non-existent dates.

 DAY, NOW, TEXT, MONTH, YEAR WEEKDAY("10/10/97") -> 6

WEEKDAY(NOW()) ->3 (for Oct 8, 1996)

YEAR Year for a date-time serial number or date string YEAR(serial_number) serial_number is either a date-time serial number or a date string ("1-1-90", "1-Jan-90").

Returns #VALUE! for non-existent dates.

 DAY, NOW, TEXT, MONTH, WEEKDAY YEAR("10/10/97") -> 1997

YEAR("1/1/1") -> 2001





Spreadsheet Capacity

The ELF spreadsheet capacity was designed to maintain compatibility with Excel 4.

Spreadsheet Capacity

Specification Capacity
Maximum number of rows 16,384
Maximum number of observations 16,383 (one row for variable names)
Maximum number of column 256
Maximum number of variables 256
Row height 0-409 points
Column width 0-255 characters
Largest positive number 9.99999999999999E307
Largest negative number -9.99999999999999E307
Smallest positive number 1E-307
Smallest negative number -1E-307
Maximum number of characters in a text string 255
Maximum number of function arguments 30
Excel version 4






Reference Greg Harvey, Excel for Dummies (IDG Books Worldwide, Inc, Foster City, CA, 1994, Second Edition).






Appendix: Table Lookup Functions

ELF has five table lookup functions that more or less work like tables that the IRS publishes with tax returns. Like the IRS table, the column or row that is searched must be sorted. The lookup functions have four rules:

HLOOKUP HLOOKUP ( search_item, search_range, row_index )

HLOOKUP searches the first row of the search_range until it finds a match for search_item. If there is no exact match, it uses the largest value less than search_item. The first row must be sorted in ascending order. It then looks in the row indicated by row_index and returns that value. Row_index starts with the first column as 0, the second is 1, etc.

A B C
1 11 12 13
2 21 22 23
3 31 32 33
4 41 42 43


HLOOKUP(12, A1:C4, 2) will return the number 22.

INDEX INDEX(reference [, row] [,column] [,range_number])

INDEX takes the range specified by reference and returns the value at the intersection of row and column where row and column are index numbers starting at the first row and column of the range. Optionally, more than one range can be specified by enclosing the ranges in parentheses such as (A1:C5, L10:Z66). In this case range_number determines which range is used.

A B C
1 11 12 13
2 21 22 23
3 31 32 33
4 41 42 43


INDEX(A1:C4, 1, 1) will return 11.

INDEX((A1:B4, B1:C3), 2, 1, 1) will return 21.

LOOKUP LOOKUP(lookup_value, lookup_range, result_range)

LOOKUP searches for lookup_value in lookup_range and returns the value in the same relative position in result_range. Both lookup_range and result_range must be of the same size and shape. Both must have only one row or only one column.

For example, with the spreadsheet:

A B C
1 11 12 13
2 21 22 23
3 31 32 33
4 41 42 43


LOOKUP(21, A1:A3, C2:C4) will return 23.



MATCH MATCH(lookup, range, type)

MATCH searches range for the value lookup and returns the position for a match of type type. The codes for type are 1 for 'largest value less than or equal to lookup', 0 for 'equal' (range need not be sorted), and -1 for 'smallest value greater than or equal to lookup.

With a type of 0, MATCH can compare strings using the wildcard charactres of * (asterisk) to match any number of characters and ? (question mark) to match one character.

For example with the spreadsheet:

A B C
1 11 12 13
2 21 22 23
3 31 32 33
4 41 42 43


MATCH(21, A1:A4, 0) will return 2.

MATCH(22, A2:A4, 1) will return 1.

VLOOKUP VLOOKUP(search_item, search_range, column_index)

VLOOKUP searches the first column of the search_range until it finds a match for search_item. If there is no exact match, it uses the largest value less than search_item. The first column must be sorted in ascending order. It then looks in the column indicated by column_index and returns that value. Column_index starts with the first column as 1, the second is 2, etc.

A B C
1 11 12 13
2 21 22 23
3 31 32 33
4 41 42 43


VLOOKUP(21, a1:c4, 1) will return 21.

VLOOKUP(25, A1:C4, 2) will return 22.

Graphics

Chapter 4--Graphics





Graphics are used to visually display data to show and to make clear the relationships between various series or groups of numbers.

Menu Path: Analysis | Graphics Series

or Views | Graphics

Typical Uses: To view data to understand some of the relationships between various data series and to show others these relationships.
Null Hypothesis: Not applicable.
Parameters, required: Database (1), variables (1+)
Parameters, optional: Various
Assumptions: Not applicable.
Missing Values: Graphed.
Capacity: Not applicable
Sample Databases: All
Output: Graph
Reference(s): Doub Lind and Bob Mason, Basic Statistics for Business and Economics (Irwin: Burr Ridge, Illinois, 1964), 35-56.
Related procedures: All


Sample Graphs

Sample graphs are displayed below:

Two-Dimensional Bar Chart

Plot | Type | 2D | Bar

This bar chart of GNP and the number of people employed uses the same scale for both variables.

Three-Dimensional Bar Chart

Plot | Type | 3D | Bar

This three-dimensional bar chart uses different scales for GNP and the number unemployed. All of the grids for both variables have been removed and the X Gap and Z Gap ratios have been increased for better display of the data



Two-Dimensional Line Chart

Plot | Type | 2D | Line

This two-dimensional line chart of GNP and the number of civilian people employed uses the same scale for both variables. Civilian employment has a marker.

Three-Dimensional Line (Tape) Chart

Plot | Type | 3D | Line

This three-dimensional chart uses different scales for GNP and the number unemployed. Grids have been added. Series labels have been added with a backdrop used to make the names stand out.

This type of chart requires many calculations and might be slow to create or modify.

Two-Dimensional Area Chart

Plot | Type | 2D | Area

Unemployment uses no fill to make it stand out more. GNP uses a fill pattern.

Each variable has its own Y scale that is explained in the footnote (which uses centered text).

Three-Dimensional Area Chart

Plot | Type | 3D | Area

This three-dimensional area chart shows annual GNP and unemployment on separate Y axes. The series labels were dragged with the mouse to their locations.

Two-Dimensional Step Chart

Plot | Type | 2D | Step Chart

This is the previous area chart converted to a step chart. The step chart has flat areas (steps) instead of points.

Three-Dimensional Step Chart

Plot | Type | 3D | Step Chart

This is the step chart version of the previous area chart.

Two-Dimensional Horizontal Bar Chart

Plot | Type | 2D | Horizontal Bar

This is a two-dimensional horizontal bar chart for unemployment and Gross National Product using the same scale.

Three-Dimensional Horizontal Bar Chart

Plot | Type | 3D | Horizontal Bar

This is a three-dimensional horizontal bar chart.

Two-Dimensional XY (Scatter) Chart

Plot | Type | 2D | XY (Scatter)

This XY chart shows Gross National Product (GNP) on the horizontal (X) axis and the number of people unemployed on the vertical (Y) axis. It appears that there is a trend for both to increase over time although there is considerable fluctuation. (The growing number of unemployed is due to a growing population.)

There is no three-dimensional XYZ chart available.




Graph Options

Graph Options

To Do This... Do This...
Select Graph Type Graphics | Plot | Type
Select Variables Windows | Input

Analysis | Graphics Series

Open File Button Select database

Select variables to graph

Note: if an X variable is not explicitly selected, the first variable in the database will be graphed on the X axis

Graphics Canvas Background Graphics | General | Backdrop

The Backdrop page customizes fill and frame. It also lets the user specify a BMP or WMF file as a background picture.

Use Fill Pattern or Gradient For the appropriate graph element, select Backdrop. Under Fill, click on Pattern or Gradient. The Fill/From Color is either the top color (gradient), or the background color (fill). The Pattern/To Color is either the color at the bottom (gradient) or the pattern color (pattern).
Hide or Show Title, Footnote, Legend or Second Y Axis Graphics | General | Options

Check the appropriate box.

Custom Locate the Graph Graphics | Plot | Location

Check custom location.

Change the Order of Variables Graphics | Plot | Order
Plot a Variable On The Second Y Axis Graphics | Series |