USE THE COUNTIF FUNCTION

You want to calculate the number of employees in each state. You set up a worksheet with a summary section to calculate statistics by states, indicated by the state abbreviations. This will require the use of the COUNTIF function. Refer to Figure 7.19 as you complete Step 1.

a. Open e07h1Salary_LastFirst if you closed it at the end of Hands-On Exercise 1 and save it as e07h2Salary_LastFirst, changing h1 to h2. Click the 2-Stats Map sheet tab.

b. Click cell J3, click the Formulas tab, click More Functions in the Function Library group on the Formulas tab, point to Statistical, scroll through the list, and then select COUNTIF. Cell J3 is the cell in which you want to calculate the total number of employees in Georgia. The Function Arguments dialog box opens so that you can enter the range and criteria arguments.

c. Select the range C2:C26 to enter it in the Range box. Press F4 twice. The range C$2:C$26 contains the states to be counted. You made the range a mixed reference so that the rows do not change as you copy the function down the column.

d. Click in the Criteria box, click cell I3 to enter it, and then click OK. Because cell I3 contains the state abbreviation, you used the cell reference rather than typing the abbreviation in the Criteria box. Keep the relative reference so that it will change to the correct cell reference as you copy the function down the column. The function results indicate that seven employees work in Georgia.

e. Double-click the cell J3 fill handle to copy the function to the range J4:J6. Save the workbook. In the copied functions, the criteria argument reflects the state abbreviation for the respective row. By using the cell reference in the original function instead of text, you do not have to manually type state abbreviations to enter the remaining COUNTIF functions.

USE THE SUMIF FUNCTION

Next, you want to calculate the total payroll by summing employee salaries in each state using the SUMIF function. Refer to Figure 7.20 as you complete Step 2.

a. Click cell K3 in the 2-Stats Map worksheet, click Math & Trig in the Function Library group, and then select SUMIF. The Function Arguments dialog box opens so that you can enter the range, criteria, and sum_range arguments.

b. Select the range C2:C26 to enter it in the Range box. Press F4 twice. The range C$2:C$26 contains the states to be used to apply the criterion.

c. Click in the Criteria box and click cell I3. Cell I3 contains the criterion for the first state.

d. Click in the Sum_range box, select the range F2:F26, press F4 twice, and then click OK. The range F2:F26 contains the salaries to be used to calculate total salaries by state. The total payroll is $387,925 for Georgia employees.

e. Double-click the cell K3 fill handle to copy the function to the range K4:K6. Save the workbook

USE THE AVERAGEIF FUNCTION

You now want to focus on average salaries by job title. You will calculate the average account rep salary and the average manager salary using the AVERAGEIF function. Refer to Figure 7.21 as you complete Step 3.

a. Click cell J9 in the 2-Stats Map worksheet, click More Functions in the Function Library group, point to Statistical, and then select AVERAGEIF. The Function Arguments dialog box opens so that you can enter the range, criteria, and average_range arguments.

b. Select the range D2:D26 to enter it in the Range box. Press F4 twice. The range D$2:D$26 contains the job titles to be used to apply the criterion.

c. Click in the Criteria box and click cell H9. Cell H9 contains the criterion, Account Rep.

d. Click in the Average_range box, select the range F2:F26, press F4 twice, and then click OK. The average salary for Account Reps is $51,881.

e. Drag the cell J9 fill handle to copy the function to cell J10. Save the workbook.

USE THE COUNTIFS, SUMIFS, AVERAGEIFS, MAXIFS, AND MINIFS FUNCTIONS

Now you want to focus on the summarizing data for Account Reps hired before 1/1/2015. Specifically, you want to calculate the total number of Account Reps, total salary payroll, average salary, highest salary, and lowest salary. Because each of these calculations requires two criteria, you will use the SUMIFS, AVERAGEIFS, COUNTIFS, MAXIFS, and MINIFS functions. Refer to Figure 7.22 as you complete Step 4

a. Click cell J13, click More Functions and point to Statistical in the Function Library group, scroll through the list, and then select COUNTIFS.

b. Type D2:D26 in the Criteria_range1 box, click in the Criteria1 box, and then click cell H9. Similar to the COUNTIF function, COUNTIFS counts data points in a range that match specified criteria. In this step the first criterion is Account Rep, which is stored in cell H9.

c. Click in the Criteria_range2 box and type E2:E26. Type <1/1/2015 in the Criteria2 box and click OK. The function returns 9, the total number of Account Reps hired before 1/1/2015, by using the criteria Account Rep and <1/1/2015 to filter the data ranges D2:D26 and E2:E26.

