How do you rank marks that do not follow each other in Excel?

Answer 1

You can use the RANK function. Put all your numbers into a column. They can be in any order. In the cell beside the first number enter the RANK function. It specifies the cell you want to rank, the list of values that it is being ranked out of, and optionally whether you want to rank it in ascending or descending order. Say your full list of values are in the cells from A2 to A15. In cell B2 you could enter the following formula to find where the value in A2 ranks overall:

=RANK(A2,A2:A15)

If, as is likely, you want to copy that down for all values, you need to absolute the list of values, so the formula would be like this:

=RANK(A2,$A$2:$A$15)

If use the order option and it is 0 (zero) or omitted, it ranks the number as if it were a list in descending order. This is the most common way of doing it. If order is any nonzero value, it is treated as if it were a list sorted in ascending order. It is omitted in the above example. If included you could have something like:

=RANK(A2,$A$2:$A$15,1)