Function Problems

How to Average Values Within a Percentage Range in Excel

Maira Mehtab Sep 22, 2026 869 views

Question details

An Excel user needs to calculate the average of percentage values in a specific range while excluding outliers that are below 0.001% and above 50%.

Product: Excel
Device & OS: not provided
Scenario: Filtering out extreme high and low values when computing the average of a percentage dataset.
Observed behavior: The user requires a formula to successfully average the values in the EA16:EA1500 range that strictly fall between the 0.001% and 50% thresholds.

Before you start

Verify that your data range contains numerical values formatted as percentages, as text-formatted numbers will be ignored by the averaging function.

Solution 1Recommended

Use the AVERAGEIFS Function to Average Within a Specific Range

The AVERAGEIFS function evaluates multiple conditions, making it the perfect tool for calculating an average with both a minimum and maximum threshold.

AVERAGEIFS allows you to apply multiple criteria to a single range. By specifying a lower limit and an upper limit as your criteria, you can easily exclude unwanted outliers from your final average calculation.

Select the target cell

Click on the empty cell in your worksheet where you want the calculated average to be displayed.

Enter the AVERAGEIFS formula

Type the formula =AVERAGEIFS(EA16:EA1500,EA16:EA1500,">=0.001%",EA16:EA1500,"<=50%") into the formula bar.

Apply the calculation

Press the Enter key to execute the formula and view the conditional average of your percentage range.

Using Decimal Values: If your cells are formatted as decimals rather than percentages, you can adjust the criteria to ">=0.00001" and "<=0.5".

Advanced Data Analysis

Calculate Conditional Averages Effortlessly in WPS Spreadsheet

WPS Spreadsheet fully supports the AVERAGEIFS function, allowing you to easily process complex datasets and exclude outliers with precision.

1. Open WPS Spreadsheet: Launch WPS Office and open your existing workbook containing the percentage data.
2. Select the target cell: Click the cell where you want to output the average result.
3. Input the formula: Type =AVERAGEIFS(EA16:EA1500,EA16:EA1500,">=0.001%",EA16:EA1500,"<=50%") and press Enter to calculate.

Fully compatible with Microsoft Excel formulas and .xlsx formats.Intuitive formula builder to seamlessly insert AVERAGEIFS and other statistical functions.Free, lightweight, and highly responsive even with large datasets.

microsoft office alternative - wps office

Frequently Asked Questions

Can I use cell references instead of typing percentages directly in the formula?

Yes. You can type your limits in separate cells (e.g., cell A1 for 0.001% and cell B1 for 50%) and reference them in the formula using an ampersand, like this: =AVERAGEIFS(EA16:EA1500,EA16:EA1500,">="&A1,EA16:EA1500,"<="&B1).

Why is my AVERAGEIFS formula returning a #DIV/0! error?

A #DIV/0! error occurs in AVERAGEIFS if no cells in the specified range meet all your criteria. Double-check your data to ensure there are numerical values strictly between 0.001% and 50%.

What is the difference between AVERAGEIF and AVERAGEIFS?

The AVERAGEIF function only allows you to apply a single condition (like finding values above 50%). The AVERAGEIFS function lets you apply multiple criteria simultaneously, which is required for defining both a minimum and maximum limit.

Excel & Data Problems