Open the start file CentralSierra$-02.$xlsx workbook. If the workbook opens in Protected View, click the Enable Editing button so you can modify it$.$ The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor, and save it$.$

Select the Tables sheet, select cells A$6$:B$10,$ and create range names using the Create from Selection button $[$Formulas tab, Defined Names group$].$

Select cells B$1$:F$2$ and click the Name box. Name the selection H$\_$Rates. Note that the first row is arranged in ascending order.

Create an HLOOKUP function to display the bonus rate.

Click the Commissions sheet tab and select cell F$5.$

Start the HLOOKUP function and use cell E$5$ as the lookup$\_$value.

For the table$\_$array argument, use the H$\_$Rates range.

Use the second row for the row$\_$index$\_$num argument $($Figure $2-100).$

The formula is $=$HLOOKUP$($E$5,$H$\_$Rates,$2)$

Figure $2-100$ HLOOKUP function to display bonus

Format the results as Percent Style with two decimal places.

Copy the formula in cell F$5$ to cells F$6$:F$14.$

Set order of operations to calculate total earnings.

Select cell G$5.$

Build a formula to add the commissions amount $($E$5)$ to the commissions amount times the rate $($F$5*$E$5).$

Copy the formula in cell G$5$ to cells G$6$:G$14.$

Create and copy a SUMIF function to calculate total earnings by branch office.

Select cell E$18.$

Start the SUMIF function with cells $D$$5$:$D$$14$ as the Range argument.

Set the Criteria argument as a relative reference to cell C$18.$

Select cells G$5$:G$14$ for the Sum$\_$range argument and make the references absolute$.$

Copy the formula in cell E$18$ to cells E$19$:E$20$ without formatting to preserve borders.

Format cells E$18$:E$21$ as Currency.

Total the earnings in cell E$21.$

Create and format the current date.

Select cell G$23$ and insert the NOW function.

Select cell G$23$ and click the Number group launcher $[$Home tab$].$ On the Number tab, select the Date category.

Scroll the Type list to find the date that displays the month spelled out, the date, a comma, and a four$-$digit year $($Figure $2-101).$

The Date category in the Format Cells dialog box

Figure $2-101$ Date format selected

Click OK$.$ Press Ctrl$+$Home.

Create and copy a VLOOKUP function to display goals for each funding source.

Click the Family Day sheet tab and select cell F$6.$

Start the VLOOKUP function and use cell E$6$ as the lookup$\_$value.

Click the Tables sheet tab for the table$\_$array argument and use cells $A$$6$:$B$$10.$ The data is sorted by the first column in ascending order.

Use the second column as the col$\_$index$\_$num The range$\_$lookup argument is empty.

Copy the formula in cell F$6$ to cells F$7$:F$20$ without formatting to preserve the fill color.

Format cells F$6$:F$20$ as Currency with no decimal places.

Create and copy an IF function.

Select cell H$6$ and start an IF

Type a logical$\_$argument to determine if cell G$6$ is greater than or equal to $(>=)$ cell F$6.$

Type Yes as the Value$\_$if$\_$true argument and No as the Value$\_$if$\_$false argument.

Copy the formula in cell H$6$ to cells H$7$:H$20$ without formatting to maintain the fill color.

Center align cells H$6$:H$20.$

Press Ctrl$+$Home.

Insert a new sheet at the end of the tab names and paste the range names starting in cell A$1.$ AutoFit columns A:B and name the worksheet as Range Names.

Save and close the workbook $($Figure $2-102).$