Transcript Slide 1

Part 2
Creating and Using Lists
What is a list?
 A list is essentially an organized collection of information.
 More specifically, a list consists of a row of headers (descriptive text)
followed by additional rows of data, which can be values or text.
 You can also think of a list as a database table that is stored in a
worksheet.
 People often refer to the columns in a list as fields and to the rows as
records.
List Example
Designing a List
 The following are some guidelines to keep in mind when creating lists:
 Insert descriptive labels (one for each column) in the first row of the




list. This is the header row.
Each column should contain the same type of information. For
example, don’t mix dates and text in a single column.
Don’t use any empty rows within the list. For list operations, Excel
determines the list boundaries automatically, and an empty row signals
the end of the list.
Select the upper-left data cell and choose Window➪Freeze Panes to
make sure that the headings are visible when the list is scrolled.
You can preformat entire columns to ensure that the data has the same
format. For example, if a column contains dates, format the entire
column with the desired date format.
Entering Data into a List
 Entering data into a list can be done in three ways:
✦ Manually, using all standard data entry techniques
✦ By importing it or copying it from another file
✦ By using a dialog box
 There’s really nothing special about entering data into a list. You just
navigate through the worksheet and enter the data into the appropriate
cells.
 If you prefer to use a dialog box for your data entry, Excel
accommodates you. To bring up a data entry dialog box, move the cell
pointer anywhere within the list and choose Data➪Form. Excel
determines the extent of your list and displays a dialog box showing
each field in the list.
Entering Data into a List (Contd.)
Entering data with the Data Form dialog box
 When the Data Form dialog box appears, the first record in the list is
displayed.
 To enter a new record, click the New button to clear the fields. Then
you can enter the new information into the appropriate fields. Use Tab
to move among the fields.
 When you click New (or Close), the data that you entered is appended
to the bottom of the list. You also can press Enter, which is equivalent
to clicking on the New button.
Other uses for the Data Form dialog box
 You can use the Data Form dialog box for more than just data entry.
You can edit existing data in the list, view data one record at a time,
delete records, and display records that meet certain criteria.
 The dialog box contains a number of additional buttons, which are
described as follows:
✦ Delete: Deletes the displayed record.
✦ Restore: Restores any information that you edited. You must click this
button before you click on the New button.
✦ Find Prev: Displays the previous record in the list. If you entered a
criterion, this button displays the previous record that matches the
criterion.
✦ Find Next: Displays the next record in the list. If you entered a
criterion, this button displays the next record that matches the
criterion.
✦ Criteria: Clears the fields and lets you enter a criterion upon which to
search for records.
Filtering a List
 Filtering a list is the process of hiding all rows in the list except those
that meet some criteria that you specify. For example, displaying
students who live in Peshawar only.
 Excel provides two ways to filter a list:
 ✦ AutoFilter for simple filtering criteria
 ✦ Advance Filter for more-complex filtering
Using autofiltering
 To autofilter a list, start by moving the cell pointer anywhere within the





list.
Then choose Data➪Filter➪AutoFilter. Excel analyzes your list and
adds drop-down arrows to the field names in the header row.
When you click the arrow in one of these drop down lists, the list
expands to show the unique items in that column.
Select an item, and Excel hides all rows except those that include the
selected item. In other words, the list is filtered by the item that you
selected.
After you filter the list, the status bar displays a message that tells you
how many rows qualified. In addition, the drop-down arrow changes
color to remind you that the list is filtered by a value in that column.
To move out of AutoFilter mode and remove the drop-down arrows
from the field names, choose Data➪Filter➪AutoFilter again. This
removes the check mark from the AutoFilter menu item and restores
the list to its normal state.
Autofiltering
Creating Dynamic Lists with PivotTables
Creating Dynamic Lists with PivotTables
 Excel worksheets let you gather and present important data, but the
standard worksheet can’t be changed from its original configuration
easily. That means you cannot easily exchange the data of rows and
columns.
 You can use an Excel tool to reorganize and redisplay your data
dynamically. You can create a PivotTable, or dynamic worksheet, that
lets you reorganize and filter your data on the fly.
 To create a PivotTable, you must have your data collected in a list.