d. Click cell J14, click Math & Trig in the Function Library group, and then select SUMIFS.

e. Type F$2:F$26 in the Sum_range box. The range F2:F26 is the range containing the salaries that you want to sum. Use mixed references in the Sum_range argument so that you can copy the function.

f. Type D$2:D$26 in the Criteria_range1 box, type H$9 in the Criteria1 box. The range D2:D26 is the first criteria range, and Account Rep is the text you want to use as a filter for that range. Use mixed references in the Criteria_range1 and Criteria1 arguments so that you can copy the function.

g. Type E$2:E$26 in the Criteria_range2 box, type <1/1/2015 in the Criteria2 box, and then click OK. The range E2:E26 is the second criteria range to apply the condition of hire dates before 1/1/2015. The total payroll for Account Reps hired before 1/1/2015 is $496,870.

h. Drag the cell J14 fill handle to the range J15:J17. This copies the SUMIFS function. You used mixed references for the arguments in the original function so that you could copy the function. The arguments are the same for the other functions you want, so it will be faster to edit the function names in the copied functions.

i. Click cell J15. Change SUMIFS to AVERAGEIFS and press Enter. Keep the rest of the function the same. The average payroll for Account Reps hired before 1/1/2015 is $55,208.

j. Click cell J16, change SUMIFS to MAXIFS, and then press Enter. Click cell J17, and change SUMIFS to MINIFS, and then press Enter. Keep the rest of the function the same. Save the workbook. The highest salary for Account Reps hired before 1/1/2015 is $61,000. The lowest sal-ary for Account Reps hired before 1/1/2015 is $46,000.

INSERT A MAP

Finally, you want to insert a map to indicate the total payroll by state. You will use the summary information generated from the SUMIF functions in the range K2:K6. Refer to Figure 7.23 as you complete Step 5.

a. Use Ctrl to select the non-adjacent ranges I2:I6 and K2:K6 in the 2-Stats Map worksheet.

b. Click the Insert tab, click Maps in the Charts group, and then select Filled Map. A map is inserted in the worksheet. The fill colors range from dark (highest salary) to light blue (lowest salary) for the four states.

c. Click the chart title, type Payroll by State, and then click the chart area.

d. Cut the map chart, click cell M1, and then click Paste.

e. Save the workbook. Keep the workbook open if you plan to continue with the next Hands-On Exercise. If not, close the workbook, and exit Excel.

USE THE COUNTIF FUNCTION

You want to calculate the number of employees in each state. You set up a worksheet with a summary section to calculate statistics by states, indicated by the state abbreviations. This will require the use of the COUNTIF function. Refer to Figure 7.19 as you complete Step 1.

a. Open e07h1Salary_LastFirst if you closed it at the end of Hands-On Exercise 1 and save it as e07h2Salary_LastFirst, changing h1 to h2. Click the 2-Stats Map sheet tab.

b. Click cell J3, click the Formulas tab, click More Functions in the Function Library group on the Formulas tab, point to Statistical, scroll through the list, and then select COUNTIF. Cell J3 is the cell in which you want to calculate the total number of employees in Georgia. The Function Arguments dialog box opens so that you can enter the range and criteria arguments.

c. Select the range C2:C26 to enter it in the Range box. Press F4 twice. The range C$2:C$26 contains the states to be counted. You made the range a mixed reference so that the rows do not change as you copy the function down the column.

d. Click in the Criteria box, click cell I3 to enter it, and then click OK. Because cell I3 contains the state abbreviation, you used the cell reference rather than typing the abbreviation in the Criteria box. Keep the relative reference so that it will change to the correct cell reference as you copy the function down the column. The function results indicate that seven employees work in Georgia.

e. Double-click the cell J3 fill handle to copy the function to the range J4:J6. Save the workbook. In the copied functions, the criteria argument reflects the state abbreviation for the respective row. By using the cell reference in the original function instead of text, you do not have to manually type state abbreviations to enter the remaining COUNTIF functions.

USE THE SUMIF FUNCTION

Next, you want to calculate the total payroll by summing employee salaries in each state using the SUMIF function. Refer to Figure 7.20 as you complete Step 2.

a. Click cell K3 in the 2-Stats Map worksheet, click Math & Trig in the Function Library group, and then select SUMIF. The Function Arguments dialog box opens so that you can enter the range, criteria, and sum_range arguments.

b. Select the range C2:C26 to enter it in the Range box. Press F4 twice. The range C$2:C$26 contains the states to be used to apply the criterion.

c. Click in the Criteria box and click cell I3. Cell I3 contains the criterion for the first state.

