Hello Mike, Happy New Year! Thank you for another great video! What a coincidence. have a question regarding this topic and I'll highly appreciate if you could kindly provide some guidance! I'm wondering if there is a way to find the number that appears the most frequently in the bottom 25% percentile of a dataset.
Please tell me, Celia, what is the data set that you are using?, what is the data?, what is the reason that you are trying to get the mode in the bottom quartile? Curious minds want to know ; ) Maybe I can make a video with your example!
@@excelisfun Hello Mike, thank you so much for teaching me how to do it! I got a spill error when using the first formula. Instead of using INC, I'm using EXC to exclude 0s. Could you please kindly advise what could be a reason for the error?
@@excelisfun Hi Mike, my reply is very long and thank you for asking the logic behind. Background: 3 factors are important in my dataset. Point of Sales volume (POS), Price, and the Order Quantity. I have the weekly data for 2021. The challenge I'm trying to tackle is to estimate the order quantity when there is no promotion (baseline). Current Methodology: There are 3 steps. 1. Calculate the average POS when the price is the highest. 2. Due to pre-loading for promotions and lead time, I cannot use the average order when the price is the highest. Therefore, I used the bottom 25 percentile of the order. 3. Then I'm selecting the highest between step 1 and step 2. Challenge: The 25 percentile in step 2 might happen once and it doesn't necessarily represent the baseline. Therefore I'm trying to find the most frequent except 0. I'm currently working as a Demand Planner. I found this position super challenging as it deals with super heavy data without a tool. Everything was done manually. I'm struggling to build some logics and tools to automate things. There are so many excel formula or power pivot knowledge blind spots to me, but I have been watching your videos a lot and they helped me so much! I don't know if it is feasible, but I will highly appreciate if you decide to make some videos regarding Demand Planning. Thank you so so much for reading this long paragraph. Please kindly let me know if more clarifications are needed.
I literally was looking for this topic on your channel on Monday and was sooo disappointed not to find on my favorite excel RUclips Chanel.. 2 days later ,there it is.. Thank you!! you made my day!!
I have 3 or 4 other videos on this topic, but they use the older versions of Excel. This one is much more fun because we can spill the five number summary? BTW, were you looing just to see how to use the functions? Or were you looking to learn how the different ".EXC" and ".INC" versions worked?
@@excelisfun the EXC and INC were a bonus. I was initially interested in the function in order to rank the specific data element relative to its percentile position. I have over 2000,00 records and that formula was a savior to help save data processing performance, which can be a challenge. Thank you again!
Hi mike, I am big fan of your video as it help me a lot..i have query if I want to found out the difference between leaving date and say number, eg if we have leave date for employee and we have the column for how many days in that month then how to get know the number of days he worked in that month. Thank you for your help.
Hello Mike, Happy New Year! Thank you for another great video! What a coincidence. have a question regarding this topic and I'll highly appreciate if you could kindly provide some guidance! I'm wondering if there is a way to find the number that appears the most frequently in the bottom 25% percentile of a dataset.
That is a great question!!!!!!
Here are two that might work:
=MODE.MULT(FILTER(F8:F41,F8:F41
Please tell me, Celia, what is the data set that you are using?, what is the data?, what is the reason that you are trying to get the mode in the bottom quartile? Curious minds want to know ; ) Maybe I can make a video with your example!
@@excelisfun Hello Mike, thank you so much for teaching me how to do it! I got a spill error when using the first formula. Instead of using INC, I'm using EXC to exclude 0s. Could you please kindly advise what could be a reason for the error?
@@excelisfun Hi Mike, my reply is very long and thank you for asking the logic behind. Background: 3 factors are important in my dataset. Point of Sales volume (POS), Price, and the Order Quantity. I have the weekly data for 2021. The challenge I'm trying to tackle is to estimate the order quantity when there is no promotion (baseline).
Current Methodology: There are 3 steps.
1. Calculate the average POS when the price is the highest. 2. Due to pre-loading for promotions and lead time, I cannot use the average order when the price is the highest. Therefore, I used the bottom 25 percentile of the order. 3. Then I'm selecting the highest between step 1 and step 2.
Challenge: The 25 percentile in step 2 might happen once and it doesn't necessarily represent the baseline. Therefore I'm trying to find the most frequent except 0.
I'm currently working as a Demand Planner. I found this position super challenging as it deals with super heavy data without a tool. Everything was done manually. I'm struggling to build some logics and tools to automate things. There are so many excel formula or power pivot knowledge blind spots to me, but I have been watching your videos a lot and they helped me so much! I don't know if it is feasible, but I will highly appreciate if you decide to make some videos regarding Demand Planning.
Thank you so so much for reading this long paragraph. Please kindly let me know if more clarifications are needed.
@@celia5132 Spill error means there is data in the path (a cell) of the spilled range
I literally was looking for this topic on your channel on Monday and was sooo disappointed not to find on my favorite excel RUclips Chanel.. 2 days later ,there it is.. Thank you!! you made my day!!
I have 3 or 4 other videos on this topic, but they use the older versions of Excel. This one is much more fun because we can spill the five number summary? BTW, were you looing just to see how to use the functions? Or were you looking to learn how the different ".EXC" and ".INC" versions worked?
@@excelisfun the EXC and INC were a bonus. I was initially interested in the function in order to rank the specific data element relative to its percentile position. I have over 2000,00 records and that formula was a savior to help save data processing performance, which can be a challenge. Thank you again!
@Julien Bouillot That is awesome : ) I love to hear that the percentile rank was helpful!
Thanks Mike for this EXCELlent video.
You are welcome, Fellow Teacher : ) : )
Thank you Mike for the great video on percentiles and quartiles. I'm following along and learning a lot; thank you!
You are welcome a lot! Stats in Excel are fun : ) : ) : )
Awesome Mike! More statistical gold using some great functions. Thanks for the good fun and learning :)) Thumbs up!!
You are welcome for the stats gold, Wayne : ) : ): )
Thank you Mike, this was great, as usual!
Glad you like it, Teammate Chris M!!!!
Thanks Mike. Great Video!!!
You are welcome, Formula Guy John : ) : ) : )
Excellent explanation 🎉
Glad you like it!
Well explained sir.
Glad you like it, Gabriel!!!!
Boom!Really Great Class..Thank You Mike :)
You are Boom Welcome, Biker darryl : ) : )
Percentile , quartile A to Z ❤️🙌👌
Yes!!!!!! A to Z : ) : )
Hi mike, I am big fan of your video as it help me a lot..i have query if I want to found out the difference between leaving date and say number, eg if we have leave date for employee and we have the column for how many days in that month then how to get know the number of days he worked in that month. Thank you for your help.
Try NETWORKDAYS.INTL function
Can you please let me know what Text Book you were referring to in your video?
First one!
First Place Trophy ; )
@@excelisfun Thank you :)
Perfect video as always!
My next1 will be about PDFs and Table.Transformcolumn
I do hope ill do it justice :)
@@ExcelInstructor Awesome!!!!
Good one, thanks. Do you know the formula to get all the sunday date of January 2022 for eg (2,9.16,23 & 30) in different cell?
Maybe: =FILTER(SEQUENCE(DAY(EOMONTH("1/1/2022",0)),,"1/1/2022"),WEEKDAY(SEQUENCE(DAY(EOMONTH("1/1/2022",0)),,"1/1/2022"))=1)
@@excelisfun Thank you so much sir, this is what I waslooking for. Now I need to understand this formula by breaking into pieces.
@@Dany-ns6hg I can make a video, but not sure when becasue of my huge work schedule at Highline College right now during covid...
@@excelisfun Please tag me whenever you can, thanks again for your help around this.
@@Dany-ns6hg You should be subbed and watch for when my videos post : )