Creating Dynamic Lists with PivotTables (Contd.)
 Consider the following data. To create a PivotTable, you must have your
data collected in a list in which every row represents a cell in the body
of the finished PivotTable. Once you have created a list, you can click
any cell in that list, open the Data menu, and click PivotTable and
PivotChart Report to launch the PivotTable and PivotChart Wizard.
Creating Dynamic Lists with PivotTables (Contd.)
Creating Dynamic Lists with PivotTables (Contd.)
 On this wizard page, you identify the data source for your PivotTable
and whether you want to create a PivotTable by itself or a PivotTable
and a PivotChart. Clicking Next accepts the default choices and moves
you to the second wizard screen.
Creating Dynamic Lists with PivotTables (Contd.)
 On this screen, you verify that the wizard has correctly identified the
cells with the data for your PivotTable. If not, you can click the Collapse
Dialog button in the Range box, select the cells that contain your data,
and then expand the dialog box to continue. Once the proper cell range
is listed in the Range box, click Next to move to the final wizard screen.
Creating Dynamic Lists with PivotTables (Contd.)
 This wizard screen asks whether you want to create your PivotTable in a
new or an existing worksheet. Because the data lists used to create
PivotTables are usually quite long, it is often best to create the
PivotTable in a new worksheet. Clicking Finish closes the wizard;
creates a new worksheet in your workbook; and adds a PivotTable, the
PivotTable toolbar, and the Pivot Table Field List dialog box to that
worksheet.
Creating Dynamic Lists with PivotTables (Contd.)
Creating Dynamic Lists with PivotTables (Contd.)
 To assign a field, or column in a data list, to an area of the PivotTable,
you drag the field head to the desired area on the PivotTable outline.
For example, you can drag the Sales Rep field head to the Drop Row
Fields Here box, drag the Region field head to the Drop Column Fields
Here box, and then drag the Sales field head to the Drop Data Items
Here box to populate the body of the PivotTable with data. After you
drop a field head in the Drop Data Items Here box, the PivotTable fills
with data.
Creating Dynamic Lists with PivotTables (Contd.)
To format PivotTable data
 Select the cells in the PivotTable data area.
 On the Format menu, click Cells.
 Use the controls in the Format Cells dialog box to format the cells in
the PivotTable, and click OK.
To apply a predefined format to a PivotTable
 If the PivotTable toolbar is hidden, right-click any toolbar and then,
from the shortcut menu that appears, click PivotTable.
 Click any cell in the PivotTable.
 On the PivotTable toolbar, click the Format Report button.
 Click the desired AutoFormat.
To apply a predefined format to a PivotTable
Editing PivotTables
 After you have created a PivotTable, you can edit it to control how your




data is displayed.
To open a PivotTable for editing, you click any cell in the PivotTable
and then, if necessary, display the fields available for the PivotTable by
clicking the Show Field List button on the PivotTable toolbar.
While the PivotTable Field List dialog box is open, you can drag any
field name from the dialog box to the active PivotTable. Dragging a
field name to the Drop Page Fields Here box doesn’t change how the
data in your PivotTable is arranged, but it does let you filter your
PivotTable based on the contents of the field.
Clicking a field head’s down arrow displays a list of values in the field.
Clicking any of these values and then clicking OK limits the data
shown in the PivotTable to data gathered on the selected weekday.
To remove a filter from a PivotTable, click the down arrow of the field
head used to filter the PivotTable, click (All), and then click OK.
Functions and Formulas
Purpose
 One important task you can perform in Excel is to
calculate totals for the values in a series of related cells.
You can also use Excel to find out other information
about the data you select, such as the maximum or
minimum value in a group of cells.
 Regardless of your bookkeeping needs, Excel gives you
the ability to find the information you want. And if
you should make an error, you can find the cause and
correct it quickly.
 Excel makes it easy to reference a number of cells at
once, letting you define your calculations quickly.
Creating Formulas to Calculate Values
 Once you’ve added your data to a worksheet and defined ranges
