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!

[@DemetriPanici]

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

[@sternritter9788]

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

[@HeatherMajors]

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

[@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!

[@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.

[@williamrivera162]

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

[@36sachin]

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

[@designmaster17]

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

[@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🤨🤨🤨🤨🤨🤨

[@eyadmasoud641]

Hi . Please record a video about CONCATENATE Function

[@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

[@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?

[@urozecevic1551]

Excel cant recognize formula, why, please help me

[@imabdulmannan]

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

[@LeilaGharani]

My pleasure 😊

[@StepDub]

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

[@supa.scoopa]

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

[@dhananjaysahoo3947]

Wondering about those 9 who disliked!!

[@arnabmajumder1916]

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

[@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!

[@DagoDeLaRosa]

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

[@drabhijeetghosh]

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

[@paschaliskagias9675]

Thank you very much. very good job

[@Kim-mp5wg]

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

[@master_jk6509]

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

[@nirmalankur]

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

[@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.

[@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?

[@NicolasRodriguez-rk4cd]

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

[@mariostudio7]

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

[@dogmalogy3666]

It is uncanny, this video came at the right time

[@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.🎅🎅

[@anillpatel]

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

[@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

[@westernugmedia.1305]

Thanks my Lecturer ever, always Ugandan watching u ever🌹

[@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.

[@taizoondean689]

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

[@bamakaze]

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

[@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?

[@bebekkartal]

Another amazing video from Leilajoon thank youuuu

[@hansklein3649]

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

[@Account2129]

She knows exactly what she's doing with these thumbnails

[@kruesae22]

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

[@kinnkano5157]

I am Kian

[@shadrackawunyo7786]

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

[@Rajiblayek]

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

[@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?

[@tifouraamina1761]

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

[@davidgallivan5615]

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

[@cchan824]

You are single-handing solving millions of people problems

[@mark91345]

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

[@Studio42Canada]

Super useful ! thank you 🙂

[@mohammadashiq9996]

Your are good as usual I love your simplicity thank you

[@vijayachandra7789]

thank u my teacher

[@tamaradissanayake5552]

I love this video..

[@jasonipfatsiong2062]

That was excellent Leila thank you so much

[@ChandreshVed]

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

[@cesara7478]

Great video as always. Thks!

[@montibhai8208]

Amazing darling

[@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

[@ourhouse2309]

Brilliant as always! Thanks Leila. :)

[@alvarorodriguezlasso]

You are the best

[@debsayan2188]

soooooo lengthy

[@dkb1111]

HaPPy Diwali in advance 💥Leila

[@shapourmoradmowri2732]

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

[@wayneedmondson1065]

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

[@LeilaGharani]

Thank you, Wayne! Cheers!

[@gzawi76]

awesome

[@Otisawide]

Cool!

[@luissabater5196]

Thanks for the lecture

[@rahulrustagi6119]

👍👍👍👏👏👏

[@kuuuyajim]

You really are the best! 🏆

[@Oprimaita]

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

[@LeilaGharani]

You're welcome 😊

[@GABSIAN1838]

you are always great!!!

[@cryptonrsc]

Great video Leila.

[@deerhunter4455]

Super explanation

[@toaderadi4261]

Thanks, Leila!

[@abdallah.kandiel]

😳 oh my I was just working on it Thanks leila

[@LeilaGharani]

Good timing then :)

[@dr.anandshedge2196]

really good work 👍

[@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?

[@atifahmad625]

Great video !

[@ravindravanjare752]

Superb... Thks

[@chrism9037]

Very useful, thanks Leila!

[@LeilaGharani]

You're very welcome!

[@superlbc]

Time to update your books...

[@LeilaGharani]

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

[@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!

[@aryanafghan83]

👍

[@cesara7478]

Thanks!

[@LeilaGharani]

Thank you!

[@Kirsteniao]

Thanks!

[@LeilaGharani]

Thank you so much K Lam!

[@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.

[@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

[@mohamedeassa]

Love your content

[@LeilaGharani]

Thank you for dropping by!

[@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.