RANK.AVG Function in Excel

The 'number' parameter contains non-numeric data such as text, blank cells, or error values. This also occurs if attempting to rank a cell containing text that looks like a number (text-formatted numbers) or when formulas in the ranked cell return errors.

**Diagnostic Steps:** 1. **Verify data type:** Select the cell and check if it shows as text in the formula bar. Text-formatted numbers appear left-aligned by default. 2. **Use ISNUMBER test:** Create a helper column with `=ISNUMBER(B2)` to identify non-numeric cells. FALSE results indicate text or errors. 3. **Convert text to numbers:** Use the VALUE function: `=RANK.AVG(VALUE(B2), $B$2:$B$10, 0)` 4. **Handle errors gracefully:** Wrap in IFERROR to display friendly messages: `=IFERROR(RANK.AVG(B2,$B$2:$B$10,0),"N/A")` 5. **Clean imported data:** For data imported from external sources, use Text-to-Columns (Data tab > Text to Columns) to convert text-formatted numbers to proper numeric format. **Prevention:** - Apply Number formatting to columns before data entry - Use Data Validation to restrict input to numbers only - When importing data, explicitly convert text columns to numbers - Implement IFERROR wrappers in production formulas

Always validate data types before ranking. Use data validation (Data > Data Validation > Settings > Allow: Decimal) to restrict input to numbers only. This prevents user entry of text values that cause errors.

The value specified in the 'number' parameter doesn't exist anywhere in the 'ref' range. This commonly happens when ranking a value from one range against a different range, when using incorrect absolute references that exclude the ranked value, or after filtering/sorting operations that separate the value from its reference range.

**Troubleshooting approach:** 1. **Verify value is in range:** Ensure the cell you're ranking is included in the reference range. If ranking B2, the range must include row 2 (e.g., $B$2:$B$100, not $B$3:$B$100). 2. **Check absolute references:** Confirm $ symbols are correctly placed. When copying formulas, the range shouldn't shift to exclude values. 3. **Test with COUNTIF:** Use `=COUNTIF($B$2:$B$10, B2)` to verify the value exists in the range. Result should be > 0. 4. **Handle with IF wrapper:** Prevent error display: `=IF(COUNTIF($B$2:$B$10,B2)>0, RANK.AVG(B2,$B$2:$B$10,0), "Not Found")` 5. **For cross-sheet scenarios:** Verify sheet names and ranges exist and are spelled correctly: `=RANK.AVG(Sheet1!B2, Sheet2!$B$2:$B$100, 0)` - confirm Sheet2 contains the value from Sheet1. **Common causes:** - Ranking external data against internal dataset - Formulas copied incorrectly, shifting the reference range - Filtered data where visible values aren't in the actual range - Rounding differences causing slight numerical mismatches

Always include the ranked value in the reference range. Use dynamic named ranges or Excel Tables that automatically adjust as data grows. For separate lookup and ranking ranges, use VLOOKUP or INDEX-MATCH to ensure synchronization.

The reference range has been deleted, moved, or is invalid. This occurs when rows or columns containing the ref parameter are deleted, when copying formulas to sheets where the referenced range doesn't exist, or when external workbook links are broken.

**Recovery steps:** 1. **Immediate undo:** If you just deleted rows/columns, press Ctrl+Z to undo and restore the references. 2. **Rebuild formulas:** If undo isn't possible, manually recreate the formula with valid ranges: `=RANK.AVG(B2, $B$2:$B$50, 0)` 3. **Use named ranges for stability:** Instead of cell references, define named ranges (Formulas > Define Name): `=RANK.AVG(B2, SalesData, 0)` where SalesData is a named range. Named ranges survive row/column deletions better. 4. **Implement Excel Tables:** Convert your data to a table (Ctrl+T) and use structured references: `=RANK.AVG([@Score], Table1[Score], 0)` - table references adjust automatically when rows are deleted. 5. **For external workbook links:** Verify the source workbook is open and accessible. Use Edit Links (Data > Edit Links) to update or remove broken links. **Prevention strategies:** - Use named ranges for all critical data ranges - Convert data to Excel Tables before creating formulas - Avoid deleting rows/columns within active formula ranges - Document formula dependencies before major worksheet restructuring

Create named ranges (Insert > Name > Define) for all ranking data ranges. Named ranges are more resilient to worksheet changes. Use Excel Tables (Ctrl+T) which automatically adjust references when rows are inserted or deleted.

Incorrect order parameter (using 0 when 1 is needed, or vice versa), relative references without $ signs causing the range to shift when formulas are copied, hidden or filtered rows affecting expectations, or misunderstanding of how RANK.AVG handles ties with decimal numbers.

**Diagnostic checklist:** 1. **Verify order parameter:** - For sales, scores, ratings (higher is better): Use 0 or omit the parameter `=RANK.AVG(B2, $B$2:$B$100, 0)` or `=RANK.AVG(B2, $B$2:$B$100)` - For costs, times, errors (lower is better): Use 1 `=RANK.AVG(C2, $C$2:$C$100, 1)` 2. **Check absolute references:** Formula should use $B$2:$B$100, not B2:B100. Without $ signs, the range shifts when copied, causing incorrect rankings. 3. **Test with known values:** Create a simple test case with values 100, 200, 300 to verify order parameter is correct. 300 should rank 1 with order=0, or rank 3 with order=1. 4. **Filtered data considerations:** RANK.AVG always ranks against the entire range, not just visible cells. If data is filtered, ranks reflect the complete dataset, not the filtered subset. 5. **Understand decimal ranks:** RANK.AVG returns decimals for ties (e.g., 2.5, 5.0). This is correct behavior, not an error. If you need integers, use RANK.EQ instead. **Common mistakes:** - Sales ranked with order=1 shows lowest sales as rank 1 (inverted) - Costs ranked with order=0 shows highest cost as rank 1 (inverted) - Copying formulas without absolute references shifts the range - Expecting integer ranks but getting decimals due to ties

Always use absolute references ($B$2:$B$100) for the ref parameter. Test your ranking logic on a small subset before applying to full dataset. Document whether your metric uses descending (0) or ascending (1) ranking. Be explicit with the order parameter even when using the default.