EXCEL TIPS:

1) LINEST: to obtain the best linear fit to your y vs x data: y = m x + b, you must first select 4 blank cells where Excel will place the results as an array

 

Slope m

Intercept b

Uncertainty of m

Uncertainty of b

 

Once you filled LINEST (y values,x values ,true,true) , noticing that the x and y labels are not to be included in the values list, you MUST place the mouse in the formula bar , and then:

 

1a) If you are using a Mac, press simultaneously Command and Enter (if you don't, you will not get values for the uncertainties, just m and b)

 

1b) If you are using a Windows PC, press simultaneously Control and Shift and Enter (if you don't, you will not get values for the uncertainties, just m and b)

 

For example:

x

y

1

5

2

8

3

11

4

14

5

17

6

20

7

23

3

2

0

0

 

2) If you use INSERT TRENDLINE on a linear plot, you will only get the equation of the line (m and b values), you will NOT get the uncertainties, so it will not be of any use for error analysis. If you want to display it, you need to check the option "Display equation on chart".

 

3) When using trig functions, you must remember that Excel has a default setting of measuring angles in RADIANS. So if you type in a cell (REMINDER: YOU MUST TYPE A "=" SIGN TO TELL EXCEL YOU WANT TO START A FORMULA CALCULATION, IF NOT EXCEL THINKS IS JUST TEXT):

 

=SIN(30)

 

you will not get the sin (30°) = 0.5 , but sin (30 radians)= -0.98803.

To "fix" this common problem, you can use the Function:

 

=RADIANS(30)

 

that converts 30° to its equivalent in radians (0.523599) before calculating its sine. In a single step you can calculate the correct value for sin (30°) by typing:

 

=SIN(RADIANS(30))

 

4) Conversely, if you are looking for the arc sin (0.5) (the angle for which its sin equals 0.5, that is 30°) you must convert to degrees (using the DEGREES Function) the answer in radians (if you want the result in degrees, there is nothing wrong on giving the answer in radians!):

 

=DEGREES(ASIN(0.5))

 

to get 30°. Try typing

 

=ASIN(0.5) instead to get the answer 0.523599 radians.

 

5) NAMED CELLS: A very useful feature is 5the ability to give a name to a cell or group of cells. For instance, if you want to do a calculation using a time step dt (lets say dt=0.01 s), you can type in let's say cell B1: 0.01 and then name it "dt" by typing dt in the name box (area on left of formula line). Then you can use "natural language" to refer to the cell value when you type your equations: C3=C2 + D2*dt, where C2 is previous position, C3 the next position, and D2 the previous velocity. Full columns can also be named, so that the x-values column are named, for instance, x. By default named cells are Absolute References (that is, they act as a fixed location in the spreadsheet, regardless where another cell is.

6) The IF Function is very useful to test for a condition. Its syntax is:

 

=IF(condition, value if true, value if false)

 

where condition is a comparison of two expressions (for instance: x<0).

Example 1) If you want to model air drag, the resistive force (of magnitude FDRAG=c v2) is opposite to the direction of motion. So if I throw a rock vertically up, drag is downward while rising, but upward while returning to the ground. One way to "flip" the direction of the force is to test which way it is going (let's assume up is taken positive):

=IF(v>0,-1,+1)

in a "sign" column, and multiply c v2 times this sign value (+ or -) to get the right drag force.

Example 2) If you would like to numerically calculate successive bounces of a ball (coeffcient of restitution e = speed just after/speed just before) , you could test the condition of having hit the ground (assumed at x=0) using:

 

=IF(x<0,-e,1)

 

and multiply this cell value times the velocity to flip up the direction of motion and reduce its value by the coefficient of restitution e (ratio of speed after to speed before the collision with the floor). Of course the x calculation update must be done in small time steps to avoid going too deeply into the ground before reversing!). For example for a 1 m drop with e =0.9:

dt=

0.0100

t

x

v

restitution

e=

0.9

0

1

0

1

a=

9.8

0.01

1

-0.098

1

 

 

0.02

0.99902

-0.196

1

 

 

0.03

0.99706

-0.294

1

 

 

0.04

0.99412

-0.392

1

 

 

0.05

0.9902

-0.49

1

 

 

0.06

0.985

-0.588

1

 

 

0.07

0.97942

-0.686

1

 

 

0.08

0.97256

-0.784

1

 

By typing this (NOTE: I used named cells for dt, g, e, and named column x)

dt=

0.01

t

x

v

restitution

e=

0.9

0

1

0

=IF(x<0,-e,1)

a=

9.8

=C2+dt

=D2+E2*dt

=F3*(E2-g*dt)

=IF(x<0,-e,1)

 

 

=C3+dt

=D3+E3*dt

=F4*(E3-g*dt)

=IF(x<0,-e,1)

 

 

=C4+dt

=D4+E4*dt

=F5*(E4-g*dt)

=IF(x<0,-e,1)

 

 

=C5+dt

=D5+E5*dt

=F6*(E5-g*dt)

=IF(x<0,-e,1)

 

 

=C6+dt

=D6+E6*dt

=F7*(E6-g*dt)

=IF(x<0,-e,1)

 

 

=C7+dt

=D7+E7*dt

=F8*(E7-g*dt)

=IF(x<0,-e,1)

 

7) Creating USER DEFINED FUNCTIONS: Excel has a powerful programming environment (VBA= Visual Basic for Applications) that allows you to create your own functions, subroutines, macros, etc.

 

Let's create a function CELSIUS that converts from degrees FAHRENHEIT to CELSIUS. The conversion formula is C = 5/9 (F-32).

 

