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