Excel work assignment

F5 ~ X v' f,, =HLOOKUP(ES,H _ Rates,2)

A I B I C I D I E I F I G I H I I I J I K I L I M N 1_

Centra l Sierra Insura nce I Commission Sales and Bonuses

2 Function Arguments ? X

3 HLOOKUP

4 ID Last Name First Name Branch Commissions Bonus Rate Lookup_value I ES 1~1 = 15000 < 101 Lincle Bob Cameron Park Sl'.,000 Rates 2) T~ble_array I H_Rates 1~1 = {3000,6000,9000, 12000,15000;0.015,0.( 6 102 McCartnev Lanita Folsom S12 ooo Row_index_num 121 1~1 = 2 7 103 Santos Eleesha Cameron Park $5,500

Range_l ookup C ~ = logteal 8 104 Baxter Roy Granite Bay s4,500 = 0.025

9 105 A laro Jennifer Folsom S3,000 Looki f or -, va l ue in th e top row of a tab l e or om1y ofvolun ond rcturn5 t h e voluc in the H i me column fro m a row you specify,

10 115 Chan Tami Cameron Park s3,ooo Row_index_num is the row number in table_arrayfrom w hi ch the match ing value

11 117 Althouse Charl ene Granite Bay s4,700 sh ould be return ed . The firit row of volu c ~ in the tabl e i ~ row 1,

12 119 Taylor Juan Granite Bay s41000

120 Reia Wayne Cameron Park s41000 Formula rnult = 0 .02~

13

14 125 Nevens Cheryl Cameron Park s12,ooo Hell;!: on this function I OK I ~ 1J

15_ j- -1- . <

2-100 HLOOKUP function to display bonus rate

USING MICROSOFT EXCEL 2016 Independent Project 2-4

Step 1: Download start file

Excel 2016 Chapter 2 Working with Formulas and Functions Last Updated: 12/18/17 Page 1

Independent Project 2-4 Central Sierra Insurance (CSI) sets bonus percentages based on commissions earned by each agent and calculates totals by branch office. This workbook also tracks fundraising efforts of employees for a community event.

Skills Covered in This Project • Create, copy, and edit formulas. • Name cell ranges. • Set mathematical order of operations. • Set cell references to be absolute.

• Use the NOW function. • Use HLOOKUP and VLOOKUP functions. • Use the SUMIF function. • Build an IF function.

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

2. On the Tables sheet, select cells A6:B10 and create range names using the Create from Selection dialog [Formulas tab, Defined Names group].

3. Select cells B1:F2 and click the Name box. Name this as H_Rates.

4. Create an HLOOKUP function to display the bonus rate. a. Click the Commissions sheet tab and select cell F5. b. Start the HLOOKUP function and use cell E5 as the lookup_value. c. For the table_array argument, use the H_Rates range. d. Use the second row for the row_index_num argument (Figure 2-100).

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. a. Select cell G5. b. Build a formula to add the commissions amount (E5) to the commissions amount times the rate

(F5*E5). c. Copy the formula in cell G5 to cells G6:G14.

8. Create and copy a SUMIF function to calculate total earnings by branch office. a. Select cell E18. b. Start the SUMIF function with cells $D$5:$D$14 as the Range argument.

Fo rmat Cells X

~ Alignment Font I Bo rder I Fill I Prot, ction I ~atego ry.

General Number Currency

.~m.°untin g

Time Percentage Fraction Scientifi c Text Special Cust om

Sample

Janua ry 1, 2018

!Ype:

14 -Ma r 14-Mar-12 14-Mar-12 Mar-12 March-12 IHffiilbf~ i 3/ 1-4/ 12 1:30 PM

.!:o cale (lo cation):

