Let me spill the beans on the PERCENTILE function in Google Sheets. This neat feature is not only present in Excel but also in older versions of Google Sheets. In newer versions, you’ll find something called PERCENTILE.INC. But fret not, my friend, because I’m here to explain both methods in detail and make your life easier.
Prepare yourself for a data analysis extravaganza! I’ll show you how to utilize the PERCENTILE function in Google Sheets using real-life examples. Get ready to dive right in!
What is the Percentile Function?
Now, I know it may sound like a snooze-fest, but bear with me because the percentile formula is downright fascinating. This formula gives you the value below which a specified percentage of observations in a group falls. Let me break it down for you. If you’re in the 5th percentile, you’re in the bottom 5% of a specific audience. If you’re in the 95th percentile, you’re among the cream of the crop, the top 5%. Intriguing, right? We’ll explore this concept further as we go along.
When you calculate the percentile of a range of data, you essentially divide it into two parts. One part lies above the percentile value, and the other part lies below it. In the world of grading curves for exams, the concept of percentile is incredibly valuable.
But hold your horses! It’s crucial not to confuse percentile with a percentage. Before we delve into the percentile function, let’s clarify the difference between the two.
Is it the same as a Percentage?
No, the percentile function is not a run-of-the-mill percentage calculation in Google Sheets. A percentage represents a value out of 100. For instance, 50% of 100 is equal to 50, and 50% of 40 is equal to 20. On the other hand, the percentile represents the rank or position of a data point within a dataset.
Allow me to illustrate with an example. Imagine you’ve just taken a test. If you only scored 55%, it would generally be considered a low score. That’s because you answered just over half of the questions correctly. However, if the rest of the class scored even lower, say around 30% or 40% correct answers on the same test, then your 55% would actually put you in the highest percentile on the grading curve. Pretty eye-opening, isn’t it? We’ll see this concept come to life in a later example.
Now, let’s roll up our sleeves and learn how to utilize the percentile function in Excel and Google Sheets.
How to Use the Percentile Function in Google Sheets
Picture this: you’re a teacher about to compile the grades for the latest exam. Naturally, the first step is to create a simple list with the students’ names and their scores. And guess what? I’ve got you covered with a dataset in Google Sheets.
As you can see, the students’ names are in cells A3 to A13, while their respective scores occupy cells B3 to B13.
Now, let’s rock the percentile formula in Google Sheets:
=PERCENTILE(StartOfData:EndOfData, Percentage)In Excel, things are slightly different. The newer versions of the software utilize the PERCENTILE.INC function:
=PERCENTILE.INC(DataArray, k)It’s important to note that “Percentage” and “k” are interchangeable. They both represent the percentile you want to target with the formula. For instance, let’s determine the 50th percentile of the test scores in our dataset.
To achieve this, let’s click on cell E3 (assuming we’re using Google Sheets for this example).
In cell E3, right next to where it says “50th percentile,” input the following formula:
=PERCENTILE(B6:B20,0.5)Make sure to use the number 0.5, as it represents the 50th percentile.
Voila! The result is 78. Scores lower than or equal to 78 land you in the lower 50th percentile for this dataset. This means that anyone with a score of 78 or less, such as Cole, Corey, and Corinne, falls into the lower 50th percentile. On the flip side, anyone with a score of 79 or higher, like Bernice, belongs to the upper 50th percentile of the test scores.
Take a look at the colorful dataset in my screenshot. I used conditional formatting in Google Sheets to create this eye-catching effect.
I also calculated the 75th and 95th percentiles of this data. It’s as simple as using the same formula while updating the value of the “Percentage” mentioned above.
For the 75th percentile, use:
=PERCENTILE(B6:B20,0.75)And for the 95th percentile, use:
=PERCENTILE(B6:B20,0.95)As you can see in my screenshot, the results are 91 and 94.3, respectively. You can employ this type of information in various ways, but it’s particularly useful for analyzing test scores. And hey, I’ve even compiled some fantastic Google Docs templates for teachers!
The Percentiles in the Latest Version of Excel
Hold on to your hats because the latest version of Excel has given the boot to the original percentile function. Why, you ask? Well, it was considered too imprecise. Fear not, my friend, because it has now been replaced with inclusive and exclusive percentile functions. The inclusive function goes by the name PERCENTILE.INC.
The syntax for the percentile function in Excel for the inclusive version is as follows:
=PERCENTILE.INC(array, k)You can call the exclusive percentile function with PERCENTILE.EXC. Here’s the breakdown:
=PERCENTILE.EXC(array, k)In most cases, you’ll likely use the inclusive percentile function. However, it’s always good to be aware of the exclusive function.
Remember, in Excel, the “array” is similar to how you would indicate it in Google Sheets. Simply state the starting point of the data, followed by a colon, and then the end point of the data. As for “k,” it represents the percentile.
Conclusion
And there you have it, my friend! You now possess the knowledge needed to wield the percentile function in Google Sheets. It’s a simple yet powerful function that allows you to calculate the ranking of specific numbers within a range of data.
I hope this tutorial has proven invaluable to you and that you feel more confident when it comes to data analysis in the future.
Looking for more information? I’ve got you covered! I’ve covered topics like how to transpose data, equipped with concrete examples. I even include videos that explain how to implement my methods flawlessly. For instance, check out this video on creating charts in Google Sheets.
For more enlightening resources and interesting information, head on over to Crawlan.com. It’s your go-to destination for all things data-related.
Remember, my friend, knowledge is power!
