Quick Start

Microsoft Excel 97 SR-2 under MS Win 95 or MS Win NT Though this manual is designed to be used with MS Excel 97, as far as I know it accurately describes MS Excel version 5 and MS Excel 97 except for the sections on graphing. Even there, the family resemblence between different versions of MS Excel is strong, making this manual not entirely unusable for earlier versions. For this reason, the practice spreadsheet, XL_LRN5.xls is provided in MS Excel version 5 format. If used with a more recent version of MS Excel, open it, and then save it in the format of your version. There is also XL_learn.xls which is in MS Excel 97 format. It makes no difference which you use. You can find more information on most of the topics here described briefly in the sections titled REFERENCE: which follow the analysis of the example experiment. See the Table of Contents.

Run Excel

Your instructor will show you how to access MS Excel.

Load XL_LRN5.xls (for MS Excel version 5.0 or later)

1. Click the yellow opening folder icon at the upper left of the MS Excel Window.

2. You instructor will tell you how to navigate to XL_LRN5.xls.

3. If you have a more recent version of MS Excel, you should save the spreadsheet in the format of the version of MS Excel that you are using.

a) From the main menu bar, choose File, Save As.

b) Your instructor will tell you how to navigate to a folder or floppy where you can save the

spreadsheet.

Selecting a cell or a block of cells

1. To select a single cell, click on it. For example, select cell A8. Column A and row 8, according to the column labeling at the top of the sheet and the row labeling on the right side of the sheet.

2. To select a block of cells,

a) click on the first cell you want to select, and then

b) drag the mouse to the last cell you want to select.

c) The selected cells will have a thick black border and, except for the first cell you selected, they will be shaded.

d) For example, select A9:A21 (column A, rows 9 through 21).

Deleting cell contents

1. Select the cell or block of cells.

2. Press the Del key. The contents of the cell or cell will be erased.

3. Try this by

a) selecting cell C8,

b) typing TUBA followed by the Enter key, and

c) then re-selecting C8 and pressing Del.

Formatting numbers displayed on the spreadsheet

If you want to change the number of decimal digits displayed or switch to scientific notation, do the following.

Page 5

1. Select the cells you want to format for numeric display.

2. Right-click on the selection.

3. From the pop-up menu, choose Format Cells.

4. From the next menu, choose Number for displaying a fixed number of digits past the decimal place or Scientific for scientific notation.

5. Select the number of decimal places.

6. Click the OK button.

Sheet and chart pages

Right-clicking on a tab (at the bottom of the sheet or chart) brings up a menu that allows you to do the following.

1. Delete the sheet or chart. For example, use this if you mess up a graph and want to re-do it.

2. Rename a sheet. For example, you might want a meaningful name for a graph, instead of the automatically assigned name Chart 1.

3. Insert new blank sheets.

Beginning of Data Analysis Project

The best way to learn how to use a tool is to use it. So, here you go!

A sample experiment to analyze

1. The analysis of the data from the experiment about to be described is typical of the data analysis required for TAS Physics labs.

2. Here is the experiment.

a) The purpose of the experiment is to measure the drag

force of air resistance on a falling sphere.

b) Theory predicts that the drag force F D is proportional to v 2 , the square of the sphere's velocity. The experiment will measure F D and compare the measured values of F D with a force proportional to v n in order to determine the value of the exponent n that applies in the case of a real falling sphere.

c) The experiment is performed by placing a police speed gun at the base of a cliff - with the gun pointed directly upward - and dropping a sphere off the cliff directly onto the speed gun. The speed gun is programmed to measure and to record the speed of the falling sphere every half second.

d) To make a long story short, from the changing velocity one can calculate the acceleration of the sphere, and from the acceleration of the sphere one can calculate the total force on the sphere. Subtracting the known gravitational force from the total force leaves F D , the drag force from air resistance.

e) The local acceleration due to gravity where the experiment is performed is carefully measured and found to be 9.8037 m/s 2 , and the mass of the sphere is 0.326 kg. These are needed for calculating forces on the sphere.

