Name: swapnika suthrave By checking the Declaration box to the
Student #: 4369491 understand it is an offence to submit an
Instructions
1. Format the data in the Inventory sheet in an appealing way using a Table. This should
a. Include a Total Row.
b. In the Total Row, include the sum of the Quantity column and the average
2. Create a Lookup worksheet.
3. On the Lookup worksheet, create an interface that allows a user to input an item numb
a. Product
b. Manufacturer
c. Description
d. Manufacture Year
e. Cost
f. Quantity
4. Add Named Ranges of the following:
a. name B2:B60 Products
b. name C2:C60 Brands
c. name D2:D60 Details
d. name E2:E60 Years
e. name F2:F60 Costs
f. name G2:G60 Amounts
5. Put a statistics section in the Lookup tab that calculates the min, max, and average cos
6. Insert another column in your Inventory table called Order that will conditionally state
a. If the quantity of that item is less than 3, the Order column should state Yes
b. If the quantity of that item is greater than or equal to 3, the Order column s
c. In the Lookup tab, include a formula that determines the number of differen
Status Continue worki
ration box to the right, I am declaring that the work I am submitting is my own and that I
ence to submit another student's work.
Instructions
Table. This should be unique and not similar to someone else. Customize it.
mn and the average of the Cost column.
nput an item number with the following output describing the item:
x, and average costs using the appropriate named range.
conditionally state whether to order an item.
mn should state Yes.
he Order column should state No.
number of different item numbers that need to be ordered.
Continue working through this assessment.
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
Item Number Product Manufacturer Description Stock Year
11498 Mouse Ctech Wired 2020
93402 Mouse Ctech Wired 2019
39040 Mouse AccessoriesInc Wired 2020
21766 Mouse AccessoriesInc Wireless 2021
64372 Mouse Integral Wireless 2020
70357 Mouse Integral Wired 2021
36479 Mouse Integral Wired 2021
98392 Keyboard Ctech Wireless 2018
69640 Keyboard Ctech Wireless 2019
27644 Keyboard AccessoriesInc Wired 2018
49345 Keyboard AccessoriesInc Wired 2019
81292 Keyboard AccessoriesInc Wireless 2020
21549 Keyboard Integral Wireless 2021
14390 Tower Integral i5 2020
18432 Tower Integral i5 2021
40331 Tower Integral 5500 2021
69636 Tower Ctech 3100 2021
49696 Monitor Ctech 27" 2020
14766 Monitor AccessoriesInc 27" 2020
24736 Monitor Integral 31" 2021
41076 Monitor Integral 31" 2020
20967 Speakers Newtech Wired 2019
49681 Speakers AccessoriesInc Wired 2019
62943 Speakers AccessoriesInc Wired 2018
27932 Speakers Newtech Wired 2021
51274 Mic Newtech Wired 2019
30997 Mic AccessoriesInc Wired 2019
15391 Blank Paper Stock Essentials White 2020
37649 Lined Paper Stock Essentials White 2021
54392 Grid Paper Stock Essentials White 2022
80767 Ball Point Pens Vibrant Blue 2022
96557 Ball Point Pens Vibrant Black 2021
82546 Gel Pens Vibrant Blue 2021
27766 Gel Pens Vibrant Black 2022
53683 Gel Pens Vibrant Red 2022
74835 Felt Tip Pens Artistic Plus Black 2023
17487 Felt Tip Pens Artistic Plus Green 2022
78268 Clear Ruler Stock Essentials Clear 2023
47964 Solid Ruler Stock Essentials Black 2023
74137 Solid Ruler Stock Essentials Purple 2023
92183 Stapler Stock Essentials Black 2022
67346 Stapler Offex Black 2022
69954 Stapler Offex Black 2022
94588 Staples Stock Essentials Silver 2021
94614 Staples Stock Essentials Red 2020
80505 Staples Offex Silver 2023
22183 Staples Offex Black 2021
96658 3 Hole Punch Supplize Silver 2021
61086 3 Hole Punch Offex Gray 2021
36727 1 Hole Punch Supplize Silver 2022
77874 1 Hole Punch Offex Red 2023
97739 Desk Chair ErgoDen Gray 2023
39507 Desk Chair ErgoDen Black 2023
82000 Desk Chair Comfort+ Black 2022
38321 Desk Chair Comfort+ Gray 2023
60093 Office Desk ErgoDen Brown 2022
41467 Office Desk Comfort+ Maple 2023
95957 Office Desk Official Black 2022
75353 Office Desk ErgoDen Chestnut 2022
Total
Cost Quantity Order
$ 49.99 4 NO
$ 44.99 5 NO
$ 25.99 3 NO
$ 29.99 10 NO
$ 69.99 2 YES
$ 75.99 1 YES
$ 79.99 1 YES
$ 45.99 6 NO
$ 54.99 2 YES
$ 34.99 4 NO
$ 39.99 5 NO
$ 44.99 11 NO
$ 59.99 4 NO
$ 499.99 1 YES
$ 549.99 2 YES
$ 649.99 1 YES
$ 499.99 1 YES
$ 109.99 2 YES
$ 149.99 2 YES
$ 199.99 5 NO
$ 249.99 3 NO
$ 39.99 3 NO
$ 29.99 2 YES
$ 24.99 1 YES
$ 45.99 1 YES
$ 19.99 2 YES
$ 24.99 1 YES
$ 4.00 35 NO
$ 4.00 19 NO
$ 4.00 10 NO
$ 2.25 44 NO
$ 2.25 24 NO
$ 1.99 27 NO
$ 1.99 13 NO
$ 1.99 21 NO
$ 3.50 15 NO
$ 3.50 6 NO
$ 1.50 8 NO
$ 1.25 8 NO
$ 1.25 15 NO
$ 20.00 7 NO
$ 29.99 5 NO
$ 39.99 3 NO
$ 3.00 11 NO
$ 3.50 10 NO
$ 4.50 18 NO
$ 5.50 4 NO
$ 15.00 10 NO
$ 10.00 3 NO
$ 4.00 3 NO
$ 3.00 4 NO
$ 25.00 6 NO
$ 32.00 10 NO
$ 38.00 12 NO
$ 28.00 9 NO
$ 50.00 3 NO
$ 46.00 2 YES
$ 51.00 2 YES
$ 39.00 4 NO
$ 71.77 456
Feel free to change the look or placement of anything here.
Lookup
Item Number Product Manufacturer Description Stock Year Cost Quantity
82000 Desk Chair Comfort+ Black 2022 $38.00 12
Statistics
Min Cost $1.25
Max Cost $649.99
Average Cost $71.77
# of Orders 17