Properly Calculate Percentile and Percentile RANK in Excel (INC vs EXC)

[@LeilaGharani]

Grab the file I used in the video from here 👉 pages.xelplus.com/percentile-file

[@mcd22630]

I just ran some calculations. It turns out my love for this channel is 100%.

[@simonkirkness2573]

That was very useful..as someone who is studying project management I’m always looking at your your KZbin videos for help to solve a problem or for something I know Excel can do but have no idea how to do it …. Thanks for all your great content 👍

[@LeilaGharani]

Great to hear!

[@cchan824]

You are single-handing solving millions of people problems

[@HeatherMajors]

Thank you for the clear explanation of the functions and how inclusive and exclusive are different.

[@sternritter9788]

This was really helpful! Never stop making videos! You have no idea how many people you've helped!

[@DemetriPanici]

This is a really useful funciton! Thanks for the tips Leila!

[@anillpatel]

I learn something new every time I watch one of your video presentations..., Thank you Leila.

[@westernugmedia.1305]

Thanks my Lecturer ever, always Ugandan watching u ever🌹

[@sajeens]

Thank you Leila for this...once again... very helpful 👍🏼

[@michaelsanders5938]

Great video.....I never realized the subtle differences between percentage and percentile. And my regular pitch for you to do an ASMR channel; I love your voice!

[@williamrivera162]

Good video, well explained and clear to understand. Thank you.

[@JayGee6996]

This is great for product design students who are collecting primary research on anthropometric measurements and wanting to display the results as percentiles as they do in industry

[@Kim-mp5wg]

thank you! this video is so helpful and is really good at explaining the uses/differences in percentile functions for Excel :)

[@Rajiblayek]

you are supeeeer !! So simply explained . great job.

[@yulinliu850]

Thanks Leila. I would say that the exc formulas are theoretically more correct but the Inc formulas are practically easier to use. They return very close results for larger data sets.

[@LeilaGharani]

Thanks for sharing!

[@tifouraamina1761]

Love you Leila, I always learn something new with you ❤❤❤❤

[@mark91345]

This is terrific. I wish I had learned this stuff when I was in school, but better late than never.

[@mohammadashiq9996]

Your are good as usual I love your simplicity thank you

[@shadrackawunyo7786]

Very beautiful presentation Leila. You are always at the peak of your game. Very useful video for my use.

[@mariostudio7]

I'm surprised it's not a popular function. Can be very handy thanks for sharing

[@ChandreshVed]

Thanks a Ton, I’m learning so much with ease 👍🏼

[@master_jk6509]

I also follow your KZbin channel. It has very grateful and useful video to study to us. Thanks Good luck

[@bebekkartal]

Another amazing video from Leilajoon thank youuuu

[@taizoondean689]

Thanks ma'am keep posting your video they are very helpful 👍 🙏

[@paschaliskagias9675]

Thank you very much. very good job

[@cryptonrsc]

Great video Leila.

[@imabdulmannan]

Thank You I learned a lot. Always you come up with something different ☺️

[@LeilaGharani]

My pleasure 😊

[@cesara7478]

Great video as always. Thks!

[@luissabater5196]

Thanks for the lecture

[@kuuuyajim]

You really are the best! 🏆

[@toaderadi4261]

Thanks, Leila!

[@vijayachandra7789]

thank u my teacher

[@jasonipfatsiong2062]

That was excellent Leila thank you so much

[@atifahmad625]

Great video !

[@Studio42Canada]

Super useful ! thank you 🙂

[@carltonbanks5470]

I believe the plain english explanation is for the inclusive percentile ranks "at or below" your number, while exclusive is simply all those "below" your number.

[@davidgallivan5615]

I recently found your channel. I enjoy the content. Can you do a session on how to make your spreadsheet pretty?

[@36sachin]

Love u lot, Thanks for helping with new things Owsome use for this

[@deerhunter4455]

Super explanation

[@Account2129]

She knows exactly what she's doing with these thumbnails

[@dogmalogy3666]

It is uncanny, this video came at the right time

[@ourhouse2309]

Brilliant as always! Thanks Leila. :)

[@barttitulaerexcelbart9400]

