OmniDocHow to use functions to calculate rankings and percentages in Excel
Explain the commonly used functions in Excel to calculate rankings and percentages: RANK.EQ, RANK.AVG, PERCENTRANK.INC, as well as how to write formulas for proportions and growth rates, with operating steps and verification methods, suitable for performance, sales and reporting scenarios.
Key points of this article
- RANK.EQ (value, area, 0) gives the ranking from large to small. When the rankings are the same, the following rankings will be skipped.
- Use "single value ÷ sum" for the proportion, and the sum area must be locked with $ so that the denominator will not be misplaced when filling downwards.
- PERCENTRANK.INC returns the percentile between 0 and 1, which is more readable after being set to percentage format
- Setting a cell to percentage format only changes the display, but does not change the formula result.
- After calculation, use the three methods of total, ranking boundary and hand calculation sampling to check, which is more reliable than just looking at it.
If you have a list of results or a sales breakdown, people often ask two things: where does this number rank in the whole, and how much does it account for in the total. Many people first think of sorting and then comparing with the naked eye. When there is too much data, it is easy to miss rows or make mistakes. You can use Excel functions to calculate these two things at once, and the results will be automatically updated when the data is changed.
This article is written for readers who can already write basic formulas and want to specifically solve ranking and percentage problems. After reading, you will be able to distinguish the differences between several ranking functions, write the formulas for proportions, percentiles, and growth rates, and know where to check when the results are wrong.
Although ranking and proportion are both "comparisons", the corresponding functions and writing methods are different. The following will give the conclusion first, and then follow the steps.
How to use functions to calculate rankings and percentages? One sentence conclusion
The RANK.EQ function is used for ranking. The proportion is divided directly and the sum area is fixed with an absolute reference. When you need to say "what proportion of data exceeds", use PERCENTRANK.INC.
| your situation | Recommended practices | reason |
|---|---|---|
| I just want to know where each number ranks in the column. Is juxtaposition acceptable? | =RANK.EQ(B2,$B$2:$B$11,0) | If the rankings are tied, the subsequent rankings will be skipped, which is consistent with common ranking habits. |
| If there is a tie, I hope to get an average ranking | =RANK.AVG(B2,$B$2:$B$11,0) | The average ranking of parallel values is suitable for comparisons that require fine distinction. |
| Want to know where a value is in the whole | =PERCENTRANK.INC($B$2:$B$11,B2) | Returns a percentile between 0 and 1, suitable for performance quantiles and similar comparisons |
| Find the proportion of a certain item to the whole | =B2/SUM($B$2:$B$11) | Get the proportion directly and set it to percentage format for easy reading. |
| Calculate year-on-year and month-on-month growth | =(C2-B2)/B2 | The new value minus the old value is divided by the old value. A negative result indicates a decrease. |
Before you start: Confirm these points first
- Whether numbers are really numbers. Numbers in text format will not participate in ranking and summing. You can select the cells to see if they are aligned to the left, or use the COUNT function to count the number of values. If the number is less than the number of data rows, it means there is text.
- Ranking direction. When the third parameter of RANK.EQ is written as 0 or omitted, it is in descending order, that is, the ones with larger values are in front; when written as 1, it is in ascending order. Results and sales are usually in descending order, while time consumption, errors, etc. are usually in ascending order. If you choose the wrong direction, the entire ranking will be reversed.
- Whether the area is locked. The ranking and summing areas must be fixed with $, such as $B$2:$B$11. Otherwise, the area will move when you copy the formula downwards, and the result will be silently wrong.
- Which rows are included in the denominator. The denominator of the proportion must include all objects participating in the comparison. If there is a total row at the bottom of the table, it should be excluded from the area, otherwise the total will be doubled and the proportion will be smaller.
- Where the data comes from. If the data is in a PDF table, you can first use the PDF editor of the OmniDoc desktop client to export it to Excel, and then do the calculation. The scanned document needs to be OCR recognized first, and OCR may recognize the numbers incorrectly, so be sure to check them one by one after exporting.
Steps to use functions to calculate ranking and proportion in Excel
- Organize data. Put the values to be compared in the same column, such as B2 to B11, and put the header on row 1. Do not include totals, notes, or blank rows in the area.
- Enter the ranking formula. Enter =RANK.EQ(B2,$B$2:$B$11,0) in cell C2, and press Enter to get the ranking of this row.
- Fill down. Drag the fill handle in the lower right corner of C2 to C11, and each line will reference the same fixed area.
- Enter the proportion formula. Enter =B2/SUM($B$2:$B$11) in D2. SUM first calculates the sum. The $ sign ensures that the denominator does not move along the line, and then fills down.
- Set percentage format. Select column D and format the cells as percentage, with one or two decimal places as desired. The format only affects the display, the value in the cell is still a decimal, and subsequent calculations are not affected.
- Calculate percentiles. Enter =PERCENTRANK.INC($B$2:$B$11,B2) in E2 and fill down. The result can be understood as: approximately this proportion of data is smaller than this value. If you need precise counting, you can use =COUNTIF($B$2:$B$11,"<"&B2)/COUNT($B$2:$B$11) instead.
Check each item after completion
| Check items | How to see | What to do if there is a problem |
|---|---|---|
| Ranking direction | Use the MAX function to get the upper limit of the value of the column and compare it with the row ranked 1 | If not, check whether the third parameter of RANK.EQ meets the requirements. |
| parallel situation | Find two identical values and see if their rankings are consistent | Use RANK.AVG instead when you need average ranking |
| Total proportion | SUM the proportion column and the result should be close to 1 (i.e. display as all) | When the deviation is large, check whether the area is missing rows or contains total rows; small deviations may come from decimal rounding |
| Numeric type | Use COUNT to count the number of values, which should be equal to the number of data rows | Insufficient quantity indicates that there are text numbers. Convert them to numerical values and recalculate them. |
| Is the formula misplaced? | Spot check the formula in the last line to see if it still refers to $B$2:$B$11 | If the region changes, re-enter the absolute reference with $ |
| error value | Check if #DIV/0! or #N/A appears | #DIV/0! The sum of many reasons is 0; #N/A Many reasons are because the query value exceeds the upper and lower limits of the area |
📊 Core comparison
There are four common ranking and percentile methods, which are applicable to different scenarios:
| method | Result form | How to handle parallelism | Suitable for the scene |
|---|---|---|---|
| RANK.EQ | Integer ranking | If the rankings are tied, the next ranking will be skipped. | Ranking list and ranking announcement |
| RANK.AVG | Maybe a decimal | Average ranking | Comparisons that need to be broken down into parallel situations |
| PERCENTRANK.INC | Decimal between 0 and 1 | Interpolate by position | Quantile, similar comparison |
| COUNTIF counting method | Integer ranking | Ranked with the same name, consistent with RANK.EQ | When you want to understand the ranking principle or are inconvenient to use new functions |
The formula of COUNTIF counting method is =COUNTIF($B$2:$B$11,">"&B2)+1. It counts the number larger than the current value and adds 1. The logic is transparent and convenient for manual verification.
❓ Frequently Asked Questions (FAQ)
1. What is the difference between RANK and RANK.EQ?
RANK is a function reserved for compatibility with older versions, and the calculation result is the same as RANK.EQ. It is recommended to use RANK.EQ for newly created tables, which has a clearer name and is easier to distinguish from RANK.AVG.
2. The proportions do not add up to 1. What is the problem?
Common causes are that the denominator range is not locked, or that the range contains total rows. Check that the range of SUM is the complete data range and make sure that the total value is not included. If the difference is only a few tenths of a percentage point, it is most likely caused by decimal rounding.
3. I want the shorter the time, the higher the ranking, what should I write?
Change the third parameter of RANK.EQ to 1, that is, sort in ascending order. Smaller values will get higher rankings, for example =RANK.EQ(B2,$B$2:$B$11,1).
4. How to express the result as "how many people exceeded"?
Use COUNTIF to count the number of people less than this value, and then divide it by the total number of people, that is, =COUNTIF($B$2:$B$11,"<"&B2)/COUNT($B$2:$B$11), and set it to percentage format. This result is more intuitive than PERCENTRANK.INC and suitable for direct explanation to the outside world.
📝 Summary
How Excel uses functions to calculate percentages, the key lies in two points: use RANK.EQ for ranking, use "single value ÷ sum" for proportion, and fix the area with $. Setting the percentage format is just for reading. Use it after the results are confirmed, which can save a lot of rework.
