Spreadsheeting with Excel

SPREADSHEETING WITH EXCEL
I. FUNDAMENTALS, FORMULAS, AND FUNCTIONS

Richard M. Felder
Department of Chemical Engineering
North Carolina State University

(Revised by E.M. Wilcox, August, 1998)

SPREADSHEETS

A spreadsheet is a matrix of information, organized by rows and columns. Each cell of the spreadsheet may contain a number, a character string, or a mathematical formula. An illustrative spreadsheet is shown below.

A B C D

1 Personal Budget Sheet: September 1998

2

3 Actual

4 Budget Expense Saving

5

6 Entertainment $50.00 $60.00 ($10.00)

7 Books $20.00 $0.00 $20.00

8 Groceries $150.00 $120.00 $30.00

9 Rent $150.00 $150.00 $0.00

10 Stationery $10.00 $0.00 $10.00

11 Transportation $20.00 $10.00 $10.00

12 Utilities $30.00 $30.00 $0.00

13

14 Total $430.00 $370.00 $60.00

The spreadsheet has 14 rows and four columns (A-D).

In preparing this spreadsheet, the user will have typed in labels (text) or numbers for the cells in Rows 1-12, Columns A-C, such as

Expense in Cell [C4] (Column C, Row 4)

150.00 in Cell [B8]

In other cells the user will have placed formulas to be used to calculate the cell contents. For instance, the formula in Cell D6 would be

=B6-C6 (entered in Cell D6)

which means, subtract the number in Cell C6 from the number in Cell B6 and put the result in Cell D6. (All formulas begin with equal signs.) The formula in Cell B14 might be

=SUM(B6:B12) (entered in Cell B14)

which means add the numbers in Cells B6 through B12 and put the result in Cell B14.

Once input values and formulas have been entered in their respective cells, the spreadsheet program executes all the formulas and inserts the results in the corresponding cells of the spreadsheet. If some of the cell values are then changed, the formulas are automatically recalculated to reflect the new input values. Once the spreadsheet has been prepared it can be printed out and used to generate plots and tables to be included in a report.

RUNNING EXCEL ON UNITY/EOS WORKSTATIONS

Excel is Microsoft's (and the world's) most popular spreadsheet program. It runs on Windows machines (like the Windows NT workstations in the basement of the laundry building) and on Macintoshes. It can also run on UNIX machines (like the SPARCstations in Riddick 118), but only under a Windows emulator like SoftWindows or WABI, both of which can be accessed from the SPARCstations. Running on a Windows machine rather than a Windows emulator is invariably faster.

IMPORTANT COMMANDS

Before taking you through an introductory tutorial, we list several important commands for easy reference. If you want to quit a session or if you get in trouble, check this list.

Entering Excel.
1. UNIX Workstations. To start an Excel session on EOS on a workstation that runs under UNIX (like the SPARCstations in 118 Riddick), move the pointer outside any existing windows, hold down the middle mouse button until the Application Menu appears, drag the pointer down to MS-Window Emulation and to the right and down to Soft Windows or WABI, and release. It may take few minutes for Windows to start. A Windows 3.1 window will eventually open.
  Under Program Manager, double-click on MS Office and then on Excel. Excel will open in a new window. If you have never used Excel before, you may want to take a few minutes to work through the "Getting Started" tutorial. Click on "Return to Microsoft Excel" to begin using Excel.
