Excel Expert Live Challenge | Celia Alves & The Cupcake Challenge

[@spilledgraphics]

12:13 = brake in steps YES !!!

[@roupenyeremian6801]

That was awesome. Also, I totally agree that maybe it's not the ideal solution that Celia was looking for but it if works and you understand what''s going on then it's okay. It certainly makes things less stressful. Well done Celia and Oz.

[@CeliaAlvesSolveExcel]

I was actually complicating things. I was assuming the cupcakes were produced already and each client would get a random pick of cupcakes. That was not the case. We wanted to calculate how many cupcakes to produce for all the orders. So it makes total sense to pick the range 6 for every client who chose a random set. 😊 Thank you for joining us.

[@AccessAnalytic]

The Random Pick was an interesting thought experiment. I definitely had to pause and think about it... wouldn't want to do it live! 🤣 #Pressure I went for a RANDARRAY to generate 8 random numbers and used that to SORTBY to give me a random sorted list of cupcakes, and then used TAKE to give me the first 6.

[@CeliaAlvesSolveExcel]

That's a similar strategy to what Oz was suggesting me afterwards. My brain froze for that one.

[@mohamedsoffar4434]

@@CeliaAlvesSolveExcel you have 28 possibility and you need to select one out of these 28, i tried to solve by this way attached 🙃 docs.google.com/spreadsheets/d/1hjnKYWTgmUkVzStPdiTEL5nrxQBauvH-/edit?usp=sharing&ouid=107786345581647998294&rtpof=true&sd=true

[@GrainneDuggan_Excel]

Oz and Celia - what a seemingly simple but actually tough challenge. Fun to watch, learn and see how Celia approached finding a solution. Thanks to you both

[@CeliaAlvesSolveExcel]

Thank you for watching, Gráinne! It's amazing the type of challenges Oz comes up with.

[@OzduSoleilDATA]

Grainne do you have ideas for how you’d approach this? 🤔

[@McIlravyInc]

Excellent job! I would have done a lot of complicated if statements because I don't have Celia's mastery of formula possibilities. I too would be plagued by my habits of needing everything dynamic!

[@CeliaAlvesSolveExcel]

@@McIlravyInc your right! The need to have everything dynamic is a killer sometimes.

[@GrainneDuggan_Excel]

@@OzduSoleilDATA I might play with Power Query to see if random would offer different selection for each row.

[@gewe2508]

You could generate 8 random numbers and then use the rank function on these random numbers. If Rank

[@Dev_Bartwal]

You guys are rock ❤

[@HugoVenturini]

Love the work of both you! This is fantastic! God bless you!

[@CeliaAlvesSolveExcel]

Thank you, Hugo! God bless you too!❤

[@DavidRussell323]

This was great--continually loving these live excel challenge videos, and listening to celia work through the question (with some really useful tips!) was a whole lot of fun to watch^^ Nice one, Oz--really excited to watch more in this series!!

[@CeliaAlvesSolveExcel]

Thank you for joining us, David! I had a lot of fun too!

[@AIJB2013]

Thank you Oz and Celia! I thoroughly enjoyed the whole video. I feel like taking my skills to the next level so that I can also participate in these challenges:))

[@CeliaAlvesSolveExcel]

👏👏

[@LimitNZ]

Initially, would've brute forced my way through this. But I quickly went from being a backseat driver to a considerate listener as I followed Celia thinking this out. The way Celia created the first array is something I'm definitely taking away from this. Also the [""]. I found that so elegant, even though it might be a common trick. Good stuff!

[@CeliaAlvesSolveExcel]

Thank you for your feedback. Glad that I brought something to keep. :)

[@denismazzucato114]

you can create a side table with an index between 1 and 28 and all the 28 possible occurrence of 6 '1' and 2 '0' in the next columns, then you can use this formula that points to the side table on column U (Pick6-MG): =VLOOKUP(RANDBETWEEN(1;28);$B$16:$J$43;{2\3\4\5\6\7\8\9};FALSE)*(D3"")

[@CeliaAlvesSolveExcel]

Building the 28 possibilities on the side would have been a nice solution and not to hard. And then I could pick one random for each order. Nice. :)

[@einoconsult5563]

I liked the idea, I will watch a few of them, if I see that I can handle, I'd like to make a live one with you @Oz :)

[@gmourmant]

