Function Problems

How to Use AVERAGEIF with a Module Prefix in Excel

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs to average a range of values based on specific column headings containing a module prefix (such as 1-1, 1-2).

Product: Spreadsheet (WPS / Microsoft Excel)
Device & OS: not provided
Scenario: Calculating an average where the criteria are dynamically linked to a module prefix stored in another cell (e.g., cell BA3).
Observed behavior: Attempting to use the LEFT function inside the AVERAGEIF criteria range results in an error or fails to correctly match the column headings.

Before you start

Ensure your column headings follow a consistent text pattern (like 'Module-Chapter') so wildcard text matching can be applied correctly without referencing errors.

Solution 1Recommended

Use Wildcard Characters in the AVERAGEIF Criterion

Instead of nesting the LEFT function, use an asterisk wildcard combined with your cell reference to correctly identify the module prefix.

The AVERAGEIF function evaluates ranges, but it does not support nesting string manipulation functions like LEFT directly inside the criteria argument array. Instead, you can utilize standard wildcard characters. By concatenating your target cell with an asterisk (*), you instruct the function to match any heading that begins with your specified prefix.

Select the Target Cell

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

Input the Formula

Type the following formula: =AVERAGEIF($C$3:$AZ$3, BA$3&"-*", $C4:$AZ4). In this formula, $C$3:$AZ$3 is the range containing your headings, BA$3 holds your target module prefix, and $C4:$AZ4 contains the values to be averaged.

Calculate the Result

Press the Enter key. The function will correctly average the cells in row 4 where the corresponding row 3 heading begins with the prefix found in BA3 followed by a hyphen.

Text Formatting Required: Wildcards only work on text strings. If your headings are formatted strictly as numerical values, you may need to convert them to text format (e.g., by adding an apostrophe before the number) for this formula to function correctly.

Advanced Data Analysis

Calculate Conditional Averages Seamlessly with WPS Spreadsheet

WPS Spreadsheet fully supports advanced functional logic, including AVERAGEIF and wildcard criteria. It's a lightweight, robust tool that makes conditional calculations effortless.

1. Open Your Data File: Launch WPS Spreadsheet and open the workbook containing your dataset.
2. Select a Blank Cell: Click on the cell where you want to output the averaged result.
3. Apply the Wildcard Formula: Enter the formula =AVERAGEIF($C$3:$AZ$3, BA$3&"-*", $C4:$AZ4) directly into the formula bar.
4. Fill Down if Necessary: Press Enter to compute. Drag the fill handle from the bottom-right corner of the cell to apply the calculation to other rows.

100% compatible with Microsoft Excel formulas and functionsBuilt-in support for advanced wildcards and conditional logicLightweight architecture for faster data processing on large datasetsFamiliar user interface ensuring zero learning curve

microsoft office alternative - wps office

Frequently Asked Questions

Why did using the LEFT function inside AVERAGEIF fail?

Functions like AVERAGEIF, SUMIF, and COUNTIF require their first argument to be a true cell range. If you use a function like LEFT to modify the range before evaluation, it turns the range into a memory array, which these specific functions cannot process directly. Wildcards are the standard workaround.

Can I use this wildcard method with multiple criteria?

Yes. If you need to average based on multiple conditions, you can use the AVERAGEIFS function instead. The same wildcard concatenation method (e.g., BA$3&"-*") works perfectly within the criteria arguments of AVERAGEIFS.

What if my module numbers don't have hyphens?

If your headings are simply followed by text or spaces (e.g., 'Module 1 Part A' instead of '1-1'), you can adjust the wildcard string. For example, changing the criteria argument to BA$3&" *" (adding a space before the asterisk) will match headings separated by spaces.

Is the AVERAGEIF function compatible between Microsoft Excel and WPS Spreadsheet?

Yes. The AVERAGEIF function, along with its support for wildcard criteria like asterisks and question marks, is completely identical and fully compatible between WPS Spreadsheet and Microsoft Excel.

Excel & Data Problems