CSIS2010 The Red Bluff Golf Course Transactions worksheet 11- On the Transactions worksheet, click cell T9. Enter an IF function with an AND with nested OR

CSIS2010 The Red Bluff Golf Course Transactions worksheet 11- On the Transactions worksheet, click cell T9. Enter an IF function with an AND with nested OR functions. The logical test will be an AND function. The first logical argument of the AND function will determine if the value in cell K9 is equal to Morning or if the value in K9 is equal to Afternoon. The second logical argument of the AND function will determine if the value in R9 is equal to High or if the value in R9 is equal to Ultra. The value if true will be 1. The value if false will be 0.

Copy the formula down to cell T30.

12- On the Transactions worksheet, click cell U9. Enter an IF function. The logical test of the IF function is an OR function where the first logical test is an AND function. The AND function tests to see if the value in cell H9 equals the value of cell I14 on the DataInputs worksheet and if the value in cell P9 is greater than the value of cell J14 on the DataInputs worksheet. Logic2 of the OR function is a second nested AND function that tests to see if the value of cell H9 is equal to the value of cell I13 on the DataInputs worksheet and the value in cell P9 is greater than the value of cell J13 on the DataInputs worksheet. If the value of the logical test is true, the result is 1. If the value is false, the result is 0.

13-On the RevenueReport worksheet, in cell B9, enter a COUNTIF function that uses the named rangeTrans_Group as the range and cell I20 on the DataInputs tab as the criteria. Copy the formula down, without formatting, to cell B12.

In cell E16, enter a COUNTIF function that uses the named range Coupon_Num as the range and cell E13 as the criteria.

In cell E4, enter a COUNTIF function that uses the named range Line_Item_Total as the range and “>=”&D4 as the criteria.

14- On the RevenueReport worksheet, click cell F8. Enter a COUNTIFS function. For Criteria_range1, enter the named range Shift. For Critera1, enter an absolute reference to cell E6. For Criteria_range2, enter the named range Coupon_Target. For Criteria2, enter E8. Copy the formula, without formatting, to cell F11.

15- On the RevenueReport worksheet, click cell B15. Enter an AVERAGEIF function where the range is the named range Cust_Cat. The criteria type is the value entered in cell B14, and the average range is the named range Line_Item_Total

16-On the RevenueReport worksheet, click cell B16 and enter an AVERAGEIFS function.

The Average_range is the named range Line_Item_Total.
The Criteria_range1 is the named range Cust_Cat.
The Critera1 is $B$14.
The Criteria_range2 is the named range Pay_Type.
The Criteria2 is A16.
Copy the formula through cell B18.

17-

On the RevenueReport worksheet, in cell B22, enter a SUMIF function that uses the named range Shift for the range, the criteria from cell B21, and Line_item_Total for the sum range.

In cell B4, enter a SUMIF function that uses Cust_Cat for the range, the criteria from cell A4, and Line_Item_Total for the sum range.

Copy the formula through cell B6.

18-

On the RevenueReport worksheet, click in cell B25 and enter a SUMIFS function that uses the named range Line_Item_Total for the Sum_range, the named range SKU_Cat for the first criteria range, cell reference $A25 for first criteria, the named range Cust_Cat for second criteria range, and the cell reference B$24 for the second criteria. Copy the formula through cell B28. With the range B25:B28 selected, copy the formula to cell range B25:D28.

On the EmployeeReport worksheet tab, in cell B6, enter a SUMIF function that uses the named range Emp_ID as the range, the criteria in A6, and the named range Line_Item_Total as the sum range.

In cell B13, enter a SUMIFS function that used the named range Line_Item_Total as the sum range, SKU_Cat as the first criteria range, criteria in cell B11 as the first criteria, the named range Emp_ID as the second criteria range, and the criteria in cell A6 as the second criteria. Divide the SUMIFS function by cell B12.