to simplify data references, you can create a formula, or an
expression that performs calculations on your data.
 To write an Excel formula, you begin the cell’s contents with an
equal sign—when Excel sees it, it knows that the expression
following it should be interpreted as a calculation and not text.
After the equal sign, you type the formula.
 For instance, you can find the sum of the numbers in cells C2
and C3 using the formula =C2+C3.
 After you have entered a formula into a cell, you can revise it by
clicking the cell and then editing the formula in the formula bar.
Formulas Contd.
 To create a new calculation, you click Function on the Insert
menu. The Insert Function dialog box appears, with a list of
functions, or predefined formulas, from which you can choose.
Finding and Correcting Errors in Calculations
 Excel makes it easy to find the source of errors in your
formulas by identifying the cells used in a given calculation
and describing any errors that have occurred. The process
of examining a worksheet for errors in formulas is referred
to as auditing.
 Excel identifies errors in several ways. The first way is to fill
the cell holding the formula generating the error with an
error code.
 When a cell with an erroneous formula is the active cell, an
Error button appears next to it. You can click the button’s
down arrow to display a menu with options that provide
information about the error and offer to help you fix it.
Error Codes
Error Code
Description
#####
The column isn’t wide enough to display the value.
#VALUE!
The formula has the wrong type of argument (such as text where a
TRUE or FALSE value is required).
#NAME?
The formula contains text that Excel doesn’t recognize (such as an
unknown named range).
#REF!
The formula refers to a cell that doesn’t exist (which can happen
whenever cells are deleted).
#DIV/0!
The formula attempts to divide by zero.
Entering and Editing Formulas
 Entering a new formula into a worksheet appears to be a
straightforward process:
1. Select the cell in which you want to enter the formula.
2. Type an equals sign (=) to tell Excel that you’re entering a
formula.
3. Type the formula’s operands and operators.
4. Press Enter to confirm the formula.
 Excel divides formulas into four groups: arithmetic, comparison,
text, and reference.
Using Arithmetic Formulas
 Arithmetic formulas are by far the most common type of
formula. They combine numbers, cell addresses, and function
results with mathematical operators to perform calculations.
 It consists of operators like addition (+), subtraction (-),
multiplication (*), division (/), percentage (%), exponentiation
(^).
Using Comparison Formulas
 A comparison formula is a statement that compares two or more
numbers, text strings, cell contents, or function results.
 If the statement is true, the result of the formula is given the logical
value TRUE (which is equivalent to any nonzero value). If the
statement is false, the formula returns the logical value FALSE (which
is equivalent to 0)
Using Text Formulas
 A text formula is a formula that returns text.
 Text formulas use the ampersand (&) operator to work with text
cells, text strings enclosed in quotation marks, and text function
results.
 One way to use text formulas is to concatenate text strings. For
example, if you enter the formula =“soft"&"ware" into a cell,
Excel displays software.
 You also can use & to combine cells that contain text. For
example, if A1 contains the text Ben and A2 contains Jerry,
entering the formula =A1&" and " &A2 returns Ben and Jerry.
Using Reference Formulas
 The reference operators combine two cell references or ranges to
create a single joint reference.
Operator Precedence
 3 ^ (15/5) * 2 – 5
 3 ^ ((15/5) * 2 – 5)
 3 ^ (15 / (5 * 2 – 5 ))
Understanding Relative Reference Format
 When you use a cell reference in a formula, Excel looks at the cell
address relative to the location of the formula.
 For example, suppose that you have the formula =A1*2 in cell A3.
To Excel, this formula says, “Multiply the contents of the cell two
rows above this one by 2.” This is called the relative reference
format, and it’s the default format for Excel.
 This means that if you copy this formula to cell A4, the relative
reference is still “Multiply the contents of the cell two rows above
this one by 2,” but the formula changes to =A2*2 because A2 is
two rows above A4.
 This way of handling copy operations will save you incredible
amounts of time when you’re building your worksheet models.
Understanding Absolute Reference Format
 When you refer to a cell in a formula using the absolute reference
format, Excel uses the physical address of the cell.
 You tell the program that you want to use an absolute reference
