Excel

Security issues.
Browser issues.
Building a library.

Useful Functions

Random Numbers
IF Decisions
Conditional formatting
Counting
Slope & Intercept
Line Fitting

Class Exercises

Beginning Excel 1
Excel Graphs 1
Transforms
Year 7 Algebra 4 Homework
Prime Numbers
Fraction Series
Fibonacci Sequence
Investigating the Excel random number generator.
Limits of sequences.
Using trial and error with equations.

Code Breaking

Simple Shift Part 1.
Simple Shift Part 2.
Viginere.
Block Transposition.
Frequency Analysis.

Advanced Maths

Frequency Distribution
Fourier Analysis
Writing a UDF

Random Numbers.

Open Excel and type =Rand() into a cell. Press enter and see what happens.

You should have a random number between 0 and 1.

Questions.

1. How many decimal places does the number run to?
2. What happens if you press F9? (The F9 function button at the top of the keyboard.)

How to make a random number of any size.

Edit the cell so that it now contains =rand()*20 and press return.

You should have a random number between 0 and 20.

Suppose that you wanted to model a dice throw. You need a random whole number between 1 & 6. To do this try entering =randbetween(1,6) Press enter and see what happens.

This gives random numbers between the first and second numbers inclusive.

IF decisions

You use the IF logic function to see if something has happened.

Suppose you want a cell to be 0 if a different cell is <0.5 or 1 if the cell is >0.5.

Type =rand() into a cell (c3 for example). Select a nearby cell and then click on the fx button on the tool bar. In the function selector box that appears highlight the logical and if options and click okay.

Now type c3>0.5 into the top box. This means if cell C3 is greater than 0.5.

In the next box down type 1. This tells Excel what to do if the condition is true.

In the lowest box type 0. This tells Excel what to do if the condition is false. Click okay and see what happens. Press F9 a few times to check it works properly.

Conditional formatting.

You can use conditional formatting to change the way a cell looks depending on the numbers that it contains.

Conditional formatting is found in the "Format" menu.

The worksheet raddecay.xls uses conditional formatting to model radioactive decay.

In this exercise the nucleus decays if a random number is greater than 0.5. Active nuclei are coloured red and decayed ones are left clear.

Making a counter.

It is often useful to be able to use circular references. This is when a cell refers back to itself in a calculation.

Excel gets upset about this. To prevent this open the tools/options menu and click on the calculation tab. Deselect automatic and select manual, then set iteration to 1; click okay and go back to your spread sheet. The sheet should only do calculations when you press f9. Try it and see.

Type =c3+1 into cell c3. Press f9 a few times. You should have made a counter.

The only problem is that you can't reset it!! You can solve this problem with an IF decision in the cell that depends on a cell that you use as a "flag". Try doing this to your spreadsheet.

If you get stuck then try looking at counter.xls for inspiration.

Slope & Intercept.

There are several ways to do this. The easiest is to put your data into the spreadsheet and then click on the cell in which you want the slope to appear.

On the fx menu select statistical and slope. A box appears. Move the box so that you can see your data. Select the y values by dragging the mouse over them. Do the same for the x values. Click okay and the slope of the data should appear in the cell.

The intercept is found by repeating the procedure using statistical & intercept on the fx menu.

For an example try graph1.xls.

Fitting a Line.

If you want to find the equation of a linear function (a straight line graph) then enter the data into a spreadsheet as normal. Use the chart menu to produce a straight line xy scatter graph.

To find the equation; click on one of the data point indicators on the graph. A series of dialogue screens comes up. Select the type of equation that you would like to fit. "Linear" is the option for a straight line. Then click on the options tab. Select the "equation on graph" option and you can choose to "extend" the line to show where it crosses the axes. It is also worth formatting the line if it appears to be too thick. You can force the line to go through the origin by using the "Intercept=" button.

For an example try graph2.xls.

Frequency Distribution.

The Excel help file (F1 and type in frequency) is very good on this topic. Open up my example freqdis.xls.

Helpful hints. Enter your data in one column and the upper bounds of your sampling intervals into another(my columns B & D). Then select a block of cells (equal to the number of cells that you used for the upper sampling limits). Now click on fx and select "statistical" and "frequency". Drag over the data column to select the right range of values from that. "Bins" is the array of upper sampling limits. Select all but the last one of these (Excel generates the last one automatically). Don't click okay but use Ctrl+Shft+Entr to tell Excel that you have entered an array.

I would try this out on simple data sets at first to ensure that you have everything set right. Check that Excel does what it is supposed to!

Fourier Analysis.

Fourier analysis is found in the data analysis section of the tools menu. See fouran1.xls for an example of how to use it.

The data that you select for analysis must be in a block that contains a power of two's worth of cells; up to a maximum of 4096. Click on an empty cell,near the data, and select fourier analysis from the data analysis section of the tools menu. Enter the correct data set.

The Fourier analysis tool then returns a block of data which contains the complex coefficients of the various frequencies present in the signal. These begin with f_o which is 1/sample size. You need to use IMABS from the engineering section of the fx menu to find the size of these coefficients. Then convert them into a chart.