<meta http-equiv="refresh" content="1; url=/nojavascript/"> Spreadsheet Skills for Computer Simulation | CK-12 Foundation
Dismiss
Skip Navigation
You are reading an older version of this FlexBook® textbook: CK-12 Modeling and Simulation for High School Teachers: Principles, Problems, and Lesson Plans Go to the latest version.

Lesson Objectives

  • Define the function of the Excel spreadsheet formulas MIN, MAX, RAND, IF, and VLOOKUP
  • Identify core concepts and techniques used in Excel spreadsheets, including:
    • cells and ranges of cells
    • naming worksheets
    • typing formulas into spreadsheet cells
    • cell references and absolute addresses
    • naming spreadsheet ranges
    • recalculation
    • copying and pasting cell contents
    • creating graphs
  • Apply formulas, concepts, and techniques in Excel spreadsheets.

Most of these skills and techniques are also illustrated by video tutorials that can be found at this web site: http://mason.wm.edu/faculty/bradley_j/EducationalMaterials/

Vocabulary

cell
A cell is a subdivision of a worksheet that can contain text, a value, or a formula that may rely on values of other cells.
Microsoft Excel
Microsoft Excel is a software product that is used mainly for numerical calculations. The work area in this software program is divided into cells. An Excel workbook can contain multiple worksheets, each which has many cells.
recalculation
Recalculating an Excel workbook means that all the formulas in all the worksheets of the workbook are recalculated, and a new value is shown for each cell based on the values of other cells. Recalculation is automatic by default in Excel, which means that the values of all cell formulas are recomputed whenever the value of any cell changes. Recalculation can also be done in manual mode, in which case the spreadsheet user is in control of recalculation and he or she can cause the workbook to recalculate by striking the F9 key.
spreadsheet
A spreadsheet is a Microsoft Excel computer file. An analogous term is Excel workbook, which may contain multiple worksheets.
worksheet
A worksheet is a portion of an Excel workbook that contains a two-dimensional grid of cells. A workbook may contain multiple worksheets.
workbook
A workbook is an Excel computer file.

Check Your Understanding

This lesson describes basic spreadsheet techniques, and so no prior experience with Excel is required, except knowing how to start the Excel program. Nonetheless, the following Excel skills are helpful if a student knows them before undertaking this lesson:

  • Typing numeric quantities into spreadsheet cells.
  • Typing text into spreadsheet cells.
  • Typing formulas into spreadsheet cells that involve addition, subtraction, multiplication, and division.
  • Referencing a numeric value in one spreadsheet cell in the formula entered into another cell.
  • Using some of the spreadsheet formulas that are included in Excel, such as SUM.

It is necessary that a student understands how graphs of numerical data are constructed, although this will be explained to some degree in this lesson. In particular, it is helpful if students understand what the x axis is, what the y axis is, how the height of a line on a line graph is interpreted, and how to find both the x-coordinate and y-coordinate of a particular point on a line graph.

Introduction

This lesson introduces spreadsheet techniques that have been selected to enable the student to create a spreadsheet simulation model. These are also among the most frequently used Excel skills, which are fundamental to many types of spreadsheet analysis. This lesson, therefore, is useful in many courses, even one in which the goal is not to create a simulation model in Excel. The text in this lesson can be supplemented with online tutorial videos, which are referenced throughout this lesson.

Lesson Content

This lesson on Spreadsheet Skills for Computer Simulation is divided into the following subsections:

A. Cells and Ranges of Cells

B. Naming Spreadsheet Ranges

C. Naming Excel Worksheets

D. Typing Formulas into Spreadsheet Cells

E. Spreadsheet Recalculation

F. Copying and Pasting Cell Contents

G. The MIN Function

H. The MAX Function

I. The IF Function

J. The VLOOKUP Function

K. The RAND Function

L. Creating Graphs

Any student who is familiar with any of these topics can skip the corresponding sections and proceed with the sections on the material with which they are not familiar.

Cells and Ranges of Cells

A video tutorial that demonstrates the topic of this subsection is available by either clicking on the appropriate link on this web site http://mason.wm.edu/faculty/bradley_j/EducationalMaterials/ or by going directly to the content:

media.wm.edu/content/business/bradley/NASAFlexBook/VideoTutorials/CellsAndRangesOfCells.asf

Spreadsheet cells are areas in a spreadsheet sheet where we can type numerical data, text, or a formula. Each cell is located in a column and a row. Columns are referenced by letters such as A, B, C, and so forth. Rows are referenced by numbers such as 1, 2, 3, and so forth. Because every cell is associated with one column and one row, we can specify a cell’s location by giving its column letter and row number. (Merged cells are an exception to this, but they are beyond the scope of this chapter.) Specifying the column letter and row number together is like giving somebody the address to your house. We give the address of a cell by putting the column letter and the row number together. For example, the cell at address A14 is in column A and in row 14.