a) In a new worksheet. Use Tools/Macro/Create to start the programming of the function (give the "macro" any name, like Ben). You will see a "Module" to open with the following text:

 

Sub Ben1()

End Sub

 

Notice that it opens by default as a "Sub" (=Subroutine). You should type the following:

 

Sub Ben()

'Converts degrees F to C

Function CELSIUS (F)

CELSIUS = (5 / 9) * (F - 32)

End Function

 

Notice that as soon as you define the Function Excel changes it from End Sub to End Function. The first line you typed:  'Converts degrees F to C is preceded by an apostrophe to indicate is a comment (or reminder) which does not execute. Use Return to finish each line or statement. The statement Function CELSIUS(F) does two things: names the function CELSIUS and says that it requires one argument (the temperature in Fahrenheits F). Notice also that you need to define how CELSIUS is calculated so that the Function can output an answer. Sadly, Excel will not warn you if you did not, and will just output a zero answer!

Now that you created the Function CELSIUS, you can find it in fx (Insert function key) as User Defined Function in the worksheet. Test that it works, for instance =CELSIUS(100) gives 37.77.

Warning: Be careful about naming variables, some are reserved by Excel for their own calculations. For instance, if you try to use the shorthand name C instead of CELSIUS it will not work!

 

8) FOR NEXT LOOPS

 

The syntax (where [ ] indicates optional, and Bold a reserved keyword

 

For counter=first To last [Step step]

[statements]

[Exit For]

[statements]

Next [counter]

 

For example, to calculate the sin(x) using its Maclaurin expansion:

 

Sin(x) = x -x3/3! + x5/5! -x7/7! + ...+ (-1)n x2n+1/(2n+1)! +

 

You can write the following Module:

 

Function MacSin(x, n)

MacSin = 0

For k = 0 To n

j = 2 * k + 1

MacSin = MacSin + ((-1) ^ k) * (x ^ j) / Application.Fact(j)

Next k

End Function

 

9) DO LOOPS

 

Example: to calculate ex (=1 +x + x2/2 + + xn/n! +) you can write the following Module:

 

Option Explicit

Function MacExp(x)

Dim term As Double, k As Integer

MacExp=0

term=1

k=0

Do While term>0.00000001

            MacExp=MacExp+term

            k=k+1

            If k>1000000 Then Exit Do

            Term=term*x/k

            Loop

End Function

 

10) MATRIX ALGEBRA

 

Example 1) To solve the 3x3 system of equations:

 

2x +  3y  -  2z = 15

3x -  2y  +  2z = -2

4x -  y  +  3z = 2

 

M

C

2

3

-2

15

3

-2

2

-2

4

-1

3

2

M^-1

M^-1*C

0.190476

0.333333

-0.09524

2

=x

0.047619

-0.66667

0.47619

3

=y

-0.2381

-0.66667

0.619048

-1

=z

 

Where M^-1 is calculated by first selecting the nine target cells, then typing:

 

=MINVERSE(A2:C4),  and Command+Enter (or Control and Shift and Enter in a PC)

and M^-1 * C is calculated as (matrix multiplication ) :

 

=MMULT(A7:C9,D2:D4),  and Command+Enter (or Control and Shift and Enter in a PC)

 

Of course, once this template is built, any 3x3 system can be solved by typing in the 12 coefficients.

 

11) SLIDERS

 

You can have a nice control of the value of a cell by associating the cell to a slider control. Ther easiest way to do it is to go to View/Toolbars/Forms, and select a Scroll Bar control, drag the mouse to give it an appropriate length, then Right-Click on the Bar (in PC's) and select Format Control. Choose Start and Stop values, increment size (unfortunately only integers are allowed), and Cell address (for instance $B$1). Test that if you move the Scroll Bar the cell value sweeps over the allowed range. If your range must be 0-1 (instead say 0-100), you will need to create an auxiliary cell, and then define its value as = $B$1/100. With slider control you can interact with the spreadsheet very nicely, and if you link the control to a time variable, can display temporal evolution, wave propagation, etc. Notice also that the control is "bi-directional", if you give the cell a value the slider moves in the Scroll Bar.

 

12) Writing VISUAL BASIC code: The easiest way to learn how to program in Visual Basic (VB) is to use the "Record Macro" feature (found in Tools/Macro/Record New Macro...) , accept default name (Macro1), and do a few actions in the spreadsheet, for instance click in cell A1 and tipe "dt", then click on cell B1 and type "= 0.1", then click stop recording button, and the go to Tools/Macro/Edit to read the code Macro1 created:

 

Sub Macro1()

'

' Macro1 Macro

'

Range("A1").Select

ActiveCell.FormulaR1C1 = "dt"

Range("B1").Select

ActiveCell.FormulaR1C1 = "=0.1"

Range("B2").Select

End Sub

Notice the syntax to select a cell: Range("A1").Select

and to assign a value to the cell: ActiveCell.FormulaR1C1 =

To give a more interesting example of VB programming, this is the code to generate and plot the Mandelbrot Set using Excel:

 

Sub Macro(1)

ActiveCell.Cells.Select

Selection.NumberFormat = "General"

For i = 1 To 200

For j = 1 To 200

Let xold = 0

Let yold = 0

For n = 1 To 49

Let xnew = xold ^ 2 - yold ^ 2 + i / 50 - 2

Let ynew = 2 * xold * yold + j / 50 - 2

Let RSq = xnew ^ 2 + ynew ^ 2

If RSq > 4 Then Exit For

Let xold = xnew

Let yold = ynew

Next n

Cells(i, j).Value = n

Next j

Next i

End Sub