BENTECH

Excel Essentials: SUM, AVERAGE, and Nested IF Automation

Master fundamental Excel calculation and decision-making functions by building a complete student gradebook from scratch.

This practical course teaches learners how to construct automated spreadsheets using core Excel functions. You will learn the mechanics of formula syntax, how to sum and average student course marks across row ranges, how to auto-fill formulas efficiently across large datasets, and how to construct multi-tier nested IF statements in the formula bar to assign letter grades dynamically.

Section 1: Basic Aggregations: SUM and AVERAGE

Learn formula entry rules and calculate row-based totals and means with fill handle automation.

Lesson 1.1: Spreadsheet Setup and the SUM Function

  • Equal sign prefix rule
  • Range reference syntax
  • Click-and-drag range selection
  • Fill handle drag and double-click

All formulas in Microsoft Excel must start with an equal sign (=). Without this sign, Excel interprets user entries as plain text strings rather than calculating commands. To aggregate multiple adjacent numeric fields, the SUM function uses the colon (:) operator to specify a contiguous range. In our student mark sheet, the subjects span columns B through E on row 5. The expression `=SUM(B5:E5)` adds Zoom (B5), WhatsApp (C5), Twitter (D5), and Facebook (E5). Rather than retyping the formula for every student row, Excel offers the fill handle—a small square at the bottom-right corner of the active cell cursor. Hovering over it turns the pointer into a slim cross. Dragging this handle downwards, or double-clicking it, instantly copies the relative formula to all subsequent rows.

Practical work

  1. Create a table with student names and test scores across columns B, C, D, and E starting at row 5.
  2. In cell F5, enter `=SUM(B5:E5)` and verify the total.
  3. Clear the cell and re-enter the formula by typing `=SUM(` and clicking-and-dragging across cells B5:E5.
  4. Double-click the fill handle in cell F5 to populate total marks for all student rows.

Lesson 1.2: Calculating Means with the AVERAGE Function

  • Arithmetic mean calculation
  • Range selection for AVERAGE
  • Propagating averages across columns

The AVERAGE function computes the arithmetic mean of a series of numbers. It sums the designated numeric values and divides that sum by the total count of numbers in the selected range. Just as with SUM, the range coordinates are supplied inside parentheses: `=AVERAGE(B5:E5)`. You can type the cell references manually or click and drag across the score columns to populate the arguments. Once entered for the first record, the formula can be dragged down column G to calculate the mean score for every student in the dataset.

Practical work

  1. Select cell G5 adjacent to Total Marks.
  2. Enter `=AVERAGE(B5:E5)` and press Enter.
  3. Verify that the calculated average accurately represents the midpoint of the four course unit scores.
  4. Drag the fill handle down to apply the average calculation to all student rows.

Section 2: Logical Grading: Nested IF Functions

Master nested IF logic in the Formula Bar to create automated, dynamic grading criteria.

Lesson 2.1: Constructing and Nesting the IF Function

  • IF statement syntax: logical test, value if true, value if false
  • Formula Bar usage for complex strings
  • Descending threshold ordering
  • Matching opening and closing parentheses

The IF function tests a condition and outputs one value if true and another if false. When multiple conditions must be evaluated (such as assigning grades A, B+, B-, C+, C-, and F), IF statements can be nested inside one another by placing the next IF check inside the `value_if_false` parameter. To ensure proper evaluation, conditions should be ordered from highest threshold to lowest: `>=90` returns 'A', `>=80` returns 'B+', `>=70` returns 'B-', `>=60` returns 'C+', `>=50` returns 'C-', and the final false fallback returns 'F'. Text values returned by formulas must always be enclosed in double quotes. Because nested expressions become long, working directly inside the Excel Formula Bar at the top of the worksheet minimizes errors. Every opened IF function introduces an opening parenthesis; therefore, you must append an equal number of closing parentheses at the very end of the formula (e.g., 5 opening parentheses require 5 closing parentheses).

Practical work

  1. Click cell H5 and activate the Formula Bar.
  2. Type `=IF(G5>=90,"A",IF(G5>=80,"B+",IF(G5>=70,"B-",IF(G5>=60,"C+",IF(G5>=50,"C-","F")))))` exactly as shown.
  3. Count your opening and closing parentheses to ensure you have 5 of each before pressing Enter.
  4. Verify that the assigned letter grade matches the student's average in G5 according to the grading scale.

Lesson 2.2: Dynamic Testing and Full Table Deployment

  • Dynamic formula recalculation
  • Testing edge cases in marks
  • Final table auto-fill

One of Microsoft Excel's core advantages is dynamic recalculation. Because formulas reference cells rather than fixed values, altering any raw score automatically updates the dependent SUM in column F, the AVERAGE in column G, and the letter grade in column H without requiring manual recalculation. Testing edge values (for example, raising a score so an average surpasses 90) provides immediate confirmation that the nested logical conditions are functioning correctly. Once verified on the first student record, the nested IF formula can be dragged down across all remaining rows in column H, completing an automated gradebook.

Practical work

  1. Change one of the student's test scores in row 5 to 99 and observe if the average updates and the grade flips to 'A'.
  2. Change the score to a lower value and confirm the grade drops accordingly.
  3. Drag the fill handle from cell H5 to the bottom of the table to generate grades for every student.
  4. Verify that students with averages below 50 correctly receive an 'F'.