Entering numerical data, text, or a formula into a cell can be accomplished with the following steps:

1. Select the cell by locating the cursor over the cell and clicking the left mouse button.

2. Type the numerical data, text, or formula.

3. Hit the "Enter" key.

Often, we need to work with multiple cells at one time. For example, in the video tutorial above, data about the Smith family is contained in multiple cells. Multiple cells are required because each cell can contain only one piece of data, and there are seven members of the Smith family, for each of whom we have recorded two items of data, age and name. A picture of this data from the tutorial can be found in Figure below. Because there are seven members in the Smith family, we need seven rows to record the data for all the family members. Because we are interested in recording ages and names, two columns of data are required. In all then, 14 cells are used for the Smith family data (2 × 7 = 14). The data are contained in columns A and B, and in rows 14 through 20. The cell in the upper left position in that range is cell A14, and the cell in the lower right position in that range is B20. When we refer to all these cells together, for example when we refer to this range of cells in formulas in other cells, we do so by referencing the upper left cell and the lower right cell addresses, separated by a colon — for example, in this case, A14:B20.

A cell can be selected so that the data in it can be changed by placing the cursor over the target cell address and clicking on the left mouse button. An entire range of data can be selected by placing the cursor over one corner of the range, such as cell A14, clicking and holding the left mouse button, and then, without letting up on the left mouse button, moving the cursor to the opposite corner of the range, in this case cell B20. Then, the left mouse can be released and the entire range will be selected. The entire range will be colored gray to indicate that it has been selected.

Data About the Smith Family

Naming Spreadsheet Ranges

A video tutorial that demonstrates the topic of this subsection is available by either clicking on the appropriate link on this web site http://mason.wm.edu/faculty/bradley_j/EducationalMaterials/ or by going directly to the content:

media.wm.edu/content/business/bradley/NASAFlexBook/VideoTutorials/NamingRanges.asf

Sometimes it is better to refer to a range of data by a name that describes it rather than by the typical notation such as A14:B20, where the upper left cell in the range is mentioned first, the lower right cell in the range is mentioned last, and the two cell addresses are separated by a colon. There can be many advantages to using range names, one of which is that it is apparent from reading the name what the data is without having to spend the time to navigate to the range of cells to investigate. If somebody is trying to understand a formula that has many references to cell addresses like B5 and range addresses like A14:B20, it can take a lot of time to navigate to each of those addresses and determine what the data is. By the time a person finishes that task, he may not remember what data are in many of the ranges. If we give cells and ranges of cells descriptive names, then it helps us understand formulas more easily. For example, the data that describes the Smith family in Figure above could be called "SmithFamilyData" or "SmithFamilyNamesAndAges." We would then know from the name what data was in the range without having to look at the data in the range to see what it represented.

There are many ways to name a range, one of which is described here. First, select the range that you wish to name. If you do not know how to do this, then see the previous subsection and the video tutorial associated with it, which describe how to select, or highlight, a range of cells. Figure below shows the data about the Smith family after it has been selected, which caused the range of cells to be shaded gray. To give that range of cells a name, move the cursor with the mouse toward the upper left portion of the Excel window to the area called the "Name Box" in Figure below. This title will appear when the mouse is moved over the "Name Box" area. In Figure below, the "Name Box" is currently showing the address of the upper left cell of the selected range, which in this case is cell A14. To name the range, click the left mouse button once while the cursor is over the "Name Box." The range address A14 will then be highlighted, as shown in Figure below. Now type "SmithFamilyData" and hit the "Enter" key. The range is now named and the "Name Box" will show the name of the range, "SmithFamilyData," when the range is highlighted, as shown in Figure below, rather than the address of the upper left cell.

Naming a Range of Cells

The Name Box

Name Box Showing New Range Name

Naming Excel Worksheets

A video tutorial that demonstrates the topic of this subsection is available by either clicking on the appropriate link on this web site http://mason.wm.edu/faculty/bradley_j/EducationalMaterials/ or by going directly to the content:

media.wm.edu/content/business/bradley/NASAFlexBook/VideoTutorials/NamingWorksheets.asf

When Excel is started, a new Excel file is created that looks like the one shown in Figure below. This entire computer file is sometimes called a spreadsheet. A more formal name that is used in Excel to describe the computer file is the term workbook. Tabs can be found at the bottom of the screen, each of which contains the names of different parts of the workbook, such as "Sheet1," "Sheet2," and "Sheet3." If your version of Excel has other settings, then there may be more or fewer than three tabs. Clicking on these tabs exposes three totally different grids of cells in which data and formulas can be typed. The data pertaining to any one of these tabs is called, formally, a worksheet. A worksheet is, therefore, a two-dimensional grid of spreadsheet cells. Notice with this terminology that each workbook contains (or can contain) multiple worksheets.

