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.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
