TEACHING EXCEL FOR DATA ANALYSIS: HANDS ON 17 Analyzing data using logical functions

 

Analyzing data using logical functions

1. Click on the link below to open the Microsoft Excel exercise document you can use to complete this exercise.

https://github.com/alcadelina/Excel-

2. First you need to fill in the ID column. This data is the first name and last name separated by an underscore (_). While you can do this manually, the TEXTJOIN function is built for exactly this type of work. Select cell C5, then, in the Formula Bar, type “=TEXTJOIN(“_”,TRUE,A5,B5)”

3. Press Enter, to enter the formula, then select cell C5 again, hover your cursor over the cell handle until your cursor icon turns into a small black cross:

4. Double click on the cell handle to automatically copy your formula down the column, to cell C14:

5. Next, you need to determine which salespeople are going to receive a bonus. Bonuses are dispensed when the representative exceeds their weekly sales goal, and if their call reports are complete. For this purpose, you can use the AND function. Use your cursor to select cells G5 on the worksheet:

6. Inside the Formula Bar, type “=AND(D5="Yes",E5>F5)” and then press Enter:

7. Because both logical tests are true, the formula returns the value of TRUE in cell G5. Now drag the AutoFill handle at the bottom right of the cell down to cell G14:

8. All sales representatives who have met both conditions now show a value of TRUE in this column. To add the bonus amount to column H, first select cells H5 through H14:

9. Inside the Formula Bar, type “=IF(G5:G14=TRUE,K2, 0)”. Because this is an array function, press Ctrl + Shift + Enter:

10. The bonus information has now been calculated for all employees in this worksheet:

11. Save the current workbook as Activity 2-2 Complete and then close Microsoft 365 Excel to complete this exercise.

12. Now, you can check out an example of a completed document in the link below:

https://github.com/alcadelina/Excel-