Solving Mathematical Problems Using Excel by Prof Robbins

NAME ______Solving Mathematical problems using excel by Prof Robbins

If you have never this spreadsheet there are many excellent references to the fine points about Excel.

Since we will be concentrating on the use of formulas see For example:

Excel formulas have the ability to use most functions you have seen:

Some of the groups of functions you can call upon are

Math and trig
statistical
Logical and database (like if’s etc …CS students take note!)
Financial
date and time
etc

So spreadsheets have proven to very valuable tools in Engineering, Science, Computers etc.

They are used almost as much as mathematical engines like Mathlab!

This is because once you understand a few basic principles it is very easy to set up a problem and get accurate solutions.

Basics: Open up the Excel program for this discussion

The spread sheet is arranged in columns labeled A,B…etc and Rows 1,2….etc.

Each cell hence has an address..like the first cell is A1 and one can put a number of different entities in a cell. The cells can be specially formatted to handle the following.

Example important to numerical solving problems.

Numbers to 30 decimal places:

Scientific numbers using the letter E for exponent

Other possibilities exit like Currency, fractions, time, date, accounting..etc.

And of course we can put formulas using numerous functions in the cells.

Essentially formulas use other cells for their numerical content.

Example: (try this to get a sense of how some of the above is done).

Put a 7.5 in cell A1 (IE CLICK THE MOUSE ON THE CELL YOU ARE WORKING WITH)

In cell B1 type +A1

What happened..note that here______

NOTE: THERE IS A LINE ABOVE THE SPREAD SHEET THAT SHOWS YOU WHAT

IS IN EACH CELL YOU CLICK ON! Do you see this!

In cell C1 type +A1/2

What happened..note that here______

In cell D1 type +2*A1 +4

What happened..note that here______

Note how we used a + (sometimes we will enter an = for formulas and functions) to trigger an operation

Try in E1 putting A1 (ie no + sign)

What happened..note that here______

Try in F1 to put =A1

What happened..note that here______

Relative addressing:

click on B1 and right click choose copy and then go to B2

choose“paste”. Note what formula shows for this cell in the line above the spreadsheet.

What happened..note that here______

This change is called relative addressing..you moved down one cell so the formula which references A1 now moves down and references A2 which has nothing in it so the answer is 0!

Put a 9 in A2 to see this reference change

What happened..note that here______

When one does not want relative address we can stop movements of the reference by referring to the cell with $ signs to hold the column or row fixed or both

Examples +$A$1 fixing both

Put the latter +$A$1 in G1 and then copy it to G2 to see how we can fix the number for any cell avoiding the automatic relative addressing change in the spreadsheet.

What happened..note that here______

Formula practice

Suppose we wish to impement y=ax+b with a=3 and b=8 constants and x going from 0 to 1 in steps of 0.1..in other words a line.

Clear the spreadsheet..highlight all and press delete key.

Cell A1 will be our “a” so put a 3 in it

Cell B1 will be our “b” so put an 8 in it

Column C will contain all our “x’s)

Put a 0 in C1..type the formula +C1+0.1 in C2

What happened..note that here______

Copy cell C2 and paste it in all cells C3 to C11 either individual paste or hightlight all cells and paste!

Note you should have all values of x we want to consider 0 to 1 in steps of 0.1 in column C

Column D will be all values of y

So in D1 write the formula +$A$1*C1+$B$1 .. we want a and b to be constant and C1 (ie x) to be a variable. Then copy this formula into D2 to D11

IF YOU MAKE A MISTAKE YOU CAN CORRECT THE CELL INFORMATION BY EITHER DOUBLE CLICKING THE CELL OR MAKE YOUR CHANGES ON THE LINE ABOVE THE SPREADSHEET AFTER YOU CLICK THE CELL

What happened..note that here______

NOTE HOW EASY ONE CAN GENERATE ANOTHER LINE BY

Put a 4 in A1 and hit enter or click on a blank cell

What happened..note that here______

Graphing data for 2007 VERSION ONLY for 2003 see below

There are numerous ways to graph our data..in this case we will attempt to graph the line you just

Generated

