In this chapter, we’re going to look at conditional logic in Excel—specifically IF statements. We’ll start with a spreadsheet that lists employees for a company and includes their gender, position, age, college GPA, and current salary.
Use this workbook for the chapter.
Next, we’ll create a dashboard. The dashboard will include input and output cells. The input cells labeled as “Conditional Inputs” will include gender, age, retirement age, years window, bonus percent, raise over percent, raise under percent, and two GPA cells. The output cells labeled as “Conditional Outputs” will include average salary, number of employees, and employees eligible to retire. Finally, we’ll insert data into the input cells. (See Figure 17.1)
Note: We can name these cells as we learned in the “Named Ranges” chapter. The cell names can be used hereafter in the formulas we will work on within this chapter.
On the data spreadsheet shown in Figure 17.1, if we want to find the average salary of employees of a specific gender, we can use an IF function to list salaries in an empty column based on the dashboard’s input cell for employees’ gender. The IF function will check if an operation (equal to, less than, or greater than) involving two data values is true or false, and it can display a specific result if true or a different result if false. In this example, the logic test will check if the employee’s gender matches the gender displayed on the dashboard, and if true, it will display the employee’s salary. Then, we can reference the column’s cells in an AVERAGE function to find the total average salary for that gender.
Copy the formula down the column for each employee. Finally, create an AVERAGE function on the dashboard's Average salary output cell to reference this column's cells. The cell will display an overall average based on the gender selected in the dashboard's Gender input cell.
Note: There is a specific function known as AverageIFS that can calculate the same result using similar formula concepts in a single cell. Please research the AverageIFS function and learn more about how it can calculate the same result.
If we want to find the total employees under a specific age, we can use an IF function in an empty column to check if the employee’s age is less than the age specified on the dashboard’s Age input cell.
Copy the formula down the column for each employee. Finally, create a SUM function on the dashboard's # of employees under the output cell to reference this column's cells. The cell will display a total of employees under the age specified in the Age input cell.
If we want to determine an employee’s raise based on their college GPA, we can write a formula to check the GPA and whether the raise will be 3% or 8%.
Format the cell as a percentage and copy the formula down the column for each employee.
If we want to find the raise dollar amount, we can multiply the percentage by the current salary in a new cell. Alternatively, we can copy or add to the raise percentage formula to multiply by the salary (=IF(F3>in_raise_gpa,in_raise_over,in_raise_under)*G3).
If we want to know the total new salary, we can modify the formula to add the IF calculation to 1 which signifies 103% or 108% (depending on the percent returned based on the employee’s GPA). The formula will calculate the total new salary in a single cell.