Nested IF functions in Excel - Dashboard Guide

Dashboard Mode - Nested IF functions

Dashboard Goal

Determine the performance rating of salespeople based on their sales numbers using nested IF functions.

Sample Data

Salesperson	Sales
Alice	1200
Bob	800
Charlie	450
Diana	300
Edward	1500
Fiona	700

Dashboard Components

• KPI Cards: Show count of salespeople in each performance category:
  • Excellent (Sales >= 1000)
  • Good (Sales >= 700 and < 1000)
  • Average (Sales >= 400 and < 700)
  • Poor (Sales < 400)
  
  Formulas:
  =COUNTIF(B2:B7,">=1000") for Excellent
  =COUNTIFS(B2:B7,">=700", B2:B7,"<1000") for Good
  =COUNTIFS(B2:B7,">=400", B2:B7,"<700") for Average
  =COUNTIF(B2:B7,"<400") for Poor
• Performance Rating Column: Add a new column Rating in column C with nested IF formula:
  =IF(B2>=1000, "Excellent", IF(B2>=700, "Good", IF(B2>=400, "Average", "Poor")))
  This formula assigns a rating based on sales value in B2. Copy down for all rows.

Dashboard Layout

+-----------------------------+
|       Performance Rating     |
| +---------+ +---------+     |
| |Excellent| |   Good  |     |
| |  KPI    | |  KPI    |     |
| +---------+ +---------+     |
| +---------+ +---------+     |
| | Average | |  Poor   |     |
| |  KPI    | |  KPI    |     |
| +---------+ +---------+     |
+-----------------------------+
| Salesperson | Sales | Rating |
|-------------|-------|--------|
| Alice       | 1200  | Excellent |
| Bob         | 800   | Good     |
| Charlie     | 450   | Average  |
| Diana       | 300   | Poor     |
| Edward      | 1500  | Excellent|
| Fiona       | 700   | Good     |
+-----------------------------+

Interactivity

Add a filter dropdown for Rating to select one or more performance categories. When a category is selected, the table below updates to show only salespeople with that rating. The KPI cards update their counts to reflect only the filtered data.

Self Check

If you filter the dashboard to show only Good ratings, which salespeople appear in the table and what are the updated KPI counts?

Key Result

Dashboard shows salespeople's performance ratings using nested IF formulas and counts per category.