d. Click in the Sum_range box, select the range F2:F26, press F4 twice, and then click OK. The range F2:F26 contains the salaries to be used to calculate total salaries by state. The total payroll is $387,925 for Georgia employees.

e. Double-click the cell K3 fill handle to copy the function to the range K4:K6. Save the workbook

USE THE AVERAGEIF FUNCTION

You now want to focus on average salaries by job title. You will calculate the average account rep salary and the average manager salary using the AVERAGEIF function. Refer to Figure 7.21 as you complete Step 3.

a. Click cell J9 in the 2-Stats Map worksheet, click More Functions in the Function Library group, point to Statistical, and then select AVERAGEIF. The Function Arguments dialog box opens so that you can enter the range, criteria, and average_range arguments.

b. Select the range D2:D26 to enter it in the Range box. Press F4 twice. The range D$2:D$26 contains the job titles to be used to apply the criterion.

c. Click in the Criteria box and click cell H9. Cell H9 contains the criterion, Account Rep.

d. Click in the Average_range box, select the range F2:F26, press F4 twice, and then click OK. The average salary for Account Reps is $51,881.

e. Drag the cell J9 fill handle to copy the function to cell J10. Save the workbook.

USE THE COUNTIFS, SUMIFS, AVERAGEIFS, MAXIFS, AND MINIFS FUNCTIONS

Now you want to focus on the summarizing data for Account Reps hired before 1/1/2015. Specifically, you want to calculate the total number of Account Reps, total salary payroll, average salary, highest salary, and lowest salary. Because each of these calculations requires two criteria, you will use the SUMIFS, AVERAGEIFS, COUNTIFS, MAXIFS, and MINIFS functions. Refer to Figure 7.22 as you complete Step 4

a. Click cell J13, click More Functions and point to Statistical in the Function Library group, scroll through the list, and then select COUNTIFS.

b. Type D2:D26 in the Criteria_range1 box, click in the Criteria1 box, and then click cell H9. Similar to the COUNTIF function, COUNTIFS counts data points in a range that match specified criteria. In this step the first criterion is Account Rep, which is stored in cell H9.

c. Click in the Criteria_range2 box and type E2:E26. Type <1/1/2015 in the Criteria2 box and click OK. The function returns 9, the total number of Account Reps hired before 1/1/2015, by using the criteria Account Rep and <1/1/2015 to filter the data ranges D2:D26 and E2:E26.

d. Click cell J14, click Math & Trig in the Function Library group, and then select SUMIFS.

e. Type F$2:F$26 in the Sum_range box. The range F2:F26 is the range containing the salaries that you want to sum. Use mixed references in the Sum_range argument so that you can copy the function.

f. Type D$2:D$26 in the Criteria_range1 box, type H$9 in the Criteria1 box. The range D2:D26 is the first criteria range, and Account Rep is the text you want to use as a filter for that range. Use mixed references in the Criteria_range1 and Criteria1 arguments so that you can copy the function.

g. Type E$2:E$26 in the Criteria_range2 box, type <1/1/2015 in the Criteria2 box, and then click OK. The range E2:E26 is the second criteria range to apply the condition of hire dates before 1/1/2015. The total payroll for Account Reps hired before 1/1/2015 is $496,870.

h. Drag the cell J14 fill handle to the range J15:J17. This copies the SUMIFS function. You used mixed references for the arguments in the original function so that you could copy the function. The arguments are the same for the other functions you want, so it will be faster to edit the function names in the copied functions.

i. Click cell J15. Change SUMIFS to AVERAGEIFS and press Enter. Keep the rest of the function the same. The average payroll for Account Reps hired before 1/1/2015 is $55,208.

j. Click cell J16, change SUMIFS to MAXIFS, and then press Enter. Click cell J17, and change SUMIFS to MINIFS, and then press Enter. Keep the rest of the function the same. Save the workbook. The highest salary for Account Reps hired before 1/1/2015 is $61,000. The lowest sal-ary for Account Reps hired before 1/1/2015 is $46,000.

INSERT A MAP

Finally, you want to insert a map to indicate the total payroll by state. You will use the summary information generated from the SUMIF functions in the range K2:K6. Refer to Figure 7.23 as you complete Step 5.

a. Use Ctrl to select the non-adjacent ranges I2:I6 and K2:K6 in the 2-Stats Map worksheet.

b. Click the Insert tab, click Maps in the Charts group, and then select Filled Map. A map is inserted in the worksheet. The fill colors range from dark (highest salary) to light blue (lowest salary) for the four states.

c. Click the chart title, type Payroll by State, and then click the chart area.

