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.

John Doe $45,000

Jane Doe $55,000

Jon Jungleboy $61,300

Roger Tallman $42,400

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 typed the data you will get something that looks like the screen below:

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 first cell you want to include in
your sum and hold the small square at the Bottom-Right of that 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 used 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 has automatically 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 * B6

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 current salary and that after a 3% raise. Label that as Average salary.

Compute the Min and Max salaries before and after a 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 – 40000)

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

What is the new percenatge of raise?

**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 an example of bar charts.

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 (find the required data on each web site you go to):**

(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

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