Do you want to find the rank of a projected value in a dataset, specifically the rank of a non-existing number within an existing data range?

This can be quite useful in certain scenarios, and the formula is also quite simple. Let’s understand the scenario first.

For example, let’s say we have the scores of a few participants in a game listed in the range B2:B7.

Sample Data:

Using the RANK function, we can easily find the rank of each participant. We can use the following array formula in Cell C2:

=ArrayFormula(RANK(B2:B7, B2:B7, 0))

Now, suppose we want to find the rank of another participant whose score has not yet been added to the existing data. We know their score is 100, but we’re waiting for confirmation before adding it to the dataset. Nonetheless, we still want to find their rank.

Here’s how to do it.

Steps for Ranking a Non-Existing Number in Google Sheets

If the data is in rows (down the column) as shown above, use VSTACK to stack the projected score with the existing data as follows:

=VSTACK(B2:B7, 100)

If your data is arranged in columns (across the row), you should replace VSTACK with the HSTACK function.

Now you can find the rank of the non-existing number as follows (we can’t call it a non-existing number anymore as we added it to the range virtually):

=RANK(100, VSTACK(B2:B7, 100), 0)

Please note that, as per the above formula, the greatest value in the data will have rank 1. Modify the formula as follows to get rank #1 for the least value in the data:

=RANK(100, VSTACK(B2:B7, 100), 1)

Important Points

Before applying VSTACK to add the data, you should first try to get the rank of the number using a regular RANK function and ensure that the formula returns #N/A. This ensures that the value does not already exist in the list.

If you use a 2D array like B2:C7, you should additionally wrap the VSTACK with IFNA as follows:

=RANK(15, IFNA(VSTACK(B2:C7, 15)))

This is because adding a value in a 2D array can cause unequal-sized arrays, and to make them equal, Google Sheets returns a #N/A error in the additional columns. RANK won’t work within a range that contains errors. IFNA solves that.

Adding a new value to the range will affect the overall ranking.

Resources

Here are a few related resources.

• Flexible Array Formula to Rank Without Duplicates in Google Sheets
• How to Rank Group Wise in Google Sheets in Sorted or Unsorted Group
• Top 10 Ranking without Duplicate Names in Google Sheets
• Compare and Highlight Up and Down in Ranking in Google Sheets
• Find the Rank of an Item in Each Column in Google Sheets
• Highlight the Top 10 Ranks in Single or Each Column in Google Sheets
• How to Rank without Ties in Google Sheets
• How to Rank Data by Alphabetical Order in Google Sheets
• How to Rank Text Uniquely in Google Sheets