Advanced Microcomputer Applications , computer science homework help

Week 4 Microsoft Excel Assignment – “Plants”

Do Project 2E Plants that begins on page 470 of your textbook. Submit the document by clicking the Attachments button to attach the file. Do not paste the document in the Message area.

You will need to download and use the following data file to complete your assignment:

Plants Spreadsheet

In addition to the assignment file, answer the following question with your submission. This can be answered directly in your Submission comment area, or as a separate Word attachment file:

In this Week’s reading and prep work, you learned about different ways to format the contents of cells in Excel. This was shown the through the use of such as features as Merge & Center, Cell Styles, and Themes. Which of these do you personally find most useful and why? Do you have any prior experience using the selected feature of Excel? If so, describe how. Please look to answer this in at least 3 complete sentences in order to address it adequately. Pasadena Facility: Inventory Status of Plants
As of September 30
Total Items in Stock
Average Price
Median Price
Lowest Price
Highest Price
Specialty Plant Types
Specialty Quantity in Stock
Quantity in Stock
95
37
58
78
93
27
78
28
24
26
56
24
49
65
175
18
28
19
56
135
45
36
52
49
23
150
42
32
52
Item #/Category
1123-Edibles
1267-Container Plants
1843-Bonsai
1852-Specialty
1862-Container Plants
2132-Edibles
2157-Specialty
2158-Specialty
2176-Container Plants
2201-Edibles
2202-Edibles
2202-Vine Roses
2219-Vine Roses
2223-Vine Roses
2266-Vine Roses
2268-Specialty
2278-Container Plants
2308-Container Plants
2317-Vine Roses
2356-Vine Roses
2364-Edibles
2402-Edibles
2404-Vine Roses
2428-Edibles
2437-Specialty
2439-Edibles
2543-Container Plants
2580-Specialty
2582-Container Plants
Item Name
Macadamia Nut
Clematis Hank
Fragrant Plum
Banana Cream
Minny Pearl
September Raspberry
Elite Gold Pineapple
Salzia Glue
Strawberry Sweet
Dwarf Banana
Top Hat Blueberry
Magid Hybrid
Sunshine Dream
Purple Splash
Wing Ding
Tetrapleed Breeder
Blanket Flower
Cinnamon Tree
Pope Benedict
Black Pie
Super Dwarf Tomato
Dwarf Raspberry
Hot Chocolate
Elite Pineapple
Cashew Tree
Holiday Assortment
Lavandula
New Dimension
Chandler Blueberry
Retail Price
15.42
28.95
24.95
13.95
16.95
7.58
12.65
15.95
6.59
18.59
18.95
11.35
16.57
25.95
45.95
54.95
24.95
16.58
11.35
26.95
34.95
28.95
10.59
38.95
26.95
34.95
14.95
31.95
18.65
Size
24″
42″
10″
14″
15″
12″
16″
12″
6″
24″
18″
14″
24″
14″
41″
24″
16″
12″
16″
11″
36″
28″
18″
28″
33″
35″
12″
26″
24″
Special Feature
Fall Color
Fall Color
Showy
Pollenizer
Showy
Fall Color
Easy Care
Fall Color
Showy
Easy Care
Easy Care
Pollenizer
Fall Color
Showy
Fall Color
Fall Color
Easy Care
Easy Care
Pollenizer
Fall Color
Easy Care
Easy Care
Pollenizer
Easy Care
Easy Care
Pollenizer
Showy
Pollenizer
Easy Care
CONTENT BASED ASSESSMENTS (MASTERY AND TRANSFER OF LEARNING)
MyITLab
Mastering Excel Project 2E Plants
In the following project, you will edit a worksheet detailing the current inventory of
plants at the Pasadena facility. Your completed worksheet will look similar to Figure 2.57.
Apply 24 skills from
these Obyectives
PROJECT FILES
For Project 2E, you will need the following file:
CO2E Plants
the SUM AVERAGE
MIEONAN AN and
A64X Punto
Alone Data Resore
Error Messages and
Totate Text
You will save your workbook as:
Lastname Firstname 2E Plants
R COUNTIF and If
actions and App
ditional Formatting
PROJECT RESULTS
Date & Time
ons and Freeze
Stock Lovet
Order
Retol Price
ok
Category
Specialty
Ecodes
Potio Roses
Special Feature
Fali Color
Eosy Core
Fall Color
Mem Mer Name
18 2268 Tetrapleed Breeder
56 2202 Top Hot Blueberry
175
2266 Wing Ding
Stre
54.95 24″
18.95 18
45.95 41″
Quanny in Stock
Fort and Filter
Table
nat, and Print
rksheet
Pasadena Facility: Inventory Status of Plants
As of September 30
Total Items in Stock
Average Price
Medfan Price
Lowest Price
Highest Price
1.650
$22.62
$18.65
$ 6.59
$54.95
Statistics
Specialty Plant Types
Specialty Quantity in Stock
6
257
Quantity in Stock
Item# Hem Name
78 1852 Bonano Cream
135
2356 Block Ple
28 2278 Blanket Flower
23
2437 Cashew Tree
52 2582 Chander Blueberry
19 2308 Cinnamon Tree
37 1267 Clematis Hank
26 2201 Dwarf Barona
36 2402 Dworf Raspbeny
78 2157 Elite Gold Pineapple
49 2428 Elite Pineapple
58 1843 Fragrant Pium
150
2439 Hofday Assortment
52 2404 Hot Chocolate
42 2543 Lavandula
95 1123 Mocadamia Nut
24 2202 Magid Hybrid
93 1862 Minny Pearl
32 2580 New Dimension
56 2317
Pope Benedict
65 2723 Purple Splash
28 2159 Soka Glue
27 2132 September Raspbery
2176 Strowbery Sweet
2219 Sunshine Dream
2364 Super Dwarf Tomato
Retail Price
Stre
13.95 14
26.95 11″
24.95 16
26.95 33
18.65 24
16.58 IZ
28.95 42
18.59 24
28.95 28
12.65 16
38.95 28
24.95 10
34.95 35
10.59 18
14.95 12
15.42 24
11.35 14
16.95 15
31.95 26
11.35 16
25.95 14
1595 IZ
7.58 IZ
6.59 6
1657 24
34.95 36
Special Feature
Pollenizer
Fall Color
Easy Care
Easy Care
Easy Care
Easy Care
Foll Color
Easy Care
Easy Care
Easy Care
Easy Cae
Showy
Pollenizer
Polenizer
Showy
Fol Color
Polenizer
Showy
Polenizer
Polenizer
Showy
Foi Color
Fol Color
Showy
Fol Color
Easy Care
Category
Stock Level
Specialty
OK
Patio Roses
OK
Container Plants Onde
Specialty
Order
Container Plants OK
Contáner Plants Order
Container Plants Order
Edibles
Order
Edibles
Order
Specialty
ОК,
Edibles
Order
Bonsai
OK
Edibles
OK
Pafio Roses OK
Container Ponts Order
Eobles
OK
Potio Roses Order
Container Pants OK
Specialty
OTO
Patio Roses
Patio Roses
Specialty
Codes
Container Ports Order
Potio Roses
Egoes
45
Lastname Riestrame 2 Plants
GURE 2.57
w
w
CONTENT-BASED ASSESSMENTS (MASTERY AND TRANSFER OF LEARNING)
2
EXCEL
Mastering Excel Project 2E Plants
(continued)
1
Start Excel. From your student files, locate and
open e02E_Plants, and then Save the file in your Excel
Chapter 2 folder as Lastname Firstname 2E Plants
2
To the right of column B, insert two new columns
to create new blank columns C and D. By using Flash
Fill in the two new columns, split the data in column B
into a column for Item # in column C and Category in
column D. As necessary, type Item # as the column title
in column C and Category as the column title in
column D. Delete column B.
3
By using the Cut and Paste commands, cut
column C—Category—and paste it to column H, and
then delete the empty column C. Apply AutoFit to
columns A:G.
If the Quantity in Stock is less than 50 the Value if_true
is Order Otherwise the value if false is OK Fill the
formula down through cell H42.
11 Apply Conditional Formatting to the Stock Level
column so that cells that contain the text Order are
formatted with Bold Italic and with a Color of Green,
Accent 6. Apply conditional formatting to the Quantity
in Stock column by applying a Gradient Fill Green
Data Bar
12 Format the range A13:H42 as a Table with headers,
and apply the style Table Style Light 20. Sort the table
from A to Z by Item Name, and then filter on the
Category column to display the Specialty types.
13 Display a Total Row in the table, and then in cell
A43, Sum the Quantity in Stock for the Specialty items.
Type the result in cell B11. Click in the table, and then on
the Design tab, remove the total row from the table. Clear
the Category filter.
4
In cell B4, insert a function to calculate the Total
Items in Stock by summing the Quantity in Stock data,
and then apply Comma Style with zero decimal places to
the result.
5 In each cell in the range B5:B8, insert functions to
calculate the Average, Median, Lowest, and Highest retail
prices, and then apply the Accounting Number Format to
each result.
14. Merge & Center the title and subtitle across
columns A:H, and apply Title and Heading 1 styles,
respectively. Change the theme to Mesh, and then select
and AutoFit all the columns.
6 Move the range A4:B8 to the range D4:E8, apply
the 40% – Accent4 cell style to the range, and then select
columns D:E and AutoFit.
7 In cell C6, type Statistics and then select the
range C4:C8. In the Format Cells dialog box, merge
the selected cells, and change the text Orientation to 25
Degrees. Format the cell with Bold, a Font Size of 14 pt,
and then change the Font Color to Blue-Gray, Text 2.
Apply Middle Align and Align Right.
1958
In the Category column, Replace All occurrences of
Vine Roses with Patio Roses
15 Set the orientation to Landscape. In the Page Setup
dialog box, center the worksheet Horizontally, insert a
custom footer in the left section with the file name, and
set row 13 to repeat at the top of each page. Display the
Print Preview. Apply the Fit All Columns on One Page
setting.
16
As the Tags, type plants inventory, Pasadena As
the Subject, type your course name and section number.
Be sure your name displays as the Author.
In cell B10, use the COUNTIF function to count the
number of Specialty plant types in the Category column.
1.6 In cell H13, type Stock Level In cell H14, enter an
IF function to determine the items that must be ordered.
17 Save your workbook. If directed by your instructor
to do so, submit your paper printout, your electronic
image of your document that looks like a printed
document, or your original Excel file. If required by your
instructor, print or create an electronic version of your
worksheet with formulas displayed. In the upper right
corner of the Excel window, click Close.
END You have completed Project 2E