In this Primer you'll find a presentation
of the basic features of Excel 2000 needed to do statistical work.
New features will be introduced with (almost)
every exercise.
The official UNC Excel tutorial may be accessed at the Web page:
http://ed.unc.edu/teach/techcomps/basic/4Comp.htm
At the bottom of the page you will find:
Exercise 2. ** Sorting **
Exercise 3. ** Defining Data Ranges for Functions **
Exercise 4. ** Using the "Series" menu in Graphics **
Exercise 5. ** Absolute and Relative References **
Important Note:
References to Exercise Numbers similar to those in the textbook (like
"2.37")
signify that data or content from
that Exercise is used in the Excel material.
Section 0.
Starting Microsoft Excel
Here are three popular ways to start Excel:
From the Windows 95/98 Desktop, click Start then Programs
then MS Office
and then select Excel.
From the Windows My Computer tool (or Windows Explorer), double click on
any
Excel filename to open that file.
From the MS Office Toolbar, select the Excel icon.
Throughout
the tutorial you will have two windows active:
- a window
displaying the tutorial and
- a window
displaying an Excel workbook.
Elements of the Excel Screen
The Menu
Microsoft has tried
to make the menus uniform in all of their products so it is easier for
users to locate commands.
For example, most of the menu options found in Excel are the
same as those in Word.
Clicking on any menu item with the mouse drops down a list of
available commands.
Some menu items have arrows, indicating there are side menus with
additional options.
Other menu items such as Format Cells bring up tabbed dialog boxes,
allowing greater flexibility
in making multiple changes at once.
The ToolBar in Excel
The standard set
of Excel tools includes two rows of toolbar icons. The top row is the
Standard toolbar and
the second one is the Formatting toolbar. To determine what a tool
does, point to the
base of the tool to see a pop-up note (the ToolTip), which describes its
function.
Additional
Tips on using Microsoft EXCEL 2000 for Stat 31:
+ On your UNC standard laptops and on
many machines, including those in the ATN Labs,
many of the statistical analysis tools we will
use require loading the "Analysis ToolPak".
+ You can see if this available, by
starting EXCEL, then clicking on "Tools" on the toolbar
at the top. If the Analysis ToolPak is
loaded, your screen should look like:
If the last option, called "Data Analysis..." is there, then the Analysis ToolPak is loaded.
+ If the Analysis ToolPak is not loaded, you should be able to load it by going to the "Add-Ins..." Option on the above menu. This will pull up a window that looks something like:
Click in the box for "Analysis ToolPak", so the screen looks like the above, and choose "OK". Then "D ata Analysis..." will show on the "Tools" menu as shown above.
******
Section 1. Getting Started
Find and Open Excel 2000.
Resize and position the window displaying
the tutorial next to the blank Excel workbook.
Excel displays a new workbook when it is
opened. In a new workbook all the cells are empty. A cell is active when
the border is highlighted. When you enter information, the information is
stored in the active cell. Let's learn how to enter information into a workbook.
Entering Text and Numbers.
Click on the Excel window, select a cell by
clicking on it, and enter: Excel is fun.
Observe the following:
Observe that your text is displayed in two areas. Text is displayed in the active cell within the workbook and it is also displayed in the formula bar. The formula bar is activated as soon as you begin typing in a cell. At the far left is the reference section, which will show the reference of the active cell (in this case is cell A1).
To left of the formula bar are the Cancel 
and Enter 
buttons (see the above image). The Cancel and
Enter buttons are only visible while Excel is in edit mode. Excel
is in edit mode anytime you begin typing an entry. To put Excel in edit mode,
click in the formula bar.
Within the Excel window, click in the formula
bar to display the Cancel and Enter buttons.
The Enter button enters the text you typed into the cell. You could also press the Return key on the keyboard.
If you want to edit the text you entered into a cell, you click the formula bar, type your changes and click on the Enter button.
The Cancel button cancels your changes.
Within the Excel window, click in the formula
bar and change the text: "fun", to "outrageous".
Click on the Enter button to enter
the edit.
Or
Click on the Cancel button to cancel
the edit.
Entering numerical values is the same as entering text, except that numerical values are right-justified by default. You will learn how to change this default when you learn other formatting changes.
Entering Formulas
All formulas in Excel must begin with an equal sign (=). When a formula is entered into a cell, the formula itself is displayed in the formula bar when that cell is highlighted, and the result of the formula is displayed in the actual cell. When you are typing in formulas, do not type spaces; Excel will delete them.
Within the Excel window, select cell A2 and
enter the constant value 12. (Remember to click the Enter button or
the Return key when you are done typing).
Within the Excel window, select cell A3 and
enter the constant value 15.
Within the Excel window, select cell A4 and
click on the formula bar.
Within the formula bar, enter an equal sign
followed by A2+A3.
Observe:
Click on the Enter button or press
the Return key to enter the formula.
Your worksheet should look as follows:
Excel displays the result of the formula in cell A4.
Within the Excel window, select cell A3 and
change the number 15 to the number 40, and enter the edit.
The formula value should have changed in
cell A4 to the number 52.
Spreadsheet Navigation
You can quickly
navigate around any spreadsheet with the mouse by using the horizontal
and vertical scrollbars.
Clicking on the up, down, left, or right arrows on the scrollbars
moves you in the selected
direction one row or column at a time. To cover greater
distances, use the
scrollbox to drag to various locations. For example, dragging the
scrollbox to the bottom
of the vertical scrollbar moves you to the bottom of the current
sheet; dragging it
to the top will take you to the top of the sheet.
If you desire you may:
Workbooks and Worksheets
The workbook is
a collection of sheets that enable you to better organize your work. A
workbook can contain
worksheets, charts, macros, or other types of sheets. You can have
up to 255 sheets in
one workbook. The Sheet Tab , which is to the left of the horizontal
scroll bar, shows
you a list of sheets in the current workbook.
The Worksheet
The worksheet
is the area in which you do all of your work on a spreadsheet. It is divided
into a grid of columns
and rows. Each worksheet has 256 columns and 65,536 rows. Each
intersection of a
column and row forms a cell. Information is stored in cells, and each
cell is
identified by its
address: the column letter and row number. Examples of the cell address,
or cell reference,
are A4, B32, and C3.
The reference
section displays the address for the active cell, which is always
surrounded by a
thick, dark border.
The Formula Bar (to the right of the reference section
) is where data
appears as you type.
This is also where you edit numeric data, text, and formulas. As soon
as you start typing,
Cancel, Enter boxes appear to the left of the Formula
Bar .
To create a simple workbook, you need to start with a blank workbook.
Within the Excel window, close and do not
save the Workbook1 document.
Click the "New" icon at the very left
end of the toolbar.
(You may also choose New from the
File menu and then OK the standard Workbook option.)
A new workbook should appear.
Resize and position the new workbook next
to the tutorial.
In this part of the tutorial you will be creating a check register.
Starting in cell A1, build the following table:
To move through your worksheet, you can use the arrow keys.
Remember to click on the Enter button
or press the Return key to enter each check number.
Select cell B1 and type in the text: Data.
Starting in cell C1, build the following table:
Select cell D1, and type the following text:
Payment Amount.
Observe that the text is too large for the
cell. You can change the width of the cell (and as a result the whole column)
to fit the text.
Changing Column Widths.
Position the pointer between the column headings
for column D and column E.
The pointer should change shape to show a
double arrow as you position the pointer between the two column headings.
When the pointer changes shape, you can change
the width of the column by dragging to the right or left.
Press the mouse button and drag to the right
until the width of column D will fix the text.
Starting in cell D3, build the following table:
Starting in cell E1, build the following table:
Make sure you change the column width of column
E.
Starting in cell F1, build the following table:
It is now time to save your worksheet.
Choose Save from the File menu
or click on the Save button and call your worksheet "checks".
You just learned how to save the worksheet. Now
Experiment
with the Print button and with the Print command from the
File menu in order to print your worksheet.
Before you add more to your "checks" worksheet, you will need to learn how to write formulas using arithmetic operators and functions.
Operators are what connects the elements of a formula. Some familiar operators are: addition (+), subtraction (-), multiplication (*), and division (/). There is an order of operations when you are evaluating a formula. Formulas are evaluated from left to right, with expressions enclosed in parentheses evaluated first, then exponents, multiplication, division, addition, and subtraction. Excel has many more operators, but we will work with the operators listed above for now.
Here is an example of how the order of operations
works:
If you have the following formula within a
cell;
=A8/(A9+A4)
The first operation would be the sum of A9 and A4 and then A8 would be divided by that sum.
Describing Formulas
Excel allows you to add comments to explain
the purpose of an inserted formula. The comments are displayed in the
Cell Note window which can be viewed by choosing Note from the
Insert menu. Let's enter a formula using operators and comment the
formula.
Open a new workbook window.
Starting in cell A1 build the following table:
You are going to enter a formula which will calculate the average of these nine grades.
Select cell A11 and type in the text: Total.
It would be best if the total sum of the grades was beneath the last grade. You need to insert a column between column A and column B so you don't have to retype all the numbers.
Insert a Column
Highlight column A by clicking in the column
heading.
Observe:
Choose Columns from the Insert
menu.
Column A should be a blank column now.
Select cell B1 and click in the formula bar.
Highlight the text: Grades and Cut
and Paste the text into cell A1.
Cut and Paste the text: Total
into cell A11.
Now to enter the formula for the total sum of the grades.
Select cell B11 and enter the following formula:
Remember to click on the Enter button
or press the Return key to enter the formula.
Your worksheet should look as follows:
Select cell A12 and enter the text: Average.
Select cell B12 and enter the following formula:
Your worksheet should look as follows:
Functions.
Functions are used to form all or part of a formula. Excel provides two general types of mathematical functions: those that are used in business applications and those that are oriented to higher mathematics. In this tutorial we will focus on the business applications formulas.
The AutoSum button (
) located in the Standard toolbar.
Whenever you click the AutoSum button, Excel inserts a SUM() function in the active cell. Not only will the SUM() function write the sum formula, but it will make a guess at what range of cells you desire to sum, and will leave you in edit mode so that you can correct the sum range.
Select cell C11 and click on the AutoSum
button located in the Standard toolbar.
Your worksheet should look as follows:
Excel has guessed that you want to insert cell B11, which contains the appropriate formula. Let's assume Excel did not guess correctly.
Within the formula bar highlight B11 and delete
it.
The Sum function is waiting for its arguments.
You want to sum up B2 through B10, this is denoted in a more compact form as B2:B10.
Position your cursor in the formula bar,
and type in B2:B10.
Observe:
Click on the Enter button or press
the Return key to enter the formula.
The Sum function is one of the many functions
Excel provides. Excel also provides many statistical functions.
Question . On your "checks" worksheet
compute in the cell D7 the sum of the payment amounts.
Hand
in one page Excel printout of your "checks" worksheet with the total computed
in the cell D7.
To explore the bounty of functions
available in Excel open the "Paste Functions" window by clicking
on the function icon (
).
As you may see in the picture below, when
one function is selected (highlighted) a brief presentation of that function
appears
at the bottom of the window:
The Logical Functions (the category selected
above) will play an important role in building statistical worksheets.
Let's learn about them.
Type in the first row the names of the logical
functions as in the image below. Then highlight the cell A3,
open the Paste
Function window, select the function "TRUE
" and click OK. The logical value "True" is assigned to the cell A3.
Repeat the procedure
to fill the cells A4:A6 as below.
Of course, you have to use the function "FALSE" for the last two
cells!
The logical function "NOT" reverse the logical value of its argument:
Highlight the cell B3, open the Paste
Function window, select the function "NOT" and type the address
A3 for the
argument (see below)
Then click OK. The logical value "False"
is assigned to the cell B3. Repeat the procedure for the cells B4:B11
. Each time use as
argument the address of the corresponding cell
from the column A (see the argument for the cell B6 in the image
below). You should get
the following values:
As you see above the function NOT, as all
the logical functions, interpret "1" as TRUE and "0" as FALSE.
Empty cells are interpreted
as FALSE, also.
The logical functions "OR" and "AND" take one or more arguments.
Fill the cells D3:E6 as in the second
image below. Then highlight the cell F3, open the Paste Function
window, select the
function "OR" and type the address
D3 for the first argument and E3 for the second argument (see
below):
Click OK. The logical value "True"
is assigned to the cell F3. Repeat the procedure for the cells F4:F6
, and then use the function "AND"
for the cells G3:G6. Each time
use as arguments the address of corresponding cells from the columns D
and E (see the argument for the cell G6 in the image below).
You should get the following values:
For each cell be sure that you understand
why you got the respective value. Read the explanations at the bottom
of each window.
To learn about the logical function "IF
" first fill the cell H3:J7 as in the image below. Then
highlight the cell K3, open the Paste Function window, select the
function "IF" and fill arguments as below:
Click OK. As explained in the function
window, because the value in cell H3 is not greater than 3
the value 0 from cell J3 is selected
as output. Repeat the procedure for the
cells K4:K7. Each time use as arguments the address of corresponding
cells from the columns
H, I and J (see the argument
for the cell K7 in the image below). You should get the following values:
Observe that the output becomes 1
(the values from column I) on row 6, when the values in column H are
greater than 3.
**
Congratulations! ** You master the use of the Logical
Functions!
Exercise 1.
On page 5 of the textbook you'll find a table with a small part of a data set from CyberStat Co. records.
Open an Excel 2000 worksheet and input the
data from Example 1.1 page 5.
Question 1). In the cells C6 and F6 compute the average Age and the average Salary of the four employees.
Copy the rows 1:5 to the rows 11:15.
Tomorrow the female employees from CyberStat Co. will
have a salary increase of 10% and the male
employees an increase of 5%.
Question 2). In the cell F16 compute the new average Salary of the four employees.
Print and hand in the one page Excel worksheet.
Exercise 2.
** Sorting **
The worksheet below presents data on
20 7th-grade students in a rural midwestern school.
The researcher was interested in the relationship
between the students' "self-concept" and
their academic performance. The data
we give here include each student's GPA, score
on a standard IQ test, and gender, coded as
1 for female and 2 for male.
Note that the top row are "headers" to help
remember (if you go back to look at this later) what is in each column.
Type the data into your Excel spreadsheet.
You may also load the data from the CD attached
to the textbook, see
Loading data
If you load the data then be sure to keep only
the top 20 rows and Insert the proper headers.
You will use the sort command to analyze this data.
Highlight all data.
From the Toolbar Click on Data (or
do Alt+D), then Click Sort,
and Choose GPA as below:
Click OK.
Observe below that the data is sorted by the GPA column.
*Note*
"Sort"-ing keeps
the data for an
individual student intact:
each entire row (all 5 variables for one student) is moved as a unit.
Sorting by two columns
successively.
Sort by "IQ" and then by "Number".
*Note*
Within a group of students
sharing a common IQ score (for example 114), they are listed in order of increasing
"Number" (5, 16, 17).
Question 1. Use sorting by two columns
to get a worksheet with:
- all
the males listed at the top, followed by all females at the bottom;
- increasing
IQ scores within each gender group.
How
many male students have IQ less than 103?
Write
the answer in cell D22.
Question 2. Write on row 24 the averages of columns B, C and E.
Print the one page Excel worksheet with the sorted data set and the
answers.
Exercise 3 (
courtesy of Professor Steve Marron).
** Defining Data Ranges for Functions **
The worksheet below presents data about the number of accounts of 16 well known US banks.
Type the data into your Excel worksheet.
Note that the columns have been widened,
for easier viewing. This can be done by putting the mouse cursor right
between two columns, holding down the left mouse button, and moving the column
boundary.
Calculating a column total using the Function wizard.
The auto sum function used in Section 3 above
is quite convenient. The Function wizard menu
is the main EXCEL tool for using functions;
it includes the "SUM" function as well as many other
functions.
Highlight cell C17.
Use menus "Insert
" "Function..." which pulls up the
"Paste Function" menu
(or just click on the Function icon).
Next
Highlight choices as shown above (moving
sliders on the right edges as needed), and choose OK.
This pulls up the menu:
Note that Excel automatically chose the range of cells above (it made a good guess about we wanted to sum, based on what was highlighted).
Warning! The following instructions are most important. Practice till you master each of them!
>
Here are some ways to
get the right range in boxes like this (e.g. when Excel doesn't make a good
guess):
i. Sometimes (but far from always) you can highlight the range you want before you pull up the menu, and that becomes Excel's guess.
ii. Just type the indices of the desired range into the
box. You should be very comfortable with this approach, as it is
the most direct and versatile.
iii. Click the little blue, red and white square just at the
right edge of the box. This pulls up a very thin menu, which will show
the range for the area that you highlight with the mouse. Go to the
spreadsheet and highlight the range that you want (Note instead of the usual
thick black boundary for the highlight, you now get a moving striped boundary).
If part of range that you want isn't visible, use the controls bars at the
side and bottom to move the sheet around. When you get the range you want,
click the little colored square at the right edge of the thin menu
>
More than one box is allowed in case you
want to sum over other types of range.
Choose OK, and note that the total appears
at the foot of Column C:
Add a new column D, where the entries in Column C are divided by the total for Column C.
Highlighting the box D2, and
typing in the formula
"=C2/$C$17". Do this EXACTLY, e.g. if you forget the "=
", Excel thinks you
are giving it a string of characters.
The "$C$17", gives an "absolute reference", which will stay unchanged
if you copy the formula
to some other cells.
When you hit "Enter", it looks like this:
Note the entered formula appears in the formula
bar (you can modify the formula at any time if you like), and the number is
the result of dividing the
C2 entry by the C17 entry.
You have computed the proportion
of accounts that belongs to Citibank.
Question 1. Find the proportions of accounts for each bank.
Question 2. In the cell D17 write the proportion of accounts
coming from the traditional banks . Hint:
You may use the function
SUMIF. You may learn more about the syntax of the function by calling
the Excel help (click "Help" on the menu bar, then "Contents and Index"
and
then type in the function
name).
Print and hand in one Excel worksheet with the answers to Question
1 & Question 2.
Exercise 4. ** Using the "Series" menu in Graphics **
In this exercise you will use the data from Ex. 3. to begin exploring the graphical facilities of EXCEL.
First
Sort the data with the traditional banks listed
first, then copy the cells C2:C16 into the cells F1:F15.
Your worksheet should look like below:
Next
Highlight the cells F1:F15. Open
the Chart Wizard by clicking on the icon 
. Choose Chart type "Column" then accept the first
sub-type.
Click "Finish
" and you will get a Bar-Chart like below:
The chart is linked to the selected cells. To observe the link,
Move the data for traditional banks from the
column F to the column G. Your Bar-Chart will display
only the
data for nontraditional banks, which is still
in the column F.
You may chart data from both columns F and G together on the same graph by using the option "Series":
Open again the Chart Wizard with the same choices
as before, but now click "Next" to choose "Series"
at "Step 2".
For "Series 1", choose the values from
the column F ( see note
about choosing the range of cells; try methods
ii and iii):
Then click the "Add" button
and for "Series 2", choose the values from the column G:
**
Warning. ** The string "Sheet1" in the addresses above
indicates the name of the spreadsheet on which the data is
located. If your sheet has another name
you'll have to use that name instead of "Sheet1". The name of the sheet
is
displayed in the left lower corner of the Excel
page:
Note that the sheet address is picked up
automatically when you use method iii) for the range.
Click Finish. You chart should look like:
Notice that the labels l, 2, 3, ..., 8 on
the horizontal axis have no numerical meaning, they just correspond to the
row
of a particular bank in the worksheet.
For example, in series 1 the bar labeled "4" corresponds to AT&T.
Question 1. Make a Bar Chart like the one above
(this is a link to the chart that you should use as
a model for your homework)
with the bars for each of the two kinds of banks arranged in decreasing
size
order.
Question 2. Make a Pie Chart showing the relative proportions
of the accounts that belong to the
traditional
versus nontraditional banks, and write these numerical values in the
cells E1:E2 respectively.
Hint:
the Pie Chart should have two slices. Choose the first sub-type, also.
Hand in one Excel worksheet with the Bar Chart and the Pie
Chart.
Exercise 5.
** Absolute and Relative References **
In Exercise 3 you have used an "absolute
reference" (see)
. Here you will learn about (and practice) "relative" and "absolute"
references.
It is a good habit to open the EXCEL help each time you encounter a new concept.
Fire up the EXCEL Help by clicking on
"Help" ( or press the F1 key), then choose "Contents" and search for
the item(s) you want to learn about. In this case you look for "Creating
formulas", then "Using references", and here is what
you get about the difference between "relative"
and "absolute" references:
Let's practice first with "Relative References ".
Open a new worksheet and type in the following
easy to handle data:
As you see, in the cell D1 the
formula "=SUM(A1:C1)" computes the sum of the numbers from the cells
A1:C1. This formula
uses "relative references" - (NO dollar signs
$ ). If you drag down this formula you will get the sums of the
corresponding rows, as
the row references in the formula change when
you drag down. Of course you will get 8's for the first 6 rows and
then zeros:
As you drag the formula down in column D, it sums the three numbers immediately to the left of the active cell. Look in the formula bar when the active cell is D3: the formula becomes =SUM(A3:C3). Now drag the formula horizontally within the first row: the row reference stays the same, while the column reference shifts. Next drag the formula to cell E3: the formula now becomes =SUM(B3:D3). As you can see, when using Relative References, the whole address in the function is changed relatively to the active cell.
Complete columns E, F and G as in the image
above.
Now let's practice with "Absolute References ".
By applying a dollar sign $ before a reference, you convert it into an Absolute Reference which will remain fixed as the formula is dragged around the worksheet. You can apply the $ to just the column reference ($A1), or to just the row reference (A$1), or to the complete address, row and column ($A$1 ). Each of these has a different operational effect.
You can change the formula in cell D1 by
giving the row(s) or (and) the column(s) absolute references.
For example, you will get the data below by modifying the formula in D1 to
be "=SUM($A$1:C1) ", and then dragging that formula through columns
D,E,F,G:
Now the summing always starts at cell
A1 (Absolute Reference), but finishes at the cell immediately to the
left of the
active cell (Relative Reference). For
example, when we drag the formula to the active cell E3, then the formula
becomes "=SUM($A$1:D3)".
Change the formula in cell D1 to "=SUM($A1:C1
)", and drag it around as before. When E3 is the active
cell, the formula now becomes "=SUM($A3:D3)
". Note how the numerical results of the summations (columns D,E,F,G)
change accordingly.
Question 1. Begin with the same data as above in columns A, B,
C. Figure out what formula in cell
D1 will
generate the worksheet shown below. Hand in an EXCEL worksheet exactly as
shown below,
and with
the formula from cell D1 written into cell A10. Write into cell A11
the formula obtained by
dragging
to active cell E3.
Exercise 6. Interactive
Worksheets.
In
this exercise you will construct your first "Interactive Worksheet", and
develop your
intuition
about straight lines defined by the equation y = a + b*x.
At the end of this exercise your interactive worksheet will be similar to that shown in the image below:
Here is how to proceed.
In column A
you generate a grid of values for the X-axis.
The equation in cell
B2 is just the linear equation (y= a + b*x) written in Excel.
The Y-values are then obtained by dragging this equation down column B.
The intercept a is the input from cell
E3, and the slope b is the input from cell
E4.
Start building your worksheet by writing the
labels exactly as in the image above, and then
generate the data in the columns A and
B, as described above.
Observe how
the absolute and relative references
are used in the equation in cell B2 so you
can drag it down.
Note. From
now on, we will use the color red to highlight
cells containing your "Interactive Inputs
".
"Interactive Inputs" are key numbers used
elsewhere in the worksheet, for example, in tables and charts.
By changing the interactive inputs, you automatically
change the rest of the worksheet (tables and charts),
without needing to re-build the
worksheet. Thus the worksheet can be reused over and over again for
analogous tasks. In the present exercise,
intercept (a) and slope (b) are the "Interactive Inputs".
Before making the chart, experiment with the interactivity of your worksheet.
Change the values in the cells E3 and
E4, and observe how the Y values in column B change!
Try (a =2, b = 20), (a = 0, b =1) and (a =
1, b = -2).
To generate a chart
like the one above, highlight the data in columns A and B, and choose the
Chart
type and sub-type like below, then click "
Finish".
Again test the interactivity of your worksheet.
This time when you input different values in the cells E3 and E4
,
the chart will change, also!
Because EXCEL automatically adjusts the scale
of the Y- axis to the values plotted, you need to fix the scales of
the
axes. With the scales fixed, it is easy
to observe how the line changes when you change the values of a and
b.
Adjust the scales of the axes of the chart
as in the image above
. Highlight the vertical axis in the center
of the chart by clicking on it, then choose
"Format" from the menu bar, and choose "Selected Axis".
Choose the values from the "Format axis" window
as in the image below:
Adjust the X-axis, also, to match
to image above
.
Adjust the color and the width of the plotted
line by highlighting it, and then choosing the same options
as in the image below:
Change the values of the
Interactive Inputs a and b
at least 10 times . Enjoy !
Question. Hand in one Excel worksheet like the one
above
with a = -2 and b = -9.
Exercise 7. Use of two "Series" to visualize Density Curves.
A "density curve" is a curve that:
i) is always on or above the horizontal axis, and
ii) has area exactly 1 underneath it.
(see pp. 66 of the textbook).
The simplest example of such density curve
is the horizontal line at height 1 above the interval [0, 1].
This is the density curve
of the outcome of a random number generator
that produce numbers that are distributed uniformly between 0 and 1.
Such density curve corresponds to the line with parameters a = 1 and b = 0 in Exercise 6.
Use the techniques from Exercise 6 to construct
an worksheet like that below:
Observe that we have chosen a finer grid
for the X-axis. Finer grids produce a superior graphical
output in the charts. Of
course you do not need to type all these numbers. EXCEL is user friendly:
Type the number -0.1 in
the cell A2 and then -0.09 in the cell A3.
Highlight both cells. Then the
screen should look something like:
Turn this into a long column of counting numbers,
-0.1, -.09, -.08, ... , 1.1, by using the mouse
to drag the little square (on the lower right
hand corner of the highlighted box) downwards to the entry A122.
Be sure that you highlight both cells
before dragging down, otherwise you will get only -0.09s!
Also observe that the values in the column
B were chosen such that the chart also
show the vertical sides of the rectangle with
area 1 above the interval [0, 1].
A better emphasis of the area under the curve
could be obtain as follow:
Choose the Chart type "Area" and the first
Chart sub-type. At the "step 2 of 4" of the Chart Wizard
choose the X and Y values for the Series 1
as below, and then remove Series 2.
With the X-values grid chosen as above the
vertical lines still look kind of rough. To improve the appearance
of
the vertical lines you can choose a finer
X-values grid, like -0.1, -0.099, -.098, ..., 1.1. To fill up the 0s
and 1s in
the column B you may use the EXCEL functions
"IF" and "OR".
Review "Logical Functions
" especially the syntax of the functions "IF" and "OR".
After you have understood these functions
type the formula below in the cell B2:
Then drag it down till the row with the value
1.1 in the column A. The number of this row depends on what grid you
have chosen for the X-values ( it will be the
row 122 for the grid -0.1, -.09, ..., 1.1).
To shade in the area
under the curve above corresponding to the segment of the X-axis between
a and b (where 0 < a < b < 1),
we will add a second Series. This second
series should have
i) values 0 for X-values
less than a,
ii) values 1 for X -values betwen
a and b,
iii) values 0 for X-values greater than
b.
Lets make our worksheet interactive.
You will input the values of a and b in the cells F1 and
F2, respectively.
Then in the column C you will generate the
Y-values with the properties i) - iii) using the EXCEL functions "IF" and
"OR".
As for the column B, type the formula below
in the cell C2:
then drag it down as needed.
For a = 0.05 and b = 0.15 you should get in the column C the same values as below:
To generate the chart from the above image
you should add a second Series having column C as the range for the
Y-values:
Test the interactivity of your worksheet by
changing the interval [a, b] several times.
The grid of X-values -0.1, -0.09, ..., 1.1
in the worksheet from the above image let you observe that the formulas from
the cells B2 and C2 generate the right values
in the respective columns. Unpleasantly the vertical lines do not look
good
and we need a finer grid. Changing the
grid is easy if you have the right formulas in the cells B2 and C2.
Type
-0.1 and -0.099 in the cells A1 and A2, respectively and then generate the
grid by dragging down with the mouse on the little square till you get 1.1
(this will happen at the row #1202). Also drag down again the formulas
from the
cells B2 and C2. Then change the ranges
of the X and Y values of the two Series from the chart.
You should get a chart like below:
You need to adjust the X-axis scale to get
labels as above.
Test again the interactivity of your worksheet
by changing the interval [a, b] several times.
Question. Hand in one Excel worksheet like the one
above
with the interval [a, b] = [0.4, 0.8].