t (s) , v (m/s)

0. 0 s, 0.0 m/s

Speed gun

Page 6

f) Here is the velocity data from the speed gun.

Here is the start of the spreadsheet that will analyze the data

The name the file containing the spreadsheet is XL_LRN5.xls (MS Excel version 5 format) or XL_learn.xls

(MS Excel 97 format). Titles for columns and the data are in place, but the spreadsheet does almost no calculation,

and it does no graphing at all. The present task is to add the calculations and the graphing to the spreadsheet.

Page 7

Run Excel and load XL_LRN5.xls

If you have not done it already, do it now! Then note the following on Sheet 1

1. g, the acceleration of gravity, in m/s 2

2. m, the mass of the sphere, in kg

3. The two columns labeled t and v

a) These columns contain the original data from the speed gun.

b) Select a cell from one of these columns, say A8.

c) From the Formula Bar (see the appropriate Reference section of this manual) you see that these cells contain numbers.

4. The column labeled t (ave)

a) The t (ave) column contains the times at the midpoints between speed measurements.

b) Select a cell from this column, say D9.

c) From the Formula Bar you see that these cells contain formulae, not numbers.

d) The formulas in the cells calculate the t (ave) values: =(A8+A9)/2 in cell D9.

e) Use the Down Arrow key to scan down the column and see how the formula changes.

5. The five columns to the right of the t (ave) column must be filled in using other formulae.

The next sections of this handout present the formulae for each of these columns.

Formula for average speed of the sphere: the v (ave) column

The formula used here is not exactly correct, but it will be close to correct as long as the times between speed measurements are short.

1. The average speed during an inverval is v = 1/2(vi + vf).

a) v i (initial speed) is the sphere's speed at the beginning of the interval.

b) v f (final speed) is the sphere's speed at the end of the interval.

2. Therefore, in cell E9, enter: =(B8+B9)/2 [Then press the Enter key]

3. Explanation Cell B8 contains v i , and cell B9 contains v f .

4. In the following steps, copy the formula for average speed into the other cells in the v (ave) column.

a) Select cell E9.

b) Edit, Copy, to copy the formula to the (invisible) Windows Clipboard.

c) Select the other cells in the v (ave) column, cells E10:E21 (meaning E10 through E21).

d) Edit, Paste, to copy the formula from the Windows Clipboard to all the selected cells.

e) Press the Esc key to make the circulating border around E9 go away.

5. Select each cell in the v (ave) column one-by-one and note that the formula stored in each cell uses the correct v i and v f for that interval. The spreadsheet automatically made the adjustment when it loaded the formula into the cells.

6. Compare the values in column E with those in the spreadsheet shown on page 9.

Formula for average acceleration of the sphere: the a (ave) column

Again, the formula used is approximate but close to correct for short time intervals.

1. The formula for the average acceleration during an inverval is a = Dv/Dt = vf - vi / tf - ti

a) v i (initial speed) is the sphere's speed at time t i , the beginning of the interval.

b) v f (final speed) is the sphere's speed at time t f , the end of the interval.

2. Therefore, in cell F9, enter: =(B9-B8)/(A9-A8)

Page 8

You might be tempted to just use 0.5 for the denominator, instead of the more cumbersome A9-A8, but there is a good reason for not doing that. In a realistic case, one might run the experiment many times, with different spheres, and the time between speed measurements might not always be the same. Using A9-A8 for the denominator makes the spreadsheet work correctly even if the speed gun timing changes.

3. Explanation

a) Cell B8 contains v i , and cell B9 contains v f .

b) Cell A8 contains t i , and cell A9 contains t f .

c) / is the symbol for division. Use / instead of ÷.

4. Copy the formula to the rest of the cells in the a (ave) column, F10:F21. The procedure is the same as you used above for the v (ave) column.

Formula for the total force on the sphere: the Ftotal column