I English (United States~ H

Date formats di splay date and time serial numb ers as date values. Date fo rmats that begin w ith an asteri st (*) re spond to changes in regio nal date and time settings that .are specified for the o perating system . Fo rmats without an a sterisk are not affeded by operating system sett ings .

~-O_K _~I I Cancel

2-101 Date format selected

USING MICROSOFT EXCEL 2016 Independent Project 2-4

c. The Criteria argument is a relative reference to cell C18.

Step 2 Upload & Save

Step 3 Grade my Project

d. Select cells G5:G14 for the Sum_range argument and make the references absolute.

e. Copy the formula in cell E18 to cells E19:E20 without formatting.

f. Format cells E18:E20 as Currency.

9. Total the earnings in cell E21.

10. Create and format the current date. a. Select cell G23 and insert the NOW function. b. Select cell G23 and click the Number group

launcher [Home tab]. On the Number tab, select the Date category.

c. 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).

d. Click OK. Press Ctrl+Home.

11. Create and copy a VLOOKUP function to display goals for each funding source. a. Click the Family Day sheet tab and select

cell F6. b. Start the VLOOKUP function and use cell E6

as the lookup_value. c. Click the Tables sheet tab for the

table_array argument and use cells $A$6:$B$10.

d. Use the second column as the col_index_num argument.

e. Copy the formula in cell F6 to cells F7:F20 without formatting to preserve the fill color.

f. Format cells F6:F20 as Currency with no decimal places.

12. Create and copy an IF function. a. Select cell H6 and start an IF function. b. Type a logical_argument to determine if cell

G6 is greater than or equal to (>=) cell F6. c. Type Yes as the Value_if_true argument and

No as the Value_if_false argument. d. Copy the formula in cell H6 to cells H7:H20

without formatting to maintain the fill color. e. Center align cells H6:H20. f. Press Ctrl+Home.

13. Insert a new sheet at the end of the tab names and paste the range names starting in cell A1. Name the worksheet as Range Names.

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

15. Upload and save your project file.

16. Submit project for grading.

Excel 2016 Chapter 2 Working with Formulas and Functions Last Updated: 12/18/17 Page 2

Central Sierra Insurance Fam ily Day Comm unit~ Fu ndrais;,r

· - I:<! Lr.qi ·-- Bot, ___ (~"HNl'l~ RidDialillM~ _ __ -m:i McC11tn•r. u rr ... __ 1"1111_,, __ w.a ~ pl·~· -- ~ S.., rm ___ a-. _ ~SQn!Pn: c..i dal'lnan _ , ·---· Ba.TIU ___ Rut_, ____ GraMill B_!l __ gra.i_~ris _ _ _ - - ~,i,.-.c; __ _ .illmM _ Falaom __ PacbntitarMN , _

·--- · C"'"'1 ·--- T~III _ ____ Q/'1 4t<ln!P.nc fbfflr;i; dD; __ __ ¥.ii_ l•W-one __ CM"tne G'IM!: Bl _ W-ltnm~}~f . __ i: ___ ug T1.fb: ___ ii1n __ __ _ "9rNBg w.,~ p1~'!1!,.

UJ lil1n W.; M Cirl4flan~ c..:.ttdohil!SM __ -i~,._._.. __ Chrft _ ~S"GniP.tr: c.-ctt•• __ , · - -:mi ~11tn1L UiltN __ Falaom __ ~-- __

--~ ~rws. __ _ B~ _ tnieNnAi'k fbffle: •dHs - · ·-- ~-_ aw-- _ "9111111 B!J A.R.111cba _ --~ Bmu ___ ~ __ G'IMe B!l,_ ~te:r~,.

itac> kmltr Fd,om CH10cno1on

=Tabfe,!,e;,6

Cmh_OJrut i::Jn =T 11 bfe,!sB$-7

H..Rat•s -Tabl•slsBusf». Pedcml!be r~ole., =T11bfe,!s8s8

lt affie_l d::ets =T 11 bfes!sfi$.g

w a111 .. .1u nJ)le<1g•-Tabl•sl~ to

2-102 Excel 2-4 completed