by placing dollar signs ($) before the row and column of the cell
address.
 Talking about the old example, Excel interprets the formula
=$A$1*2 as “Multiply the contents of cell A1 by 2.”
 No matter where you copy or move this formula, the cell
reference doesn’t change. The cell address is said to be anchored.
Naming Formulas
 Follow these steps to name a formula:
 Choose Insert, Name, Define Name to display the New Name
dialog box.
 Enter the name you want to use for the formula in the Name text
box.
 In the Refers To box, enter the formula exactly as you would if
you were entering it in a worksheet.
 Click OK.
Functions
 Excel has various function categories, including the following:
 Text
 Logical
 Information
 Lookup and reference
 Date and time
 Math and trigonometry
 Statistical
 Financial
 Database and table
Typing a Function into a Formula
 Whether you use a function on its own or as part of a larger




formula, here are a few rules and guidelines to follow:
You can enter the function name in either uppercase or
lowercase letters. Excel always converts function names to
uppercase.
Always enclose function arguments in parentheses.
Always separate multiple arguments with commas. (You might
want to add a space after each comma to make the function more
readable. Excel ignores the extra spaces.)
You can use a function as an argument for another function. This
is called nesting functions. For example, the function
AVERAGE(SUM(A1:A10), SUM(B1:B15)) sums two columns of
numbers and returns the average of the two sums
Using the IF() Function (The Simplest Case)
 Let’s start with the simplest version of the IF() function:
 IF(logical_test, value_if_true)
 logical_test: A logical expression—that is, an expression that returns TRUE
or FALSE (or their equivalent numeric values: 0 for FALSE and any other
number for TRUE).
 value_if_true: The value returned by the function if logical_test evaluates to
TRUE.
 For example, consider the following formula:
=IF(A1 >= 1000, “It’s big!”)
IF() Function (Handling a FALSE Result)
 IF(logical_test, value_if_true, value_if_false)
 logical_test: A logical expression.
 value_if_true: The value returned by the function if logical_test
evaluates to TRUE.
 value_if_false: The value returned by the function if logical_test
evaluates to FALSE.
 For example, consider the following formula:
=IF(A1 >= 1000, “It’s big!”, “It’s not big!”)
IF() Function (Avoiding Division by Zero)
 Excel displays the #DIV/0! error if a formula tries to divide a
quantity by zero. To avoid this error, you can use IF() to test the
divisor and ensure that it’s nonzero before performing your
division.
 For example, the basic equation for calculating gross margin is
(Sales – Expenses)/Sales. To make sure that Sales isn’t zero, use
the following formula:
 =IF(Sales <> 0, (Sales - Expenses)/Sales, “Sales are zero!”)
Performing Multiple Logical Tests
 Excel offers several techniques for performing two or more
logical tests: nesting IF() functions, the AND() function, and the
OR() function.
Nesting IF() Functions
 When building models using IF(), it’s common to come upon a second





fork in the road when evaluating either the value_if_true or
value_if_false arguments.
For example, consider the variation of our formula that outputs a
description based on the value in cell A1:
=IF(A1 >= 1000, “Big!”, “Not big”)
What if you want to return a different string for values greater than, say,
10,000? In other words, if the condition A1 > 1000 proves to be true, you
want to run another test that checks to see if A1 > 10000.
You can handle this scenario by nesting a second IF() function inside
the first as the value_if_true argument:
=IF(A1 >= 1000, IF(A1 >= 10000, “Really big!!”, “Big!”), “Not big”)
For example, if you want to return the description Small for a cell value
less than 100, you would use this version of the formula:
=IF(A1 >= 1000, “Big!”, IF(A1 < 100, “Small”, “Not big”))
The AND() Function
 It’s often necessary to perform an action if and only if two conditions






