Formula Errors

How to Rank Excel Values by Distance from the Mean

Emma BrownEmma Brown Nov 10, 2026 870 views

Question details

The user needs to rank a dataset based on how close each value is to the dataset's average, assigning the highest rank to the value closest to the mean.

How to Rank Excel Values by Distance from the Mean
Product
Excel
Device & OS
not provided
Scenario
Performing statistical data analysis where data points must be ordered or ranked by their absolute deviation from the average.
Observed behavior
Requires a specific formula combination to calculate the mean, determine the absolute distance of each value from that mean, and rank the results accordingly.
Before you start

Ensure your dataset does not contain error values, as these will cause the AVERAGE function to fail. It is also highly recommended to use absolute references (like $I$4:$I$227) for your data range so the formula remains accurate when copied down.

Solution 1Recommended

Use RANK.EQ, ABS, and AVERAGE Functions

Combine RANK.EQ with ABS and AVERAGE to calculate the absolute deviation from the mean and rank it in a single formula.

This method works across most versions of Excel. It calculates the average of the dataset, finds the absolute difference between the current cell and the average, and then ranks that difference against the array of all differences.

1
Select the output cell

Click on the cell adjacent to your first data point (for example, J4 if your data starts in I4).

2
Enter the ranking formula

Type the formula: =RANK.EQ(ABS(I4-AVERAGE($I$4:$I$227)),ABS($I$4:$I$227-AVERAGE($I$4:$I$227)),1) and adjust the range $I$4:$I$227 to match your actual dataset.

3
Evaluate as an array formula

If you are using an older version of Excel that does not support dynamic arrays, press Ctrl + Shift + Enter to apply the formula correctly.

4
Fill down the column

Click and drag the fill handle at the bottom-right corner of the cell down to apply the ranking formula to the rest of your data.

Use RANK.EQ, ABS, and AVERAGE Functions
Accurate Ranking: The '1' at the end of the RANK.EQ formula sorts in ascending order, meaning the smallest deviation (the value closest to the mean) receives rank 1.
Advanced Data Analysis

Rank and Analyze Data Easily with WPS Spreadsheet

WPS Spreadsheet provides full support for advanced array formulas, including RANK.EQ, AVERAGE, and ABS, allowing you to seamlessly analyze and rank your data without compatibility issues.

  1. 1. Open your dataset: Launch WPS Spreadsheet and open the workbook containing your data.
  2. 2. Select an empty cell: Click on the cell next to your first data point where you want the rank to appear.
  3. 3. Input the formula: Type the RANK.EQ, ABS, and AVERAGE combination formula and press Enter (or Ctrl+Shift+Enter for array evaluation).
  4. 4. Apply to all rows: Drag the fill handle down to instantly rank all values in your dataset.
Fully compatible with Microsoft Excel formulas, including complex array functions.Supports dynamic arrays for modern and efficient data analysis.Lightweight and fast, handling large datasets without lagging.Free to use with an intuitive, tabbed interface.
microsoft office alternative - wps office

Frequently Asked Questions

Why does my RANK.EQ array formula return a #VALUE! error?

This usually happens in older spreadsheet versions if you press just Enter instead of Ctrl+Shift+Enter. Array formulas require Ctrl+Shift+Enter to process the array of absolute differences correctly.

How can I reverse the ranking order?

Change the last argument in the RANK.EQ function from 1 (ascending) to 0 (descending). This will assign the highest rank (rank 1) to the value furthest from the mean.

Can I ignore blank cells when calculating the mean?

Yes, the AVERAGE function automatically ignores blank cells and text strings in the referenced range, so they will not skew your mean calculation.

What does the ABS function do in this formula?

The ABS function returns the absolute value of a number. It ensures that deviations below the mean (negative numbers) are treated as positive distances, allowing for an accurate comparison of distance regardless of direction.