Compatibility
Excel 2007 (Compatibility)
◉ 15 views
=PERCENTRANK()
Returns the rank of a value in a data set as a percentage of the data set. This function can be used to evaluate the relative standing of a value within a data set. For example, you can use PERCENTRANK to evaluate the standing of an aptitude test score among all scores for the test. This function has been replaced with one or more new functions that may provide improved accuracy. Although this function is still available for backward compatibility, this function may not be available in future versions of Excel.
Applies To:
Excel for Microsoft 365Excel for Microsoft 365 for MacExcel 2024Excel 2024 for MacExcel 2021Excel 2021 for MacExcel 2019Excel 2016
=PERCENTRANK(array,x,[significance])
Hover argument names in the table for details.
Output typeNumber
Popularity21 / 100
VolatileNo
Dynamic ArrayNo
📥 Download sample .xlsx — Coming soon
▦ Try in Playground
Dedicated file for this function — safe & lightweight · sample data + live formulas
⬇ Download Sample Workbook
A dedicated .xlsx for =PERCENTRANK() with Persian sample data & live formulas — open in Excel and practice.
- Sheet “Sample” — data table + formula in place
- Sheet “Guide” — syntax, arguments & tips from this page
- Instant & secure (token + rate limit)
🧠 🧠 Quiz this function
%d questions — syntax, example & errors — test yourself!
◧ Arguments
| Argument | Type | Required | Description |
|---|---|---|---|
array |
Range | Required | The range of data (or pre-defined array) of numeric values within which percent rank is determined. |
x |
Number | Required | The value for which you want to know the rank within the array. |
significance |
Number | Optional | A value that identifies the number of significant digits for the returned percentage value. If omitted, PERCENTRANK uses three digits (0.xxx). |
▣ Examples
رتبه درصدی مقدار 2 در محدوده A2:A11. نتیجه 0.333 است، زیرا سه مقدار در مجموعه کوچک‌تر از 2 و شش مقدار بزرگ‌تر از 2 هستند. چون مقدار 2 در محدوده (سلول A8) یافت می‌شود، تعداد مقادیر کوچک‌تر از 2 بر مجموع تعداد مقادیر کوچک‌تر و بزرگ‌تر از 2 تقسیم می‌شود؛ یعنی 3/(3+6)=0.333.
=PERCENTRANK(A2:A11,2)
رتبه درصدی مقدار 4 در محدوده A2:A11. پنج مقدار کوچک‌تر از 4 و چهار مقدار بزرگ‌تر هستند؛ مانند مثال قبل، 5/(4+5)=0.555.
=PERCENTRANK(A2:A11,4)
رتبه درصدی مقدار 8 در محدوده A2:A11. شش مقدار کوچک‌تر از 8 و 3 مقدار بزرگ‌تر هستند؛ مانند مثال قبل، 6/(6+3)=0.666.
=PERCENTRANK(A2:A11,8)
رتبه درصدی مقدار 5 در محدوده A2:A11. برخلاف مثال‌های بالا، مقدار 5 در محدوده یافت نمی‌شود. رتبه درصدی 5 با یافتن یک‌چهارم فاصله بین رتبه درصدی 4 و رتبه درصدی 8 محاسبه می‌شود؛ یعنی (0.555)+(0.25*(0.666-0.555)) یا 0.583.
=PERCENTRANK(A2:A11,5)
✦ Tips
- This function has been replaced along with other functions that may provide improved accuracy and whose names better reflect their purpose. This function remains available for backward compatibility but may not be available in future versions of Excel. Consider now using the newer functions.
- If X does not specifically match one of the values in the array (e.g., a list of specific student test scores) PERCENTRANK interpolates to return a percentage rank for X.
⚠ Common Errors
#NUM!
If the array is empty, PERCENTRANK returns the #NUM! error value. If significance < 1, PERCENTRANK returns the #NUM! error value.
Fix: مطمئن شوید آرایه پر است و برای significance عدد صحیح 1 یا بیشتر (تعداد ارقام معنادار) وارد کنید.
💬 Comments & Q&A 0 approved
🔒 Your comment will be published after review.Share your thoughts — visible after admin approval
No comments yet — be the first!