are true.
The AND() result is calculated as follows:
If all the arguments return TRUE (or any nonzero number), AND()
returns TRUE.
If one or more of the arguments return FALSE (or 0), AND() returns
FALSE.
You can use the AND() function anywhere you would use a logical
formula, but it’s most often pressed into service as the logical condition
in an IF() function.
In other words, if all the logical conditions in the AND() function are
TRUE, IF() returns its value_if_true result; if one or more of the logical
conditions in the AND() function are FALSE, IF() returns its
value_if_false result. Here’s an example:
=IF(AND(B2 > 0, C2 > 0), “1000”, “No bonus”)
The OR() Function
 The OR() result is calculated as follows:
 If one or more of the arguments return TRUE (or any nonzero
number), OR() returns TRUE.
 If all of the arguments return FALSE (or 0), OR() returns FALSE.
 As with AND(), you use OR() wherever a logical expression is
called for, most often within an IF() function.
 This means that if one or more of the logical conditions in the
OR() function are TRUE, IF() returns its value_if_true result; if
all of the logical conditions in the OR() function are FALSE, IF()
returns its value_if_false result.
 Here’s an example:
=IF(OR(B2 > 0, C2 > 0), “1000”, “No bonus”)
Working with Lookup Functions
 In many worksheet formulas, the value of one argument often
depends on the value of another. Here are some examples:
 In a formula that calculates an invoice total, the customer’s
discount might depend on the number of units purchased.
 In a formula that charges interest on overdue accounts, the
interest percentage might depend on the number of days each
invoice is overdue.
 The usual way to handle these kinds of problems is to look up
the appropriate value.
Understanding Lookup Tables
 The table—more properly referred to as a lookup table—is the
key to performing lookup operations in Excel.
 The most straightforward lookup table structure is one that
consists of two columns (or two rows):
 Lookup column—This column contains the values that you look
up. For example, if you were constructing a lookup table for a
dictionary, this column would contain the words.
 Data column—This column contains the data associated with
each lookup value. In the dictionary example, this column would
contain the definitions.
 In most lookup operations, you supply a value that the function
locates in the designated lookup column. It then retrieves the
corresponding value in the data column.
The CHOOSE() Function
 The simplest of the lookup functions is CHOOSE(), which enables you





to select a value from a list. Specifically, given an integer n, CHOOSE()
returns the nth item from the list. Here’s the function’s syntax:
CHOOSE(num, value1[, value2,...])
Num: Determines which of the values in the list is returned. If num is 1,
value1 is returned; if num is 2, value2 is returned (and so on). num
must be an integer (or a formula or function that returns an integer)
between 1 and 29.
value1, value2...: The list of up to 29 values from which CHOOSE selects
the return value. The values can be numbers, strings, references,
names, formulas, or functions.
For example, consider the following formula:
=CHOOSE(2,”Surface Mail”, “Air Mail”, “Courier”)
The num argument is 2, so CHOOSE() returns the second value in the
list, which is the string value Air Mail.
Looking Up Values in Tables
 CHOOSE() does have its drawbacks:
 The lookup values must be positive integers.
 The maximum number of data values is 29.
 Only one set of data values is allowed per function.
 Excel can use a wider variety of lookup values (negative or real
numbers, strings, and so on), and it can also accommodate
multiple data sets that each can have any number of values
(subject, of course, to the worksheet’s inherent size limitations).
 Excel has two functions that meet these criteria: VLOOKUP()
and HLOOKUP().
The VLOOKUP() Function
 The VLOOKUP() function works by looking in the first column of a table for
the value you specify. (The V in VLOOKUP() stands for vertical.)
 It then looks across the appropriate number of columns (which you specify)
and returns whatever value it finds there.
 Here’s the full syntax for VLOOKUP():
 VLOOKUP(lookup_value, table_array, col_index_num)
 lookup_value: This is the value you want to find in the first column of
table_array. You can enter a number, string, or reference.
 table_array: This is the table to use for the lookup. You can use a range
reference or a name.
 col_index_num: If VLOOKUP() finds a match, col_index_num is the column
number in the table that contains the data you want returned (the first
column—that is, the lookup column—is 1, the second column is 2, and so on).
The VLOOKUP() Function (Contd.)
 If VLOOKUP() doesn’t find a match in the lookup column, it returns
#N/A.
 If col_index_num is less than 1, VLOOKUP() returns #VALUE!; if