When a lot of data and many calculations are needed in a workbook, it is good practice to use multiple worksheets to organize the workbook. For example, putting data about customers in one worksheet and data about a manager’s business operation in another worksheet, and giving each a descriptive name, allows each of those sets of data to be easily found. Also, calculations and the data from which the calculations are made often require different worksheet formats; for example, different column widths. Trying to combine data and calculations when different formatting is required results in a messy and confusing worksheet. Keeping different types of data and calculations on different worksheets resolves this issue.

Naming a worksheet is relative easy. First, use the mouse to locate the cursor over the tab of the worksheet that you want to rename. Then, double click the tab with the left mouse button. The name of the worksheet on the tab will then be highlighted as shown in Figure below. Then, type the new name of the worksheet and hit the "Enter" key. The worksheet now has the desired name.

New Excel File

Highlighted Worksheet Tab Read for Renaming

Typing Formulas into Spreadsheet Cells

A video tutorial that demonstrates the topic of this subsection is available by either clicking on the appropriate link on this web site http://mason.wm.edu/faculty/bradley_j/EducationalMaterials/ or by going directly to the content:

media.wm.edu/content/business/bradley/NASAFlexBook/VideoTutorials/SpreadsheetFormulas.asf

Spreadsheet cells can contain numbers, text, or formulas.

To enter a number into a spreadsheet cell, place the cursor over the cell into which you want to add the data and then click the left mouse button. The cell is now selected. Then type the number. Finally, hit the "Enter" key. The numerical value in this cell can now be referenced by formulas in other cells. A specific example of entering numerical data is described in the next paragraph and in the accompanying video tutorial.

We demonstrate how to enter numbers into cells and how to enter a spreadsheet formula into a cell using the example below, in which we calculate the change that a customer would receive if he or she purchased a soft drink at a convenience store. Let’s say that the price for the soft drink is $1.79 and that the customer pays for the drink with a $5 bill. Then, the change the customer should receive back from the cashier is calculated using subtraction:

$5.00 - $1.79 = $3.21.

To demonstrate how this calculation could be done in a spreadsheet, let us first enter the $5.00 into a spreadsheet cell that represents the money received by the cashier. To do this, select a cell as described above using the left mouse button. In our case, position the cursor over cell A1 with the mouse and click the left mouse button. This selects cell A1. Then type the numeral 5 and hit the "Enter" key. The value of cell A1 is now 5. To do the calculation for the change, select cell A2, using the method described previously by positioning the mouse over A2 and left-clicking with the mouse. Cell A2 is now selected. To enter the formula in that cell first note that the $5.00 given to the cashier by the customer is data that already exists in another cell, so we must find a way to incorporate that data into the calculation. We do that by first typing an equal sign, =, which tells Excel that we will be typing a formula. Then we need to tell Excel what numbers we want to include in the formula and which mathematical operations we want to perform on them. The first number that we want to include in the formula is the 5 from cell A1. We do that by positioning the mouse over cell A1 and clicking the left mouse button. That causes the characters "A1" to appear after the equal sign in the formula in cell A2, so that our formula now looks like =A1. We need to subtract $1.79 from the $5.00. To do that we type a dash, -, to tell Excel to subtract the next number from $5.00. Then we type 1.79 followed by hitting the "Enter" key. The value now displayed in cell A2 is 3.21, which is the correct change. If we again select cell A2 (the default is for the selected cell to move down one cell when we strike the "Enter" key), we see that the formula in that cell is =A1-1.79. Figure below shows this and the "Formula Bar" region of the worksheet, where the formula can be viewed. Excel computes this formula by first finding the value being displayed in cell A1 and then subtracting 1.79 from it. This example shows that we can type numerical quantities directly into cell formulas, or we can use values from other cells in formulas. The cells referenced in a formula can contain values, such as was the case with the 5 in the example above, or the referenced cells can have formulas that determine their value.

Formula Bar

This example shows how to create a formula in Excel with subtraction. The symbols used for the other basic mathematical operations are as follows (Table below):

Basic Mathematical Operations in Excel
Mathematical Operation Symbol Used Type These Keys
Addition + Shift and =
Subtraction - -
Multiplication * Shift and 8
Division / /

When Excel evaluates a function, it uses the standard order of mathematical operations. What that means is that Excel first goes through the equation and performs the multiplication and division operations on the values adjacent to those operators in the order that the multiplication and division signs appear, going from left to right in the formula. Subsequently, Excel makes a second pass through the equation, performing the addition and subtraction operations. If this is not the correct order of operations for a particular formula, then parentheses need to be used to cause a part of the formula to be calculated first. If any parentheses are present, then calculations on the values inside the parentheses are done before making the two passes through the formula as described above. Within parentheses, multiplication and division are done first, followed by addition and subtraction.

