1 cell formula for PD(3) =LET( Arrivals;SEQUENCE(MAX(T_Arrivals[X = Arrivals During 1 min])+1;;0); CountingIFS;COUNTIFS(T_Arrivals[X = Arrivals During 1 min];Arrivals); Total;SUM(CountingIFS); Probability;CountingIFS/Total; Spilling;CHOOSE({1\2\3};Arrivals;CountingIFS;Probability); Spilling ) and for simplicity I converted that range A14:A1319 into a Table T_Arrivals I do love playing with LET
Fantastic lesson as always! For fun (after all Excel IS), I tried doing the Room report with a Pivot Table (and yes, you're correct - formulas are better for this, just exploring!). I found I was unable to create a pivot table that included 0 rooms in the Rows section of the pivot table with only the pre July 1st data. Is there a way to show all the values of a PT Row when there's no data for it? Thanks again.
Hi Mike, thanks for the video. I have been trying to create an algorithm to calculate the delivery efficiency rate of a logistics delivery company. The variables I have currently are late delivery risk( 1 or 0), distance between departure and destination city, time taken for shipment, time taken for pre-shipment, weight of the parcel, the delivery fee revenue and delivery fee expense. Since you have exceptionally proficient in excel and statistics, I was wondering if you have any input that might help me in creating the algorithm.
Little background about my learnings. "I have been learning excel from your channel from last three years now. When I started learning I struggled with simple vlookup, handling data on excel was challenging, time consuming and a nightmare. Thanks to your detailed explanation tips and trick which helped me to learn the excel to advance level (complex formulas, statistics, working with arrays, PQ, DAX, Power BI) all this was learnt without paying a single penny". Hence I have no words on how to thank you 🙏🙏
@@excelisfun @ExcelIsFun I tried everything sir Did not work for me Actually im working in a company where i hav to sort data that start with 3 which contains in different rage of intervals like 3-6 7-10... Like that So really need help on that if u can
@@naeemjako Hi, not sure if this helps. To create that pattern 3-6, 7-10.... LET(s,3,r,100,i,3,SEQUENCE(r,,s,i+1)&"-"&SEQUENCE(r,,s+i,i+1)) s: start r: nr. rows i: interval For sorting or how you intend to use this pattern, we need more details, some context.
@@naeemjako Or, if you have an upper value "u", and nr. of rows "n" needs to be calculated such that upper value has to be included in last interval, for a starting value "s" and an interval value "i" , you can use this formula: =LET(u,476,s,3,i,3,n,ROUNDUP((u-s+1)/(i+1),0),q,SEQUENCE(n,,s,i+1),q&"-"&q+i) Formula calculates 119 rows and last interval will be 475-478 or a lambda function: INTERVALS(u,s,i)= =LAMBDA(u,s,i,LET(q,SEQUENCE(ROUNDUP((u-s+1)/(i+1),0),,s,i+1),q&"-"&q+i)) And call for example INTERVALS(476,3,3)
Mike, the link to the website is not working ( people_highline_edu_mgirvin_excelisfun ) . Where can I download files from previous playlists of Power Query, Power Pivot, Power BI, etc.? Thank you so much for sharing your knowledge.
More statistics and Excel goodness! Thanks Mike. Thumbs up!!
This is a fun one : ) You are welcome, Wayne!!! : )
Love the way, calculation of sd in single cell❤️🤟🙌
Gotta do it: it is more efficient and more fun : ) : ): ): )
Thanks Mike for this EXCELlent video.
You are welcome, Fellow Teacher Syed MM : ) : ) : )
Thank you Mike!!
You are welcome, GOWTHAM!!!!
Two videos a day! This is too much for us 😎👍🏻
The two go together. The first defines all the terms and the second is the fun with the formulas : )
I think I got the links working on both videos too ; )
Don’t think about it, I’m checking them every single time 👌🏻
1 cell formula for PD(3)
=LET(
Arrivals;SEQUENCE(MAX(T_Arrivals[X = Arrivals During 1 min])+1;;0);
CountingIFS;COUNTIFS(T_Arrivals[X = Arrivals During 1 min];Arrivals);
Total;SUM(CountingIFS);
Probability;CountingIFS/Total;
Spilling;CHOOSE({1\2\3};Arrivals;CountingIFS;Probability);
Spilling
)
and for simplicity I converted that range A14:A1319 into a Table T_Arrivals
I do love playing with LET
@@BaniMoniah Cool : )
Thank you Mr Mike, 👏🏻👏🏻👏🏻👏🏻👏🏻👏🏻👏🏻
You are welcome, Carlos!!!!!
Boom!2nd Awesome Class Today,Loving These Statistical Lesson's...Thank You Mike :)
@nd in one day - a rareity!!!!
Fantastic Mike! Thanks for another great video!!
You are welcome, Chris M!!!!!
Fantastic lesson as always!
For fun (after all Excel IS), I tried doing the Room report with a Pivot Table (and yes, you're correct - formulas are better for this, just exploring!). I found I was unable to create a pivot table that included 0 rooms in the Rows section of the pivot table with only the pre July 1st data. Is there a way to show all the values of a PT Row when there's no data for it?
Thanks again.
Yes, there is a setting: PivotTable Options, Display Tab, "Show items with no data". But this feature cannot add an item that is not in data set.
Great video as usual 👌 Thank you Mike for your hard work
You are welcome, Nader!!!
Excellent
EXCELlent ; )
Hi Mike, thanks for the video.
I have been trying to create an algorithm to calculate the delivery efficiency rate of a logistics delivery company. The variables I have currently are late delivery risk( 1 or 0), distance between departure and destination city, time taken for shipment, time taken for pre-shipment, weight of the parcel, the delivery fee revenue and delivery fee expense. Since you have exceptionally proficient in excel and statistics, I was wondering if you have any input that might help me in creating the algorithm.
I have no idea whatsoever what your algorithm is... Sorry about that.
Thanks!
👍Great video & Great explanation
Thank you, thank you, thank you, Santosh!!!!!! : ) : ) : ) : )
@@msantosh1220 Glad you like the explanation : ) : ) : )
Little background about my learnings. "I have been learning excel from your channel from last three years now. When I started learning I struggled with simple vlookup, handling data on excel was challenging, time consuming and a nightmare. Thanks to your detailed explanation tips and trick which helped me to learn the excel to advance level (complex formulas, statistics, working with arrays, PQ, DAX, Power BI) all this was learnt without paying a single penny". Hence I have no words on how to thank you 🙏🙏
Mike plz plz don't ever Thank me. I should be thanking you for all this video🙏🙏
Excel magic trick 1585
Hello sir,
Suppose i have to start with 3, not 1
Then how its done?
I tried bt didn't work
Realy appreciate if you tell
You can add or subtract what you want to ROWS
@@excelisfun @ExcelIsFun
I tried everything sir
Did not work for me
Actually im working in a company where i hav to sort data that start with 3 which contains in different rage of intervals like 3-6 7-10... Like that
So really need help on that if u can
@@naeemjako Hi, not sure if this helps. To create that pattern 3-6, 7-10....
LET(s,3,r,100,i,3,SEQUENCE(r,,s,i+1)&"-"&SEQUENCE(r,,s+i,i+1))
s: start
r: nr. rows
i: interval
For sorting or how you intend to use this pattern, we need more details, some context.
@@naeemjako Or:
=LET(r,100,s,SEQUENCE(r),4*s-1&"-"&4*s+2)
@@naeemjako Or, if you have an upper value "u", and nr. of rows "n" needs to be calculated such that upper value has to be included in last interval, for a starting value "s" and an interval value "i" , you can use this formula:
=LET(u,476,s,3,i,3,n,ROUNDUP((u-s+1)/(i+1),0),q,SEQUENCE(n,,s,i+1),q&"-"&q+i)
Formula calculates 119 rows and last interval will be 475-478
or a lambda function:
INTERVALS(u,s,i)=
=LAMBDA(u,s,i,LET(q,SEQUENCE(ROUNDUP((u-s+1)/(i+1),0),,s,i+1),q&"-"&q+i))
And call for example INTERVALS(476,3,3)
2nd :)
@nd place trophy!!!!
@@excelisfun Thank you :) appriciated
Mike, the link to the website is not working ( people_highline_edu_mgirvin_excelisfun ) . Where can I download files from previous playlists of Power Query, Power Pivot, Power BI, etc.? Thank you so much for sharing your knowledge.
The web site might have been temporarily down. The web site is up and working now.
@@excelisfun Thank you, Mike!