col_index_num is greater than the number of columns in table,
VLOOKUP() returns #REF!.
VLOOKUP Example
The HLOOKUP() Function
 The HLOOKUP() function is similar to VLOOKUP(), except that it






searches for the lookup value in the first row of a table. (The H in
HLOOKUP() stands for horizontal.)
If successful, this function then looks down the specified number of
rows and returns the value it finds there.
Here’s the syntax for HLOOKUP():
HLOOKUP(lookup_value, table_array, row_index_num)
lookup_value: This is the value you want to find in the first row of
table_array. You can enter a number, string, or reference.
table_array: This is the table to use for the lookup. You can use a range
reference or a name.
row_index_num: If HLOOKUP() finds a match, row_index_num is the
row number in the table that contains the data you want returned (the
first row—that is, the lookup row—is 1, the second row is 2, and so on).
HLOOKUP Example
Math & Statistical Functions
The ROUND() Function
 The rounding function you’ll use most often is ROUND():
 ROUND(number, num_digits)
 number: The number you want to round
 num_digits: An integer that specifies the number of digits you
want number rounded to, as explained here:
num_digits
Description
>0
Rounds number to num_digits decimal places
0
Rounds number to the nearest integer
<0
Rounds number to num_digits to the left of the
decimal point
 =round(236.1234, 2) gives 236.12.
 =round(236.1234, 0) gives 236.
 =round(236.1234, -2) gives 200.
The EVEN() and ODD() Functions
 The EVEN() and ODD() functions round a single numeric





argument:
EVEN(number)
ODD(number)
number: The number you want to round
Both functions round the value given by number away from 0, as
follows:
EVEN() rounds to the next even number. For example,
EVEN(14.2) returns 16, and EVEN(–23) returns –24.
ODD() rounds to the next odd number. For example, ODD(58.1)
returns 59 and ODD(–6) returns –7.
The INT() and TRUNC() Functions
 The INT() and TRUNC() functions are similar in that you can use both to convert a value
to its integer portion:
 INT(number)
 TRUNC(number[, num_digits])
 number: The number you want to round
 num_digits: An integer that specifies the number of digits you want number rounded to,
as explained here:
num_digits
Description
>0
Truncates all but num_digits decimal places
0
Truncates all decimal places (this is the default)
<0
Converts num_digits to the left of the decimal
point into zeroes
 For example, INT(6.75) returns 6, and TRUNC(3.6) returns 3. However, these functions
have two major differences that you should keep in mind:
 For negative values, INT() returns the next number away from 0. For example, INT(–3.42)
returns –4. If you just want to lop off the decimal part, you need to use TRUNC() instead.
 You can use the TRUNC() function’s second argument—num_digits—to specify the
number of decimal places to leave on. For example, TRUNC(123.456, 2) returns 123.45,
and TRUNC(123.456, –2) returns 100.
The SUM() Function
 Here’s the syntax of the SUM() function:
 SUM(number1[, number2, ...])
 number1, number2,... The values you want to add
 For example, the following formula returns the sum of the values in
three separate ranges:
 =SUM(A2:A13, C2:C13, E2:E13)
The MOD() Function
 The MOD() function calculates the remainder (or modulus) that




results after dividing one number into another. Here’s the syntax for
this more-useful-than-you-think function:
MOD(number, divisor)
number: The dividend (that is, the number to be divided)
divisor: The number by which you want to divide number
For example, MOD(24, 10) equals 4 (that is, 24 •10 = 2, with remainder
4)
Counting Items with the COUNT() Function
 The simplest of thedescriptive statistics is the total number of
values, which is given by the COUNT() function:
 COUNT(value1[,value2,...])
 value1, value2,... One or more ranges, function results,
expressions, or literal values of which you want the count
The AVERAGE() Function
 The mean is what you probably think of when someone uses the term
average. That is, it’s the arithmetic mean of a set of numbers.
 In Excel, you calculate the mean using the AVERAGE() function:
 AVERAGE(number1[,number2,...])
 number1, number2,... A range, or list of values of which you want the mean
The MEDIAN() Function
 The median is the value in a data set that falls in the middle when all the