1. By Newton's Second Law: Ftotal = m·a Also seen as SF = m·a

2. Therefore, in cell G9 enter: =$E$4*F9

3. Explanation

a) F9 is the cell holding the acceleration.

b) E4 is the cell holding the mass of the sphere.

c) * is the symbol for multiplication. Use * instead of x .

d) The dollar signs ( $ ) tell the spreadsheet not to change the cell address when copying the

formula. $E$4 is an absolute address, a reference to cell E4 that cannot be changed to refer to any other cell.

4. Copy the formula to the rest of the cells in the Ftotal column, cells G10:G21. The procedure is the same as you used above for the v (ave) column.

5. Select each cell in the Ftotal column one-by-one and as you scan down the column

a) verify that $E$4 appears in every cell

b) while F9 changes to F10, F11, etc.

c) You can see what the $s do; they prevent E4 from changing to E5, E6, etc.

Formula for the gravitational force on the sphere: the Fgravity column

1. The gravitational force on the sphere: Fgravity = m·g. g is the acceleration due to gravity.

2. Therefore, in cell H9 enter: =$E$4*$A$4

3. Copy the formula to the other cells in the Fgravity column, cells H10:H21.

4. Since the gravitational force is just the constant weight of the sphere, nothing in the formula changes when you copy it to the rest of the column.

Formula for the drag force on the sphere: the Fdrag column

1. Here is how to calculate the drag due to air resistance

a) Take down to be the positive direction.

b) Then, the total force is Ftotal = +Fgravity - Fdrag.

c) It follows that Fdrag = Fgravity - Ftotal.

2. Therefore, in cell I9 enter: =H9-G9

3. Copy the formula to the other cells in the Fdrag column, cells I10:I21.

Preparing to Make the Graphs

There will be three graphs

1. Original data: v versus t That is, v on the vertical axis, and t on the horizontal axis

2. Analysis results: Fdrag versus v (ave)

3. A log - log graph: ln( Fdrag ) versus ln( v (ave) ) ln is the natural logarithm, log e

Page 9

Why the log - log graph?

1. Theory predicts a power law relation between F drag and v ave . Precisely, the prediction is

a) Fdrag = C * (v ave)^n where

b) C is a constant, and

c) n = 2.

2. Take the logarithm of both sides of the power law equation. You get the following.

3. Slightly rearranging

4. Compare with the standard equation of a straight line.

5. Therefore, plotting ln(F drag ) versus ln(v ave ) should produce

a) a straight line with

b) the exponent n as its slope, and

c) hopefully the slope will be 2.

Page 10

Set up data columns for the graph of Fdrag versus v (ave)

The following make copies of the v (ave) and Fdrag values in adjacent columns for graphing.

1. Enter into cell A27: =E9 This formula copies v (ave) from E9 to A27

2. Enter into cell B27: =I9 This formula copies Fdrag from I9 to B27

3. Copy the rest of the v (ave) and Fdrag values

a) Select A27:B27 Select both A27 and B27

b) Edit, Copy Copy to Window clipboard

c) Select A28:B39 Select block containing two columns

d) Edit, Paste Copy formulae to the rest of the two columns

Set up data columns for the graph of ln( Fdrag ) versus ln( v (ave) )

The following place the ln( v (ave) ) and ln( Fdrag ) values in adjacent columns for graphing.

1. Enter into cell D27: =ln(E9) Place ln( v (ave) ) value

2. Enter into cell E27: =ln(I9) Place ln( Fdrag ) value

3. For the rest of the ln( v (ave) ) and ln( Fdrag ) values,

a) Copy the formula in cell D27:E27 to cells D28:E39.

b) The procedure is as for copying the rest of the v (ave) and Fdrag values.

Making the Graphs

The instructions here are for MS Excel 97 SR2 on the PC platform.

Graph the original data, v versus t

1. Select the block of cells A8:B21

a) Do not include the column titles (because the titles occupy two lines; if they only

included one line you could include them in the selection).