d. Cut the map chart, click cell M1, and then click Paste.

e. Save the workbook. Keep the workbook open if you plan to continue with the next Hands-On Exercise. If not, close the workbook, and exit Excel.

USE THE COUNTIF FUNCTION

You want to calculate the number of employees in each state. You set up a worksheet with a summary section to calculate statistics by states, indicated by the state abbreviations. This will require the use of the COUNTIF function. Refer to Figure 7.19 as you complete Step 1.

a. Open e07h1Salary_LastFirst if you closed it at the end of Hands-On Exercise 1 and save it as e07h2Salary_LastFirst, changing h1 to h2. Click the 2-Stats Map sheet tab.

b. Click cell J3, click the Formulas tab, click More Functions in the Function Library group on the Formulas tab, point to Statistical, scroll through the list, and then select COUNTIF. Cell J3 is the cell in which you want to calculate the total number of employees in Georgia. The Function Arguments dialog box opens so that you can enter the range and criteria arguments.

c. Select the range C2:C26 to enter it in the Range box. Press F4 twice. The range C$2:C$26 contains the states to be counted. You made the range a mixed reference so that the rows do not change as you copy the function down the column.

d. Click in the Criteria box, click cell I3 to enter it, and then click OK. Because cell I3 contains the state abbreviation, you used the cell reference rather than typing the abbreviation in the Criteria box. Keep the relative reference so that it will change to the correct cell reference as you copy the function down the column. The function results indicate that seven employees work in Georgia.

e. Double-click the cell J3 fill handle to copy the function to the range J4:J6. Save the workbook. In the copied functions, the criteria argument reflects the state abbreviation for the respective row. By using the cell reference in the original function instead of text, you do not have to manually type state abbreviations to enter the remaining COUNTIF functions.

USE THE SUMIF FUNCTION

Next, you want to calculate the total payroll by summing employee salaries in each state using the SUMIF function. Refer to Figure 7.20 as you complete Step 2.

a. Click cell K3 in the 2-Stats Map worksheet, click Math & Trig in the Function Library group, and then select SUMIF. The Function Arguments dialog box opens so that you can enter the range, criteria, and sum_range arguments.

b. Select the range C2:C26 to enter it in the Range box. Press F4 twice. The range C$2:C$26 contains the states to be used to apply the criterion.

c. Click in the Criteria box and click cell I3. Cell I3 contains the criterion for the first state.

d. Click in the Sum_range box, select the range F2:F26, press F4 twice, and then click OK. The range F2:F26 contains the salaries to be used to calculate total salaries by state. The total payroll is $387,925 for Georgia employees.

e. Double-click the cell K3 fill handle to copy the function to the range K4:K6. Save the workbook

USE THE AVERAGEIF FUNCTION

You now want to focus on average salaries by job title. You will calculate the average account rep salary and the average manager salary using the AVERAGEIF function. Refer to Figure 7.21 as you complete Step 3.

a. Click cell J9 in the 2-Stats Map worksheet, click More Functions in the Function Library group, point to Statistical, and then select AVERAGEIF. The Function Arguments dialog box opens so that you can enter the range, criteria, and average_range arguments.

b. Select the range D2:D26 to enter it in the Range box. Press F4 twice. The range D$2:D$26 contains the job titles to be used to apply the criterion.

c. Click in the Criteria box and click cell H9. Cell H9 contains the criterion, Account Rep.

d. Click in the Average_range box, select the range F2:F26, press F4 twice, and then click OK. The average salary for Account Reps is $51,881.

e. Drag the cell J9 fill handle to copy the function to cell J10. Save the workbook.

USE THE COUNTIFS, SUMIFS, AVERAGEIFS, MAXIFS, AND MINIFS FUNCTIONS

Now you want to focus on the summarizing data for Account Reps hired before 1/1/2015. Specifically, you want to calculate the total number of Account Reps, total salary payroll, average salary, highest salary, and lowest salary. Because each of these calculations requires two criteria, you will use the SUMIFS, AVERAGEIFS, COUNTIFS, MAXIFS, and MINIFS functions. Refer to Figure 7.22 as you complete Step 4

a. Click cell J13, click More Functions and point to Statistical in the Function Library group, scroll through the list, and then select COUNTIFS.

b. Type D2:D26 in the Criteria_range1 box, click in the Criteria1 box, and then click cell H9. Similar to the COUNTIF function, COUNTIFS counts data points in a range that match specified criteria. In this step the first criterion is Account Rep, which is stored in cell H9.