highlight all values in D
go to the “insert” column and pick chart
select “line”
pick first line graph 2D version
You should see the y values on a graph with the x values only being the point number
To have our true x values portrayed on the x axis do the following
Click on the numbers 1 to 11 on the x axis this highlight them
Right click and select “select Data”
A box of select data source pops up
The box is subdivided, click on edit in the HORIZONTAL CATERGORY
Only ie the x axis
Select the c column values
Click ok on the box that appears
Again click ok on the large Select data source box

Looking over your graph now describe what happened?\

Can you move the embedded graph around the spreadsheet?______

There are numerous other embellishments to be put on a graph

PRINT THE SPREADSHEET (DO A PREVIEW FIRST) AND HAND IN WITH THIS EXERCISE!

(Graphing data for 2003 VERSION ONLY

There are numerous ways to graph our data..in this case we will attempt to graph the line you just

Generated.

go to the “insert” column and pick chart
select “line”
pick “next”
select “column”

5 click the spreadsheet symbol and then highlight the column D1 to D11 then hit enter

You should see the y values on a graph with the x values only being the point number

To have our x values portrayed

Click the series tab at the top and note at the bottom “Category x axis labels”

Click the spreadsheet symbol on this area and highlight all the C column values and pick enter. Then click finish!

Note the graph!

What happened..note that here______

Can you move the embedded graph around the spreadsheet?______

There are numerous other embellishments to be put on a graph

PRINT THE SPREADSHEET (DO A PREVIEW FIRST) AND HAND IN WITH THIS EXERCISE! End of 2003 version only)

Solving problems

In general it is best to organize your approach. I recommend the following steps not necessarily in this order.

what are the constants and known variables
what are you looking for; Unknowns?
What laws or formulas must you use.
Solve(set It up) the formula(s) for the unknown in terms of what is known
plug in the numbers in the formula ..calculate

Example: What is the frequency of the red color 600nm?

Assume Formula c is what you know

then 1. constant c=speed of light=3.0 x 108 m/s and we know the wavelength

nm=600x 10-9 m

2.we are looking for the frequency 

3.Formula solution

c3.0 x 108 m/s / 600x 10-9 m = 5.0 x 1014 /s or 5.0 x 1014 cps

Note in a paper solution we carry the units to be sure our final unit is correct!

Spread sheet solution. Generalized to many wavelengths.

Set up a new spread sheet (select the Symbol for New blank document is a blank page)

Click on A1 right click Format cells and note the tabs and pick the Number tab and the many choices some of them were mentioned above.

Select the scientific choice with 2 decimal places and then click OK!

TYPE 3.00E8 then enter in cell A1

What happened..note that here______

This is our speed of light constant for the problem

Format B1 and C1 like you did A1

In B1 enter the wavelength as 600E-9

What happened..note that here______

In C1 we enter an equivalent to the formula c

Or we want c1 to have the constant in A1 or $A$1 divided by the value in B1 which we can change so enter the formula +$A$1/B1 in C1

Did you get the same answer as above.

What happened..note that here______

Since we are referencing column B for our wavelengths we can use the relative addressing to do more of the same so format cells B2 to B5 scientifically and enter the wavelengths, 400nm,550nm,678nm and 10.17nm. as you did for the 600nm

Copy the formula in C1 to C2 to C5.

What happened..note that here______

Print the spread sheet

TASKS: Do these on one spread sheet and PRINT!

SET UP FORMULA 2.2(YOUR TEXT) ON THE SPREAD SHEET AND SOLVE FOR THE TWO SOLUTION GIVEN AFTER FORMULA 2.3

NAMELY 2.2 b1/b2 =10(m2-m1) m1 = 1 m2 =6 and the other m1 = 6 m2 =26

TO DO THIS USE INSERT THE “POWER” FUNCTION IN A CELL TO GET 10 TO A POWER (m2-m1) . WHEN YOU PICK A FUNCTION EXCELL GIVES YOU DETAILS ON HOW TO USE IT..

Did you get the correct answers? (102 and 108). No ? see me

2 SET UP FORMULA 2.3 m2 – m1 =2.5 log10(b1/b2) AND SOLVE FOR m =m2 – m1 for a factor of 106 in brightness.

TO DO THIS INSERT FUNCTION “LOG10”

Did you get the correct answers? (15 mag). No ? see me

DO PROBLEMS 2.1, 2.2 AND 2.3 ON THE SPREADSHEET!

PRINT ALL