1

Open the downloaded file, GolfSales.xlsx. Save the workbook as GolfSales_LastFirst using your last and first name. If a Security Warning message displays, click the Enable Editing button.

2

On the Transactions worksheet, freeze the panes so that the Trans_ID column is always visible and the heading row.

3

On the Transactions worksheet, in cell P9, enter a formula that will calculate the Line Item Total. Copy the formula down to cell P30

4

On the Transactions worksheet, in cell Q9, enter an IF function that will test if the value in cell D9 is equal to Dwallet. If the value is true, the result will be Digital Wallet. If the value is false, the result will be blank. Copy the formula down to cell Q30.

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.

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.

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.

8

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.  

Transcribed Image Text:

Revenue Report Customer Type Non_Local Local Unknown 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 Customer Type Average Sale Cash Check Ccard Sum of Shift Sum of Revenue Clothing Clubs Accessories Shoes Revenue Number of transactions Local Morning Local Ad Hoc Transaction Listing $ Shift Category Transaction Count Report 1 2 3 4 Coupon First Transaction Used Amount of transaction Number of times coupon used Revenue Goal Qty Sold Goal Qty Sold Status Qty Sold and Revenue Status 25.00 Non_Local Unknown Number of transactions >= to value Morning Category T453X $ Local Non Local 1,000.00 50 AH_Coupon_Targets Coupon_Target Revenue Report Customer Type Non_Local Local Unknown 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 Customer Type Average Sale Cash Check Ccard Sum of Shift Sum of Revenue Clothing Clubs Accessories Shoes Revenue Number of transactions Local Morning Local Ad Hoc Transaction Listing $ Shift Category Transaction Count Report 1 2 3 4 Coupon First Transaction Used Amount of transaction Number of times coupon used Revenue Goal Qty Sold Goal Qty Sold Status Qty Sold and Revenue Status 25.00 Non_Local Unknown Number of transactions >= to value Morning Category T453X $ Local Non Local 1,000.00 50 AH_Coupon_Targets Coupon_Target