Spreadsheet Recalculation

A video tutorial that demonstrates the topic of this subsection is available by either clicking on the appropriate link on this web site http://mason.wm.edu/faculty/bradley_j/EducationalMaterials/ or by going directly to the content:

media.wm.edu/content/business/bradley/NASAFlexBook/VideoTutorials/Recalculation.asf

We will discuss workbook recalculation using the example that was created in the previous section, where we entered a value in cell A1 representing the amount of money paid for a soft drink and determined the change due a customer in cell A2 by subtracting the cost of a soft drink from the value in cell A1. To accomplish the desired goal in that example, we could have just as well typed the equation =5-1.79 into cell A2. Typing the formula as we did in the prior example offers an advantage that, if the customer gave the cashier something other than a $5 bill, then we can easily calculate the change in cell A2 by simply changing the value in cell A1 by entering a 10, 20, 50, or 100 to represent the denominations of other types of bills. For example, if we select cell A1 and type 10 followed by hitting the "Enter" key, then the value in cell A2 automatically changes to 8.21, which would be the correct change if the customer gave the cashier a $10 bill. When we entered 10 in the cell A1, Excel automatically went through all the formulas in the workbook and changed them based on the new number we entered in cell A1. Updating the cell values in this manner is called recalculation.

The default mode is for Excel to recalculate the formulas in a workbook automatically each time the value of any cell changes. The mode of recalculation, however, can be changed by following the steps, shown in Figure below. This figure shows the steps used in Excel 2010. The steps look almost identical in Excel 2007. The steps are different for Excel 2003, in which case an Excel user should consult the Excel Help menu. In Excel 2010, first choose the "Formulas" ribbon. Then click on "Calculation Options" on the Calculation submenu. Several choices will be available at that point. Choosing the "Manual" option enables a user to control when Excel recalculates the spreadsheet.

If the spreadsheet used to calculate change is put in manual mode, then when we enter a new value in cell A1, the value in cell A2 will not change. For example, if we put $50 in cell A1, then the value in cell A2 does not change: it remains the same value that was appropriate for the former value in cell A1. In order to get cell A2 reflect the proper change if the customer were to give the cashier a $50 bill, we need to force the workbook to recalculate. To do that we hit the "F9" key, at which point cell A2 reflects the proper value of $48.21. Hence, "F9" causes the workbook to recalculate.

Steps for Changing the Recalculation Mode to Manual

Copying and Pasting Cell Contents

A video tutorial that demonstrates the topic of this subsection is available by either clicking on the appropriate link on this web site http://mason.wm.edu/faculty/bradley_j/EducationalMaterials/ or by going directly to the content:

media.wm.edu/content/business/bradley/NASAFlexBook/VideoTutorials/CopyingAndPasting.asf

Using the example of computing change from the cashier for purchasing a soft drink, we might want to construct a table showing the change due for a variety of different amounts tendered from a customer. Assume that we want to know what the change would be for these amounts given to the cashier: $2, $5, $10, $20, and $50. We can construct such a table by typing a formula only once and then using Excel’s copy and paste capabilities rather than typing five formulas.

Let’s start afresh with a new Excel workbook and construct a table with two columns, one with the amount tendered and one with the change due. We can start by typing headings for the two columns in cells A4 (Amount Tendered) and B4 (Change Due) (see Figure below). For the first amount tendered, select cell A5 and enter 2 for $2.00. In cell B5 enter this formula, =A5-1.79, to calculate the amount of change due as shown in Figure below. Now enter the remaining quantities in cells A6 through A9 that represent the potential amounts that the cashier might tender from the customer as shown in Figure below.

Table for Change Due for Various Amounts Tendered

To determine the change for the other amounts tendered, we could type formulas in cells B6 through B9 for each amount tendered similar to the formula we typed in cell B5 when $2.00 was tendered. However, this could be time consuming if we had a very large number of alternative amounts that could be tendered. It is easier to use the formula that is already typed into cell B5 as a template for the remaining formulas than to type the formula multiple times. There are many ways to copy a formula from one cell to another, but one of the easiest is as follows:

  • Select the cell that contains the formula that you want to copy to other cells.
  • Use the mouse to position the cursor over the small black square that is located in lower right corner of the cell. The cursor image will change to crosshairs.
  • Click the left mouse button and hold it as you move the cursor over the range to which you want to copy the formula.
  • Release the left mouse button when the entire area to which you are copying the formula is highlighted.

Note that this method of dragging the lower-right corner of a cell will work only if the cells to which the formula is being copied are adjacent to the cell that contains the original formula.

Formulas in Change Table after Copying and Pasting