c. Click in the Criteria_range2 box and type E2:E26. Type <1/1/2015 in the Criteria2 box and click OK. The function returns 9, the total number of Account Reps hired before 1/1/2015, by using the criteria Account Rep and <1/1/2015 to filter the data ranges D2:D26 and E2:E26.

d. Click cell J14, click Math & Trig in the Function Library group, and then select SUMIFS.

e. Type F$2:F$26 in the Sum_range box. The range F2:F26 is the range containing the salaries that you want to sum. Use mixed references in the Sum_range argument so that you can copy the function.

f. Type D$2:D$26 in the Criteria_range1 box, type H$9 in the Criteria1 box. The range D2:D26 is the first criteria range, and Account Rep is the text you want to use as a filter for that range. Use mixed references in the Criteria_range1 and Criteria1 arguments so that you can copy the function.

g. Type E$2:E$26 in the Criteria_range2 box, type <1/1/2015 in the Criteria2 box, and then click OK. The range E2:E26 is the second criteria range to apply the condition of hire dates before 1/1/2015. The total payroll for Account Reps hired before 1/1/2015 is $496,870.

h. Drag the cell J14 fill handle to the range J15:J17. This copies the SUMIFS function. You used mixed references for the arguments in the original function so that you could copy the function. The arguments are the same for the other functions you want, so it will be faster to edit the function names in the copied functions.

i. Click cell J15. Change SUMIFS to AVERAGEIFS and press Enter. Keep the rest of the function the same. The average payroll for Account Reps hired before 1/1/2015 is $55,208.

j. Click cell J16, change SUMIFS to MAXIFS, and then press Enter. Click cell J17, and change SUMIFS to MINIFS, and then press Enter. Keep the rest of the function the same. Save the workbook. The highest salary for Account Reps hired before 1/1/2015 is $61,000. The lowest sal-ary for Account Reps hired before 1/1/2015 is $46,000.

INSERT A MAP

Finally, you want to insert a map to indicate the total payroll by state. You will use the summary information generated from the SUMIF functions in the range K2:K6. Refer to Figure 7.23 as you complete Step 5.

a. Use Ctrl to select the non-adjacent ranges I2:I6 and K2:K6 in the 2-Stats Map worksheet.

b. Click the Insert tab, click Maps in the Charts group, and then select Filled Map. A map is inserted in the worksheet. The fill colors range from dark (highest salary) to light blue (lowest salary) for the four states.

c. Click the chart title, type Payroll by State, and then click the chart area.

d. Cut the map chart, click cell M1, and then click Paste.

e. Save the workbook. Keep the workbook open if you plan to continue with the next Hands-On Exercise. If not, close the workbook, and exit Excel.

USE THE COUNTIF FUNCTION

USE THE COUNTIF FUNCTION

USE THE SUMIF FUNCTION

USE THE SUMIF FUNCTION

c. Click in the Criteria box and click cell I3. Cell I3 contains the criterion for the first state.

c. Click in the Criteria box and click cell I3. Cell I3 contains the criterion for the first state.

e. Double-click the cell K3 fill handle to copy the function to the range K4:K6. Save the workbook

e. Double-click the cell K3 fill handle to copy the function to the range K4:K6. Save the workbook

USE THE AVERAGEIF FUNCTION

USE THE AVERAGEIF FUNCTION

c. Click in the Criteria box and click cell H9. Cell H9 contains the criterion, Account Rep.

c. Click in the Criteria box and click cell H9. Cell H9 contains the criterion, Account Rep.

e. Drag the cell J9 fill handle to copy the function to cell J10. Save the workbook.

e. Drag the cell J9 fill handle to copy the function to cell J10. Save the workbook.

USE THE COUNTIFS, SUMIFS, AVERAGEIFS, MAXIFS, AND MINIFS FUNCTIONS

USE THE COUNTIFS, SUMIFS, AVERAGEIFS, MAXIFS, AND MINIFS FUNCTIONS

d. Click cell J14, click Math & Trig in the Function Library group, and then select SUMIFS.

d. Click cell J14, click Math & Trig in the Function Library group, and then select SUMIFS.

INSERT A MAP

INSERT A MAP

a. Use Ctrl to select the non-adjacent ranges I2:I6 and K2:K6 in the 2-Stats Map worksheet.

a. Use Ctrl to select the non-adjacent ranges I2:I6 and K2:K6 in the 2-Stats Map worksheet.

c. Click the chart title, type Payroll by State, and then click the chart area.

c. Click the chart title, type Payroll by State, and then click the chart area.

d. Cut the map chart, click cell M1, and then click Paste.

d. Cut the map chart, click cell M1, and then click Paste.