Mark the steps as checked when you complete them. Open the workbook CentralSierr

Mark the steps as checked when you complete them.
Open the workbook CentralSierra-02.xlsx start file. 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 A1:B6, and create range names using the Create from Selection button [Formulas tab, Defined Names group].
Create an XLOOKUP function to display the bonus rate. (If XLOOKUP is not available, build the following formula =VLOOKUP(E5,Tables!$A$2:$B$6,2). Leave the Range_lookup argument empty so that VLOOKUP finds the closest match).
Click the Commissions sheet tab and select cell F5.
Start the XLOOKUP function and use cell E5 as the Lookup_value.
For the Lookup_array argument, use the Commission_Level range.
For the Return_array argument, use the Bonus_Rate range.
For the Match_mode argument, type −1 which finds the next smaller match (Figure 2-106).
XLOOKUP function to display bonus rate
Figure 2-106 XLOOKUP function to display bonus rate
Format the result as Percent Style with two decimal places.
Copy the formula in cell F5 to cells F6:F14.
Set order of operations to calculate the commission and bonus totals. (An alternative formula is =E5*(1+F5)).
Select cell G5. The total paid is the dollar amount of the commissions plus the commissions times the bonus rate.
Start a formula and select cell E5.
Add cell E5 to the product of commissions amount times the bonus rate (Figure 2-107).
Use parentheses to set the arithmetic order
Figure 2-107 Use parentheses to set the arithmetic order
Format the result to show zero decimal places.
Copy the formula in cell G5 to cells G6:G14.
Create and copy a SUMIF function to calculate total earnings by branch office.
Select cell E18.
Start the SUMIF function with cells $D$5:$D$14 as the Range argument.
Set the Criteria argument as a relative reference to cell C18.
Determine the Sum_range argument and make the references absolute.
Copy the formula in cell E18 to cells E19:E20 without formatting to preserve borders.
Format cells E18:E21 as Currency with zero decimal places.
Total the earnings in cell E21.
Calculate the average, mode, and median values in cells E22, E23, and E24 for the commission and bonus totals in column G.
Correct border and number format inconsistencies as needed.
Create and copy an IF function.
Select the By Policy sheet and click cell F7.
Start an IF function with the logical_test E7=“business policy” to determine if the policy is for a business. If it is for an individual, it is a personal line policy. You must type the quotation marks because text follows the cell reference.
Type No as the Value_if_true argument. You need not type quotation marks for this argument because it is alone in the entry box.
Type Yes as the Value_if_false argument (Figure 2-108).
IF function that uses text in the logical_argument
Figure 2-108 IF function that uses text in the logical_argument
Copy the formula in cell F7 to complete the column. If it is a business policy, the result is No because the label in cell F6 asks if it is a personal line.
Increase the indent two times for cells F7:F27.
Press Ctrl+Home.
Display names and locations using CONCAT.
Select the Tables sheet and click cell D2.
Start a CONCAT function.
The Text1 argument is cell C5 on the Commissions sheet.
The Text2 argument is a space.
The Text3 argument is cell B5 on the Commissions sheet.
The Text4 argument is a comma and a space.
The Text5 argument is cell D5 on the Commissions sheet.
Copy the formula in cell D2 to complete the list.
Select cell D13 and paste the range names.
Press Ctrl+Home.
Group all the sheets and center the pages horizontally.
While the sheets are grouped, insert a footer with the file name in the right section.
Ungroup the sheets.