If we inspect the formulas in cells B5 through B9, we find that the formulas in those cells are as shown in Figure above. Excel has anticipated that as we copied the formula down that we wanted the reference to cell A5 to change to A6 in row 6, A7 in row 7, and so on. As a formula is copied from one cell to cells below it, Excel by default adjusts the cell references in the formula. If a cell to which a formula is copied is one row below the cell from which the formula was copied, then Excel will increase all the row references by 1. If the cell to which the formula is being copied is two rows below the cell from which the formula is being copied, the row references will be increased by 2, and so on. Similarly, as a cell formula is copied to the right, each reference to a column in a cell address will be increased by the number of columns the destination cell is from the cell whose formula is being copied. In this instance, we want exactly this to happen: the row reference should increase as we copy the formula down.

However, sometimes we do not want the cell references to change as they did in the example above. In that case, a user must specify that the row reference should remain the same. To demonstrate, start by selecting a cell to represent the price of the soft drink. In cell B2 enter the cost of the soft drink: 1.79. Now change the formula in cell B5 to =A5-B2. The result of the formula remains the same because we have replaced the value 1.79 with a reference to another cell (B2) that contains that same value. Now copy the formula in cell B5 down to cells B6 through B9 in the same manner that we did previously. Are the results correct? No, they are not, as is shown in Figure below. Unfortunately, the reference to cell B2 changed as we copied the formula down, which we didn’t want to happen. We wanted the reference to cell B2 to remain the same.

Incorrect Copy and Paste Result

We can cause a cell reference to remain the same as we copy cell formulas by using a technique called "absolute addressing." As is the case with most techniques in Excel, there are many ways to accomplish this, and the following is only one of the possible ways.

  • Left click with the left mouse button on cell B5 to select it.
  • Highlight B2 in the formula in the Formula Bar or, alternately, place the cursor either before the B, between the B and the 2, or immediately after the 2.
  • Hit the "F4" button on the keyboard, which will cause dollar signs ($) to appear before the B and before the 2.

Then, copy the formula from cell B5 down to cells B6 through B9 as previously described. The dollar signs in the reference to cell B2 causes that reference to remain the same as the formula in B5 is copied. Again, this technique is called "absolute addressing." Verify that using this technique results in the correct change values in column B.

The MIN Function

A video tutorial that demonstrates the topic of this subsection is available by either clicking on the appropriate link on this web site http://mason.wm.edu/faculty/bradley_j/EducationalMaterials/ or by going directly to the content:

media.wm.edu/content/business/bradley/NASAFlexBook/VideoTutorials/MinFunction.asf

In addition to being able to calculate basic formulas using addition, subtraction, multiplication, and division, Excel has many built-in functions that perform convenient and sometimes complex calculations. The MIN function is one of the many functions that are built into Excel. There are many types of functions, including functions for mathematics (like the MIN function), trigonometry, text manipulation, logical operations, and so forth. The MIN function is used to determine the minimum numerical value in a range of cells. To use the MIN function, and any other function, start by typing an equal sign, =. The equal sign alerts Excel that we will be typing a function that we want it to calculate. Then type "min" (typing functions is not case sensitive). Note that as you type the "m" in "min," a list of functions appears (in Excel 2007 and Excel 2010) that start with the letter m, as shown in Figure below. At this point, one may choose to double click (with the left mouse button) on the desired function to eliminate the need to type the entire function name. Once =min is typed, then type an open parenthesis, (. (Typing the open parenthesis is not necessary if you double click on the desired function.) Next, specify a range. Using the Excel workbook containing the data about the Smith Family, we could find the minimum age of people in the Smith Family by typing in the range A14:A20, followed by a closing parenthesis,). Striking the "Enter" key gives the result shown in Figure below.

Function Drop-Down Menu

MIN function

The MAX Function

