IF function in Excel - Dashboard Guide

Dashboard Mode - IF function

Goal

Check if sales meet a target and show "Target Met" or "Target Not Met" for each sale.

Sample Data

Sale ID	Sales Amount	Target
1	150	100
2	90	100
3	120	100
4	80	100
5	200	100

Dashboard Components

• KPI Card: Total Sales
  Formula: =SUM(B2:B6)
  Result: 640
• KPI Card: Number of Sales Meeting Target
  Formula: =COUNTIF(D2:D6,"Target Met")
  Result: 3
• Table: Sales Status
  Formula in D2: =IF(B2>=C2,"Target Met","Target Not Met") copied down to D6
  Results:
  Row 2: Target Met
  Row 3: Target Not Met
  Row 4: Target Met
  Row 5: Target Not Met
  Row 6: Target Met

Dashboard Layout

+----------------------+----------------------------+
|      Total Sales      | Number of Sales Meeting     |
|        640           |         Target (3)           |
+----------------------+----------------------------+
|                                                      |
|               Sales Status Table                      |
|  Sale ID | Sales Amount | Target | Status             |
|  1       | 150          | 100    | Target Met        |
|  2       | 90           | 100    | Target Not Met    |
|  3       | 120          | 100    | Target Met        |
|  4       | 80           | 100    | Target Not Met    |
|  5       | 200          | 100    | Target Met        |
+------------------------------------------------------+ 

Interactivity

Add a filter to select sales above a certain amount. When you change the filter, the Sales Status table updates to show only those sales. The KPI cards update to reflect totals and counts for the filtered sales.

Self Check

If you add a filter to show only sales with Sales Amount >= 120, which sales appear in the Sales Status table? What is the updated Total Sales and Number of Sales Meeting Target?

Key Result

Dashboard shows sales compared to target using IF function to label each sale as 'Target Met' or 'Target Not Met', with summary KPIs.