How to Rank if Greater Than 0 in Excel

Sometimes, you might want to rank values in Excel only if they are greater than 0. Here's how to do it using the IF and RANK functions:

Step 1: Insert Your Data

In column A, list your values.

Step 2: Use the IF and RANK Functions

In column B, next to your first value (assume A2), type this IF function: =IF(A2>0, RANK.EQ(A2,$A$2:$A$5, 1),""). This formula ranks the value in A2 if it's greater than 0. Otherwise, it leaves the cell empty.

Step 3: Drag the Formula Down

Click on the cell with the formula you just typed (B2). Drag the fill handle (small square in the cell's bottom-right corner) down to copy the formula for all the cells in the column.

Step 4: Check the Results

You should see that Excel has ranked only the values greater than 0, ignoring any 0 or negative numbers.