Thanks Leila, well explained, but one tip (for next time...?) In my lessons I use an example dataset of exactly 100 numbers and then sort the list. The difference between inc and ex is then clear.

[@chrism9037]

Very useful, thanks Leila!

[@LeilaGharani]

You're very welcome!

[@wayneedmondson1065]

Awesome Leila! Very instructive and useful. Thanks for sharing :)) Thumbs up!!

[@LeilaGharani]

Thank you, Wayne! Cheers!

[@GABSIAN1838]

you are always great!!!

[@nikkinikki4860]

Mam your teaching command is very nice and really i am very impressive. Thanks mam Mh name is nitish Bhardwaj from india. I am waiting for your complete pivot table video made.🎅🎅

[@dr.anandshedge2196]

really good work 👍

[@eyadmasoud641]

Hi . Please record a video about CONCATENATE Function

[@DagoDeLaRosa]

Would be great if you can make a video for google drive for desktop

[@NicolasRodriguez-rk4cd]

Hi Leila, im new subscriber, thanks for your clear Videos. Should i expect Power Bi soon? thanks!!

[@ravindravanjare752]

Superb... Thks

[@shapourmoradmowri2732]

جالب و کاربردی

[@supa.scoopa]

Can you make an example where you want to use the EXC function?

[@Kirsteniao]

Thanks!

[@LeilaGharani]

Thank you so much K Lam!

[@alvarorodriguezlasso]

You are the best

[@abdallah.kandiel]

😳 oh my I was just working on it Thanks leila

[@LeilaGharani]

Good timing then :)

[@arnabmajumder1916]

Is there an efficient way to calculate conditional percentile, that too for a large data set in Excel?

[@kruesae22]

Thank you for the great tutorials. Does anyone know a good how to for ABC Analysis Graphs?

[@wshulman]

Video Idea: How to use Excel without a Mouse using Alt and Ctrl plus other shortcut keys. I know some of the shortcuts so you do not need a mouse but struggle with filters and sorting to name a few. Thank you.

[@tamaradissanayake5552]

I love this video..

[@noorsyafiqah6782]

Thank you for the video. I have a question. How could we use the percentile function when there is a 0 score and duplication of score?

[@bamakaze]

Excel's Box Plot chart uses the percentile.exc for the 25 to 75 percentile range.

[@mohamedeassa]

Love your content

[@LeilaGharani]

Thank you for dropping by!

[@Oprimaita]

How could i work with excel for so long without using percentrank. ? 😀 Thanks a lot Leila !

[@LeilaGharani]

You're welcome 😊

[@nirmalankur]

Hi Leila....need your help in calculating percentile AHT thresholds on filtered data in production. Basically to identify anomaly/defect population.

[@SalmanTariqCh]

Hi. Suppose i use it in salary sheet and want to get exact number of employees above or below someone’s salary? Can i use same function or countif would be suitable?

[@teoxengineer]

Hi Leila, Thanks for your nice explanation. The shelf in the background caught my attention. May I know the author of the Visual Basic book?

[@LeilaGharani]

Thanks Emre. It's a very old book - 1999 by Dan Fox. Visual basic hasn't changed much though ;)

[@teoxengineer]

@@LeilaGharani is it available in the market now? or is there any book which you can recommend?

[@Dominus_Potatus]

imagine... I had a debate with statistic lab's assistant about this calculation, it seems like I was using the EXC while he is using the INC and neither of us can't explain why our answer was different and I got C+ because I was holding my argument

[@michaelsanders5938]

Leila, I am new to your channel. Between your and Kevin Stratvert's Microsoft-centric videos, I decided to purchase a Microsoft 365 subscription. Thanks for all your great videos. Just a quick comment, which is based upon my OCD: I notice that is some of your videos, you have a coffee mug with tech-related logo/wording on it. The coffee mug handle is pointing to the left as the video is viewed. For some reason, this "bothers" me. Again, I think it is just my OCD.

[@gzawi76]

awesome

[@NikunjDM]

HaPPy Diwali in advance 💥Leila

[@k0023382]

Thanks Leila, in my case this function does not do the job because the client wants to show the EXACT value of the series, for instance following your dataset, the 25th percentile should be between the element 3 and 4, therefore it should select the lower element by default ($46,508), in others words the data point position is int(count*0.25). I wonder if you can create another video taking this into consideration this fact and the best to implement this.

