Question

Assignment Instructions In cell B10, use the COUNTIF function to count the number of Specialty plant types in the Category column. 9 In cell H13, type Stock Level. In cell H14, enter an IF function to determine the items that must be ordered. 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. 10 Apply Conditional Formatting to the Stock Level column so that cells that contain the text Order are formatted with Bold Italic and with a Font Color of Green, Accent 6. Apply Conditional Formatting to the Quantity in Stock column by applying a Gradient Fill Green Data Bar. 11 Note, Mac users, ensure that the background color of the cells is set to No Color. 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 ltem Name, and then filter on the Category column to display only the Specialty types 12 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 remove the total row from the table. Clear the Category filter. 13 14 Merge & Center A1 across columns AH and apply the Tite cell style. Merge & Center A2 across columns A:H, and apply the Heading 1 cell style. Change the theme to Mesh, and then select and AutoFit all the columns. 15 Set the orientation to Landscape. From the Page Setup dialog box, center the worksheet Horizontally, 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. Insert a custom footer in the left section with the file name. Note, Mac users, use the Page Setup dialog box to fit the columns to one page. File Edit Insert Format Help Calibri f Pasadena Facility: Inventory Status of Plants 1 Pasadena Facility Inventory Status of Plants 2 As of September 30 4 Total Items in Stock 5 Average Price 6 Median Price 7 Lowest Price 8 Highest Price 10 Specialty Plant Types Specialty Quantity in Stock 11 13 Quantity in Stock Item Name Macadamia Nut Retail Price Size Special Feature 15.42 24 Fall Color 28.95 42" Fall Color Itern B/Category 37 1267-Container Plants Clematis Hank 58 1843-Bonsal 78 1852-Specialty 93 1862-Container Plants Minny Pearl 2495 10 Showy 13.95 14" Pollenizer 1695 15" Showy Fragrant Plum September Raspberry Elite Gold Pineapple 7.58 12 Fall Color 12.65 16" Easy Care 15.95 12" Fall Color 78 2157-Specialty 28 2158-SpecialtySalzia Glue 24 2176-Container Plants Strawberry Sweet 26 2201-Edibles 56 2202-Edibles 24 2202-Vine RosesMagid Hybrid 49 2219-Vine RosesSunshine Dream 65 2223-Vine Roses Purple Splash 175 2266-Vine Roses Wing Ding 18 2268-Specialty 28 2278-Container Plants Blanket Flower 19 2308-Container Plants Cinnamon Tree 56 2317-Vine Roses Pope Benedict 6596Showy 1859 24" Easy Care 18.95 18 Easy Care 11.35 14 Pollenizer 1657 24 Fall Color 25.95 14" Showy 45.95 41" Fall Color 54.95 24 Fall Color 24.95 16 Easy Care 1658 12 Easy Care 11.35 16 Pollenizer 26.95 11 Fall Color 3495 36" Easy Care 2895 28 Easy Care 10.59 18 Pollenizer 38.95 28 Easy Care 26.95 33 Easy Care 34.95 35" Pollenizer Dwarf Banana Top Hat Blueberry Tetrapleed Breeder 135 2356-Vine Roses Black Pie Super Dwart Tomato 3495 36 45 2364-Edibles 36 2402-Edibles 52 2404-Vine RosesHot Chocolate 49 2428-Edibles 23 2437-Specialty 150 2439-Edibles 42 2543-Container Plants Lavandula 32 2580-Specialty 52 2582-Container Plants Chandler Blueberry Dwarf Raspberry Cashew Tree Holiday Assortment 14.95 12" Showy 31.95 26" Pollenizer 18.65 24 Easy Care New Dimension

Answer 1

9.

Use the following formula in B10:

=COUNTIF($B$14:$B$42, "*Specialty*")

10.

Enter the following formula in H14:

=IF(A14<50, "Order", "OK)

Then copy this up to H42.

11.

Select the entire array H14:H42

Go to: Home --> Conditional Formatting --> Highlight cell rules --> Text that contains

Text That Contains er Format cells that contain the text: ct -"Order" with Custom Format mat Ce Cancel Format Cells Number Font Border Fill Font style Bold Italic Font: Size: iT Cambria (Headings) T Calibri (Body) r aakar T Abyssinica SIL T Algerian T Andale Mono Regular Italic Bold Bold Italic 10 12 Color: ects Strikethrough Superscript revieW AaBbCcYyZz 「Subscript For Conditional Formatting you can set Font Style, Underline, Color, and Strikethrough. Clear Cancel

For the rest of the parts, please provide a text form of the data so that it can be copied in Excel and steps can be shown. I hope you understand that it is not possible to type the entire data in Excel as this is large.