Reference no: EM132389821
Excel Project - Inventory Status Report
Project Description: In the following activities you will edit a worksheet for Holeman Hill, President, detailing the current inventory of trees at the Pasadena nursery.
Steps to Perform: Instructions -
1. Open the file go16_xl_ch02_grader_2a_hw.docx downloaded with this project. Then, in your textbook, turn to Project 2A and complete all of the steps in Activities 2.01-2.20.
2. Complete Activity 2.01 -
Note, Mac users, instead of using Flash Fill in steps 6-9, select the range B11:B39, and on the Data tab, start the Text to Columns wizard. Select the text delimiter as Other and type a dash (-). Set the destination cell as C11:D11 and finish the wizard to separate the item numbers and the item category. Enter Category in cell D10.
3. Complete Activity 2.02 - Moving a Column.
4. Complete Activity 2.03 - Using the SUM and AVERAGE Functions
In this Activity, you will use the SUM and AVERAGE functions to gather information about the product inventory.
5. Complete Activity 2.04 - Using the MEDIAN Function
The MEDIAN function is a statistical function that describes a group of data-it is commonly used to describe the price of houses in a particular geographical area. The MEDIAN function rinds the middle value that has as many values above it in the group as are below it. It differs from AVERAGE in that the result is not affected as much by a single value that is greatly different from the others.
6. Complete Activity 2.05 - Using the MIN and MAX Functions
The statistical MIN function determines the smallest value in a selected range of values. The statistical MAX function determines the largest value in a selected range of values.
7. Complete Activity 2.06 - Moving Data and Resolving a ##### Error message.
8. Complete Activity 2.07 - Rotating Text
Note, if necessary, after rotating the text, reapply the formatting from steps 2 and 3 of the activity.
9 Complete Activity 2.08 - Using the COUNTIF Function
The COUNT function counts the number of cells in a range that contain numbers. The COUNTIF function is a statistical function that counts the number of cells within a range that meet the given condition-the criteria that you provide. The COUNTIF function has two arguments-the range of cells to check and the criteria.
The trees of Rosedale Landscape and Garden will be featured on an upcoming segment of a TV gardening show. In this Activity, you will use the COUNTIF function to determine the number of Oak trees currently available in inventory that can be featured in the TV show.
10. Complete Activity 2.09 - Using the IF Function
A logical lest is any value or expression that you can evaluate as being true or false. The IF function uses a logical test to check whether a condition is met, and then returns one value if true. and another value if false.
For example, C14=228 is an expression that can be evaluated as true or false. If the value in cell C14 is equal to 228, the expression is true. If the value in cell C14 is not 228, the expression is false.
In this Activity, you will use the IF function to evaluate the inventory levels and determine if more products should be ordered.
11. Complete Activity 2.10 - Applying Conditional Formatting by Using Highlight Cells Rules and Data Bars
A conditional format changes the appearance of a cell based on a condition-a criteria. If the condition is true, the cell is formatted based on that condition; if the condition is false, the cell is not formatted. In this Activity, you will use conditional formatting as another way to draw attention to the Stock Level of trees.
12. Complete Activity 2.11 - Using Find and Replace
The Find and Replace feature searches the cells in a worksheet-or in a selected range-for matches, and then replaces each match with a replacement value of your choice.
Comments from customers on the company's bldg indicate that using the term Erosion control would be clearer than Erosion when describing the best landscape use for specific trees. Therefore, all products of this type will be relabeled accordingly. In this Activity, you will replace all occurrences of Erosion with Erosion Control.
13. Complete Activities 2.12 & 2.13 -
Activity 2.12 - Using the NOW Function to Display a System Date
The NOW function retrieves the date and time from your computer's calendar and clock and inserts the information into the selected cell. The result is formatted as a date and time.
Activity 2.13 - Freezing and Unfreezing Panes
In a large worksheet, if you scroll down more than 25 rows or scroll beyond column O (the exact row number and column letter varies, depending on your screen size and screen resolution you will no longer see the top rows or first column of your worksheet where identifying information about the data is usually placed. You will find it easier to work with your data if you can always view the identifying row or column titles.
The Freeze Panes command enables you to select one or more rows or columns and then freeze (lock) them into place. The locked rows and columns become separate panes. A pane is a portion of a worksheet window hounded by and separated from other portions by vertical or horizontal bars.
14. Complete Activity 2.14 - Creating an Excel Table and Applying a Table Style
Note, depending on the version of Office you are using, the table style may be Light Blue, Table Style Light 16.
15. Complete Activity 2.15 - Sorting an Excel Table
You can sort tables-arrange all the data in a specific order in ascending or descending order.
16. Complete Activity 2.16 - Filtering an Excel Table and Displaying a Total Row
You can filter tables-display only a portion of the data based on matching a specific value-to show only the data that meets the criteria that you specify.
17. Complete Activity 2.17 - 2.19
Activity 2.17 - Clearing Filters
When you are finished answering questions about the data in a table, you can clear the filters and remove the total row.
Activity 2.18 - Modifying and Shrinking the Worksheet View
Activity 2.19 - Splitting a Worksheet Window into Panes
The Split command splits the window into multiple resizable panes that contain views of your worksheet. This is useful to view multiple distant pails of your worksheet at one time.
18. Complete Activity 2.20 - Printing Titles and Scaling to Fit
Note, Mac users, in step 8, scale to fit 1 pages wide by 1 pages tall.
19. Save and close the file, and then submit.
Note - For details information for activities see attached file.
Attachment:- Assignment Files.rar