b) The values in column A are for the x axis.

c) The values in column B are for the y axis.

2. Either (both have the same result)

a) click on the Chart Wizard icon, or

b) select Insert, Chart, beginning with the main menu bar.

3. Step 1 of 4: select the following

a) XY (Scatter) Never use Line or any othr chart type in a physics course.

b) Dots only subtype Normally, do not connect data points with lines.

4. Step 2 of 4

a) Data Range tab: verify that the data range is correct.

b) Data Range tab: verify the Columns radio button is selected.

c) Series tab: optionally name the series v vs t.

d) Series tab: verify that the x and y columns are correctly identified.

5. Step 3 of 4

a) Titles tab: enter useful titles, with correct units for the x and y axes.

b) Legend tab: when there is only one set of points on graph, uncheck the Show Legend box.

6. Step 4 of 4

a) Select the As New Sheet radio button

b) Optionally enter a short name for the sheet tab, v vs t, for example.

Graph Fdrag versus v (ave)

Follow the same steps as for the v versus t graph except

Chart Wizard icon

Page 11

1. select A27:B39,

2. use appropriate titles, and

3. append units to physical quantities.

Graph ln(Fdrag ) versus ln( v (ave) )

In making this graph, you will exclude the first two points from the graph. The reason for

this is that the approximations in the analysis method are badly wrong when the ball is falling

slowly, as it is at first.

Follow the same steps as for the v versus t graph except

1. select D29:E39, and This excludes the points in rows 27 and 28.

2. use appropriate titles and no units.

There are no units for ln( Fdrag ) or for ln( v (ave) ), so the labels for the x and y axes must not show any units.

Adding the Best Straight Line Fit to the Analysis Results

You can plot a straight line that fits data points directly on a graph, or you can add the values of the slope and intercept of the straight line that fits data points to the spreadsheet that holds the data. The two sections below show how to do both.

Adding the best straight line fit to the graph

Do the following for the third graph, the log-log graph, but not for the first two graphs.

1. Select the Chart that holds the graph (the log-log graph should be in Chart 3).

2. Right-click on any one of the data points.

3. Select Add Trendline.

a) Type tab: select linear.

b) Options tab: check the "Display equation on chart" box.

c) Options tab: check or un-check the "Set intercept = 0" box, whichever is appropriate.

4. After adding the best fit straight line, position the equation on the graph so it is easy to read.

Editing the names of variables in the best fit equation

The names of the variables in the best fit equation will be y and x. Usually these names are inappropriate. To change them, do the following.

1. Single-click on the equation.

2. Edit the names.

3. Click anywhere outside the equation when you are done.

Adding the best straight line fit to the spreadsheet

1. Here is how to add a calculation of the slope and intercept of the best fit straight line to the log-log analysis results of XL_LRN5.xls.

a) In cell D44 enter: m

b) In cell E44 enter: b

c) Select the block of cells D45:E45. This selects exactly two cells, D45 and E45.

d) Type the following: =LINEST(E30:E40,D30:D40,TRUE)

e) Press the following key combination: Ctrl, Shift, Enter

2. Explanation of the LINEST() function arguments

a) The first argument, E30:E40 in this example, specifies the y-axis values

Recall that the first two values, in E28:E28, are excluded from the fit.

Page 12

b) The second argument, D30:D40 in this example, specifies the x-axis values.

c) The third argument, TRUE in this case, causes the intercept b to be calculated. Change this argument to FALSE to make the intercept exactly 0.0.

How to Print Pages from a Spreadsheet

To print a single sheet

1. Select the sheet by clicking on its tab.

2. Click the printer icon. Only the selected sheet will print.

To print multiple sheets

1. Hold down the Ctrl key on the keyboard and click on sheet tabs to select them.

2. Click the printer icon. All selected sheets will print.

Print formulae rather than values

Sometimes you need a printout of the formulae in the cells instead of the values in the cells.

Here is how to get it.

1. From the main menu bar: select Tools, Options

