Visualization and Computing with Excel

 

Open an Excel Spreadsheet.  Each workbook is divided into cells, which are boxes designed to hold one data entry. 

 

This is cell B14

 

This is cell H8

 

 

Lesson 1 – Working with Data and Formulas

To enter data in each cell click in the cell and type the data in that cell and press enter to store the content of the cell.  You can format and/or change format of each cell using Right-Click on each cell then Format option. 

 

Entering Data and Building Formulas

Let’s enter some data and start manipulating that data in Excel.   The following spreadsheet holds the salaries for 4 employees in a company.  The salary of each employee is stored in column B of the spreadsheet.  For example, in cell B7 you will find the salary of Jane Doe which is $55,000.  We want to increase the salary of these employees by 3%.  To do so we need a formula:

NewSalary = CurrentSalary + 0.03*CurrentSalary

 

To enter a formula you will go to the cell where you want to store the result and will start typing starting with =.  For example to compute the new salary for JohnDoe you will go to cell C6 and will type:

 = B6 + 0.03*B6

 

You can see this in the spreadsheet below.  Note that B6 contains the John Doe’s salary.  Repeat this for the rest of employees.  Remember to press enter after each entry.

 

 

After you type these you get something that looks like this:

 

Let’s compute the total salary for all the employees before and after the raise.  There are many pre-defined functions in Excel that help you compute different things.  To find out what these functions are click in a cell and then under Insert click on Function.  This will give you the list of all built-in functions and their applications.  A function we want to use to compute the total salary is called sum.  To compute the sum we need the range of cells for which we want to compute the sum.  In our spreadsheet, the range of salaries before the raise is B6 through B9, i.e., B6, B7, B8, and B9.  So go to cell A11 and type Sum (this is a label), then go to cell B11 and type:

= sum(B6:B9)

The B6:B9 will give us a range of cells starting from B6 and ending at B9. 

 

 

For the next sum, I want to do it a bit differently.  In cell C11 type:

