- To post as a guest, your comment is unpublished.· 2 years agoCloset to zero, or other value you specify.
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?
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) (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.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 2 years agoWhat closet?