Guided Project 6-3
USING MICROSOFT EXCEL 2016
Guided Project 6-3
The Wear-Ever Shoes company maintains inventory data and customer satisfaction survey results in your
workbook. You use Lookup & Reference, Database, and Logical functions to complete the data. You also use a
Financial function to calculate depreciation and a Text function to enter email addresses.
Skills Covered in This Project
•
•
•
Step 1:
Download
start file
•
•
•
Nest the INDEX and MATCH functions.
Use SUMIFS from the Math & Trig category.
Use DAVERAGE.
Create an IFS formula.
Use a Text function to concatenate text strings.
Calculate depreciation with the DB function.
1. Open the WearEverShoes-06 start file. Click the Enable Editing button. The file will be renamed
automatically to include your name. Change the project file name if directed to do so by your
instructor, and save it.
2. Click the Inventory sheet tab.
3. Select cells A3:I39, click the Name box, type Inventory as the range name, and press Enter.
4. Select cell L5 and type WE006.
5. Create a nested function with INDEX and MATCH to display inventory for a product.
a. Select cell L6.
b. Click the Lookup & Reference button [Formulas tab, Function Library group] and choose
INDEX. Select the first argument list array, row_num, column_num and click OK.
c. For the Array argument, type Inventory.
d. Click the Row_num box and click the Name box arrow. Choose MATCH in the list or choose
More Functions to find and select MATCH. The INDEX function uses this MATCH statement to
find the row.
e. Click cell L5 for the Lookup_value argument.
f. Click the Lookup_array box and select cells A3:A39. This MATCH function finds the row that
matches cell L5 in column A.
g. Click the Match_type argument and type 0.
h. Click INDEX in the Formula bar.
i. Click the Column_num argument, click the Name box arrow, and choose MATCH (Figure 6-91).
=INDEX(lnvent ory,MATCH (l5,!>.3:A39,0),MATCH ("quant ity" ,A3:13,0))
L6
A
...J
l
we a r - ~
2
September 3
3
Prod u ct ID
4
WE001
5
WE002
C
I
D
1
1
I
G
I
H
Function Arguments
I
I
X
MATCH
Lookup_Yi lue
Lookup_array
Match_type
I· quantity"
1~1
I:=:::::::::::::===~::;:::::
AJH
IF!!.I
~l~===~::;;;
o________~l~~=I
· quantity"
(" Produd ID", "PY.o dud", "Colo r -, "Size: ',·
Wom en·s·,·cost·,"Retail", "Reorderl
0
I--W_E_o_o=3
Returns the re lative po@ o n of an item in an a,ray that matches a specified va lue in a specified order.
7 W E004
M atch_type i s a number 1, 0, or -1 indicating w hich value to return ,
8 WEoo5
9 WEoo6
10 WEoo7
W-Eo-0~8- - -----< Formu la result "'
1-1+
12 WEoog
Help on this function
13 WE010
4
~- +
:-::-:-:------:f:~-:-:-- ~~:-:-:~-:-~=~-!-~-:-::--f--:~-,:-:: I 7~5
- - ----i
!order.
N
y
Product ID
N
Quantity
WEoo6
N
N
y
N
y
Criteria
:f-~--f-:-:---1: :~; ~ (:!~;:f·~
Y
In Stock
Blac k, Size 8
Blac k, Size g
Men's, Brown
6-91 MATCH Is nested twice
j.
Type quantity in the Lookup_value box.
Excel 2016 Chapter 6 Exploring the Function Library
Last Updated: 12/27/17 Page 1
Guided Project 6-3
USING MICROSOFT EXCEL 2016
k.
l.
Click the Lookup_array box and select cells A3:I3. This MATCH function finds the cell in the
“Quantity” column after the row is located by the first MATCH function.
Click the Match_type box and type 0. The formula is
=INDEX(Inventory,MATCH(L5,A3:A39,0),MATCH(“quantity”,A3:I3,0)).
m. Click OK. The result is 2.
n. Click cell L5, type WE010, and press Enter. The quantity is updated.
6. Use SUMIFS to calculate total pairs in stock by specific criteria.
a. Select cell L13.
b. Click the Math & Trig button [Formulas tab, Function Library group] and choose SUMIFS.
c. Select cells E4:E39 for the Sum_range argument and press F4 to add the absolute reference.
Important: Be sure that to add the absolute reference to both the start cell and the end cell
in the range.
d. Click the Criteria_range1 box, select cells C4:C39, the Color field, and press F4.
e. Click the Criteria1 box and type bla*.
f. Click the Criteria_range2 box, select cells D4:D39, and press F4.
g. Click the Criteria2 box and type 8. This criteria specifies the number of black pairs, size 8
(Figure 6-92).
T
A
4 WE001
WE002
WE003
7 WEoo4
8 WEoo,;
- - ·-·-·
9 WEoo6
10 WE007
11 WEoo8
12 WEoo~
13 WE010
14 WE011
15 WE012
16_ WE013
17 WE014
18 WE015
19 WE016
20 WE017
21 WE018
5
6
X
"'
f,,
=SUM IFS($E$4:$E$39, $C$4:$C$39,"bla'",$D$4:$D$39,8)
B
I
C
D
E
F
G
I
H
J
,;
Ru91i_ed _HildnQ Boots
!Brown ! 8 I
I M
I si~~tsqo.ool N I
,
,n I
I~ "
·-- __ ,
I
V
I
Ru.!1.9..~ ~ u a.:-- c--•·
?
X
Rugg_e Function Arguments
Rug~ SUMIFS
R.~g_1i_e
Sum_~ng~ (_s__
{S;3;6;4;4;2;3;2;4;1;4;1;2;3;4;0;2;3;1;2; ...
E.S4:SE539
I~
"
Ru_!j_9..e
f Brown"tBrown·;·e1ack:;· e1ack·;· e1 .. ,
Oittrb_r11nge1 ISCS4:SCS39
\~
Rug9_e
Oitel'Y1
'1,!jj] "bla ..
Rug9e
Criteria_range2 ISDS4:SDS39
{8;10;9.S;10.S;7,S;8;9;8,S;8.S;9;7.S;8;7, S
'1~
Comfy
Criteria2
8
Comfy
"
Comfy
7
Add
s
the
cells
sptcified
by
a
given
set
of
conditions
or
criteria.
Comfy
Criteria2: is the condition or criteriai in the form of a number, exp ression, or text
Comfy
that defines which cells wil l be added.
Comfy
Lazy__F
Lazy_f formulai resutt "' 7
Lazy.£ Hel~ on this function
Cancel
OK
Lazyf
J0--···-'
l'bl•··
18
...
.
.
.
.
I~ .
.
I
K
Product ID
Quantity
Criteria
Black, Size 8
Black, Size 9
'
L
WE010
1
In Stock
>s~q,8)
Men 1s1 Brown
I
6-92 SUMIFS to calculate number by color and size
h. Click OK. The result is 7.
i. Select cell L14 and use SUMIFS to sum black, size 9 shoes in stock.
j. Select cell L15 and use SUMIFS to sum men’s brown shoes in stock.
7. Click the Satisfaction Survey worksheet tab and review the data.
8. Select cells A4:H40 and name the range as Survey.
9. Use DAVERAGE to summarize customer survey data.
a. Click the Average Ratings worksheet tab and select cell C5 to calculate an average
comfort rating for the Rugged Hiking Boots.
b. Click the Criteria sheet tab.
c. Select cell B2 and type rug*.
d. Click the Average Ratings sheet tab and verify that cell C5 is selected.
e. Click the Insert Function button [Formulas tab, Function Library group].
Excel 2016 Chapter 6 Exploring the Function Library
Last Updated: 12/27/17 Page 2
Guided Project 6-3
USING MICROSOFT EXCEL 2016
Choose Database in the Or
select a category list.
g. Select DAVERAGE and click
OK.
h. Type Survey for the Database
f.
i.
argument.
Click the Field box and type
comfort.
,-
l
~
A
B
I
I
I
.,,
Ix
C
I
=DAVERAGE(Survey,"comfort",Oiteria !$8$1:$8$2)
D
E
F
Size
Quant ity
Women•s
I
I
I
G
H
I
Cost
Ret ail
Reorder
M2n 's or
Product 10: Product : Color
-
~
4
X
I
~':!£_':_ ___i
Function Arguments
7
X
Product I DAVEP.AGE
(_
Database
~
[survey
Field l·comf=9 Value_if_true argument of $J$5, and Value_if_false argument of 0.
b. Click the Logical_test1 argument, select cell G5, and type >=9.
Excel 2016 Chapter 6 Exploring the Function Library
Last Updated: 12/27/17 Page 3
USING MICROSOFT EXCEL 2016
Guided Project 6-3
c. Click the Value_if_true1 box, click cell J5, and press F4 (Figure 6-94).
INDEX
C
2
I
D
I
E
~
X
~
I
F
ix
;lfS(GS>a9, $J$5,
G
I
I
H
I
J
I
I
I
~yle
4
Comfort
Fit
Style
Value
8.625
8.75
8.5
5.333333
3.3
7.5
5.5
6.5
6.313333
8
7.75
7.5
4.9
6.666667
9
9.166667
6
7
5
9.5
~
I
Overall
Above Average
I
I
9 and above
7 and above
Logica l_test1
ll_
Log ica l_test2
13
Value_if_true2
X
?
IFS
Value_if_true1
I
Ratings
Excellent
Fun ction Argu ments
~
11
-
Rating
8.40625 ;5, ;9,sJs5,
6.666667 Average
8.833333
L
I
t
3
5
K
[~I .
1~I .
1~I .
1~ I .
IG5> • 9
ISJS,
11
I
FALSE
· Excel lent·
logical
any
1~
~
~
17
.
Checks whether one or mo re cond it ions are met and returns a value co rre spond ing to the first TRUE
con dition .
~
Logical_test2:
is any value o r express ion that can be evaluat ed t o TRUE or FALSE.
19
20
-
21
Formul a re su lt=
22
~
24
I
Help on this function
I
OK
[
Ca ncel
7
I
6-94 First logical test and result for IFS
d. Click the Logical_test2 box, click cell G5, and type >= 7.
e. Click the Value_if_true2
box, click cell J6, and
press F4.
f. Click the Logical_test3
box, click cell G5, and
type >= 5.
g. Click the down scroll
arrow to reveal the
Value_if_true3 box, click
cell J7, and press F4.
h. Click the down scroll
arrow to reveal the
Logical_test4 box, click
cell G5, and type =9,$J$5,G5>=7, $J$6,G5>;5,$J$7,G5 ; 5
ISJS7
Log ica l_t est4 ! GS :ia: • .•••••.•••••.••••. l!lJi9i;ldi-llltln_gB-ait. •••.••. B""CWI ••1.S
•. , ~ ................. COml\'W"Jlb, 9 S ti ca;-;. ••••. B""CWI ••. 1..L
•eou ··················· ~Wiitti~l s"tiaai··
/ltloo.--!:~•Ul!IIZ
Mnq,
""°'
_1.0..s:_ .... ,. __________ _M, __ __ __ s!Ji, __ s;io.!XI __________ _
• .'E~ ................... .Ai:,q~i:id l-llldn;tB-mt. ,...... B:l,di: •••. f-i ..... :. .. ,.. ..... W ....... ' ,.S;!;. ··""·CCI ••. • H .••
_ 'E-:>,S. · ·-----------·----- .111.oq~iv.:IMl10n9S-co1: •••.•.. El~~t . __ E: _________ ___________ w ______ 1~
__ s~ .co ____ "I' ___ _
·!···
Eiirt .. ··· s ··· ·····1·· ··· ······,~r····· ·1Q:f ··MSSAO ·· ··"'· ··
fil,ipe
JJ
2
W
lQ
5CS-OO •••• tf ••••
(,orrrfyWalbfloS h~ ·~ ..... &Clf41 _ •.• 8 ........ J. .......... W...... !t,7,5 ••. 915(.-DO
r>tflpfb_e~ .............. Pirlt .. .. 6 ...... c. ...........W ..... . '!1· . . n.c..CICJ
... !YflipfbF .............. Pint ••• •.•1 ... ..... a ..... ..... w...... ?1: .. nt,.CICJ
,~ fW-Fb~ .... .......... f"ffl: .. ..•.. ...... 2 .••••••••• . w ..... . .,. .. n:,.ai
... ,eyFl;;i-fbp, ............. 111--ra:._ •.• 6 ........ J. .......... W.. ....?1: .. nt,.'IJIJ
.."J f ~ -!="bi;:- .... .......... ~ . ... 7 ...... 1 . ......... . w ........SJ.:. .. :Jlt,.0a
l:i,,:!)' f lO;fb p .............. I~
••.• 9 ........ 2 .......... W........ 51; •• :J-1'.00
l:l~ f k,-!= bi;:- .............. ~ .. 9 ...... 2..... ...... M ..... . S1· .. 1..u,.oa
..'Eo~ .................. 'L.:q::i•.flpfbp .............. ~ •... 5 .•..••.. 4. .......... Mi ...... •• ST: •. ll4·00
.!~~l.. ................. .. ,=1l.flP.:f.~F................ ~~. .. ~ ...... 2.......... J~......... 11· . Ut,.0CI
.. 6::-14 .................. u ,~ .J:lip~ bp. ....... ....... ~
. u. ..... :;i . . . . . . . . . . M.••.•..• 57: .. H4·0CI
• 'E:>15. •.••••.•••••.••••. SAno:.i:ty Ta i s.:.:,i:; ...•.•. ~.~'!;t . .. A ..
.a
••. ~•••.••••~~
1sa.i:o
E:>25
•.•. Sano·-1.tr T; I e.:ot; . ..... ~~ .....~.L
..........~ ...... ~¥.: .. ~~:~
•.'6ol.\ ...................
. 'D:JlL ..................
•.'6otofi ...................
. '6ol.l'. ................ ....
••'6c:ii:s, ...................
\ 'Eo~-, ...... .. ..........
•. 1£o;:,;:, ..................
. 'Eo u . ..................
····" · ···
•••• H... .
••.• H ...
.... " ... .
.... H ...
.... H ... .
••••" ••••
.... 'y ••••
.... N' .••
... .'Y..
.. .. '( ..•
•••• "i ..•.
.:~ . . . . . . . - · :~~~:=~~-- ··-::-·
~:·· ==- -~-·~f." ~·:.~."... ..... ..........
• 'Eo29 •.•••••••••••.••••
set1oostt r- • Brots ..•....
j~ ...... ;.
~
. : -'. ...
:
w •..... S42- •.• s&:1.00 •••• "
••••
Wear-Ever Shoes
Asset Depreciation for Packaging Equipment
Icost
Wear-Ever Shoe s
Em pl oyee E- IVlall List
First Name
Last Name
Ema11 Addre5-5es
Vircent
Bowman
Vincent Bowman@weshoes.ora
Louis
White
Lou isWh ite@weshoes .ora
Patricia
Lynwood
Pai ne ia l ynwood@,11 oshoos. o rq
DrE!w
Cron in
Dro...Cronin@wes hoes.org
I
I
81
I
I
I
cf Equipm~nt
ISalvaqe Va lue
ILife in Years
$175,000
Sll,000
Amount
Year
1
$39, 9 00.00 ;
. . . . . . ....L. . . · . . . :. . . . . . . . . . . . . .:;::i~tm
.. .................. 5... .............. ....:................................. S>4/l"J2 ,l 6.!
6
· · ·· · ·· ·····c
·····....... ...
r···· ·············:!:~~~}~·!
Tot al
... .. ....,.. .. ....
$10,941 .0 5 :
.. .. .$1:5~,9~':: ':5.i
6-98 Completed worksheets for Excel 6-3
Excel 2016 Chapter 6 Exploring the Function Library
Last Updated: 12/27/17 Page 6
Wear-Ever Inventory
September 30
Product ID
WE001
WE002
WE003
WE004
WE005
WE006
WE007
WE008
WE009
WE010
WE011
WE012
WE013
WE014
WE015
WE016
WE017
WE018
WE019
WE020
WE021
WE022
WE023
WE024
WE025
WE026
WE027
Product
Rugged Hiking Boots
Rugged Hiking Boots
Rugged Hiking Boots
Rugged Hiking Boots
Rugged Hiking Boots
Rugged Hiking Boots
Rugged Hiking Boots
Rugged Hiking Boots
Comfy Walking Shoes
Comfy Walking Shoes
Comfy Walking Shoes
Comfy Walking Shoes
Comfy Walking Shoes
Comfy Walking Shoes
Lazy Flip-Flops
Lazy Flip-Flops
Lazy Flip-Flops
Lazy Flip-Flops
Lazy Flip-Flops
Lazy Flip-Flops
Lazy Flip-Flops
Lazy Flip-Flops
Lazy Flip-Flops
Lazy Flip-Flops
Seriously Tall Boots
Seriously Tall Boots
Seriously Tall Boots
Color
Size
Brown
Brown
Black
Black
Black
Black
Brown
Brown
Brown
Black
Navy
Black
Taupe
Brown
Pink
Pink
Pink
White
White
White
Brown
Brown
Brown
Brown
Black
Black
Black
8
10
9.5
10.5
7.5
8
9
8.5
8.5
9
7.5
8
7.5
8
6
7
8
6
7
8
8
9
10
11
6
6.5
7
Men's or
Quantity Women's
5
3
6
4
4
2
3
2
4
1
4
1
2
3
4
0
2
3
1
2
2
4
2
2
0
0
1
M
M
M
M
W
W
W
W
M
M
W
W
W
W
W
W
W
W
W
W
M
M
M
M
W
W
W
Cost
Retail Reorder
$46.50
$46.50
$46.50
$46.50
$53.50
$53.50
$53.50
$53.50
$47.50
$47.50
$47.50
$47.50
$47.50
$47.50
$7.50
$7.50
$7.50
$7.50
$7.50
$7.50
$7.50
$7.50
$7.50
$7.50
$42.50
$42.50
$42.50
$90.00
$90.00
$90.00
$90.00
$98.00
$98.00
$98.00
$98.00
$65.00
$65.00
$65.00
$65.00
$65.00
$65.00
$14.00
$14.00
$14.00
$14.00
$14.00
$14.00
$14.00
$14.00
$14.00
$14.00
$80.00
$80.00
$80.00
N
Y
N
N
N
Y
N
Y
N
Y
N
Y
N
Y
N
N
Y
N
N
Y
Y
N
Y
Y
Y
Y
Y
Product ID
Quantity
Criteria
Black, Size 8
Black, Size 9
Men's, Brown
WE028
WE029
WE030
WE031
WE032
WE033
WE034
WE035
WE036
Seriously Tall Boots
Seriously Tall Boots
Seriously Tall Boots
Glide Running Shoes
Glide Running Shoes
Glide Running Shoes
Glide Running Shoes
Glide Running Shoes
Glide Running Shoes
Black
Black
Black
White
White
White
Black
Black
Black
7.5
8
8.5
8
9
10
8
9
10
0
2
1
6
6
6
2
3
1
W
W
W
M
M
M
M
M
M
$42.50
$42.50
$42.50
$48.00
$48.00
$48.00
$48.00
$48.00
$48.00
$80.00
$80.00
$80.00
$75.00
$75.00
$75.00
$75.00
$75.00
$75.00
Y
Y
Y
N
N
N
N
N
Y
In Stock
Wear-Ever Shoes
Customer Satisfaction Survey
Product ID
WE001
WE002
WE003
WE004
WE005
WE006
WE007
WE008
WE009
WE010
WE011
WE012
WE013
WE014
WE015
WE016
WE017
WE018
WE019
WE020
WE021
WE022
WE023
WE024
WE025
WE026
Product
Rugged Hiking Boots
Rugged Hiking Boots
Rugged Hiking Boots
Rugged Hiking Boots
Rugged Hiking Boots
Rugged Hiking Boots
Rugged Hiking Boots
Rugged Hiking Boots
Comfy Walking Shoes
Comfy Walking Shoes
Comfy Walking Shoes
Comfy Walking Shoes
Comfy Walking Shoes
Comfy Walking Shoes
Lazy Flip-Flops
Lazy Flip-Flops
Lazy Flip-Flops
Lazy Flip-Flops
Lazy Flip-Flops
Lazy Flip-Flops
Lazy Flip-Flops
Lazy Flip-Flops
Lazy Flip-Flops
Lazy Flip-Flops
Seriously Tall Boots
Seriously Tall Boots
Color
Size
Comfort
Fit
Style
Brown
Brown
Black
Black
Black
Black
Brown
Brown
Brown
Black
Navy
Black
Taupe
Brown
Pink
Pink
Pink
White
White
White
Brown
Brown
Brown
Brown
Black
Black
8
10
9.5
10.5
7.5
8
9
8.5
8.5
9
7.5
8
7.5
8
6
7
8
6
7
8
8
9
10
11
6
6.5
7
8
8
6
8
7
9
9
8
10
9
7
8
3
10
3
3
3
6
1
3
2
10
8
4
10
9
8
9
9
8
9
9
8
3
10
3
5
8
3
1
1
4
7
9
4
1
3
1
2
2
7
7
10
8
9
10
9
8
9
8
8
8
10
3
8
4
9
1
1
5
9
10
9
1
6
8
7
Value
7
8
9
10
9
10
8
7
6
9
4
6
7
6
2
8
3
4
6
7
10
4
2
10
9
3
WE027
WE028
WE029
WE030
WE031
WE032
WE033
WE034
WE035
WE036
Seriously Tall Boots
Seriously Tall Boots
Seriously Tall Boots
Seriously Tall Boots
Glide Running Shoes
Glide Running Shoes
Glide Running Shoes
Glide Running Shoes
Glide Running Shoes
Glide Running Shoes
Black
Black
Black
Black
White
White
White
Black
Black
Black
7
7.5
8
8.5
8
9
10
8
9
10
2
4
10
10
9
10
9
9
8
10
5
4
3
9
9
9
10
9
10
10
10
10
2
2
9
9
8
8
10
9
10
10
9
10
9
8
9
9
10
9
Wear-Ever Shoes
Average Ratings by Shoe Style
Product ID
WE001
WE009
WE015
WE025
WE031
Product
Rugged Hiking Boots
Comfy Walking Shoes
Lazy Flip-Flops
Seriously Tall Boots
Glide Running Shoes
Comfort
Fit
Style
Value
Overall
Rating
Ratings
Excellent
Above Average
Average
Poor
Ratings
9 and above
7 and above
5 and above
Below 5
Product ID Product
Product ID Product
Product ID Product
Product ID Product
Product ID Product
Color
Color
Color
Color
Color
Size
Size
Size
Size
Size
Quantity
Men's or
Women's
Cost
Retail
Reorder
Quantity
Men's or
Women's
Cost
Retail
Reorder
Quantity
Men's or
Women's
Cost
Retail
Reorder
Quantity
Men's or
Women's
Cost
Retail
Reorder
Quantity
Men's or
Women's
Cost
Retail
Reorder
Wear-Ever Shoes
Asset Depreciation for Packaging Equipment
Cost of Equipment
Salvage Value
Life in Years
$175,000
$22,000
8
Year
1
2
3
4
5
6
7
8
Amount
Total
Purchase answer to see full
attachment