How to Use SUM, AVERAGE, and Nested IF Functions in Microsoft Excel

Overview

This tutorial covers three essential Microsoft Excel functions: SUM, AVERAGE, and nested IF. Using a student grade sheet from 'YouTube University' containing test scores across Zoom, WhatsApp, Twitter, and Facebook, the instructor demonstrates how to calculate total marks and student averages before assigning letter grades based on a specific grading scale. The video explains fundamental spreadsheet mechanics, including why every formula must begin with an equal sign (=), how cell references (such as B5:E5) define ranges, and how to rapidly replicate formulas across entire columns using either click-and-drag or double-clicking the fill handle. Finally, the tutorial introduces nested IF logic to automate grade assignments from 'F' to 'A' based on calculated student averages. The instructor shows how building formulas in the formula bar improves readability, how to match opening and closing parentheses, and how dynamic recalculation automatically updates averages and grades when underlying raw scores change.

Introduction and Dataset Setup

00:00

The tutorial introduces a student academic report dataset for 'YouTube University' covering academic year 2020/2021. The table contains student names alongside marks across four subjects or communication platforms: Zoom (column B), WhatsApp (column C), Twitter (column D), and Facebook (column E). The goal is to compute Total Marks in column F using SUM, average marks in column G using AVERAGE, and assign performance letter grades in column H using an automated IF condition referencing a predetermined grading scale.

• All Excel formulas must begin with an equal sign (=).
• The example dataset tracks four subject scores per student across columns B through E.
• Total Marks, Average, and Grade occupy separate adjoining columns.

Calculating Total Marks with the SUM Function

01:45

To calculate total scores, the SUM function aggregates values across a specified cell range. The instructor demonstrates two ways to write this formula in cell F5: typing `=SUM(B5:E5)` using the colon operator to signify the range from the first subject (Zoom) to the last (Facebook), or typing `=SUM(` and dragging across cells B5 through E5 before closing the parenthesis and pressing Enter. Once calculated for the first student, the formula can be copied to the rest of the column by dragging the bottom-right corner fill handle downward or simply double-clicking the corner handle when it turns into a small black plus sign.

• Range syntax `B5:E5` includes all cells between B5 and E5 inclusive.
• Clicking and dragging across adjacent cells automatically inserts the range coordinates.
• Double-clicking the fill handle rapidly propagates the formula down adjacent populated rows.

=SUM(B5:E5)

Calculating Mean Scores with the AVERAGE Function

04:05

The AVERAGE function calculates the arithmetic mean of a student's marks. Working in column G (cell G5), the user enters `=AVERAGE(B5:E5)` or types `=AVERAGE(` and drags the mouse across cells B5 to E5. Closing the bracket and pressing Enter calculates the student's average score. Similar to the SUM column, the user copies this formula across all student rows by dragging down the fill handle.

• The AVERAGE function calculates the arithmetic mean of the numeric range.
• The argument syntax `=AVERAGE(B5:E5)` matches the range structure used in SUM.
• The fill handle copies the relative formula down column G for all student records.

=AVERAGE(B5:E5)

Constructing Nested IF Statements for Automated Grading

05:13

To assign letter grades, the instructor builds a multi-level nested IF formula referencing the student's average score in column G against a reference scale: >=90 produces 'A', >=80 produces 'B+', >=70 produces 'B-', >=60 produces 'C+', >=50 produces 'C-', and anything below 50 results in 'F'. Because of the length of nested statements, entering the formula into the Excel Formula Bar is recommended. Each IF level tests a condition and evaluates the next IF in its value_if_false argument. Because five IF statements are opened, exactly five closing parentheses must be appended at the end.

• Nested IF statements evaluate conditions sequentially from highest threshold to lowest.
• String values (such as letter grades 'A', 'B+', etc.) must be enclosed in double quotes.
• The number of closing parentheses at the end must equal the number of opened IF statements (5 in this case).

=IF(G5>=90,"A",IF(G5>=80,"B+",IF(G5>=70,"B-",IF(G5>=60,"C+",IF(G5>=50,"C-","F")))))

Testing Dynamic Updates and Filling the Column

07:57

The instructor verifies formula responsiveness by modifying raw subject scores (e.g., updating marks to 97 and 99). Excel immediately recalculates the Total Marks and Average, and the nested IF automatically updates the grade to 'A'. Finally, dragging the fill handle down column H applies the grading logic to all students in the class list.

• Excel dynamically recalculates dependent formulas when source cell inputs change.
• Dragging or double-clicking the fill handle applies the nested IF formula to all rows.
• Dynamic grading eliminates manual re-evaluation when student marks are revised.