How to quickly rank closet to zero or a specific value in Excel?

In Excel, you may usually rank a list of values in ascending order or descending order, but in some cases, you want to rank values close to zero or 100 or a specific value as below screenshot shown, how can you quickly handle this task?

Rank close to zero or a specific value

Rank close to zero or a specific value

You can rank values close to zero or a specific value by calculating the absolute difference between them first, then rank as general.

1. Select a blank cell next to the values you want to rank, for instance, C2, and type this formula =ABS(0-B2) (0 is the specified value you want to rank closet, B2 is the value you want to rank), drag auto fill handle down to apply this formula to the cells you use. See screenshot:

2. Go to the cell you want to place the ranking, D2 for example, type the Rank function, =RANK(C2,$C$2:$C$14,1) (C2 is the absolute difference between the closet value and the value you rank, C2:C14 is the range you use to rank), drag auto fill handle to fill the formula to the cells. See screenshot:

There is another somewhat long formula that also can directly rank closet a specific value, =SUMPRODUCT((ABS(B2-0)>ABS(B$2:B$14-0))+0)+1, this formula will rank in ascending order.