Excel Review
• This review uses material and questions from
Practice Excel Exam 1 found on the Lab Exam 2
Study Guide webpage.
• Print out a copy of Practice Excel Exam 1.
• Download the Practice Excel Exam 1 file
(Practice_1.xlsx) by following the instructions
at the top of Practice Excel Exam 1.
• Complete the exam before reading this review.
• Then, go to the next slide.
Commission Formula
• The information needed to design this formula is found in the Commission area of Question 2 under the Salary section of Practice Excel Exam 1.
• The formula calculates the commission for the
salesperson in row 4, with the formula typed in cell D4. • But, design the formula general enough such that it can
correctly calculate the commission for any salesperson. • That way, one can just copy the formula to rows 5
through 17 in column D for the other salespeople
without having to alter or fix the commission formula for any of the other salespeople.
Commission Formula
• In this situation, the formula should calculate
either the total sales (cell B4) multiplied by 4%
(cell B25) or the total sales (cell B4) multiplied
by 3% (cell B24).
• Design the formula to have the formula
determine which calculation to perform.
• The If function is perfect for this task.
Commission Formula
=if( , , )
Logical Test Parameter
B4 >= $B$26
Value If True Parameter Value If False Parameter
B4 * $B$25 B4 * $B$24
• The formula above is the commission formula entered into cell D4.
• Copy the formula in cell D4 to cells D5 through D17 for the other salespeople.
Commission Formula
• An observation, there may be more than one correct formula for a cell.
• Case in point, the Salesperson’s Commission formula. • The commission formula for the salesperson in row 4
could be any of the following four formulas:
1. =if(B4 >= $B$26, B4 * $B$25, B4 * $B$24) 2. =if(B4 < $B$26, B4 * $B$24, B4 * $B$25) 3. =B4 * if(B4 >= $B$26, $B$25, $B$24)
4. =B4 * if(B4 < $B$26, $B$24, $B$25)
Comparison Operators
• Comparison Operators for Use in Formulas
< less than
> greater than = equal to
<> not equal to
<= less than or equal to
>= greater than or equal to
Absolute vs. Relative Cell Addressing
• In the commission formula, why do some of
the cell addresses have dollar signs ($) in them
and other cell addresses do not?
B4 $B$24 $B$25 $B$26
• Answer: When copying the formula in cell D4
to cells D5 through D17, Excel should alter
certain cell addresses and not alter other cell
addresses.
Relative Cell Addressing
• A relative cell address does not have dollar signs ($) in the address.
• For example, in the commission formula, the relative cell addresses are the B4’s.
• When copying the commission formula to cell D5, Excel alters the B4 cell addresses to B5.
• This is natural because B4 contained the Total Sales for the salesperson in row 4.
• The commission formula in cell D5 should refer to the cell containing the Total Sales for the salesperson in row 5 which is B5!
Absolute Cell Addressing
• An absolute cell address has dollar signs ($) in the address.
• For example, in the commission formula, the absolute cell addresses are $B$26, $B$25, and $B$24.
• When copying the commission formula to cells D5 through D17, Excel does not alter the absolute cell addresses!
• This is natural because the commission formula for each salesperson should always refer to those same cells containing $50,000 ($B$26), 4% ($B$25), and 3% ($B$24).
Relative/Absolute Cell Addressing Rules
1. If you do not copy the formula then make all
cell addresses relative cell addresses.
2. If you do copy the formula then, for each cell
address in the formula, you have to
determine if the cell address should be a
relative cell address or an absolute cell
address.
Salesperson’s Evaluation Formula
• The information needed to design this formula is found in the Salesperson’s Evaluation area of Question 2 under the Salary section of Practice Excel Exam 1.
• The formula calculates the salesperson’s evaluation for the salesperson in row 4, with the formula typed in cell F4.
• But, design the formula general enough such that it can correctly determine the salesperson’s evaluation for any salesperson.
• That way, one can just copy the formula to rows 5 through 17 in column F for the other salespeople without having to alter or fix the salesperson’s evaluation formula for any of the other salespeople.
Salesperson’s Evaluation Formula
• In this situation, use the vlookup function.
• Vlookup stands for “vertical lookup.”
• Given a “vertical lookup” table and a “lookup”
value, the vlookup function determines which
row the lookup value fits within the lookup table.
• From within that row, vlookup returns the value
located in the column specified in the function’s
third parameter.
Salesperson’s Evaluation Formula
=vlookup( , , )
Lookup Value Parameter
B4
• The lookup value is the salesperson’s Total Sales, in cell B4. • Go to the next slide.
Salesperson’s Evaluation Formula
=vlookup( , , )
Lookup Value Parameter
B4
Table Array Parameter
$E$24
$E$24
Salesperson’s Evaluation Formula
=vlookup( , , )
Lookup Value Parameter
B4
Table Array Parameter
$E$24
$F$27
: $F$27
Salesperson’s Evaluation Formula
=vlookup( , , )
Lookup Value Parameter
B4
Table Array Parameter
$E$24
• The lookup table is given within the rectangular cell range: E24 through F27, E24:F27.
• E24 is the upper left corner cell of the table. • F27 is the lower right corner cell of the table. • Go to the next slide.
Salesperson’s Evaluation Formula
=vlookup( , , )
Lookup Value Parameter
B4
Table Array Parameter Column Index Number Parameter
$E$24 : $F$27 2
Column 1 Column 2
An Example of How Vlookup Works
• B4 ($98,000) contains the Total Sales for John Adams.
• Row 1 - Vlookup determines if $98,000 ≥ $0 and $98,000 < $25,000. But, the
answer is false.
• Row 2 - vlookup determines if $98,000 ≥ $25,000 and $98,000 < $50,000. But, the
answer is false.
• Row 3 - vlookup determines if $98,000 ≥ $50,000 and $98,000 < $75,000. But, the
answer is false.
• Row 4 - vlookup determines if $98,000 ≥ $75,000. The answer is true.
• Vlookup returns the value from column 2 of row 4.
• The value returned by vlookup in this case is the text “Excellent”.
• Go to the next slide.
PMT Function
• The information needed to design this formula is found in the PMT section of Practice Excel Exam 1.
• PMT stands for “payment.”
• Calculate the payment amount for a loan at a particular interest rate and length of time to repay the loan.
• Before creating your formula, you need to know how many payments are made per year.
Monthly payments – 12 per year. Quarterly payments – 4 per year. Semiannual payments – 2 per year.
PMT Function
=pmt( , , )
• Rate parameter – interest rate per period (payment.)
• Nper parameter – total number of payments for the loan. • PV parameter – present value of the loan.
• Go to the next slide.
A PMT Example
• Loan amount – $10,000 is in cell B1.
• Yearly interest rate – 5% is in cell B2.
• Term of loan (years) – 2 in cell B3.
• Compute the monthly payment for this loan.
=pmt( , , ) B2/12 B3*12 -B1
