File Needed: CentralSierra-02.xlsx (Available from the Start File link.)

Completed Project File Name: [your name]-CentralSierra-02.xlsx

1. 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.

NOTE: If group titles are not visible on your Ribbon in Excel for Mac, click the Excel menu and select Preferences to open the Excel Preferences dialog box. Click the View button and check the Group Titles check box under In Ribbon, Show. Close the Excel Preferences dialog box.

2. Select the Tables sheet, select cells A6:B10, and create range names using the Create from Selection button [Formulas tab, Defined Names group].
3. Select cells B1:F2 and click the Name box. Name the selection H_Rates. Note that the first row is arranged in ascending order.
4. Create an HLOOKUP function to display the bonus rate.
   1. Click the Commissions sheet tab and select cell F5.
   2. Start the HLOOKUP function and use cell E5 as the lookup_value.
   3. For the table_array argument, use the H_Rates range.
   4. Use the second row for the row_index_num argument (Figure 2-100).

      Figure 2-100 HLOOKUP function to display bonus

5. Format the results as Percent Style with two decimal places.
6. Copy the formula in cell F5 to cells F6:F14.
7. Set order of operations to calculate total earnings.
   1. Select cell G5.
   2. Build a formula to add the commissions amount (E5) to the commissions amount times the rate (F5*E5).
   3. Copy the formula in cell G5 to cells G6:G14.
8. Create and copy a SUMIF function to calculate total earnings by branch office.
   1. Select cell E18.
   2. Start the SUMIF function with cells $D$5:$D$14 as the Range argument.
   3. Set the Criteria argument as a relative reference to cell C18.
   4. Select cells G5:G14 for the Sum_range argument and make the references absolute.
   5. Copy the formula in cell E18 to cells E19:E20 without formatting to preserve borders.
   6. Format cells E18:E21 as Currency.
9. Total the earnings in cell E21.
10. Create and format the current date.
    1. Select cell G23 and insert the NOW function.
    2. Select cell G23 and click the Number drop-down list, and select More Number Formats. On the Number tab, select the Date category.
    3. 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).

       Figure 2-101 Date format selected

    4. Click OK. Press fn+Ctrl+Left Arrow or fn+Cmd+Left Arrow.
11. Create and copy a VLOOKUP function to display goals for each funding source.
    1. Click the Family Day sheet tab and select cell F6.
    2. Start the VLOOKUP function and use cell E6 as the lookup_value.
    3. 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.
    4. Use the second column as the col_index_num The range_lookup argument is empty.
    5. Copy the formula in cell F6 to cells F7:F20 without formatting to preserve the fill color.
    6. Format cells F6:F20 as Currency with no decimal places.
12. Create and copy an IF function.
    1. Select cell H6 and start an IF
    2. Type a logical_argument to determine if cell G6 is greater than or equal to (>=) cell F6.
    3. Type Yes as the Value_if_true argument and No as the Value_if_false argument.
    4. Copy the formula in cell H6 to cells H7:H20 without formatting to maintain the fill color.
    5. Center align cells H6:H20.
    6. Press fn+Ctrl+Left Arrow or fn+Cmd+Left Arrow.
13. Insert a new sheet at the end of the tab names and paste the range names starting in cell A1. AutoFit columns A:B and name the worksheet as Range Names.
14. Save and close the workbook (Figure 2-102).