=sum(

then left click on the cell and hold the small square at the Bottom-Right of cell C6 and drag until you are on cell C9 (note that these cells gets highlighted).  Click on a blank cell once done. The cell gets filled automatically due to fill automatic feature of Excel as:

=sum(C6:C9)

 

Naming Cells

In the above example we use cell B3 to keep the raise percents.  But in our salary calculations we didn’t use it because we used a fix 0.03.  Now we want to use that cell in the calculation of the new salary.  If we use:

C6 = B6 + B3*B6

and then use the automatic copy and fill option to copy the formula to other cells B3 will change as we move to different cells due to relative cell addressing.  Let’s try this so you can see what will happen.  Click in C6 and type:

= B6 + B3*B6

and press enter.  You will get the same value as before.  Now, click on that cell and drag the bottom-right of the cell down on cell C7.    Once done, click in a blank cell, then double click on cell C7, you will see:

= B7 + B4*B7

Did you notice that B3 is not used and Excel automatically has used the next cell which is B4.  To avoid this problem we can fix the cell B3 by placing a $ in front of it in the formula in cell C6.  So let’s try this.  Click on cell C6 and type:

= B6 + $B$3 * 7

Press enter. The result for C6 is the same.  But this time when you drag the bottom-right of the cell on C7, you will see:

= B7 + $B$3*B7

 

 

But there is another way to do this which is very powerful and often more popular.  Using this approach we name the cell that holds the rate. So, we name B3 as raise.  How do we do this?

            Click on cell B3

            Click on Insert at top, Name, then Define

            Type raise in the Names in the workbook, then click on OK

This will name the cell B3 as raise.  From now on we can use raise instead of B3. 

           

Let’s try this (see the screen below):

Now when we drag the bottom-right of the cell C6 down we no longer need to be worried about rate being changed as that will always stay raise.

 

What is interesting with this approach is that now we can change the value in cell B3 where we stored the rate and that affects the calculations automatically without the need of copying the formula again.  Here is an example:

 

Assignment

Compute the average salary for both the current and the salaries after 3% raise.  Label that as Average salary.

 

Compute the Min and Max salaries of the current salaries and after 3% raise.

 

Dynamics in Excel and Goal Seek

In the above example we calculated 3% or 4% raise and the sum of salaries was computed at the bottom.  Suppose we are given $240,000 to spend on new salaries and we want to give a raise such that the sum of salaries after the raise is $240,000.  One way to do this is to play with B3, rate, until the sum of salaries is $240,000.  But this is not really an efficient way.  To do this more efficiently we use Goal Seek.  Here is some basics we need to know to do this:

            What parameter do you want to change to make it happen? In our case that is rate, B3

            What will change as a result of the rate change? In our case sum of salaries, C11

            What target value do we want to reach for sum? In our case, $240,000

 

Ok we are ready.  Click on a cell and then, on Tool at the top, and Goal Seek.  This will gives you a window:

 

 

Now click on Ok.  Also, click on the next screen.  Your calculations are done.  You will get:

 

So you need 18% raise to get the sum of the salaries to $240,000.  What a great raise J. 

 

Assignment

Suppose we want to include a tax penalty rate in the salary calculation.  This rate is 3.2% that will be applied to the amount higher than $40,000.  Thus, in case of John Doe, the new salary is:

NewSalary = CurrentSalary + Rate*CurrentSalary – PenaltyRate*(Current – 45000)

 

Note that the sum of salaries after the raise and penalty has to stay $240,000.  So you need to run Goal Seek.

 

Graphing and Curve Fitting

To graph data in Excel, enter your data one variable per column in consecutive columns. Highlight the data you wish to graph and then choose Chart... from the Insert menu. This brings up the Chart Wizard. There are many graph formats to choose from; once you choose one, the wizard will walk you through customizing your graph. Here is a bar chart of the retail prices from the example above:

 

 

A few items to note while working through the chart wizard:

 

     Most scientific graphs show the relationship between two quantities. In Excel, such a relationship is graphed using a scatter plot. Use a blank sheet by clicking on a Sheet at the bottom.  Type in the independent (x) and dependent variables (y) in adjacent columns; Excel will assume that the left column is intended for the horizontal axis. Graph a scatter plot of these points by highlighting the data and then clicking on the chart wizard Be sure to choose scatter plot and not line graph. Line graphs assume that the horizontal axis should be marked with counting numbers (1, 2, 3, etc.) and that each highlighted column is a separate set of vertical axis coordinates.

            Read the directions in the wizard to customize your graph. After you have done all of the steps, you should see a plot of your data.

 

            

 

Fitting Curves to Data

Often scientists look at scatter plots like the one above to determine the trend of the data by finding a function that fits the data well. Excel has Least Squares Regression routines built in for the most common behaviors: linear, polynomial, exponential, power, logarithmic.

            To fit a curve to the data in the chart above, first graph it as a scatter plot. Now you are ready to add a fit.  Click once on any part of the chart, and then choose Add Trendline from the Chart menu. You will be asked what kind of trend. Also set the options so that the formula for the fit and the r-squared value are printed on the graph if desired.

 

                       

 

 

The curve will appear on the graph along with the data.

 

 

            As always, you should use caution when interpreting the r-squared value. Mathematically this value must increase for “curvier” functions such as polynomials of higher degree. Choosing between different functions should be done visually, not relying solely on the r-squared value.

 

Assignments:

(graphing 1)   Use linear regression to find a formula relating the amount of the speciman to the output from the gas chromatograph in the dataset.

         http://lib.stat.cmu.edu/DASL/Stories/Chromatography.html   

(graphing 2)   The inverse of Hubble’s constant can be used as an estimate of the time since the big bang. Use the data to fit a curve to find Hubble’s constant (note that the relationship has no intercept).

http://lib.stat.cmu.edu/DASL/Stories/Hubble'sConstant.html 

(graphing 3)   Investigate Gompertz’ law for population growth.

         http://lib.stat.cmu.edu/DASL/Stories/Gompertz'sLaw.html    

(graphing 4)   Investigate the relationship between mortality from breast cancer and average annual temperature..

http://lib.stat.cmu.edu/DASL/Stories/Breastcancer.html

 

Research Project - Projectile Motion

The projectile motion is a two-dimensional motion where the projectile moves in x (distance) and in y (height) directions.  In this motion the projectile is usually sent of from its initial location (x0 and y0) with an initial speed (v0) at an initial angle (angle0).

Assuming our initial velocity is v0 = 40 (m/s) and the initial angle is angle = 35 degree.  The initial horizontal and vertical speeds are computed using:

 

InitialHorizontalSpeed =  v0 * cost(angle)

InitialVerticalSpeed = v0 * sin(angle)

 

On Earth gravitational constant g = 9.8 (m/s^2).

 

 We want to create 4 different plots to:

Note that horizontal speed will never change and it is always the same as InitialHorizontalVelocity.  The above values are computed as following (note three are time-dependent):

 

distance =  InitialHorizontalSpeed*cos(angle0)*time
height =  -(1/2)*g*time^2 + InitialVerticalSpeed * time + initialHeight
verticalSpeed = -g*time + IntialVerticalSpeed


Please note that in Excel you have to type angle by pi/180 to convert to Radian. you will type angle*PI()/180. for example:
=v0*COS(angle*PI( )/180)

 

and

horizontalSpeed =  InitialHorizontalSpeed

Once again note that the last one is always constant because it is not time-dependent.

 

We need to compute these values at some time instances.  We will use the total time that the projectile is traveling before it comes to stop and will create equally spaced time instances between 0 to TMAX.  TMAX is computed as:


TMAX = 2*v0*sin(angle0)/g

 

So you need to start the time from 0 and end it at TMAX.  Use an Excel spreadsheet to solve this problem.  Please use cell naming for angle, v0, Initial height, and g.

 

Create three plots.

 

Make observations based on the plots you have created. 

 

What will happen to the graph if we repeat the same experiment on planets:

moon:  g = 1.63333 m/s2

Mars:  g = 3.26667 m/s2

Europa (moon of Jupiter): g = 1.61 m/s2

 

Create all the graphs for these cases and make your observations.