excel project

User Generated

fnenu2016

Computer Science

Description

read the guidelines and do the excel make sure to follow instructions.... . . . . . . . . . . .

Unformatted Attachment Preview

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
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

Hi! Here is your file. Take a look, if you need changes contact me.

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
...


Anonymous
Really helpful material, saved me a great deal of time.

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Similar Content

Related Tags