Why logical functions enable decision-making in Excel - Dashboard Impact

Dashboard Mode - Why logical functions enable decision-making

Dashboard Goal

Understand how logical functions like IF help make decisions based on data in Excel.

Sample Data

Employee	Sales	Target
Alice	1200	1000
Bob	800	1000
Charlie	1500	1000
Diana	950	1000
Eva	1100	1000

Dashboard Components

• KPI Card: Total Sales
  Formula: =SUM(B2:B6)
  Shows total sales of all employees.
• KPI Card: Number Meeting Target
  Formula: =COUNTIF(B2:B6, ">=" & C2) (Note: Excel does not support array in COUNTIF, so use helper column)
  Better approach: Add helper column D with formula =IF(B2>=C2,1,0) copied down, then sum:
  =SUM(D2:D6)
  Shows how many employees met or exceeded their sales target.
• Table: Sales Status
  Helper column E with formula:
  =IF(B2>=C2,"Met Target","Below Target")
  Shows status for each employee.
• Chart: Sales vs Target
  Bar chart comparing Sales and Target for each employee.

Dashboard Layout

+----------------------+-----------------------+
| Total Sales: 5550    | Number Meeting Target: 3 |
+----------------------+-----------------------+
|                                      |
|       Sales vs Target Chart          |
|                                      |
+-------------------------------------+
| Employee | Sales | Target | Status   |
|-------------------------------------|
| Alice    | 1200  | 1000   | Met Target |
| Bob      | 800   | 1000   | Below Target |
| Charlie  | 1500  | 1000   | Met Target |
| Diana    | 950   | 1000   | Below Target |
| Eva      | 1100  | 1000   | Met Target |
+-------------------------------------+

Interactivity

Add a filter to select employees who met or did not meet the target. When filtered, the KPI cards, chart, and table update to show only the selected group.

Self Check

If you add a filter to show only employees who met the target, which components update?

• Total Sales will show sum of sales for those employees only.
• Number Meeting Target will update to count only filtered employees.
• Sales vs Target chart will display bars for filtered employees only.
• Sales Status table will list only employees who met the target.

Key Result

Dashboard shows how logical IF functions help decide if employees met sales targets and summarize results.