The MAX function is similar to the MIN function, except that it calculates the maximum value within a range of cells rather than the minimum value. The instructions for the MIN function can be followed exactly, except that we start out by typing =max( rather than =min(. In that case, one should find that the oldest Smith is 37 years old.

The IF Function

A video tutorial that demonstrates the topic of this subsection is available by either clicking on the appropriate link on this web site http://mason.wm.edu/faculty/bradley_j/EducationalMaterials/ or by going directly to the content:

media.wm.edu/content/business/bradley/NASAFlexBook/VideoTutorials/IfFunction.asf

Sometimes we want to calculate the value that is displayed in a spreadsheet cell using one formula in some circumstances and another formula in other circumstances. Sometimes we want to display one text message in a cell if certain conditions are satisfied and another message otherwise. In order to tailor the formula or message in a spreadsheet cell to the circumstances, we can use the IF function. The format of the IF function is:

=IF(LogicalTest,FormulaIfTrue,FormulaIfFalse)

The words "LogicalTest," "FormulaIfTrue," and "FormulaIfFalse" indicate the three components of the IF function, which must be specified. These three placeholders are replaced by formulas, values, or text as we will now describe.

LogicalTest needs to be replaced with a mathematical statement that can either be true or false. We can use an equal sign (=), less than sign (<), greater than sign (>), less than or equal to sign (<=), or greater than or equal to sign (>=) in these statements. The Table below gives some examples in the left column that could be used as the LogicalTest in an IF function. The right column of the table indicates whether the LogicalTest statements are true or false.

Examples of LogicalTest Statements
LogicalTest Statement True or False?
1 < 10 True
1 > 10 False
5 < 5 False
5 <= 5 True
5 >= 5 True
1 = 10 False
1 = 1 True

Rather than type numerical values for the LogicalTest component of the IF function, we can also reference cell values. For example, if the LogicalTest component was D3 > E4, then whether the statement was true or false depends on the values of cells D3 and E4. If the value of D3 is greater than the value of E4, then the statement is true. If the value of D3 is equal to or less than the value of E4, then the statement would be false.

If the LogicalTest is true, then the value of the cell in which the IF statement is typed is determined by the second component of the IF statement, which we have called FormulaIfTrue. If the LogicalTest is false, then the value of the cell is determined by the component we have called FormulaIfFalse. FormulaIfTrue and FormulaIfFalse can be numerical values, cell references, formulas, or text. When either FormulaIfTrue or FormulaIfFalse is text, the text must have a double quote (") at the beginning and end of the text.

For example, we could use the IF statement to determine the name of the oldest Smith parent as shown in Figure below. If this formula is typed in cell A15, then the result is as shown in Figure below:

=IF(A23>A24,B23,B24)

The LogicalTest part of this IF function evaluates whether Mrs. Smith’s age is greater than Mr. Smith’s age, if it is, then the function would display the value from cell B23, which is Mrs. Smith’s name. However, the value of cell A23 is less than the value of cell A24, and so the LogicalTest is false. Therefore, Mr. Smith’s name from cell B24 is displayed, and Mr. Smith is, indeed, the oldest Smith.

Formula for the Name of the Oldest Smith Parent

The VLOOKUP Function

A video tutorial that demonstrates the topic of this subsection is available by either clicking on the appropriate link on this web site http://mason.wm.edu/faculty/bradley_j/EducationalMaterials/ or by going directly to the content:

media.wm.edu/content/business/bradley/NASAFlexBook/VideoTutorials/VlookupFunction.asf

The VLOOKUP function is another built-in Excel function, and it is used when we want to select the contents of a particular cell within a table or range of cells that encompasses some number of rows and columns of data in an Excel worksheet. In our case, each row of data will tell us something about one member of the Smith Family, as already shown in Figure above. Each row of data contains the age and name of one of the Smiths. Data is often arranged in Excel in this manner, where each row describes a particular instance, and the same data (in this case, age and name) is listed for each instance. We sometimes need to search the table to find a particular instance (row) and then report one of the pieces of data related to that instance. In our case, we want to find a particular member of the Smith Family and then report that name.

As an example, we want to search the data about the Smiths and find the name of the Smith Family member who is the oldest Smith who is no older than a certain age. One such situation in which we could use this type of calculation would be to identify the oldest Smith who is eligible to be in a baseball league where participants’ age is limited by some upper value, say 10 years old. Or, similarly, we might want to identify the oldest Smith eligible to be a cub scout, 12 years old or younger.

We can use the VLOOKUP function to accomplish this task. Specifically, when we give VLOOKUP a range of cells, it searches down the first column of data and finds the largest value that is equal or less than a specified value. Using the row of data corresponding with the cell that was selected from the first column, we then specify the column in the range of cells from which we want VLOOKUP to find and report a value. So, we can view the VLOOKUP function as first going down vertically in the first column of a range of data and identifying a row, and then going over to the right a specified number of cells to find a particular cell. The VLOOKUP function then returns the value of that cell. Figure below shows the logic that VLOOKUP follows when finding the oldest Smith who can play in a baseball league for children 10 years old and under, if we tell VLOOKUP to return a value from the second column of the range of cells A14:B20.

VLOOKUP Function

In our case the data range to search is in cells A14, in the upper-left corner, to cell B20, in the lower-right corner, as shown in Figure below. Let’s type the age threshold that we used for the baseball league into cell A8. That is, we type a value of 10 into cell A8. Then we can enter the VLOOKUP function in Cell B8 as =VLOOKUP(A8,A14:B20,2). (One can use a fourth value in the VLOOKUP function, but we can ignore that in our case.) VLOOKUP will find the row of data in the range A14:B20 with the greatest value in column A that is no greater than the value in cell A8. In our data, that is row 18 because Donnie Smith is the oldest Smith who is no older than 10. The third value within the parentheses is 2, which means that we want VLOOKUP to return the value from the second column in the range A14:B20, which is column B. Having already selected row 18, VLOOKUP gives us the value of cell B18, which, in this case, is Donnie Smith. See Figure below to see how to enter the VLOOKUP formula.

Smith Family Data

The RAND Function

A video tutorial that demonstrates the topic of this subsection is available by either clicking on the appropriate link on this web site http://mason.wm.edu/faculty/bradley_j/EducationalMaterials/ or by going directly to the content:

media.wm.edu/content/business/bradley/NASAFlexBook/VideoTutorials/RandFunction.asf

The RAND function is used to create what is called a random number. In particular, RAND generates a number that is between 0 and 1. Furthermore, each time RAND generates one of these numbers, they are equally likely to fall anywhere in the range of 0 to 1, and the sequence of numbers generated by RAND has no obvious patterns. That is, observing the last number generated by RAND gives us no idea of what the next number generated by RAND might be. Therefore, there are no discernible patterns where the sequence of numbers goes up, then down, then up, then down, and there are no sequences of numbers that either increase or decrease for a large sequence of numbers generated by RAND. A mechanism such as RAND is called a random number generator. The numbers are generated with a mathematical formula and are not actually totally random. Thus, they are technically called pseudo-random numbers. In any case, they are close enough to random to suit the purposes of simulation, which is the ultimate topic of this chapter.

The RAND function is implemented by typing =RAND() into a spreadsheet cell. Functions normally have numbers or references to cells within the parentheses, like we used with the VLOOKUP function, but the RAND function is an exception. In this case, we leave the parentheses of the RAND function empty. After typing the function into a spreadsheet cell, the sequence of numbers generated by RAND can be observed by recalculating the workbook (hitting the F9 button) multiple times. Each time F9 is struck, every RAND function in a spreadsheet generates a new random number.

As previously mentioned, any number generated by RAND is equally likely to be any value between 0 and 1. Thus we can calculate the probability that any random number will be in some portion of the range of numbers between 0 and 1. For example, the range of numbers from 0 to 0.5 covers half of the range from 0 to 1. This means that if we were to hit F9 a large number of times, we would expect that half the time, or 50% of the time, the number would between 0 and 0.5. When something is expected to happen 50% of the time if we were to hit F9 many, many times, then this is the same as saying that the probability of that event is 50%. Similarly, if we want to know the percentage of times that we should expect a random number generated by RAND to be between 0.25 and 0.60, we observe that the difference between these two numbers is 0.60 - 0.25 = 0.35, and so that range covers 0.35/1 = 0.35 = 35% of the range between 0 and 1. So, if we hit F9 many, many times, we would expect a number between 0.25 and 0.60 35% of the time.

Creating Graphs

A video tutorial that demonstrates the topic of this subsection is available by either clicking on the appropriate link on this web site http://mason.wm.edu/faculty/bradley_j/EducationalMaterials/ or by going directly to the content:

media.wm.edu/content/business/bradley/NASAFlexBook/VideoTutorials/Charts.asf

Graphing data gives us a visual representation that can be more informative than just looking at the numerical data in worksheet cells. In simulation analyses, this helps build our intuition about how a particular business operation performs. We will use the data shown in Figure below to demonstrate the basics of graphing data in Excel. That data represent how the number of people waiting in line to be served changes over time. This might be a line at a bank, at a movie theater, or at a fast food restaurant. The numbers in column A indicate the minute at which the count was taken of people in line, and the data in column B indicate the number of people in line. The two numbers in a particular row, one from column A and one from column B are, therefore, related to one another. For example, the data in row 10 means that when the count was taken in minute 9, there were 11 people in line. (This Excel file can be downloaded from this site: http://mason.wm.edu/faculty/bradley_j/EducationalMaterials/.)

Data for Graph Example

We will construct a line graph where the height of the line represents one of the two columns of data. We are interested in seeing how the line length changes over time, and, because we use the height of the line to emphasize the key data in which we are interested, we will chose the data in column B to plot as a line. So, start by selecting the range in column B from B2 to B29. Then, choose the "Insert" ribbon, as shown by the active tab in Figure above. Next, choose the line graph icon as in step 1 in Figure above. Next, select the icon from the menu that then appears for creating a line graph with markers (or symbols) at the data points. This is indicated as step 2 in Figure above. The resulting graph is as shown in Figure below.

Initial Version of the Graph

The graph is not quite complete. Although it appears that the x axis (the horizontal axis) correctly corresponds to minutes 1 through 28 as displayed in the data in column A, this is not the case. The x axis values of 1 through 28 were the default as selected by Excel, and they do not correspond to any data in the worksheet. We want the x axis data to correspond to the minutes in the range A2:A29. To do this, select the "Design" ribbon. Note that this ribbon is available only when a graph is selected, as shown on Figure above. Then, choose the "Select Data" icon as indicated in Figure above. A dialog window will then open as shown in Figure below. This dialog window allows the data for the x axis to be specified by clicking on the "Edit" button that is associated with the "Horizontal Axis Label" region of the dialog window, as indicated in Figure below. Clicking on that button causes another dialog window to appear, which is used to specify the data for the x axis, as shown in Figure below. Selecting the data range that contains the x axis data (from cell A2 to cell A29) enters that range specification into the dialog window, as shown in Figure below. Clicking on "OK" finishes the task and updates the x axis data. Subsequently, click on the "OK" button in the "Select Data Source" dialog window that is shown in Figure below.

Dialog Window to Edit Graph Data

Dialog Window to Select x-Axis Data

Selection of x-Axis Data

Other important tasks to make the graph complete and legible include:

  • Changing the x-axis title to "Time (minutes)."
  • Changing the y-axis title to "Customers in Line."
  • Deleting the legend, which is not needed because we are plotting only one line.

Lesson Summary

  • MIN: This function calculates the minimum numerical value with the specified range of cells.
  • MAX: This function calculates the maximum numerical value with the specified range of cells.
  • IF: This function is used to display text, numerical quantities, or to calculate formula values when the text, numbers, or formula to be used depend on certain conditions being satisfied. A single IF function can display one of two alternatives depending on the logical test that is specified as the first parameter within its parentheses.
  • VLOOKUP: This function searches a range of data and returns the value from a cell in a particular row and column based on the values in the first column of the data range, a reference value that is compared with the values in the first column, and a number that specifies a column from which to extract a value.
  • RAND: This function generates a random value between 0 and 1.
  • Cells are the smallest denomination of data in spreadsheets. Cells display one piece of data and can use text, numerical quantities, or formulas to determine which value or data are displayed. Ranges are a collection of multiple cells. Many values, or a range of cells, are sometimes needed to describe a situation. For example, two columns and seven rows of data (14 cells in total) were needed to describe the names and ages of the Smith Family members.
  • Worksheets within Excel workbooks can be given names that describe the data that they contain.
  • A formula can be entered into a spreadsheet cell using basic arithmetic operations, including addition, subtraction, multiplication, and division.
  • A spreadsheet user can copy a formula from one cell to another so that the formula does not need to be retyped. This makes constructing a spreadsheet model much quicker and reduces the chance that a formula is mistyped.
  • We sometimes do not want the reference to a cell in a formula to change as we copy the formula to other cells. Using absolute addressing and, in particular, dollar signs, allows us keep a cell reference from changing during copy and pasting.
  • Single cells and ranges of spreadsheet cells can be given names that describe the data in the cells. This allows formulas to be more understandable and intuitive.
  • Recalculation is the process by which the computer re-computes every formula in a workbook. This can be done in automatic or manual mode. In automatic mode, recalculation is performed any time the contents of any cell in a workbook changes. In manual mode, a user must tell the computer to recalculate. One way to do this is to strike the F9 key.
  • Graphs are visual representations of data that help us understand the data that are being plotted.

Review Questions

  1. Use the Excel MAX function to find the age of the oldest Smith from the worksheet data about the Smith family.
  2. What is the smallest possible value that the RAND function will give?
  3. What is the largest possible value that the RAND function will give?
  4. If one were to hit the F9 key to recalculate the spreadsheet, what percentage of the time would a cell that contains =RAND() give a result that is:
    1. equal to or less than 0.30?
    2. equal to or greater than 0.70?
    3. between 0.40 and 0.60 (or equal to those two values)?
    4. between 0.10 and 0.75 (or equal to those two values)?

Further Reading / Supplemental Links

Video tutorials for most of the techniques contained in this lesson can be found at this web site: http://mason.wm.edu/faculty/bradley_j/EducationalMaterials/

Specifically, the links for each of the tutorials are as follows:

In addition, many of the spreadsheets discussed in this lesson can be downloaded from this web site: http://mason.wm.edu/faculty/bradley_j/EducationalMaterials/

Points to Consider

  • The spreadsheet techniques described in this lesson can be used to accomplish all sorts of analyses with spreadsheets. One example is described in the next lesson, which is a computer simulation analysis of a system where the number of customers who arrive varies, as does the pace at which workers serve those customers.

Image Attributions

Description

Categories:

Grades:

Date Created:

Aug 06, 2012

Last Modified:

Aug 21, 2014
Files can only be attached to the latest version of None

Reviews

Please wait...
Please wait...
Image Detail
Sizes: Medium | Original
 
CK.SCI.ENG.SE.1.Modeling-and-Simulation-for-High-School-Teachers.6.3

Original text