2. Select the View tab.

3. Check the Formulas box.

4. Click the Okay button.

5. Print as usual.

6. Please uncheck the Formulas box when printing is complete.

Hand In the Following

If you are doing this as part of a class, print and hand in the following, then continue with the last section this spreadsheet project, The "What if?" Capability of Spreadsheets, below.

1. The spreadsheet displaying the calculated values (this should be Sheet 1)

2. The spreadsheet again, but this time displaying formulae

3. The graph of the original data, v versus t (which should be Chart 1)

4. The graph of Fdrag versus v (ave) (which should be Chart 2)

5. The graph of ln (Fdrag) versus ln ( v (ave) ) (which should be Chart 3)

The "What if?" Capability of Spreadsheets

This short (and last) exercise illustrates what is probably the most powerful capability of spreadsheets and the primary reason for their immense popularity.

Example

1. In your copy of XL_LRN5.xls, after all formulae and graphs have been added, change the values of v in the original data, but keep the v values in the range 0 m/s < v < 40 m/s.

2. Every time you make a change, the analysis results and the graphs change automatically.

Uses for this

1. If you discover a mistake in the original data, all the calculations and graphing are re-done as soon as you make the change.

2. If you do the experiment many times, all you need do is enter new data into the spreadsheet to see the new results immediately.

Page 13

3. In some kinds of work, one needs to see what happens if something is changed. For example, in this case, what would happen if the value of g were different? What would happen if the value of m were different? Just make the change to find out.

End of Data Analysis Project

At this point, you have completed the analysis of the sample experiment and you have exercised the features of MS Excel that will be most useful to you in introductory laboratory work. You can find more information about MS Excel in the reference sections of this manual. Study them now or keep them handy for reference. If appropriate, be sure to hand in your printouts before you leave.

REFERENCE: Formula for Motion With Friction Proportional to v 2

An object released from rest at time t = 0 has a velocity at time t > 0 given by

where v T is the object's terminal velocity. tanh( ) is the hyperbolic tangent function. The "data" for the experiment used as an example in this document were generated using v T = 42.5 m/s (a typical terminal velocity for a typical baseball) and g = 9.8037 m/s^ 2 .

The data are therefore exact, and discrepancies between theory and data are the result of the analysis method, which fails when relatively large time steps are used for relatively slowly moving objects, which is the case for the first second of motion in this simulated experiment.

REFERENCE: Menus

Activating menu items using keystrokes

You can also use the mouse to point and click. Keystrokes tend to be faster and easier once you get used to them. Use whatever method you like best.

1. Press Alt to activate the main menu bar.

2. Press the underlined letter (often the first letter) to select a menu option.

3. Press Esc (multiple times if necessary) to get back to the spreadsheet.

Activate the menu, and note the descriptions of the menu items

1. Use the left and right arrow keys to move to the different menu items, and note the brief description of each at the bottom of the screen.

2. Use the Esc key to exit from the menu and return to the spreadsheet.

REFERENCE: Sheets

Show the size of a single sheet in the spreadsheet

1. Use arrow keys to move the cursor around the screen.

2. Use the PgDn and PgUp keys to move up and down by a full screen.

3. Use Ctrl Home to get back to the cell at the upper left (cell A1).

4. Use the right arrow key to get to the 26th column. The 26 th column is labeled Z. Note the labeling of the 27 th , 28 th , etc. columns.

5. Use Home to return to column A.

Page 14

6. Full size of a single sheet: 256 columns by 66,536 rows. Therefore, a single sheet contains 256 66536 = 17,033,216 cells.

a) Use End arrow left to get to the 256th column.

b) Use End arrow down to get to the 66,536th row.

7. There can be up to 255 sheets.

Move from one sheet to another as follows

1. The arrow buttons at the lower left of the window allow you to maneuver tabs for sheets into position so you can click on them and make them active.

2. Alternatively, you can use the following keystrokes.