Home Insert Draw Page Layout Formulas Date Review View Tell me A Share Comments le 5 TO Analysis Tools 21 El Flash Fill 27 Data Validation ... Connections Properties Edit Links LEE Get Data 0 Data from Picture Clear Reapply Advanced HA L What If Analysis El Group Ungroup Subtotal Refresh All STORE Currencies Geography Sort Filter Text to Remove Duplicates - Consolidate Columns G23 fx 11/3/2021 8:34:14 PM A C E F I 2 L M N P R S T V W Y 132,000 0.0225 0019 0.015 6 6 7 8 9 10 12 0.015 102 McCartney Lanita Folson 109 Santos Eleesha Carreron Park 104 Bact Roy Granite Ray 105 Alaro Jennifer Folsom 215 Chan Tami Cameron Park 217| Althouse Charlene Granite Boy 119 Taylor Juan Granite Bay 120 Repa ware Cameran Park 135 Nevens Cheryl Cameran Park 0.015 53000 53,000 54.790 $4,000 $4,000 542,000 $12, 270 $5.982.50 54.562.50 53.045 53.045 $4.77.52 $4,060 50,660 512,270 0011 001 12 12 0015 0.02251 16 15 16 17 18 19 Branch Totals Cameron Park 5 40.332.50 Folsom 5 25.325.00 Granite Bay $12,298.00 Total 69.64550 20 21 22 23 11 12/9/22 20:34 24 25 26 28 29 30 31 32 34 35 36 Commissions Family Day Tables + Ready El + 100% Home Insert Draw Page Layout Formulas Data - Review Share P Comments Clear H 4 Analysis Tools ! A V ET E View Tell me ALA Y Ceography Filter 41 Sort Connections Properties TIT Stocks 103 2 | Flash Fill Date Validation Teart to Remove Duplicates - Consolidate Columns C v Group E Ungroup Subtotal Get Data Currencies Reapply Advanced Data tram Picture Ratrash All What If Analysis 3 Edit Links F6 fx A B C H J K L M N 0 P 0 R S U V W X Y Central Sierra Insurance Family Day Community Fundraiser Goal Reached Goal Amount ID Last Name First Name Branch Source 103 Lingle Bub Cameron Park Raifle tickets 102 McCartney Lanita Folsom Walbrun pledge B 203 Santos Eleesha Cameron Park Cash conation 104 Dexter Rey. Granite Bay Candy sals 10 105 Alors Jennifer Folsom Pedometer sales 12 115 Chan Tami Cameron Park Raftickets 12 13317 Althause Charlene Granite Bay Walkinplada 13.11 Taylor Juan Granite Bay Walkinnpledge . ***** . 14.. 139 Reus ware.... Cameron Park Cach donation 15 ...25 Nevens 15 Chery Cameran Pak Candy sales 16 102 McCartney Lanita Folsom Candy sales 17 103 Santos 209 Eleesha Cameron Park Ralfle tickets 18 217| Althout Charlene Granite Bay Raffle tickets 19 104|Daxter Roy Granite Bay Pedometer sales ---- 20 205 Alarc Jennifer Folsom Cash conation 21 22 23 24 25 26 27 28 29 30 $790 1225 5725 $ $450 5790 5275 5275 $225 5229 $295 $39 1825 suo 1825 $1,500 Commissions Family Day Tables + Ready # 2 2 100% Home Insert Draw Page Layout Formulas Data --- Review Share Comments C Analysis Tools View Tell me 21A Y 21 Sart Y G E P 57. Carnections TOUT 10 Clea Y Reapely Advanced EN & Flash Fill 1 Data Validation Text to Remove Duplicates - Consolidate Columns Group v El Ungroup Subtotal Properties Edit Links Gat Data Data fram Picture Refresh All Currencies Stocks Filter Geography What if Analysis Al fx Commission A A D E E F F G . H I J K L L M . N 1 Commission $3,000 $6,000 $9,000 $12,000 $15,000 2 Bonus 1.50% 1.75% 1.90% 2.25% 2.50% 3 4 4 5 Source Goal 6 Candy sales $300 7 Cash donation $500 8 Pedometer sales $500 9 Raffle tickets $500 10 Walk/run pledge $250 11 12 13 Commissions Family Day Tables + Ready 1 - + 172% Home Insert Draw Page Layout Formulas Date Review View Tell me A Share Comments le 5 TO Analysis Tools 21 El Flash Fill 27 Data Validation ... Connections Properties Edit Links LEE Get Data 0 Data from Picture Clear Reapply Advanced HA L What If Analysis El Group Ungroup Subtotal Refresh All STORE Currencies Geography Sort Filter Text to Remove Duplicates - Consolidate Columns G23 fx 11/3/2021 8:34:14 PM A C E F I 2 L M N P R S T V W Y 132,000 0.0225 0019 0.015 6 6 7 8 9 10 12 0.015 102 McCartney Lanita Folson 109 Santos Eleesha Carreron Park 104 Bact Roy Granite Ray 105 Alaro Jennifer Folsom 215 Chan Tami Cameron Park 217| Althouse Charlene Granite Boy 119 Taylor Juan Granite Bay 120 Repa ware Cameran Park 135 Nevens Cheryl Cameran Park 0.015 53000 53,000 54.790 $4,000 $4,000 542,000 $12, 270 $5.982.50 54.562.50 53.045 53.045 $4.77.52 $4,060 50,660 512,270 0011 001 12 12 0015 0.02251 16 15 16 17 18 19 Branch Totals Cameron Park 5 40.332.50 Folsom 5 25.325.00 Granite Bay $12,298.00 Total 69.64550 20 21 22 23 11 12/9/22 20:34 24 25 26 28 29 30 31 32 34 35 36 Commissions Family Day Tables + Ready El + 100% Home Insert Draw Page Layout Formulas Data - Review Share P Comments Clear H 4 Analysis Tools ! A V ET E View Tell me ALA Y Ceography Filter 41 Sort Connections Properties TIT Stocks 103 2 | Flash Fill Date Validation Teart to Remove Duplicates - Consolidate Columns C v Group E Ungroup Subtotal Get Data Currencies Reapply Advanced Data tram Picture Ratrash All What If Analysis 3 Edit Links F6 fx A B C H J K L M N 0 P 0 R S U V W X Y Central Sierra Insurance Family Day Community Fundraiser Goal Reached Goal Amount ID Last Name First Name Branch Source 103 Lingle Bub Cameron Park Raifle tickets 102 McCartney Lanita Folsom Walbrun pledge B 203 Santos Eleesha Cameron Park Cash conation 104 Dexter Rey. Granite Bay Candy sals 10 105 Alors Jennifer Folsom Pedometer sales 12 115 Chan Tami Cameron Park Raftickets 12 13317 Althause Charlene Granite Bay Walkinplada 13.11 Taylor Juan Granite Bay Walkinnpledge . ***** . 14.. 139 Reus ware.... Cameron Park Cach donation 15 ...25 Nevens 15 Chery Cameran Pak Candy sales 16 102 McCartney Lanita Folsom Candy sales 17 103 Santos 209 Eleesha Cameron Park Ralfle tickets 18 217| Althout Charlene Granite Bay Raffle tickets 19 104|Daxter Roy Granite Bay Pedometer sales ---- 20 205 Alarc Jennifer Folsom Cash conation 21 22 23 24 25 26 27 28 29 30 $790 1225 5725 $ $450 5790 5275 5275 $225 5229 $295 $39 1825 suo 1825 $1,500 Commissions Family Day Tables + Ready # 2 2 100% Home Insert Draw Page Layout Formulas Data --- Review Share Comments C Analysis Tools View Tell me 21A Y 21 Sart Y G E P 57. Carnections TOUT 10 Clea Y Reapely Advanced EN & Flash Fill 1 Data Validation Text to Remove Duplicates - Consolidate Columns Group v El Ungroup Subtotal Properties Edit Links Gat Data Data fram Picture Refresh All Currencies Stocks Filter Geography What if Analysis Al fx Commission A A D E E F F G . H I J K L L M . N 1 Commission $3,000 $6,000 $9,000 $12,000 $15,000 2 Bonus 1.50% 1.75% 1.90% 2.25% 2.50% 3 4 4 5 Source Goal 6 Candy sales $300 7 Cash donation $500 8 Pedometer sales $500 9 Raffle tickets $500 10 Walk/run pledge $250 11 12 13 Commissions Family Day Tables + Ready 1 - + 172%