This should work for the random 6, but the issue is that there is very very very small possibility that we get 7 or 8 items if by any luck, excel pick two identical random numbers. =IF(D3:D12="X",LET(r,RANDARRAY(1,8,0,1500000),hv,LARGE(r,2),--(r

[@67duiker]

for the random items I used in the first 2 columns =IF($D3="X";ROUND(RAND();0);"") and in the remaining =IF(AND($D3="x";SUM($N3:O3)

[@justinankeney6344]

How about =XLOOKUP(INDEX(UNIQUE(RANDARRAY(6^2,1,1,8,1)),SEQUENCE(6)),[Vertical Array of of Index # of Flavors],[Vertical Array of Flavor Initials],"Not Available",0,), then TRANSPOSE that output? Finally get the random assortment applied to the Dynamic Array to add the Arrays up?

[@wcthrill]

Q, when I clear the 'x' on the 'Pick 6' it still shows as TRUE and doesnt update the calc.. I then need to clear that cell. When Celia did it, Excel would automatically update. Im using Excel 365 for web.. Also can use SUM with XLookup to add Total of each Cupcake. Simpler formula

[@erikguzik8204]

To do this under preasure live.... no way, given a bit of time then can come up with a table to sum the totals from. =(E3:L3)+C3:C3+IF(D3="x",IF(TRANSPOSE(SORTBY(SEQUENCE(8),RANDARRAY(8)))

[@CeliaAlvesSolveExcel]

That's pretty much how it went, Erik. Oz cut out the head scratching part. 😅

[@MrKamranhaider0]

🧠 stromed

[@einoconsult5563]

To randomly generate 1 and 0 with at least 6 times 1 you can use the following ( can be improved with LET) =TRIERPAR(STXT(CONCAT(REPT(1;6);REPT(0;2));SEQUENCE(;NBCAR(CONCAT(REPT(1;6);REPT(0;2))));1);TABLEAU.ALEA(;NBCAR(CONCAT(REPT(1;6);REPT(0;2)))))*1

[@CeliaAlvesSolveExcel]

I wanted 8 digits of 0s and 1s, where exactly 6 are 1s.

[@excelpro365_1]

Oz, I like your shirt. Will you give me one?

[@OzduSoleilDATA]

It’s the only one. 🫨

[@excelpro365_1]

@@OzduSoleilDATA 😀😊 Thank you.

[@jenniferdustin8214]

I turned it into a table called tOrders. First I made a N6 the starting point for the pick 6 using: =6*COUNTIF(tOrders[Pick6],"x") Then I added a pick 6 column in M. The first cell (Caramel Popcorn) got this formula (=RANDBETWEEN(0,6)) and the rest got this formula (=RANDBETWEEN(0,$N$2-SUM($M$3:M3)) dragging it down so the last M3 went up with each cell. That gave me a random number in each row, but they still all added up to N6. Next, I added the initials to column P behind their respective flavors. Then, in column N, I went with this formula (=SUM(tOrders[All])+SUM(INDIRECT("tOrders["&P3&"]"))+M3) where M3 is the pick 6 column from above for each flavor, and P3 is that flavors initials. so basically, you can the sum of the all column since each number there represents 1 of every flavor, it gets counted for every flavor, plus the sum of the flavors individual column, plus the pick 6 field for that flavor.

[@rubenmunozverdu7528]

For the all items I used =SEQUENCE(1,COUNTA($M$2:$T$2),C3,0) to generate a sequence of 1 row, as many columns as cupcakes and repeting the number in the ALL column. For the random part, in the first two columns I just used =RANDBETWEEN(0,1) and then in every next column I am checking how many zeros in the previous columns, if 2 zeros already then return a 1 otherwise another random: so for HP it would be =IF(COUNTIF($U3:V3,0)=2,1,RANDBETWEEN(0,1)) and when you copy the formula to the right is counting from U to W, then U to X and so on... In the last two columns formulas have additional check, if the count of ones in previous columns is 6 then I force to 0, otherwise random! Then finally you just sum the individual orders, the all orders and the random orders if they have an x in column D I I don't have excel 365 desktop so I saved it onedrive if you want to check: 1drv.ms/x/s!AlAFF6UdQfZHgQyyuxNFs_XwCQAU?e=c9NuFg

[@jeaneric77]

Hello, Another solution to generate the 6 random numbers.

[@jeaneric77]

With formula : =IF(E3="x";LET(a;SEQUENCE(;8);b;TAKE(SORTBY(a;RANDARRAY(;8));;6);IFNA(INDEX(a;MATCH(a;b;0))^0;0));0)