Ctrl PgDn moves you from Sheet 1 to Sheet 2 to Sheet 3 etc.

Ctrl PgUp moves you backwards: Sheet 3 to Sheet 2 etc.

REFERENCE: Cells and the Formula Bar

Selecting a particular cell

1. To select a particular cell, just click on it.

2. Home takes the cursor to the left-most cell in a row.

3. Ctrl Home takes the cursor to the top left-most cell in the spreadsheet (cell A1).

Put your name in cell A1

1. Use Ctrl Home to return to cell A1.

2. Type your name.

3. Press the Enter key. The Enter key terminates entry of data into the cell.

Put a number in cell A2

1. Type 3.14159 in cell A2.

2. Note that numbers align in the cell differently than does text.

Put a formula in cell A3

1. Select cell A3.

2. Enter the following into the cell.

=sin(0.3) 0.3 radians, which equals 17.2º.

3. An = sign is always used to start a formula entered into a cell.

4. The cell displays the value of the formula in the cell, not the formula you typed.

The Formula Bar

1. Between the Toolbar and the spreadsheet is a blank area called the Formula Bar

It is just to the right of an = sign

2. Note contents of a selected cell are displayed in the formula bar when the cell is selected.

You can see the contents appear as you type.

3. The Formula Bar displays the formula in cell A3, not the value of the formula.

4. To edit the contents of a cell, do the following.

a) Select the cell by clicking on it.

b) Click anywhere in the Formula Bar.

c) Make changes to the text in the Formula Bar.

d) Press Enter or click anywhere on the spreadsheet to save your changes.

Page 15

Selecting groups of cells

The following are used for selecting a row of cells, a column of cells, or a rectangular block of cells. You will

see why this is useful shortly.

1. Place the cursor at the start of the row or column or at one corner of the block.

2. Then do one or the other of the following.

a) Either drag the mouse to the end of the row or column or to the diagonally opposite corner of the block,

b) or hold down the Shift key while using arrow keys to move the cursor to the end of the row or column or to the diagonally opposite corner of the block.

REFERENCE: Setting the Display Format for a Cell or a Group of Cells

Example of alignment of the contents of a cell

1. Select cell A2.

2. Enter your initials into the cell.

3. Re-select A2.

4. Click the Left, Center, and Right alignment buttons on the tool bar.

Example of setting number formats

1. Right-click cell A3.

2. Select the Format Cells menu item.

3. Select the Number tab.

4. Try the following

a) Number with two decimal places (note the rounding),

b) Percentage with two decimal places,

c) Scientific with two decimal places, and

d) General.

Formatting groups of cells

If you select a group of cells, you can use these methods to format all of them at once.

REFERENCE: Saving and Retrieving Spreadsheets

Saving the spreadsheet for the first time

The following saves the spreadsheet on the hard drive. Use Windows Explorer to copy the spreadsheet from the hard drive to your floppy when you are finished working with it.

1. With the mouse or using keystrokes, select the following menu items.

a) File

b) Save As

2. Select a folder on the hard drive in which to place the spreadsheet. This is best done with the mouse. Ask for assistance if necessary.

3. Enter a file name terminated by Enter.

a) Do not enter a file name extension.

b) Do not put a period ( . ) at the end of the name you type.

c) The extension .xls (for Excel Spreadsheet) will be added automatically.

Drag the mouse means keep the left mouse button pressed while you move the mouse. Use Dragging in moving, copying, and selecting.

Page 16

If you want to save directly to a floppy (not recommended)

1. First, proceed as described immediately above

2. In the file name input box, enter a:, to specify the floppy drive, followed by the file name.

3. Example To save your spreadsheet as test.xls on the floppy disk drive, the input box should contain the following. a:test

To retrieve an existing spreadsheet

Here are two ways to do this. Use whichever you prefer.

1. Method I Use Windows Explorer

a) Run Windows Explorer.

b) Locate the spreadsheet file.

c) Double click the spreadsheet file name. (Alternatively, click the file name once, and press Enter.)

