Workbook and Analysis

PRO Example for add data to your spreadsheet Add Data In Section 1 on the Data page, complete each column of the spreadsheet to arrive at the desired calculations. Use Excel formulas to demonstrate that you can perform the calculations in Excel. Remember, a cell address is the combination of a column and a row. For example, C11 refers to Column C, Row 11 in a spreadsheet.

Reminder: Occasionally in Excel, you will create an unintentional circular reference. This means that within a formula in a cell, you directly or indirectly referred to (back to) the cell. For example, while entering a formula in A3, you enter =A1+A2+A3. This is not correct and will result in an error. Excel allows you to remove or allow these references.

Hint: Another helpful feature in Excel is Paste Special. Mastering this feature allows you to copy and paste all elements of a cell, or just select elements like the formula, the value or the formatting.

"Names" are a way to define cells and ranges in your spreadsheet and can be used in formulas. For review and refresh, see the resources for Create Complex Formulas and Work with Functions.

Ready to Begin?

1. As a starting point, in cell D9, use Define a Name under the Formulas menu to create the name "Annual_Hrs." When setting up the name, enter the constant =2080 in the box named "Refers to." This is the number most often used in annual salary calculations based on full time, 40 hours per week, 52 weeks per year.

2. In E11, create a formula that calculates the hourly rate for each employee, by referencing the employee’s salary in Column C, divided by the name you created for the annual hours of 2080. Complete the calculations for the remainder of Column E.

3. In Column F, calculate the number of years worked for each employee by creating a formula that incorporates cell F9 and demonstrates your understanding of relative and absolute cells in Excel.

4. In Column I, use an IF statement to flag with a "Yes" any employees who have been employed 10 years or more.

5. Using the function VLookup, use the Region Key located at F417:G420 to fill in the cells in Column N to identify the region in which the employee is located based on the state listed in Column M. (If this function is new to you – hang in there – this one is worth it!

There are some video resources available that address some common "hard spots" in this Excel

https://umuc.equella.ecollege.com/file/e47bc737-daef-4348-8e99-0f4d5a027315/1/PRO600-Add_Data.html 10/27/16, 10H06 AM Page 1 of 3

There are some video resources available that address some common "hard spots" in this Excel assignment. Do not be confused if you see a data set that is different than yours - the principles are the same! Remember, if you have any questions, ask.

Used with permission from Microsoft.

Resources Paste Special Options in Excel (https://umuc.equella.ecollege.com/file/dbf1f2da-e348-4592-a437- 44a880ffa5b8/1/Paste_Special_Options_In_Excel.html) Remove or Allow a Circular Reference (https://umuc.equella.ecollege.com/file/84653f65-03c9-4f13-8503- d637f1a214b7/1/Remove_or%20_Allow_a_Circular_Reference.html) Data Presentations (https://umuc.equella.ecollege.com/file/7f89d856-ace0- 4fa1-a291-dc2a3f8ff85f/1/Data_Presentations.html) Excel Tutorials: Formulas and Functions (https://umuc.equella.ecollege.com/file/92f3c482-679c-4383-92ad- a260fc082d6c/1/Excel_Tutorials_Formulas_and_Functions.html) Interpreting Statistics (https://umuc.equella.ecollege.com/file/7bcda488-

https://umuc.equella.ecollege.com/file/e47bc737-daef-4348-8e99-0f4d5a027315/1/PRO600-Add_Data.html 10/27/16, 10H06 AM Page 2 of 3

Interpreting Statistics (https://umuc.equella.ecollege.com/file/7bcda488- 1529-4b4b-9d2b-8a7553816564/1/Interpreting_Statistics.html)

https://umuc.equella.ecollege.com/file/e47bc737-daef-4348-8e99-0f4d5a027315/1/PRO600-Add_Data.html 10/27/16, 10H06 AM Page 3 of 3