California State University Inventory Status Report Project You need to complete Project A B C D Read each chapter, follow the instructions to complete each project, use the student data file, if required, name and save the files as instructed in the book.Place all the files completed (total six) in a folderName the folder: “Excel in-Chapters A B C D Project 2 ”Zip the folder (right-click on the folder, send to, and compress/zip)Submit your zipped folder here Inventory Status Report
2A
PROJECT
MyITLab
Project 2A Training
Project 2A Grader
ProJeCt aCtiVities
In Activities 2.01 through 2.20, you will edit a worksheet for Holman Hill, President of
Rosedale Landscape and Garden, detailing the current inventory of trees at the Pasadena nursery.
Your completed worksheet will look similar to Figure 2.1.
ProJeCt Files
MyITLab If your instructor wants you to submit Project 2A in the
grader
MyITLab Grader system, log in to MyITLab, locate Grader
Project 2A, and then download the files for this project.
For Project 2A, you will need the following file:
e02A_Tree_Inventory
ProJeCt resUlts
You will save your workbook as:
Lastname_Firstname_2A_Tree_Inventory
Please
always review the
downloaded Grader
instructions before
beginning.
Excel 2016, Windows 10, Microsoft Corporation
GO!
Walk Thru
Project 2A
FigUre 2.1
416
Excel
|
Chapter 2: Using FUnCtions, Creating tables, and Managing large Workbooks
Objective 1 Use Flash Fill and the SUM, AVERAGE, MEDIAN, MIN,
and MAX Functions
Activity 2.01
aLERT!
| Using Flash Fill
To submit as an autograded project, log into MyITLab and download
t e i es o t is p o ect, and egin wit t ose i es instead o
e02A_Tree_Inventory.
1
Start Excel, and then in the lower left corner of Excel’s opening screen, click Open Other
Workbooks.
2
Click Browse. Navigate to the student files that accompany this chapter, and then locate and
to display the Save As dialog box, and then navigate to
open e02A_Tree_Inventory. Press
the location where you are storing your projects for this chapter.
3
Create a new folder named Excel Chapter 2 and then open the new folder. In the File name
.
box, type Lastname_Firstname_2A_Tree_Inventory and then click Save or press
4
Scroll down. Notice that the worksheet contains data related to types of trees in inventory,
including information about the Quantity in Stock, Item #/Category, Tree Name, Retail
Price, Light, and Landscape Use.
5
In the column heading area, point to column C to display the
pointer, and then drag to
the right to select columns C:D. On the Home tab, in the Cells group, click the Insert button
arrow, and then click Insert Sheet Columns.
New columns for C and D display and the remaining columns move to the right.
ANOTHER WAY
Select the columns, right-click anywhere over the selected columns, and then on the shortcut menu, click
Insert.
6
Click cell C11, type 13129 and then on the Formula Bar, click Enter
and keep C11 as the active cell.
7
On the Home tab, in the Editing group, click Fill, and then click Flash Fill. Compare your
screen with Figure 2.2.
to confirm the entry
Use this technique to split a column of data based on what you type. Flash Fill looks to the left and
sees the pattern you have established, and then fills the remaining cells in the column with only the
Item #. The Flash Fill Options button displays.
Project 2A: Inventory Status Report
|
Excel
417
EXCEL
GO! Learn How
Video E2-1
Flash Fill recognizes a pattern in your data, and then automatically fills in values when you
enter examples of the output that you want. Use Flash Fill to split data into two or more cells or to
combine data from two cells.
A function is the name given to a predefined formula—a formula that Excel has already built
for you—that performs calculations by using specific values that you insert in a particular order
or structure. Statistical functions, which include the AVERAGE, MEDIAN, MIN, and MAX
functions, are useful to analyze a group of measurements.
2
Excel 2016, Windows 10, Microsoft Corporation
New columns C and D added
Flash Fill Options button
Flash Fill fills in the Item
numbers from column B
FIGURE 2.2
8
Near the lower right corner of cell C11, click the Flash Fill Options button, and notice
that here you can Undo Flash Fill, Accept suggestions, or Select all 28 changed cells, for
example, to apply specific formatting. Click the button again to close it.
If Excel is not sure what pattern to use, it suggests a pattern by filling with pale gray characters,
and then you can use the Accept suggestions command to accept or start again.
9
Click cell D11, type Oak and then on the Formula Bar, click
to confirm the entry and
+ , which is the keyboard shortcut for Flash Fill.
keep D11 as the active cell. Press
Flash Fill extracts the text from the Item#/Category column and also inserts Category as the
column name. Now that Item # and Category are in two separate columns, the data can be sorted
and filtered by both Item # and Category.
10 Select column B, and then in the Cells group, click the Delete button arrow. Click Delete
Sheet Columns. On the Quick Access Toolbar, click Save .
ANOTHER WAY
Select the column, right-click anywhere over the selected column, and then on the shortcut menu, click
Delete.
Activity 2.02
1
In cell B10, type Item # and then press
. Select column C, and then on the Home tab, in
the Clipboard group, click Cut . Click cell H1, and then in the Clipboard group, click the
upper portion of the Paste button.
ANOTHER WAY
2
418
Excel
|
Press
+
to cut and
+
to paste.
Select and then delete column C. Select columns A:G. In the Cells group, click Format, and
then click AutoFit Column Width.
ANOTHER WAY
3
| Moving a Column
Select the columns, in the column heading area point to any of the selected column borders to display the
pointer, and then double-click to AutoFit the columns.
Merge & Center cell A1 across the range A1:H1, and then apply the Title cell style. Merge &
Center cell A2 across the range A2:H2, and then apply the Heading 1 cell style. Compare
your workbook.
your screen with Figure 2.3. Save
Chapter 2: USInG FUnCTIOnS, CREaTInG TabLES, and ManaGInG LaRGE WORkbOOkS
2
Title centered across A1:H1
with Title cell style
EXCEL
Subtitle centered across A2:H2
with Heading 1 cell style
Category column moved
Excel 2016, Windows 10, Microsoft Corporation
FIGURE 2.3
Activity 2.03
MOS
MOS
4.1.2, 4.1.5
| Using the SUM and AVERAGE Functions
In this Activity, you will use the SUM and AVERAGE functions to gather information about
the product inventory.
1
Click cell B4. Click the Formulas tab, and then in the Function Library group, click the
upper portion of the AutoSum button. Compare your screen with Figure 2.4.
The SUM function is a predefined formula that adds all the numbers in a selected range of cells.
Because it is frequently used, there are several ways to insert the function. For example, you can
insert the function from the Home tab’s Editing group, or by using the keyboard shortcut
+ ,
or from the Function Library group on the Formulas tab, or from the Math & Trig button in that
group.
Formulas tab
Function Library group
Excel 2016, Windows 10, Microsoft Corporation
AutoSum button
SUM function in cell B4
FIGURE 2.4
Project 2A: Inventory Status Report
|
Excel
419
2
With the insertion point blinking in the function, type the cell range a11:a39 to sum all the
; your result is 3022.
values in the Quantity in Stock column, and then press
3
Click cell B4, look at the Formula Bar, and then compare your screen with Figure 2.5.
SUM is the name of the function. The values in parentheses are the arguments—the values that an
Excel function uses to perform calculations or operations. In this instance, the argument consists
of the values in the range A11:A39.
Function and arguments
display in Formula Bar
Result of SUM function displays in B4
Excel 2016, Windows 10, Microsoft Corporation
FIGURE 2.5
4
Click cell B5. In the Function Library group, click More Functions, point to Statistical, point
to AVERAGE, and notice the ScreenTip. Compare your screen with Figure 2.6.
More Functions button
Statistical functions
AVERAGE function
ScreenTip describes function
FIGURE 2.6
5
Click AVERAGE, and then if necessary, drag the title bar of the Function Arguments dialog
box down and to the right so you can view the Formula Bar and cell B5.
The AVERAGE function adds a group of values, and then divides the result by the number of
values in the group. In the cell, the Formula Bar, and the dialog box, Excel proposes to average the
value in cell B4. Recall that Excel functions will propose a range if there is data above or to the
left of a selected cell.
6
In the Function Arguments dialog box, notice that B4 is highlighted. Press
existing text, type d11:d39 and then compare your screen with Figure 2.7.
to delete the
Because you want to average the Retail Price values in the range D11:D39—and not cell B4—you
must edit the proposed range.
420
Excel
|
Chapter 2: USInG FUnCTIOnS, CREaTInG TabLES, and ManaGInG LaRGE WORkbOOkS
Excel 2016, Windows 10, Microsoft Corporation
The ScreenTip describes how the AVERAGE function will compute the calculation.
2
Formula Bar displays function
name and arguments
EXCEL
Function Arguments dialog
box for AVERAGE function
Range of cells to average
Excel 2016, Windows 10, Microsoft Corporation
FIGURE 2.7
7
In the Function Arguments dialog box, click OK, and then click Save
.
The result indicates that the average Retail Price of all products is 107.89.
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 finds 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.
1
Click cell B6. In the Function Library group, click More Functions, display the list of
Statistical functions, scroll down as necessary, and then click MEDIAN.
2
Press
to delete the text in the Number1 box. Type d11:d39 and then compare your screen
with Figure 2.8.
When indicating which cells you want to use in the function’s calculation—known as defining
the arguments—you can either select the values with your mouse or type the range of values,
whichever you prefer.
Excel 2016, Windows 10, Microsoft Corporation
Function Arguments dialog
box for MEDIAN function
FIGURE 2.8
Project 2A: Inventory Status Report
|
Excel
421
3
Click OK to display 107.99 in cell B6. Click Save
and compare your screen with Figure 2.9.
In the range of prices, 107.99 is the middle value. Half of all trees in inventory are priced
above 107.99 and half are priced below 107.99.
Median Price
Excel 2016, Windows 10, Microsoft Corporation
FIGURE 2.9
MOS
MOS
4.1.3
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.
1
Click cell B7. On the Formulas tab, in the Function Library group, click More Functions,
display the list of Statistical functions, scroll as necessary, and then click MIN.
2
Press
, and then in the Number1 box, type d11:d39 Click OK.
The lowest Retail Price is 102.99.
4
Click cell B8, and then by using a similar technique, insert the MAX function to determine
the highest Retail Price, and then check to see that your result is 117.98.
Press
Style
5
+
. Point to cell B4, right-click, and then on the mini toolbar, click Comma
one time and Decrease Decimal
two times.
Select the range B5:B8, apply the Accounting Number Format
compare your screen with Figure 2.10.
, click Save
, and then
Comma style applied to cell B4
Accounting
Number Format
applied to B5:B8
MIN function calculates lowest price
MAX function calculates highest price
FIGURE 2.10
422
Excel
|
Chapter 2: USInG FUnCTIOnS, CREaTInG TabLES, and ManaGInG LaRGE WORkbOOkS
Excel 2016, Windows 10, Microsoft Corporation
3
Objective 2 Move Data, Resolve Error Messages, and Rotate Text
Activity 2.06
1
| Moving Data and Resolving a # # # # # Error Message
Select column E and set the width to 50 pixels. Select the range A4:B8. Point to the right
pointer, and then compare your screen with
edge of the selected range to display the
Figure 2.11.
Width of Column E set to 50 pixels
Selected range
Move pointer
Excel 2016, Windows 10, Microsoft Corporation
FIGURE 2.11
2
Drag the selected range to the right until the ScreenTip displays D4:E8, release the mouse
button, and then notice that a series of # symbols displays in column E. Point to any of the
cells that display # symbols, and then compare your screen with Figure 2.12.
Using this technique, cell contents can be moved from one location to another; this is referred to as
drag and drop.
If a cell width is too narrow to display the entire number, Excel displays the ##### message,
because displaying only a portion of a number would be misleading. The underlying values remain
unchanged and are displayed in the Formula Bar for the selected cell. An underlying value also
displays in the ScreenTip if you point to a cell containing # symbols.
Range moved to D4:E8
ScreenTip indicates underlying value
# symbols display
Excel 2016, Windows 10, Microsoft Corporation
FIGURE 2.12
3
Select columns D:E, and then in the column heading area, point to the right boundary of
column E to display the
pointer. Double-click to AutoFit the column to accommodate the
widest entry.
4
Select the range D4:E8. On the Home tab, in the Styles group, display the Cell Styles gallery.
Under Themed Cell Styles, click 20%-Accent1. Click Save .
Project 2A: Inventory Status Report
|
Excel
423
2
EXCEL
GO! Learn How
Video E2-2
When you move a formula, the cell references within the formula do not change, no matter
what type of cell reference you use.
If you move cells into a column that is not wide enough to display number values, Excel will
display a message so that you can adjust as necessary.
You can reposition data within a cell at an angle by rotating the text.
Activity 2.07
| Rotating Text
1
In cell C6, type Tree Statistics and then press
2
Select cell C6. On the Home tab, in the Font group, change the Font Size
to 14, and then
and Italic . Click the Font Color arrow
, and then in the fifth column,
apply Bold
click the first color—Blue, Accent 1.
3
In the Alignment group, apply Align Right
4
Select the range C4:C8, right-click over the selection, and then on the shortcut menu, click
Format Cells. In the Format Cells dialog box, click the Alignment tab. Under Text control,
select the Merge cells check box.
5
Under Orientation, click in the Degrees box to select the value, type 30 and then compare
your screen with Figure 2.13.
ANOTHER WAY
.
.
In the upper right portion of the dialog box, under Orientation, point to the red diamond, and then drag
the diamond upward until the Degrees box indicates 30.
Format Cells dialog box
Excel 2016, Windows 10, Microsoft Corporation
Range C4:C8 selected
Merge cells selected
Orientation set to 30 degrees
FIGURE 2.13
6
In the lower right corner of the Format Cells dialog box, click OK. Press
your workbook, and then compare your screen with Figure 2.14.
+
, Save
Text rotated and formatted
Excel 2016, Windows 10, Microsoft Corporation
FIGURE 2.14
7
In the row heading area, point to row 9 and right-click to select the row and display the
two times to repeat the last action and insert
shortcut menu. Click Insert, and then press
two additional blank rows.
is useful to repeat commands in Microsoft Office programs. Most commands can be repeated
in this manner.
424
Excel
|
Chapter 2: USInG FUnCTIOnS, CREaTInG TabLES, and ManaGInG LaRGE WORkbOOkS
8
From the row heading area, select rows 9:11. On the Home tab, in the Editing group, click
Clear
and then click Clear Formats to remove the blue accent color in columns D and E
from the new rows. Click Save .
2
9
EXCEL
When you insert rows or columns, formatting from adjacent rows or columns repeats in the new
cells.
Click cell E4, look at the Formula Bar, and then notice that the arguments of the SUM
function adjusted and refer to the appropriate cells in rows 14:42.
The referenced range updates to A14:A42 after you insert the three new rows. In this manner,
Excel adjusts the cell references in a formula relative to their new locations.
Objective 3 Use COUNTIF and IF Functions and Apply Conditional
Formatting
GO! Learn How
Video E2-3
Recall that statistical functions analyze a group of measurements. Another group of Excel functions,
referred to as logical functions, test for specific conditions. Logical functions typically use conditional tests
to determine whether specified conditions—called criteria—are true or false.
Activity 2.08
MOS
MOS
4.1.4, 4.2.4
| 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.
1
In cell A10, type Oak Trees and then press
2
With cell B10 as the active cell, on the Formulas tab, in the Function Library group, click
More Functions, and then display the list of Statistical functions. Click COUNTIF.
.
Recall that the COUNTIF function counts the number of cells within a range that meet the given
condition.
3
In the Range box, type g14:g42 Click in the Criteria box, type Oak and then compare your
screen with Figure 2.15.
Excel 2016, Windows 10, Microsoft Corporation
Function displays in Formula Bar
Function Arguments dialog
box for COUNTIF function
Range indicated as g14:g42
Criteria indicated as Oak
FIGURE 2.15
Project 2A: Inventory Status Report
|
Excel
425
4
In the lower right corner of the Function Arguments dialog box, click OK.
There are 13 different Oak trees available to feature on the TV show.
5
On the Home tab, in the Alignment group, click Align Left
your workbook.
row title. Save
Activity 2.09
MOS
MOS
4.2.1
to place the result closer to the
| Using the IF Function
A logical test 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.
1
Click cell H13, type Stock Level and then press
2
In cell H14, on the Formulas tab, in the Function Library group, click Logical, and then in
the list, click IF. Drag the title bar of the Function Arguments dialog box up or down to view
row 14 on your screen.
3
With the insertion point in the Logical_test box, type a14=
Greater than or equal to
Purchase answer to see full
attachment