In cell B15, add two SUMIF function together. The fist SUMIF function uses the named range Emp_Id as the range, the criteria in cell A6, and the named range Sales_Point1 as the sum range. The second SUMIF function uses the named range Emp_ID as the range, the criteria in cell A6, and the named range Sales_Point2 as the sum range.

19- Click the DatabaseTotals worksheet. Click cell G2, type Non_Local and then press ENTER. This value is the constraint for filtering the records; only records that are nonlocal customers will be used in the DSUM function. In cell B5, enter a DSUM function that uses the named range DataSet as the Database, cell B4 as the field, and the range A1:K2 as the criteria.

Click cell A2, type Check and then press ENTER. This changes the value reflected in B5 immediately to $32.95.

20-

Click the EmployeeReport worksheet. In cell B16, enter an approximate match VLOOKUP function that looks up the value in cell B15 from column 2 of the IncentivePts table array.

21- On the Transactions worksheet, in cell O9, enter an exact match VLOOKUP function that looks up the value of cell C9 from column 6 of the SKU_List table. Copy the formula down to cell O30.

In cell N9. enter an exact match VLOOKUP function that looks up the value of cell F9 from column 2 of the Coupons table array. Copy the formula down to cell N30.

In cell I9, enter an exact match VLOOKUP function that looks up the value in cell C9 from column 3 of the SKU_List table array. Copy the formula down to cell I30.

22- On the DataInputs worksheet, select range B12:G17. Name the selected range AH_ReportTable. With range B12:G17 still select, name the column data using the names in the top row (row 12) as the range names.

On the RevenueReport worksheet, in cell E8, enter an exact match HLOOKUP function that looks up the value in cell F6 from the AH_ReportTable table array. For the Row_index_num, type D8+1.
Copy the formula down through E11.

On the Transactions worksheet, in cell K9, enter an approximate HLOOKUP function that looks up the value in cell B9 from row 2 of the Shifts table array. Copy the formula down to cell K30 to overwrite the static values.

On the EmployeeReport worksheet, in cell B8, enter an exact match HLOOKUP function that looks up the value in cell A4 from row 6 in cell range B4:F9 of the DataInputs table array. Click cell B9, enter the formula =B6/B8.

23-

On the RevenueReport worksheet, in cell E14, enter a MATCH function that has a lookup value of E13, from the lookup array range name Coupon_Num with a match type of 0.

24- On the RevenueReport worksheet, in cell E15, enter the formula =INDEX(Trans_Qty,E14)*INDEX(Retail,E14).

On the EmployeeReport worksheet, in cell B20, enter the formula =INDEX(Level_1,A20). Copy the formula down to B22.

Click cell B4. Type =MATCH(A4,DataInputs!L27:L31).
Press F2 to edit cell B4. Click in the Formula Bar to position the insertion point to the right of the = sign, and then type INDEX(DataInputs!I27:I31,
Position the cursor at the end of the formula, type ) and then press ENTER.

25- On the Transactions worksheet, in cell J9, enter the formula =INDEX(DataInputs!$I$27:$I$31,MATCH(G9,DataInputs!$J$27:$J$31,0)). Fill the formula down through J30.

Click the EmployeeReport worksheet, and then click cell B12.
On the EmployeeReport worksheet, in cell D12 and D13, enter two test functions.
In cell D12, type =MATCH(B11,DataInputs!A5:A9,0)
In cell D13, type =MATCH(A4,DataInputs!B4:F4,0)
Click cell B12. Since the MATCH functions work, the complex function can be constructed. Type =INDEX(Goals,MATCH(B11,DataInputs!A5:A9,0),MATCH(A4,DataInputs!B4:F4,0))

Select cells D12:D13, press DELETE to remove the test functions.

26- On the EmployeeReport worksheet, in the formula in cell B20, replace Level_1 with =INDIRECT($B$19). Copy the formula to B22

27-On the Transactions worksheet, in cell I9, edit the formula to include the IFERROR function that will return a blank if there is an error with the result in the formula. Copy the edited formula to cell I30.

In cell J9, edit the formula to include an IFERROR function that will return a blank if there is an error with the result in the formula.