2. Windows NT Workstations. (Instructions still to follow.)
Loading a stored spreadsheet.
To recover a spreadsheet that you previously stored, choose the File menu and select Open. Alternatively, click the "Open File" icon (which looks like a yellow file folder being opened) at the far left of the Excel tool bar. In the dialog window that opens, point to the name of the desired spreadsheet file under "File Name" (scroll to it if necessary) and then double-click the left mouse button. The stored spreadsheet will appear in the worksheet window and its name will appear in the header. If you do not see any files, make sure that under "List Files of Type" it says Microsoft Excel Files (*.xl*) or alternatively All Files (*.*).
Saving.
To store (save) the worksheet you have been working on, choose the File menu and select Save, or click the ``Save File'' icon (which looks like a floppy disk with an arrow pointing into it on the Excel tool bar). Name the spreadsheet in the dialog window that opens, or click on OK to save under the previous name. The file name may be anything you like, but the file type should be .xls (e.g. budget.xls).
Save frequently while you are working!
Getting on-line help.
If you have never done so, explore the on-line Help facility (use the Help menu button at the upper right of the Excel window) to get an idea of the kind of information available there. If you don't explore the facility first, you may not think of using it when you need it.
Quitting.
To quit Excel, choose the File menu and select Exit, or double click on the horizontal bar in the upper left-hand corner. To exit the windows emulator, go to the Program Manager window and exit as you did in Excel.

TUTORIAL

Getting Started

Log on and open Excel, following the procedure given previously for the type of workstation you are running (Windows or UNIX). The Excel window that opens has the following components (find them on the screen as we describe them):