values are sorted in numeric order. That is, 50% of the values fall below the
median, and 50% fall above it.
 You calculate the median using the MEDIAN() function:
 MEDIAN(number1[,number2,...])
 number1, number2,... A range, or list of values of which you want the
median
The MODE() Function
 The mode is the value in a data set that occurs most frequently.
 The mode is most useful when you’re dealing with data that doesn’t




lend itself to being either added (necessary for calculating the mean)
or sorted (necessary for calculating the median).
For example, you might be tabulating the result of a poll that included
a question about the respondent’s favorite color. The mean and median
don’t make sense with such a question, but the mode will tell you
which color was chosen the most.
You calculate the mode using the MODE() function:
MODE(number1[,number2,...])
number1, number2,... A range, or list of values of which you want the
mode
The MAX() and MIN() Functions
 If you want to know the largest value in a data set, use the MAX()





function:
MAX(number1[,number2,...])
number1, number2,... A range, or list of values of which you want the
maximum
To get the smallest value in a data set, use the MIN() function:
MIN(number1[,number2,...])
number1, number2,... A range, or list of values of which you want the
minimum
Calculating the Range
 The simplest measure of variability is the range (also sometimes




called the spread), which is defined as the difference between a
data set’s maximum and minimum values.
Excel doesn’t have a function that calculates the range directly.
Instead, you first apply the MAX() and MIN() functions to the
data set. Then, when you have these extreme values, you
calculate the range by subtracting the minimum from the
maximum.
For example, here’s a formula that calculates the range for the
defects database:
=MAX(D3:D22) - MIN(D3:D22)
Speaking generally, the range is a useful measure of variation
only for small sample sizes.
Calculating the Variance with the VAR() Function
 Excel calculates the variance using the VARP() and VAR()




functions:
VARP(number1[,number2,...])
VAR(number1[,number2,...])
number1, number2,... A range, or list of values of which you want
the variance
You use the VARP() function if your data set represents the entire
population; You use the VAR() function if your data set
represents only a sample from the entire population.
Calculating the Standard Deviation with the STDEVP()
and STDEV() Functions
 You could calculate the standard deviation by taking the square




root of the VAR() result, but Excel offers a more direct route:
STDEVP(number1[,number2,...])
STDEV(number1[,number2,...])
number1, number2,... A range, or list of values of which you want
the standard deviation
You use the STDEVP() function if your data set represents the
entire population; you use the STDEV() function if your data set
represents only a sample from the entire population.
Some More Functions
The PMT Function
 The PMT function returns the loan payment (principal plus interest)









per period, assuming constant payment amounts and a fixed interest
rate.
To return the periodic payment for a loan, use the PMT() function:
PMT(rate, nper, pv[, fv][, type])
rate: The fixed rate of interest over the term of the loan.
nper: The number of payments over the term of the loan.
pv: The loan principal.
fv: The future value of the loan.
type: The type of payment. Use 0 (the default) for end-of-period
payments; use 1 for beginning-of-period payments.
For example, the following formula returns the monthly payment of a
$10,000 loan with an annual interest rate of 6% (0.5% per month) over
5 years (60 months):
=PMT(0.005, 60, 10000)
Calculating the Principal and Interest
 Any loan payment has two components: principal repayment and interest
charged.
 Interest charges are almost always front-loaded, which means that the interest
component is highest at the beginning of the loan and gradually decreases with
each payment. This means, conversely, that the principal component increases
gradually with each payment.
 To calculate the principal and interest components of a loan payment, use the
PPMT() and IPMT() functions, respectively:
 PPMT(rate, per, nper, pv[, fv][, type])
 IPMT(rate, per, nper, pv[, fv][, type])
 rate: The fixed rate of interest over the term of the loan.
 per: The number of the payment period (where the first payment is 1 and the
last payment is the same as nper).
 nper: The number of payments over the term of the loan.
 pv: The loan principal.
 fv: The future value of the loan (the default is 0).
 type: The type of payment. Use 0 (the default) for end-of-period payments; use
1 for beginning-of-period payments.
The End