Click cell K9, edit the formula to include an IFERROR that will return a blank if there is an error with the result in the formula. Copy the edited formulas in J9:K9 through cells J30:K30.

In cell L9, edit the formula by adding in IF function the will test if the value in cell F9 is not equal to blank. If the value is false, the result will be blank. Copy the formula down to cell L30.

In cell M9, edit the formula by adding in IF function the will test if the value in cell H9 is not equal to blank. If the value is false, the result will be blank. Copy the formula down to cell M30.

In cell N9,edit the formula include the IFERROR function that will return a 0 if there is an error with the result in the formula.Copy the formula down to cell N30.

Click cell O9, edit the formula include the IFERROR function that will return a 0 if there is an error with the result in the formula. Copy the formula down to cell O30.

Click cell P9, enter the following function =ROUND(O9*E9-(O9*E9*N9),2). Copy the formula down to P30.

28- On the Transactions worksheet, in cell R9 edit the formula by adding in IF function the will test if the value in cell A9 is not equal to blank. If the value is false, the result will be blank. Copy the formula down to R30.

In cell S9,edit the formula by adding in IF function the will test if the value in cell A9 is not equal to blank. If the value is false, the value is false, the result will be blank. Copy the formula down to S30. Revenue Report
Customer Type
Non_Local
Local
Unknown
Revenue
Transaction Listing
LOW – Less than $25
MEDIUM – $25 or more and less than $100
HIGH – $100 or more and less than $250
ULTRA – $250 or more
Number of transactions
Customer Type
Average Sale
Cash
Check
Ccard
Local
Morning
Sum of Shift
Sum of Revenue
Clothing
Clubs
Accessories
Shoes
Local
20190207195524e03ch05_grader_pc_golfsales.xlsx
Non_Local
Ad Hoc Transaction Listing
Number of transactions >= to value
$
25.00
Shift Category
Transaction Count Report
1
2
3
4
Morning
Category
Coupon
First Transaction Used
Amount of transaction
Number of times coupon used
T453X
Revenue Goal
Qty Sold Goal
Qty Sold Status
Qty Sold and Revenue Status
AH_Coupon_Targets
Coupon_Target
Local
Non_Local
$
Unknown
20190207195524e03ch05_grader_pc_golfsales.xlsx
1,000.00
50
Employee Sales Report
Sales Staff
Position
Asst Manager
Total Sales Revenue
Hample
Staff ID Number
15
Overall Goal
Percent of Goal
$
Category
Category Goal
Percent of Category Goal
Clothing
$
1,250.00
10.2%
200.00
Incentive Points
Incentive Points Status
Asst Mgr Status
Reward Level
Level_1
1
2
3
20190207195524e03ch05_grader_pc_golfsales.xlsx
Transactions
Range of possible Data
A9:H208
Raw Data Import Area
Trans_ID
Trans_Time SKU
Pay_Type Trans_Qty Coupon_Num
P000023455
7:02 AM T981Q11 Cash
3
P000023456
7:34 AM R483P24 Cash
1
P000023457
9:34 AM X349R39 Cash
1
P000023458
10:15 AM X487P48 Ccard
1 T453X
P000023459
11:49 AM T981Q11 Check
2
P000023460
1:45 PM C584T23 Ccard
1
P000023461
2:25 PM C583K29 Ccard
2 T382P
P000023462
2:34 PM R483P24 Cash
3
P000023463
2:51 PM F833K19 Cash
3
P000023464
3:23 PM F375P67 Ccard
2
P000023465
4:41 PM C483T89 Ccard
1 T453X
P000023466
5:59 PM C492R32 Ccard
1
P000023467
6:04 PM C581T49 Cash
2 T382P
P000023468
6:11 PM T981Q11 Ccard
2
P000023469
7:18 PM F833K19 Check
3
P000023470
7:21 PM T822Z48 Check
2 C384R
P000023471
7:32 PM F232P37 Ccard
1
P000023472
7:39 PM C583K29 Cash
1
20190207195524e03ch05_grader_pc_golfsales.xlsx
Emp_ID
38
24
24
15
38
38
15
38
38
24
15
26
26
76
76
76
26
15
Cust_Cat
Local
Non_Local
Non_Local
Non_Local
Local
Unknown
Local
Local
Non_Local
Non_Local
Unknown
Non_Local
Local
Local
Unknown
Non_Local
Non_Local
Non_Local
Calculated Fields
SKU_Cat
Accessories
Accessories
Clothing
Clubs
Accessories
Shoes
Shoes
Accessories
Accessories
Accessories
Clothing
Clothing
Accessories
Accessories
Accessories
Clothing
Accessories
Shoes
Emp_Position
Associate
Asst Manager
Asst Manager
Associate
Associate
Associate
Asst Manager
Associate
Associate
Asst Manager
Asst Manager
Associate
Associate
Associate
Associate
Associate
Associate
Asst Manager
Shift
Morning
Morning
Morning
Morning
Morning
Afternoon
Afternoon
Afternoon
Afternoon
Afternoon
Afternoon
Evening
Evening
Evening
Evening
Evening
Evening
Evening
Coupon_Target
Local
Local
Local
Non_Local
Local
Local
Local
Local
Local
Local
Non_Local
Local
Local
Local
Local
Non_Local
Local
Local
20190207195524e03ch05_grader_pc_golfsales.xlsx
Coupon_Hit
Coupon Match
No Match
No Match
Coupon Match
Coupon Match
No Match
Coupon Match
Coupon Match
No Match
No Match
No Match
No Match
Coupon Match
Coupon Match
No Match
Coupon Match
No Match
No Match
Coupon_Amt
0%
0%
0%
15%
0%
0%
5%
0%
0%
0%
15%
0%
5%
0%
0%
10%
0%
0%
Retail
$ 12.00
$ 2.00
$ 49.95
$ 419.00
$ 12.00
$ 49.00
$ 59.00
$ 2.00
$ 5.95
$ 15.45
$ 14.95
$ 39.95
$ 11.39
$ 12.00
$ 5.95
$ 32.95
$ 129.00
$ 59.00
Calculated Fields
Line_Item_Total
$
36.00
$
2.00
$
49.95
$
356.15
$
24.00
$
49.00
$
56.05
$
6.00
$
17.85
$
30.90
$
12.71
$
39.95
$
21.64
$
24.00
$
17.85
$
59.31
$
129.00
$
59.00
Card_Charge Trans_Group
Medium
Low
Medium
Charge
Ultra
Low
Charge
Medium
Charge
Medium
Low
Low
Charge
Medium
Charge
Low
Charge
Medium
Low
Charge
Low
Low
Medium
Charge
High
Medium
20190207195524e03ch05_grader_pc_golfsales.xlsx
Big_Ticket_Item
Big Ticket Item
Big Ticket Item
Big Ticket Item
Big Ticket Item
Big Ticket Item
Big Ticket Item
Big Ticket Item
Big Ticket Item
Sales_Point1 Sales_Point2
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
Data Inputs
Sales Goals
Tyner
Hample
Accessories
Clothing
Clubs
Shoes
Total
Thurman
300
200
500
150
1150
400
200
500
150
1250
200
300
700
250
1450
Ad Hoc Reports
Data Name to count
Shifts
Shift Start Time
Shift
SKU List
Item ID
X349R39
X487P48
T981Q11
C584T23
C583K29
R483P24
F833K19
AH_Shifts
1 Morning
2 Afternoon
3 Evening
4
Shift
AH_Trans_Groups
Low
Medium
High
Ultra
Trans_Group
6:00 AM
Morning
Description
Windbreaker (Resort)
Windren Pro (right-handed)
Resort golf balls
Trimble Comforts
Trimbel Striders
Resort Tees
Resort divot key chain
20190207195524e03ch05_grader_pc_golfsales.xlsx
AH_Coupon_Targets
Local
Non_Local
Coupon_Target
12:00 PM
Afternoon
Category
Clothing
Clubs
Accessories
Shoes
Shoes
Accessories
Accessories
5:00 PM
Evening
Wholesale
$
$
$
$
$
$
$
29.00
242.00
3.00
27.00
29.00
0.50
1.23
F375P67
C483T89
C492R32
C581T49
C557P29
T822Z48
X733R14
X740Q29
F232P37
C884P23
R239T57
Grabber golf gloves
Resort cap
Resort polo shirt
golf shoe insert
golf button, shirt
golf button, shirt
golf button, shirt
Windren pro (left-handed)
Resort golf bag (elite)
Resort golf bag (carryall)
Resort golf easy-go pull cart
20190207195524e03ch05_grader_pc_golfsales.xlsx
Accessories
Clothing
Clothing
Accessories
Clothing
Clothing
Clothing
Clubs
Accessories
Accessories
Accessories
$
$
$
$
$
$
$
$
$
$
$
3.45
5.44
17.00
4.32
12.00
12.00
12.00
255.00
79.00
38.00
49.00
20190207195524e03ch05_grader_pc_golfsales.xlsx
Wilcox
Yammel
200
300
700
250
1450
AH_Customer_Types
Non_Local
Local
Unknown
Customer_Type
Coupons
Coupon_Code
T453X
T382P
C384R
100
300
300
100
800
AH_Prod_Categories
Accessories
Clothing
Clubs
Shoes
Prod_Cat
AH_Pay_Types
Cash
Ccard
Check
Sales Points
Customer
Non_Local
Local
Pay_Type
Transaction Groups
Low
Medium
High
Ultra
Unit
LG
6 pk
Retail Price
$
$
$
$
$
$
$
20190207195524e03ch05_grader_pc_golfsales.xlsx
49.95
419.00
12.00
49.00
59.00
2.00
5.95
Employees
Position
Asst Manager
Asst Manager
Associate
Associate
Associate
XL
LG
MED
$
$
$
$
$
$
$
$
$
$
$
15.45
14.95
39.95
11.39
32.95
32.95
32.95
453.00
129.00
57.00
97.00
Incentive System Points
0
15
25
50
100
Reward Levels with Rewards
Level_1
CoffeeShop Gift Card, $5
Movie Gift Card
Discount 5% for massage
Named Ranges
20190207195524e03ch05_grader_pc_golfsales.xlsx
20190207195524e03ch05_grader_pc_golfsales.xlsx
Coupon_Per
Coupon_Notes
Online Travel Promotion
Local Magazine Promotion
Travel Agent Promotion
15%
5%
10%
Transaction target
$
$
Coupon_Target
Non_Local
Local
Non_Local
100.00
50.00
Greater than or equal
Less than
25
100
250
25
100
250
ID
15
24
26
38
76
First Name
Ramal
Pat
Tyrone
Jerrold
Christy
20190207195524e03ch05_grader_pc_golfsales.xlsx
Last Name
Hample
Tyner
Thurman
Wilcox
Yammel
Get those points cranking
Level 1 rewards
Level 2 rewards
Level 3 rewards
Level 4 rewards
Level_2
Coffeeshop Gift Card, $10
Restaurant Gift Card
10% for massage
Level_3
Pizza Night
Movie Night
Gardener’s Delight
20190207195524e03ch05_grader_pc_golfsales.xlsx
Level_4
Shopping spree
Zoo Package
Tech Shop Gift Card
Level_5
Steak dinner for two
Massage Package
Golf Outing
20190207195524e03ch05_grader_pc_golfsales.xlsx
Pay_Type
Trans_Qty
Cust_Cat
Retail
Sum of Filter
20190207195524e03ch05_grader_pc_golfsales.xlsx
SKU_Cat
Emp_Position
Shift
Coupon_Target
Coupon_Hit
Retail
Line_Item_Total
Card_Charge
20190207195524e03ch05_grader_pc_golfsales.xlsx
Grader – Instructions
Excel 2016 Project
YO16_XL_CH05_GRADER_PC_HW – Golf Sales 1.6
Project Description:
The Red Bluff Golf Course & Pro Shop generates revenue through its golfers, golfer services, and pro shop sales. Aleeta
Herriott, the pro shop manager, receives revenue data on a monthly basis. She would like to have some reports developed
that will help to track sales and to analyze her profit margins. She has a workbook started with some sample data and wants
you to continue developing some reports. These reports will help Aleeta make educated decisions about the business, such as
which items to place on sale or discontinue.
Steps to Perform:
Step
Points
Possible
Instructions
1
Open the downloaded file, e03ch05_grader_pc_GolfSales.xlsx. Save the workbook as
e03ch05_grader_pc_GolfSales_LastFirst using your last and first name. If a Security
Warning message displays, click the Enable Editing button.
0
2
On the DataInputs worksheet, in cell I48, paste the current named ranges.
3
On the Transactions worksheet, in cell Q9, enter an IF function that will test if the value in cell
D9 is equal to Ccard. If the value is true, the result will be Charge. If the value is false, the
result will be blank. Copy the formula down to cell Q30.
3
On the Transactions worksheet, in cell M9, enter an IF function that will determine if the value
in cell H9 is not equal to the value in cell L9. If the value is true, the result will be No Match. If
the value is false, the result will be Coupon Match. Copy the formula down to cell M30.
1
4
On the RevenueReport worksheet, in cell E21, enter an IF function that determines if the SUM
of the Trans_Qty range is greater than cell E20. If the value is true, then Goal Met is the
result. If the value is false, then Under Goal is the result.
2
5
On the Transactions worksheet, in cell R9, enter an IF function to determine if the value in P9
is less than the value in cell K20 on the DataInputs worksheet. If the value is true, then the
results will be cell I20 on the DataInputs worksheet. If the value is false, then the result will be
Everything Else. Be sure to use absolute references where necessary. Copy the formula
down to cell R30.
2
6
On the Transactions worksheet, click cell R9, and then press F2 to enter edit mode. Select
“Everything Else”, including the quotes, and replace it by entering an IF function that
determines if the value in cell P9 is less than the value in cell K21 on the DataInputs
worksheet. If the value is true, then the result is the value in cell I21 on the DataInputs
worksheet. If the value is false, then the result is the value in cell I22 on the DataInputs
worksheet. Be sure to use absolute references where necessary. Copy the formula through
cell R30.
2
7
Click cell R9, and then press F2 to enter edit mode. Select DataInputs!$I$22, replace it by
typing IF(P9< DataInputs!$K$22,DataInputs!$I$22,DataInputs!$I$23) Copy the edited formula through cell R30. 2 8 On the EmployeeReport worksheet, and click cell B17 and enter an IF function with a nested AND function. For the logical test, enter an AND function to test if the value in cell B4 is Asst Manager and if the value in cell B9 is greater than 0.25. If both tests are true, then the result will be On Target or else the result will be Increase Sales. 2 Created On: 09/19/2018 1 YO16_XL_CH05_GRADER_PC_HW - Golf Sales 1.6 Grader - Instructions Excel 2016 Project Points Possible Step Instructions 9 On the Transactions worksheet, click cell L9 and enter an IF function with a nested OR function. The logical test will use the OR function to determine if F9 is equal to cell I5 on the DataInputs worksheet or if F9 is equal to I7 on the DataInputs worksheet. If either test is true, then the result will be Non_Local. If both tests are false, then the result will be Local. Be sure to use absolute references where necessary. Copy the formula down to cell L30. 1 10 On the Transactions worksheet, click cell S9, enter an IF function with a nested NOT function. The logical test will use the NOT function to determine if the value in I9 is Accessories. The result if true will be Big Ticket Item or else the result if false will be blank. Copy the formula down to cell S30. 1 11 On the Transactions worksheet, click cell T9. Enter an IF function with an AND with nested OR functions. The logical test will be an AND function. The first logical argument of the AND function will determine if the value in cell K9 is equal to Morning or if the value in K9 is equal to Afternoon. The second logical argument of the AND function will determine if the value in R9 is equal to High or if the value in R9 is equal to Ultra. The value if true will be 1. The value if false will be 0. Copy the formula down to cell T30. 2 12 On the Transactions worksheet, click cell U9. Enter an IF function. The logical test of the IF function is an OR function where the first logical test is an AND function. The AND function tests to see if the value in cell H9 equals the value of cell I14 on the DataInputs worksheet and if the value in cell P9 is greater than the value of cell J14 on the DataInputs worksheet. Logic2 of the OR function is a second nested AND function that tests to see if the value of cell H9 is equal to the value of cell I13 on the DataInputs worksheet and the value in cell P9 is greater than the value of cell J13 on the DataInputs worksheet. If the value of the logical test is true, the result is 1. If the value is false, the result is 0. Be sure to use absolute references where necessary to be able to copy the formula to cell U30. 2 13 On the RevenueReport worksheet, in cell B9, enter a COUNTIF function that uses the named range Trans_Group as the range and cell I20 on the DataInputs tab as the criteria. Copy the formula down, without formatting, to cell B12. In cell E16, enter a COUNTIF function that uses the named range Coupon_Num as the range and cell E13 as the criteria. In cell E4, enter a COUNTIF function that uses the named range Line_Item_Total as the range and ">=”&D4 as the criteria.
6
14
On the RevenueReport worksheet, click cell F8. Enter a COUNTIFS function. For
Criteria_range1, enter the named range Shift. For Critera1, enter an absolute reference to cell
E6. For Criteria_range2, enter the named range Coupon_Target. For Criteria2, enter E8.
Copy the formula, without formatting, to cell F11.
4
15
On the RevenueReport worksheet, click cell B15. Enter an AVERAGEIF function where the
range is the named range Cust_Cat. The criteria type is the value entered in cell B14, and the
average range is the named range Line_Item_Total.
4
16
On the RevenueReport worksheet, click cell B16 and enter an AVERAGEIFS function.
The Average_range is the named range Line_Item_Total.
The Criteria_range1 is the named range Cust_Cat.
The Critera1 is $B$14.
The Criteria_range2 is the named range Pay_Type.
The Criteria2 is A16.
Copy the formula through cell B18.
3
Created On: 09/19/2018
2
YO16_XL_CH05_GRADER_PC_HW – Golf Sales 1.6
Grader – Instructions
Step
17
Excel 2016 Project
Points
Possible
Instructions
On the RevenueReport worksheet, in cell B22, enter a SUMIF function that uses the named
range Shift for the range, the criteria from cell B21, and Line_item_Total for the sum range.
4
In cell B4, enter a SUMIF function that uses Cust_Cat for the range, the criteria from cell A4,
and Line_Item_Total for the sum range.
Copy the formula through cell B6.
18
On the RevenueReport worksheet, click in cell B25 and enter a SUMIFS function that uses the
named range Line_Item_Total for the Sum_range, the named range SKU_Cat for the first
criteria range, cell reference $A25 for first criteria, the named range Cust_Cat for second
criteria range, and the cell reference B$24 for the second criteria. Copy the formula through
cell B28. With the range B25:B28 selected, copy the formula to cell range B25:D28.
8
On the EmployeeReport worksheet tab, in cell B6, enter a SUMIF function that uses the
named range Emp_ID as the range, the criteria in A6, and the named range Line_Item_Total
as the sum range.
In cell B13, enter a SUMIFS function that used the named range Line_Item_Total as the sum
range, SKU_Cat as the first criteria range, criteria in cell B11 as the first criteria, the named
range Emp_ID as the second criteria range, and the criteria in cell A6 as the second criteria.
Divide the SUMIFS function by cell B12.
In cell B15, add two SUMIF function together. The fist SUMIF function uses the named range
Emp_Id as the range, the criteria in cell A6, and the named range Sales_Point1 as the sum
range. The second SUMIF function uses the named range Emp_ID as the range, the criteria
in cell A6, and the named range Sales_Point2 as the sum range.
19
Click the DatabaseTotals worksheet. Click cell G2, type Non_Local and then press ENTER.
This value is the constraint for filtering the records; only records that are nonlocal customers
will be used in the DSUM function. In cell B5, enter a DSUM function that uses the named
range DataSet as the Database, cell B4 as the field, and the range A1:K2 as the criteria.
3
Click cell A2, type Check and then press ENTER. This changes the value reflected in B5
immediately to $32.95.
20
Click the EmployeeReport worksheet. In cell B16, enter an approximate match VLOOKUP
function that looks up the value in cell B15 from column 2 of the IncentivePts table array.
3
21
On the Transactions worksheet, in cell O9, enter an exact match VLOOKUP function that
looks up the value of cell C9 from column 6 of the SKU_List table. Copy the formula down to
cell O30.
3
In cell N9. enter an exact match VLOOKUP function that looks up the value of cell F9 from
column 2 of the Coupons table array. Copy the formula down to cell N30.
In cell I9, enter an exact match VLOOKUP function that looks up the value in cell C9 from
column 3 of the SKU_List table array. Copy the formula down to cell I30.
Created On: 09/19/2018
3
YO16_XL_CH05_GRADER_PC_HW – Golf Sales 1.6
Grader – Instructions
Step
22
Excel 2016 Project
Points
Possible
Instructions
On the DataInputs worksheet, select range B12:G17. Name the selected range
AH_ReportTable. With range B12:G17 still select, name the column data using the names in
the top row (row 12) as the range names.
9
On the RevenueReport worksheet, in cell E8, enter an exact match HLOOKUP function that
looks up the value in cell F6 from the AH_ReportTable table array. For the Row_index_num,
type D8+1.
Copy the formula down through E11.
On the Transactions worksheet, in cell K9, enter an approximate HLOOKUP function that
looks up the value in cell B9 from row 2 of the Shifts table array. Copy the formula down to
cell K30 to overwrite the static values.
On the EmployeeReport worksheet, in cell B8, enter an exact match HLOOKUP function that
looks up the value in cell A4 from row 6 in cell range B4:F9 of the DataInputs table array.
Click cell B9, enter the formula =B6/B8.
23
On the RevenueReport worksheet, in cell E14, enter a MATCH function that has a lookup
value of E13, from the lookup array range name Coupon_Num with a match type of 0.
2
24
On the RevenueReport worksheet, in cell E15, enter the formula
=INDEX(Trans_Qty,E14)*INDEX(Retail,E14).
6
On the EmployeeReport worksheet, in cell B20, enter the formula =INDEX(Level_1,A20).
Copy the formula down to B22.
Click cell B4. Type =MATCH(A4,DataInputs!L27:L31).
Press F2 to edit cell B4. Click in the Formula Bar to position the insertion point to the right of
the = sign, and then type INDEX(DataInputs!I27:I31,
Position the cursor at the end of the formula, type ) and then press ENTER.
25
On the Transactions worksheet, in cell J9, enter the formula
=INDEX(DataInputs!$I$27:$I$31,MATCH(G9,DataInputs!$J$27:$J$31,0)). Fill the formula
down through J30.
Click the EmployeeReport worksheet, and then click cell B12.
On the EmployeeReport worksheet, in cell D12 and D13, enter two test functions.
In cell D12, type =MATCH(B11,DataInputs!A5:A9,0)
In cell D13, type =MATCH(A4,DataInputs!B4:F4,0)
Click cell B12. Since the MATCH functions work, the complex function can be constructed.
Type =INDEX(Goals,MATCH(B11,DataInputs!A5:A9,0),MATCH(A4,DataInputs!B4:F4,0))
3
Select cells D12:D13, press DELETE to remove the test functions.
26
On the EmployeeReport worksheet, in the formula in cell B20, replace Level_1 with
=INDIRECT($B$19). Copy the formula to B22.
Created…
Purchase answer to see full
attachment