Sheet (or Worksheet:) The large grid with columns labeled A,B,C,... and rows labeled 1,2,3,... You enter values (sometimes numbers, sometimes alphanumeric text) in some of the cells of the worksheet (e.g. Cell [C3], located in Column C and Row 3) and enter formulas in other cells. You can build a spreadsheet with many more rows and columns than are shown on the screen at the moment; you just can't display all of it at once.
For large programs it is often convenient to work on more than one sheet. To create or switch to a different sheet, you would click on the number of the sheet you wish to change to at the bottom of the Excel window. (Don't do it now.) The Workbook is the set of worksheets that comprise a complete file.
Title bar. The bar at the top of the window frame with "Microsoft Excel - Book 1" in it. As soon as you save a current spreadsheet or load a new one, the spreadsheet file name will appear in the title bar.
Standard toolbar. This toolbar contains buttons that provide convenient shortcuts for some of the more commonly used operations, such as opening and saving files, printing the spreadsheet, formatting cells, cutting and pasting, and creating graphs.
Menu bar. You select items for each of the menus shown by holding down the left mouse button on the desired menu button (e.g. File or Format or Tools), dragging the pointer down to the desired menu selection, and releasing the mouse button.
Formatting toolbar. The formatting toolbar displays the font used for text and numbers (Arial is default), the font size (10 is default), buttons to make selected text boldface (B), italicized (I), or underlined (U), buttons to make text left-adjusted, centered, or right-adjusted within a cell, and several others.
Formula bar. The line just above the worksheet column headings (A B C...) contains a narrow window (the address window) and a wide window (the edit line).
- The address window should now contain [A1], indicating that the cell cursor is at Column A, Row 1. (The cursor location is the cell with the darkened border on the worksheet.) Type a number now, but don't hit the return key.
- As soon as you entered information in the cell, an X, a check mark, and an = or an fx (depending on which type of machine you're on) were displayed to the right of the edit line if they weren't already there. If you click on the X, the previous value in the cell will remain unchanged. If you click on the check mark or hit [Return], what you typed will be entered. (Don't worry about the other button for now.) Try it.
- When you click on a cell, whatever is in the cell will be displayed on the edit line. As soon as you type anything, whatever you type will appear on the edit line, replacing the former cell contents.

Some of our descriptions may not exactly match what you seen on your screen. The reason is that two different versions of Excel are installed on the Windows NT and UNIX file servers.

We will explain the other features of the Excel window in the exercises that follow. The symbol [Return] henceforth denotes the Return or Enter key.

Moving around the worksheet

Move the cursor around the worksheet using the mouse. Move the pointer to different cells of the worksheet and click the left mouse button. Note that the cell cursor jumps to the cell on which you click and the cell address of its new location is shown on the control/status line.
Move the cursor with the arrow keys. Use the four arrow keys toward the lower right of the keyboard to move the cell cursor up, down, left, and right.
Go directly to a named cell. Using the mouse, choose the Edit menu (move the pointer to it and press and hold the left mouse button) and select Go to... (drag the pointer down to that item and release the button). In the Selection window of the dialog box that appears, type D5 [Return]. The cursor should now be in Cell D5.
Return the cursor to Cell A1. Press [Control][Home]. (The control key is toward the lower left of the keyboard and the home key is in the second group of keys from the right.)
Shift the worksheet. Hold the right arrow down to push the cursor past the rightmost column showing on the screen. Notice that you are bringing additional columns into view on the right and pushing others off to the left. Keep going past Column Z and observe how the adjacent columns are labeled (AA, AB, AC,...). Now move down to higher numbered rows at the bottom with the down arrow, pushing upper rows out of view at the top.

Enter a number in a cell. Send the cursor to Cell AV61. (Use the Go To command under the Edit menu.) Type 27 [Return]. The number you typed should appear in Cell AV61.

Scrolling the worksheet.

Scroll horizontally one column at a time. Look at the bar just below the last visible row of the worksheet. On the left are some arrow buttons (for scrolling from one sheet to another) and sheet numbers, and on the right is the horizontal scroll bar, a window with a button or bar inside it and arrow buttons on either side. Point to the small arrow button at the left of the scroll bar and click. The worksheet shifts left by one column. Do it once more. Now scroll back to the right, with the arrow at the right of the scroll bar, until the 27 reappears at the lower right.

Scroll horizontally with the slider bar. Move the pointer to the slider bar (the button within the scroll bar). Hold the left mouse button down and move the pointer left and then right to scroll the worksheet horizontally.

Scroll vertically. Use the vertical scroll bar to the right of the worksheet to scroll up and down a row at a time (click on arrows) or continuously (drag the slider bar with the pointer).

Return to the upper left of the worksheet. Press [Control][Home]. Cell [A1] should appear at the upper left corner of the worksheet with the cursor in it.

In the remainder of this tutorial, you will produce a spreadsheet that appears as follows:

Trial Worksheet: M= 3

Run X Y M(X-Y)

1 2 4 -6

2 3 4 -3

3 6 2 12

TOTAL 11 10 3

You will enter the first row, the column titles, the entries for the Columns headed RUN, X, and Y, and the word TOTAL, and you will then enter formulas that Excel will use to calculate the values of M(X-Y) and the column totals.

Entering labels and numbers in cells

A cell can contain:

a number
a label--any string of characters, like RUN, X, and TOTAL
a formula, like =(2.0+B5)/C5 (two plus the number in Cell B5 divided by the number in Cell C5). Formulas always begin with equal signs.

We will show you how to enter labels and numbers in the next series of instructions, and we will eventually get to formulas.

Note: Throughout the remainder of the exercise we will tell you to type things enclosed in angled brackets (< >) followed by [Return]: for example, type < 26.3 > [Return]. (Don't type it now.) When you see instructions like this, type what is in the brackets but not the brackets themselves and then hit the Return key.

11. Enter a label in a cell.

Move the cursor to Cell [A1] if it is not already there.
Type < Trial Wormsheet: M = >. For the moment, don't follow it with [CR]. (Don't type the brackets, and really type ``Wormsheet"). What you typed should appear on the edit line.
Whenever text is displayed on the edit line, you can edit it using the mouse, arrow keys, and delete key, just as though you were editing a text file in a text editor or word processing program.

Now hit the [Return] key to insert the text in Cell A1. The text has gone over the width of Column A, but since nothing will be entered in Cell B1 this is not a problem.

Instead of hitting the [Return] key to enter what you typed into Cell [A1], you could have clicked on the check symbol. In either case the cursor would remain on Cell [A1]. You could also have hit the down arrow key to move the cursor down to Cell [A2] or the right arrow to move the cursor right to Cell [B1] after the entry had been made in Cell [A1]. If you had wanted to cancel the editing without changing the content of Cell [A1], you could have clicked on the X symbol.

12. Move the cursor to Cell C1 (point at it and click the left mouse button). Type < 3 >.

Don't type [Return] yet. (If you did, type another 3.) As soon as you typed the number, it appeared on the edit line preceded by an =. If you had wanted the 3 to be considered a label, you would have preceded it with an apostrophe ('3).

13. Type [Return] to enter the 3 in Cell C1.

14. Move the cursor to Cell A3. Type RUN[Return].

Notice that the text appears at the left of the cell (i.e., it is left-justified in the column). Excel does this automatically when you enter anything but a number or formula.

15. Justify and center labels.

You can position a label in a cell using the justification buttons on the formatting tool bar. These buttons have icons consisting of left justified, centered, and right justified lines. Clicking on these buttons will adjust the positioning of the text in a highlighted region or in the current cell if no region is highlighted. Try it in Cell C3, ending by right-justifying the label.

16. Enter a number and move the cursor down one cell.

Move the cursor to Cell A5 and type 1. Then hit the down-arrow key to enter the number and move the cursor down to the next cell. Notice that numbers are automatically right justified.

17. Enter 2 and 3 respectively in Cells A6 and A7, moving down with each entry.

Enter < TOTAL >[Return] in Cell A9, and then right-justify it.
Move the cursor to Cell B3. (Point and click.)
Enter < X > [Return] and right-justify it.
Enter 5, 3, and 6 in Cells B5, B6, and B7.
Move the cursor to Cell C3 and enter < Y >(right justified).
Enter 4, 4, and 2 in Cells C5, C6, and C7.

At this point the worksheet should appear as follows (with slightly different spacing):

Trial Wormsheet: M =

RUN X Y

1 5 4

2 3 4

3 6 2

TOTAL

Not shown but still part of the worksheet is the 27 in Cell AV61.

Erasing and editing cell contents

18. Erase the contents of a cell. Let's now get rid of that 27.

Move the cursor to Cell AV61 (use the Go To command under the View menu).
Choose the Edit menu and select Clear then All. Alternatively, you can just hit the delete or backspace button. The 27 should disappear from Cell AV61.
Send the cursor back to Cell A1. ([Control][Home])

19. Edit (revise) the contents of a cell.

We now observe that the word Wormsheet in Cell A1 should really be Worksheet. Move the pointer next to the offending m in the edit line. Hold down the left mouse button and drag the pointer over the m, so that it (and only it) is highlighted. Type k [Return]. The corrected text should appear in Cell A1.

Naming and saving a worksheet

Choose the File menu and select Save As... After a few seconds a dialog window will come up with your current directory name in the Selection window. Click on the file name and type [mod1.xls]. The file worksheet has now been stored under the name mod1.xls and the file name appears at the top of the spreadsheet window. A convenient shortcut for saving the spreadsheet is the Save button on the tool bar, which appears as a floppy disk icon.

You can give a stored spreadsheet file any name you want. However, for normal applications the type should always be .xls.

Selecting a range of cells (highlighting a region of the worksheet)

20. Select a range with the mouse.

The black rectangular region defined by your starting point (Cell A5) and finishing point (Cell C7) has been selected. Once you have selected a region, you may do a variety of things with it (don't do any of them now):

Erase the contents of the selected cells (using the Clear command under the Edit menu).
Copy the contents to another region of the worksheet, having them also remain where they are (using the Copy command under the Edit menu).
Cut them from their current location and Paste them somewhere else (using the Cut and Paste commands under the Edit menu).
Format the selected cells (using the Cells command under the Format menu) so that whatever numbers appear in them now and subsequently are written as decimals with a specified number of digits past the decimal point (fixed format), or in scientific notation, e.g. 4.1e-03 (scientific format), or with dollar signs, etc.

21. Deselect.

Click the left mouse button anywhere in the worksheet outside the highlighted region. The highlighting disappears.

22. Select a range using the Select command.

Choose the Edit menu and select the Go To command. A dialog window should come up with a cursor blinking in the selection box.

Type [ a5:b7 ] [Return]. The highlight box should cover the designated region (a5 at the upper left to b7 at the lower right). (This is a convenient way to select a range that goes beyond the visible portion of the worksheet.)
Deselect. (Point and click anywhere in the worksheet area.)

23. Select an entire column, an entire row, or the complete worksheet..

Click the left mouse button on the letter at the top of any column. The entire column is selected. You can select an entire row by clicking on its number to the left of the worksheet, or select the entire worksheet area by clicking the button at the top left corner of the worksheet (above 1 and to the left of A). Try it.
Deselect in the usual way.

Copying Cells

Select the range you wish to copy. Select range A3:B7 by pointing at Cell A3, holding down the left button, dragging to Cell B7, and releasing.
Copy the selected range. Right click on the highlighted cells, and select copy. Point to Cell D3 (the upper left cell of the region in which you want the selected range to be copied) and click the right mouse button and select paste. The range should be duplicated, with the upper left cell in the location you clicked on (Cell D3). You could have done the same thing by choosing Copy from the Edit menu and then clicking on Cell D3and choosing Paste from the Edit menu. There are also copy and paste buttons on the toolbar.
Erase the cell values just inserted. Select range D3:E7 by pressing the left mouse button and dragging the pointer. Choose the Edit menu and select Clear - All. The cells should now be blank. Alternatively, you can undo the copy by selecting Edit - Undo, or by clicking the counterclockwise arrow icon on the tool bar.

Saving the revised worksheet.

24. Choose the File menu and select Save. Alternatively, click the disk icon on the tool bar. The current version of the spreadsheet will be saved under the same file name (mod1.xls).

You should never work for more than three minutes without saving. If the system crashes while you are working, you will lose everything you have done since the last save.

Quitting Excel

25. Choose the File menu and select Exit.

You can now log off and take a break or proceed directly to Step 26 to continue the exercise. Before you log off, be sure to close the windows emulator.

* * * * *

Recalling stored worksheets

If you logged off before, bring up Excel again. (On a UNIX workstation, press the middle mouse button to bring up the Applications Menu and select MS-Windows Emulators - Soft Windows; under Program manager double click on MS Office; double click on Excel.)

26. Open the previously saved file.

Choose the File menu and select Open. When the dialog window comes up, point to the name of the desired file (mod1.xls), first scrolling to it or changing the drive if necessary, and then double-click with the left mouse button. The worksheet generated previously should appear on the screen. If it does not, recreate it.

Entering Formulas

27. Enter a column heading.

Enter M(X-Y) in Cell D3 and right-justify it (i.e., enter M(X-Y) [Return] and right-justify it (button on the formatting toolbar).

28. Enter a formula in a cell.

Move the cursor to Cell D5.
Type < =$C$1 * (B5-C5) >[Return]. (Don't omit the = sign.)

You have just entered the formula D5 = C1*(B5-C5). The value in Cell D5 will be that in Cell C1 (3) times the difference between the value in Cell B5 (5) minus that in Cell C5 (4). The formula appears on the edit line and the calculated value (3) appears in the cell on the worksheet. (We will explain the dollar signs in front of the column and row labels of Cell C1 when we talk about copying formulas.)

Note the following points about formulas:

When you begin an entry with an equal sign (=), Excel understands that what follows is either a number or a formula (as opposed to a label). (To enter a label, which begins with an equal sign, you must prefix the label with a left quote mark (').)
Any component of a cell entry consisting of one or two letters followed by one or two numbers (e.g. B6, D16, AM27) is taken to be a cell address, and the number in that cell will be substituted in the formula.
The arithmetic operators that can go in formulas are +, -, * (multiplication), / (division), and ^ (exponentiation). For example, D6^2 signifies the square of the value in Cell D6.
If a formula contains a string of operations, e.g. 5^2 + 3/4 * 6, all exponentiations will be done first, then all multiplications and divisions from left to right, and finally all additions and subtractions from left to right. To change this order, use parentheses.

29. Enter a formula by pointing and clicking on component cells.

Keeping the cursor on Cell D5,

Type < =$C$1 * ( >( (no [Return] yet)
Point to Cell B5 and click. Notice that the cell address appears in the formula on the edit line.
Type < - >
Click on Cell C5. Observe the result on the edit line.
Type < ) >[Return].

The same formula has now been entered in Cell D5, with the same result.

In short, you can insert a cell address in a formula by typing it in or by pointing at the cell and clicking the left mouse button.

30. Change the value of a cell that appears in a formula.

Change the value in Cell B5 to 2 (click on Cell B5 and type 2 [Return]). Notice that the value in Cell D5 immediately changes (to -6), reflecting the new value in B5.

Copying a Formula

We now want to copy the formula in Cell D5 into Cells D6 and D7, but not to copy it exactly. We want each cell in Column D to contain the difference between the values in the same row of Column B (X) and Column C (Y) multiplied by the value in C1. Thus, the formula in Cell D6 should be D6 = C1*(B6-C6), and that in Cell D7 should be D7 = C1*(B7-C7). This task turns out to be very easy to accomplish with Excel. Excel will automatically change the cell names when a formula is copied.

31. Copy a formula in one cell into selected adjacent cells.

Select the cell containing the formula to be copied. Point to Cell D5 and click on it.
Copy the formula by clicking and dragging over the desired target range. Point to the little black square at the lower right corner of Cell D5, press and hold the left mouse button, drag the cursor down to Cell D7, and release.
The formula has now been copied, with the appropriate changes in cell addresses having been made automatically. (Check this: click on Cell D6 and look at the formula in the edit line, and then do the same for Cell D7.)

How did Excel know which cell addresses should go in the copied formulas? The answer follows.

Relative and Fixed Cell Addresses in Formulas

When you enter cell addresses such as B5 and C5 in formulas they are relative addresses. Suppose the formula cell is the cell containing the formula (D5). To Excel, the formula (B5-C5) in Cell D5 means the value two cells to the left of the formula cell (B5) minus the value one cell to the left of the formula cell (C5).
When you copy a formula from one cell (D5) to another (D6), the same addresses relative to the new formula cell are used. If the formula in Cell D5 involves B5, then the copied formula in Cell D6 will instead involve B6 (as before, the cell two columns to the left of the formula cell).

Sometimes you may want to refer to a fixed cell address in a formula, i.e., a cell address that should not change when you copy the formula to a new cell. For example, we did not want Cell C1 in our formula to change to C2, C3, etc., when we copied to lower formula cells. To keep a cell address fixed, we precede the row and column identifiers of the fixed address in the formula with dollar signs. Thus, $C$1 in a formula will always be Cell C1, regardless of where on the worksheet the formula is copied. ($C1 would fix the column but would let the row vary.)

Excel Functions

32. Move to Cell B9. Type < =SUM(B5:B7) >[Return].

You have just used an Excel function called SUM, which calculates the sum of the cell values in its argument (in this case, Cells B5 through B7).

Excel has a variety of built-in mathematical functions (ABS, EXP, LN, SIN, etc.), statistical functions (AVERAGE, MAX, MIN), financial functions, and others. Some of these functions are listed at the end of this tutorial.

33. Copy the formula in Cell B9 into Cells C9 and D9.

Click on Cell B9.
Point to the square at the lower right of Cell B9, drag over C9:D9, and release.

You copied the formula in Cell B9 into Cells C9 and D9, changing the cell addresses appropriately. Verify that the formulas (edit line) and the values (worksheet) in Cells C9 and D9 are correct.

The worksheet should now appear like the one shown at the beginning of this exercise.

34. Save.

35. Change a cell value and recalculate the spreadsheet.

Change the value in Cell B6 to 5. Notice that all cells affected by this change (B9, D6, and D9) instantly assume their new values.

Printing a Spreadsheet

36. Print the worksheet.

If you only wanted to print a portion of the spreadsheet you would select the desired range and then click on "Selection" in the dialogue box. If you have a number of sheets and want to print all of them, you would choose "Entire Workbook."

37. Exit from Excel. Choose the File menu and select Exit.

APPENDIX: EXCEL FUNCTIONS

Listed below are some (but not all) of the built-in functions provided by Excel. Arguments may be numbers, cell addresses, or cell ranges [e.g. EXP(4.5), SUM(A2,A4), AVERAGE(C6:C15)].

If a function has multiple arguments, the argument values or the addresses of the cells containing these values may be listed as ranges (C3:C9), or as individual cell addresses and/or numbers separated by commas (A4,C5,17.3), or a combination (A5,B4:B8,C3). To get a complete list of functions and more information about them, use the on-line help facility.

ABS(X)

Absolute value of X

COS(X)

Cosine of X (X in radians)

EXP(X)

e^x

INT(X)

Integer value of X

LN(X)

ln X

LOG(X)

log X

RADIANS(X)

3.14159 X/180

RAND()

Returns a random number uniform on the interval [0,1].

SIN(X)

Sine of X (X in radians)

SQRT(X)

Square root of X

TAN(X)

Tangent of X (X in radians)

AVERAGE(X:Y)

Average of the arguments

RSQ(R1,R2)

Pearson's product-moment correlation coefficient for he paired data in Ranges R1 and R2

COUNT(X:Y)

Count of non-blank arguments

MAX(X:Y)

Maximum of the arguments, not counting blank cells and cells containing text strings

MIN(X:Y)

Minimum of the arguments

SUMSQ(X:Y)

Sum of squares of the arguments

STDEV(X:Y)

Sample standard deviation of the arguments (N-1 weighting)

SUM(X:Y)

Sum of the arguments

VARS(X:Y)

Sample variance (N-1 weighting) of the arguments

MDETERM(M)

Determinant of the square matrix specified by Range M

MINVERSE(M)

Inverse of square matrix M

MMUL(M1,M2)

Product of multiplication of matrix M2 by matrix M1

TRANSPOSE(M)

Transpose of matrix

	A	B	C	D
1	Personal Budget Sheet: September 1998
2
3			Actual
4		Budget	Expense	Saving
5
6	Entertainment	$50.00	$60.00	($10.00)
7	Books	$20.00	$0.00	$20.00
8	Groceries	$150.00	$120.00	$30.00
9	Rent	$150.00	$150.00	$0.00
10	Stationery	$10.00	$0.00	$10.00
11	Transportation	$20.00	$10.00	$10.00
12	Utilities	$30.00	$30.00	$0.00
13
14	Total	$430.00	$370.00	$60.00

	ABS(X)		Absolute value of X
	COS(X)		Cosine of X (X in radians)
	EXP(X)		e^x
	INT(X)		Integer value of X
	LN(X)		ln X
	LOG(X)		log X
	RADIANS(X)		3.14159 X/180
	RAND()		Returns a random number uniform on the interval [0,1].
	SIN(X)		Sine of X (X in radians)
	SQRT(X)		Square root of X
	TAN(X)		Tangent of X (X in radians)

	AVERAGE(X:Y)		Average of the arguments
	RSQ(R1,R2)		Pearson's product-moment correlation coefficient for he paired data in Ranges R1 and R2
	COUNT(X:Y)		Count of non-blank arguments
	MAX(X:Y)		Maximum of the arguments, not counting blank cells and cells containing text strings
	MIN(X:Y)		Minimum of the arguments
	SUMSQ(X:Y)		Sum of squares of the arguments
	STDEV(X:Y)		Sample standard deviation of the arguments (N-1 weighting)
	SUM(X:Y)		Sum of the arguments
	VARS(X:Y)		Sample variance (N-1 weighting) of the arguments

	MDETERM(M)		Determinant of the square matrix specified by Range M
	MINVERSE(M)		Inverse of square matrix M
	MMUL(M1,M2)		Product of multiplication of matrix M2 by matrix M1
	TRANSPOSE(M)		Transpose of matrix