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

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:

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.

** **

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.

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:

- Experiment with highlighting data and labels to see what happens
when you choose different types of graphs. Excel will try to make sense of
your data selection in terms of the graph type, but sometimes the graph
will not be what you expected. Take advantage of the “Press and Hold to
View Sample” option in the first step of the chart wizard.
- The chart options step has lots of graph label customization
options; you can set titles, axis names, and what shows in the legend (or
remove the legend). This is
*not*where you list what the axis labels are. To get data labels (like the animals above) you need to highlight them before graphing or add them in step 2 of the chart wizard. To do this: Click on the “series” tab in step 2, and give the range of cells containing the labels in the “category labels” box. - Once you have completed the chart wizard steps, you still have options
for customizing your graphs.
Double click on something and a panel of options will appear.

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.

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

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:

- Visualize the
**horizontal distance**of the projectile from its initial location at different times. - Visualize the
**height**of the projectile from its initial location at different times. - Visualize the
**horizontal speed**at different times. - Visualize the
**vertical speed**at different times.

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

=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/s^{2}

Mars: g = 3.26667 m/s^{2}

Europa (moon
of Jupiter): g = 1.61 m/s^{2}

^{ }

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