How to Rank Values in Excel Ignoring Errors

It was a regular Monday morning at Shahrokh Enterprises, and Boss Shahrokh was scanning the latest performance report.

He frowned.

Shahrokh: “Anushka! Can you come to my cabin for a minute?”

Anushka, the Excel wiz of the team, walked in with her trusty laptop.

Shahrokh: “These sales numbers look fine, but look here…”
“Some cells show #DIV/0! and the ranks are all messed up. I just want to see who’s on top and who’s not — ignore these errors. Can you fix it?”

Anushka smiled. She had a trick up her sleeve.

---

🧠 The Challenge: Rank values but ignore error cells

Let’s say the data in column A looks like this:

Sales
1200
980
#DIV/0!
1150
#N/A
950

If you use the usual formula:

excelCopyEdit=RANK(A2, A$2:A$7)

It will throw errors because the range includes non-numeric values.

---

💡 The Solution: Use RANK + IF + ISNUMBER + FILTER / IFERROR

✅ Option 1: For Excel 365 or 2021 (with FILTER)

Anushka typed:

excelCopyEdit=IF(ISNUMBER(A2), RANK(A2, FILTER(A$2:A$7, ISNUMBER(A$2:A$7))), "")

“This will rank only the numeric values, and skip the error cells, Sir!” she explained.

• FILTER(A$2:A$7, ISNUMBER(A$2:A$7)) gives only numbers from the range.
• RANK(..., ...) works only on those.
• If A2 has an error, it shows a blank instead of another error.

---

✅ Option 2: For older Excel versions (without FILTER)

excelCopyEdit=IF(ISNUMBER(A2), RANK(A2, IF(ISNUMBER(A$2:A$7), A$2:A$7)), "")

Press Ctrl + Shift + Enter (array formula for Excel 2016 and below).

---

✨ Boss Shahrokh was impressed.

Shahrokh: “Anushka, this is brilliant! You’ve saved me from manually checking 300 rows of data.”

Anushka: “All in a day’s work, Sir. Excel never fails when you ask it the right way.”

---

📌 Summary:

To rank numbers while ignoring errors, wrap your formula in IF(ISNUMBER(...)) and use FILTER or IF + array logic to clean the input range.

---