2. Method II Use Excel

a) Run Excel.

b) Use the mouse to access the File Open menu.

To copy a spreadsheet (or any file) from one place to another

1. Run Windows Explorer.

It is available from the Start button under Programs.

2. You can drag the file name from one place to another.

a) If you drag between two different drives, you create a second copy of the file on the destination drive.

b) If you drag between two folders on the same drive, you move the file from its original folder into the destination folder.

3. How to force a copy or a move

a) Hold down the Ctrl key while dragging the mouse to force a copy operation; note the tiny + that appears attached to the cursor.

b) Hold down the Shift key while dragging the mouse to force a move operation; no extra + will be attached to the cursor.

Page 17

REFERENCE: MS Excel Mathematical Functions

The following information is taken from the Excel's on-line help. In the following, X can be a numerical value or a cell address. Examples EXP(X) indicates any of EXP(-3) for evaluating the exponential function of the number -3 (yielding e -3 ), EXP(C8) for evaluating the exponential function at the numerical value of a relative cell location, EXP($C$8) for an absolute (fixed) cell location.

X+Y meands X plusY (addition)

X-Y means X minus Y (subtraction)

X*Y means X times Y (multiplication)

X/Y means X divided by Y (division)

X^Y means X raised to the Y power; Examples 2^3 = 8; 125^(1/3) = 3 125 = 5

X/Y*Z This translates to (x/y) * z which may not be what you intended.

X/(Y*Z) This translates to x / y*z

ABS(X) means |X|, the absolute value of X

ACOS(X) means arccos(X) or cos -1 (X), the arc cosine of X; output is in radians

ASIN(X) means arcsin(X) or sin -1 (X), the arc sine of X; output is in radians

ATAN(X) means arctan(X) or tan -1 (X), the arc tangent of X (2-quadrant), output in radians

ATAN2(X,Y) means arctan(Y/X) or tan -1 (Y/X), the arc tangent of Y/X (4-quadrant), output in radians

COS(X) means cos(X), the cosine of X; X is in radians

DEGREES(X) means Converts X radians to degrees

EXP(X) means e X , e raised to the X power; the exponential function

INT(X) means The integer portion of X

LN(X) means ln(X), the natural logarithm of X; log to the base e

LOG10(X) means log(X), the common logarithm of X; log to the base 10

MOD(X,Y) means X modulo Y: the remainder of X/Y

PI( ) means The value pi (3.14159...)

POWER(X,Y) means X to the Y power; same as X^Y (see above)

RADIANS(X) means Converts X degrees to radians

RAND( ) means A random number in the range 0 £RAND() < 1; updates upon recalculation

ROUND(X,N) means X rounded to the number of digits specified (up to 15)

SIN(X) means sin(X), the sine of X; X is in radians

SQRT(X) means the square root of X

TAN(X) means tan(X), the tangent of X; X is in radians

REFERENCE: MS Excel Aggregation and Counting Functions

The functions described here include two important statistical functions: average and standard deviation. The counting, sum, max, and min functions are also very useful. The following information is taken from the Excel on-line help system, which contains full documentation for all of the mathematical functions. In what follows, "List" can be any block of numbers. These functions act not on single cells but on blocks of cells.

The following is an example of how you specify a rectangular block of cells: A1:C4. This is a block containing 12 cells. A1 is the upper left corner of the block and C4 is the lower right corner of the block. As another example, B5:B25 specifies cells 5 through 25 in column B. To take the average of B5..B25, use AVERAGE(B5:B25).

AVERAGE(List) means The average of the values in non-blank cells in (List)

COUNT(List) means A value equal to the number of non-blank cells in (List)

MAX(List) means The maximum value in the cells in (List)

MIN(List) means The minimum value in the cells in (List)

STDEV(List) means The standard deviation of all non-blank values in (List)

The following relations make clear how STDEV should be used.

SUM(List) means The sum of values in the non-blank cells in (List)