[@LeilaGharani]

I see. One way of returning the first lower number from the dataset is XLOOKUP(PERCENTILE.EXC($B$2:$B$15,F3),$B$2:$B$15,$B$2:$B$15,,-1)

[@k0023382]

@@LeilaGharani Thanks Leila for getting back to me. That is another thing, lastest formulas (XLOOKUP) are not an option either, some of them use Excel 2016 even Excel 2010. Ideally, I would love the Excel team to implemente a variantion of the formula like =PERCENTILE.NEW(array,k,exact) where it could be exact=before or exact=after options. I did try to build as VBA function but I did not get head around it.

[@montibhai8208]

Amazing darling

[@Otisawide]

Cool!

[@yohannanpchacko9896]

Hi percentile ranking is possible in powerbi? I want to plot sales figures in scatter plot (quadrant analysis) but the problem is majority of the sales accumulated at the bottom left corner because of outliers & I don't want to remove outliers so before that I want to find out percentile ranking inorder to plot visualization properly. Please help & make vedio in powebi

[@drabhijeetghosh]

what if there are duplicate numbers / figures / salaries in the list ?

[@chh8860]

Percentile vs. Percentage ... not needed in my day-to-day work ... but a spot on explanation (am I surprised? ... ahhh, no ...not at all). PS "Elon Musk" on the bookshelf ... the Thomas Edison of our time ... IMHO

[@LeilaGharani]

True, an amazing entrepreneur!

[@mansigupta]

Hi Leila, I have tried using the Percentrank function on our data and the problem I see in it is that it does not give values that exist within the array. So I use COUNTIF/COUNT to calculate percentiles instead. Any thoughts on that?

[@LeilaGharani]

I'm not sure I can follow. Percentrank returns a percentage. It's based on any number you provide and compares it to the existing array.

[@mansigupta]

@@LeilaGharani sorry. I mean percentile. The value that the percentileinc returns is not necessarily a part of the input array

[@mansigupta]

Also, just cos I know you may read this, you are a celebrity in my group of excel folk. ❤️

[@LeilaGharani]

@@mansigupta ah I see. With Xlookup, you can return the first lower number (or if you change the last argument to 1, then the next higher number) XLOOKUP(PERCENTILE.EXC($B$2:$B$15,F3),$B$2:$B$15,$B$2:$B$15,,-1) - if you don't have XLOOKUP, you can use VLOOKUP but make sure your data is sorted (you don't need to worry about sorting with XLOOKUP).

[@mansigupta]

@@LeilaGharani thanks. I'll try this! And of course xlookup! I broke up with vlookup the day I met x. Definitely the superior in every sense

[@michaelelkin9542]

Can anybody explain why the excel percentile does not match the elementary book definition of percentile and how to get it to match textbook results?

[@dhkgyif8417]

If there are 5 students in my class adn i got 3rd rank out of 5 students, so it means two students got higer rank than me and two got lower. So now, (2*100) /5=40th So 40th percentile is correct or I have to include myself as well🤨🤨🤨🤨🤨🤨

[@chrisbonney7563]

In the example with 50,000 salary there are actually 5 countries below this out of 14 which equals 35.7% therefore the numbers given do not agree with the calculated value?

[@hansklein3649]

Great thing, every time you change the cell value, it affects your references, comments, other values - whatever you like.

[@urozecevic1551]

Excel cant recognize formula, why, please help me

[@dhananjaysahoo3947]

Wondering about those 9 who disliked!!

[@aryanafghan83]

👍

[@superlbc]

Time to update your books...

[@LeilaGharani]

It's true. I'm just so attached to these :)

[@kinnkano5157]

I am Kian

[@rahulrustagi6119]

👍👍👍👏👏👏

[@designmaster17]

Me watching free KZbin’s 😎 Me paying for computer application classes 🤡

[@debsayan2188]

soooooo lengthy

[@StepDub]

Please don’t do the KZbin face. You are in the 99th percentile of presenters.

[@cesara7478]

Thanks!

[@LeilaGharani]

Thank you!