Function Problems

How to Average Grades and Exclude Withdrawn Students using AVERAGEIFS

Jason Miller Oct 16, 2026 869 views

Question details

Calculate the average of student grades based on multiple criteria: ensuring the student name column is not empty and excluding students with a 'Withdraw' status.

How to Average Grades and Exclude Withdrawn Students

Product: Spreadsheet
Device & OS: not provided
Scenario: A teacher or administrator needs to calculate the average grades of a class, but must skip rows where a student has withdrawn or where the student name is blank.
Observed behavior: Requires a formula that evaluates both a non-blank text condition and an exclusion condition to correctly average numbers in a specific range without errors.

Before you start

Ensure that your data ranges for grades, student names, and enrollment status are exactly the same size (e.g., rows 4 to 23) to prevent formula calculation errors.

Solution 1Recommended

Use the AVERAGEIFS Function

Use the built-in AVERAGEIFS function to evaluate multiple criteria, requiring a valid student name and a status other than 'Withdraw'.

The AVERAGEIFS function allows you to calculate the average of a range based on one or more true/false criteria. In this scenario, we use wildcards and logical operators to define our conditions.

Select the result cell

Click on the cell where you want the final average grade to be displayed.

Enter the AVERAGEIFS formula

Type the following formula: =AVERAGEIFS(N4:N23, E4:E23, "?*", G4:G23, "<>Withdraw")

Understand the criteria

The "?*" criterion requires at least one text character (ignoring completely blank cells), and "<>Withdraw" tells the spreadsheet to ignore any row where the status is exactly 'Withdraw'.

Calculate the result

Press Enter to apply the formula and view the average.

Dynamic Data Ranges: For a more reliable solution as your class size changes, format your data as a Table. This allows formulas to use structured references that expand automatically when new students are added.

Solution 2

Alternative: Calculate Average Using SUM and COUNTIF

If blank cells cause issues with wildcard matching in AVERAGEIFS, you can manually calculate the average by dividing the total sum by the number of active students.

Solve complex calculations effortlessly

Easily Manage Student Grades with WPS Spreadsheet

WPS Spreadsheet provides full support for advanced functions like AVERAGEIFS, SUM, and COUNTIF. Easily calculate grades, filter data, and analyze student performance smoothly with high accuracy and compatibility.

1. Open your gradebook: Launch WPS Spreadsheet and open the document containing your student data.
2. Select the target cell: Click on the cell where you want the class average to appear.
3. Apply the formula: Input the AVERAGEIFS formula, adjusting the cell ranges to match your specific layout.
4. Get the result: Press Enter to instantly calculate the class average excluding withdrawn students.

Fully compatible with Microsoft Excel formulas and data.User-friendly interface for managing tables, ranges, and formulas.Free built-in educational templates for teachers and educators.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my AVERAGEIFS formula return a #DIV/0! error?

This error occurs when no cells in your data range meet all the specified criteria, meaning the formula is trying to divide by zero. Double-check your spelling for criteria like 'Withdraw' and ensure your data ranges are aligned.

What does '<>Withdraw' mean in spreadsheet formulas?

The '<>' symbol is the logical operator for 'not equal to'. When used in a formula like '<>Withdraw', it instructs the function to include everything except the cells that contain exactly the word 'Withdraw'.

How do I make the formula update automatically when adding new students?

You can format your data range as a Table (Insert > Table). This allows you to use structured references (table names and column headers) in your formula instead of fixed cell references, so it expands automatically when you add new rows.

Excel & Data Problems