Nested IF functions in Google Sheets - Dashboard Guide

Dashboard Mode - Nested IF functions

Goal

We want to classify student scores into grades: A, B, C, or Fail based on their marks.

Sample Data

Student	Score
Alice	92
Bob	85
Charlie	73
Diana	58
Edward	40

Dashboard Components

• Grade Column: Adds a grade for each student using nested IF formula.
  =IF(B2>=90,"A",IF(B2>=80,"B",IF(B2>=70,"C","Fail")))
  This formula checks the score in B2 and assigns a grade:
  • 90 or above: A
  • 80 to 89: B
  • 70 to 79: C
  • Below 70: Fail
• Count of Each Grade: Shows how many students got each grade.
  For example, count of A:
  =COUNTIF(C2:C6,"A")
  Similarly for B, C, and Fail.
• Pass Rate: Calculates percentage of students who passed (grades A, B, or C).
  =COUNTA(C2:C6)-COUNTIF(C2:C6,"Fail") gives number passed.
  Pass rate formula:
  =((COUNTA(C2:C6)-COUNTIF(C2:C6,"Fail"))/COUNTA(C2:C6))*100

Dashboard Layout

+----------------------+--------------------+
|      Student Scores  |    Grade Counts    |
|  (Table with Scores) |  A: [count of A]   |
|                      |  B: [count of B]   |
|                      |  C: [count of C]   |
|                      |  Fail: [count Fail]|
+----------------------+--------------------+
|                 Pass Rate: [pass rate]%          |
+--------------------------------------------------+

Interactivity

You can add a filter to select students by score range or name.

Self Check

If you filter to show only students with scores above 80, which students and grades will be visible? What would the pass rate be for those students?

Key Result

Dashboard classifies student scores into grades using nested IF and shows counts and pass rate.