I did a video a while back that might give you what you’re looking for: The Excel shortcuts I use most for financial modeling kzbin.info/www/bejne/oHiof51ooZZ6eNk
@kkf2243 күн бұрын
A way to make finding the shortest route faster would have been to: - Make a copy of the map - Highlight big rectangular chunks of road that are not part of the quickest route - Clear all colors(and or text) in that rectangle - REPEAT: Highlight and clear. Highlight and clear. Highlight and clear. etc... - Do that until all pieces of road that are not part of the shortest route are gone. Don't worry if it looks pretty. - Count the remaining road cells that are the shortest route.
@abde52414 күн бұрын
Great FWMC, the small error of ABS was nothing but it changes a little bit the score. Overall, everything was perfect and can I ask about the function MID, did you use it to divide the length of the text ? Thanks early , I love the content
@DimEarly3 күн бұрын
Yes, I used MID to split the text into individual characters. MID(txt,st,len) gives you 'len' characters from the input 'txt' starting at position 'st', so if you give len = 1 and st = 1, 2, 3, ..., n (where n is the length of the text) it gives you the whole string character-by-character.
@abde52413 күн бұрын
@@DimEarly Thank you
@JeanWolleh4 күн бұрын
On Level 6, I am not sure exactly, but wouldnt your scan have worked, if you would have put the indirect around the a in the offset instead of around your Startvalue at the beginng of SCAN? Every video I learn someting. I usually have a lookup table and I really liked your way of building the offset logic with comparing the Arrows. impressive as always. Thanks and keep them coming :)
@JeanWolleh4 күн бұрын
oh wait probably not as it does give a different value, and not the Address.
@DimEarly3 күн бұрын
Thanks Jean! To be honest, I haven't really worked out what went wrong with my first attempt... I've been meaning to write a custom function for a while to take in a starting address and a series of arrows and output all the stops visited along the way (basically what I tried to write on the fly here), but I haven't got around to it. I'll have to go back and take a closer look later...
@KlinAprimora4 күн бұрын
Just wanna point this out: Dim's Portuñol >>>> TRANSLATE() hahahahah Thanks, Dim!
@asheeshpahwa75087 күн бұрын
Hello can you please share the Excel file?
@DimEarly6 күн бұрын
It’s not mine to give I’m afraid - but it should be available in the FMWC store shortly (the first round is already up). This is the section you’ll find it in: fmworldcup.com/product-category/case-studies/excel-esports-cases/
@asheeshpahwa7508Күн бұрын
@@DimEarly thanks dim
@jazzista19677 күн бұрын
Great Attempted Diarmuid! .... I will follow your naming convention for tables as T ... much quicker to reference the columns. Did you create a name range for the map?
@DimEarly6 күн бұрын
I have to emphasize that good practice for zipping through esports rounds isn’t the same as good practice for working with things in the real world! 😂 Yes, I had a named range mp for the map area, and another one col for the color-coded version.
@JeanWolleh8 күн бұрын
Well played as always, I think Bonus 1 is wrong, as you took the highest, and not the second highest Population. Also for Bonus 4 you could go iterative calculation for shortest distance. I also tried to do it manually but i missed one and didnt get to 158. Thanks for the video. Do you know when the results are going to be published?
@RenierWessels8 күн бұрын
Hi Jean. I’m ironically camping currently in the Kruger National Park. We’ll send our walkthrough and results soon.
@DimEarly8 күн бұрын
Yeah, I saw that almost immediately after I hit submit! 🤦♂️ I also re-did B4 with formulas, but even after thinking about it I ended up feeling about the same as my initial instinct - the total time and the risk of a mistake are both pretty similar either way… Renier, that’s about as good a reason as any to put some things on hold. Enjoy!
@JeanWolleh15 күн бұрын
Hi @DimEarly, is it possible to receive the solution file for this? I bought the file and I just cant Replicate some of the Solutions. If you want some confirmation of my purchase, I can provide it, I just need help with this case. Some answers just dont make sense to me, but you got them so easily, so i wanted to compare our results. Thanks and best Regards, Jean
@DimEarly3 күн бұрын
No worries, happy to send it. I just sent you a connection request on LinkedIn so I can send it to you.
@0netom18 күн бұрын
From a theoretical perspective, I don't see a reason for establishing relationships from multiple columns of a table to the same column of the same table. In an SQL DB context it would certainly be just fine.
@HadynWiseman19 күн бұрын
This looks like a brain teaser of a case
@DimEarly19 күн бұрын
It was a tricky one!
@JeanWolleh20 күн бұрын
Yesterday when i tried the case live, i didnt understand anything and got nothing out of it. Now i understand what was meant with a single chosen denomination: from each currency you go to pence. Now you have everything. I tried so much yesterday but i didnt even think about that. Today i wrote a reduce, which checks the possible options for my base and takes all those options again, and repeats. with that i just Multiply my values and I get the exchange rate for my target as result. I am sure there is a more efficient way, but atleast today i had some result, as yesterday was pretty bad for me haha. thanks for your videos. I am looking forward to your other solution :)
@DimEarly20 күн бұрын
I like the REDUCE approach! You can do something similar without that if you use circular references, which was the approach I was trying to think of, but that's probably the more robust option.
@daviddleon912720 күн бұрын
Hello everyone; with me is 5 am 😂 🥱 🌞🌎
@DimEarly19 күн бұрын
That’s awfully early to be watching esports videos! 😂
@hugedick81020 күн бұрын
First Idol. I'm a huge fan of yours like my name. 😊
@tawesome23 күн бұрын
As a beginner my mind keeps getting blown by how much there is to learn in Excel.
@ChristianMbolanantenaina24 күн бұрын
It's simply fantastic how you solve the problem, sometimes I wonder what really goes on in your head for you to think in such an effiscient way.😮
@DimEarly20 күн бұрын
Thanks! It's a real challenge for my channel trying to find a way to capture or explain the thoughts that go through my head in the few seconds right after looking at a problem. It's hard to do it justice on a live solve, because I'm always rushing on; but when you're reviewing a problem you've already solved, that's not the same thought process either...
@mineralfellow24 күн бұрын
Everything was clear and straightforward until the Log popped out. I think we would all appreciate a breakdown of how you came to that logic! Very impressive.
@klabbertrap103723 күн бұрын
We need to find T from D*1.1^T=3 (or 7) where D is the decay ratio. Divide both sides by D to get 1.1^T=3/D. Then to get T out of the exponent, you apply Log with base 1.1 to both sides. LOG(1.1^T,1.1)=LOG(3/D,1.1). LOG(1.1^T,1.1)=T so T=LOG(3/D,1.1)
@HadynWiseman23 күн бұрын
@@klabbertrap1037 Thanks, Haven't touched logs in years and was wondering how the division came in.
@jegane24 күн бұрын
10:00 formula which combines all the answers looks too long. Can you simplify with lambda please..
@DimEarly20 күн бұрын
You could certainly make it a lot simpler with LAMBDA, or even just with regular dynamic arrays - CONCAT(IFERROR(range,"")) would work fine. But I assume they want to keep it as backward compatible as possible so it works for people who compete on older versions.
@eashton1234524 күн бұрын
wow amazing job!
@ValyGreavu25 күн бұрын
That Log() was cool! I solve the hole case today in my way and I am very invidious for your fast thinking. :)
@klabbertrap103725 күн бұрын
I wish I had an "It's not just three, is it? No. Shit." moment yesterday. It in fact did kill me.
@DimEarly20 күн бұрын
It's so easy to miss a little detail in the moment! 😬
@vikhattangady25 күн бұрын
Amazing solution. I have no idea how the Log calculation worked. Something for me to learn. Btw, I noticed while rewatching the Battle 7 stream that you cheered for me. Thanks for that.
@HadynWiseman25 күн бұрын
It's shocking how efficient your solution is here, especially the use of log, that was really genius. Man I really have some work to do to improve.
@HadynWiseman25 күн бұрын
I did terribly on this case, not sure why as on a second go managed to get it all done in a similar time. Nerves I think. On question 4 since they are all left handed isn't the question just asking for the code for left in that language? Unless I misunderstand it. So its actually as easy as the first question just xlookup what left is in the given language!
@DimEarly20 күн бұрын
Haha, yeah, I totally missed that! And I even noticed at the start when I added my 'standard' L / R column that they were all left-handed... Don't feel too bad about a bad day, it happens to us all (and I definitely include myself in that!).
@drkazza553325 күн бұрын
Bravo - seriously impressive
@sledgehammer-productions25 күн бұрын
I might as well throw in the towel..... amazing, truely on the road to Las Vegas. Again!
@DimEarly20 күн бұрын
It's the place to be! 😁
@grant990425 күн бұрын
Good solve! For Level 4, since everyone is already left handed, the names are irrelevant and it's really just an xlookup of the language's left reference. I was hoping you'd catch that since you saw that everyone was left handed in the table.
@klabbertrap103725 күн бұрын
I realized that too, but only in my second go around. It's so easy to get caught up in the spirit of the question to not realize getting the answer is so much simpler.
@DimEarly20 күн бұрын
Good point! I didn't think of it at the time, but I noticed Andrew did pick up on it in his live solve. With the setup I had, I could also have saved some time by just looking up the standardized reference column (L / R only) that I had already added... then I could have dropped col I and col J (which I never actually used 😂) and had a relatively simple solution even without spotting that...
@KlinAprimora25 күн бұрын
Dear Lord, that was FAST! Hahahaha And I must admit, that LOG on Level 6 and 7 is out of my toolkit since the Engineering classes! (It looks like being a Mathematician may help sometimes, right? 🤣 hahaha) Thanks for your video and also by being an inspiration for me! Obs: In the video description it says "case Sommeliar by Peter Scharl..." 🙊🙊🙊
@DimEarly20 күн бұрын
Oops, good catch on the description - fixed now! 🤦🏻♂️ Between this and all your FMWC Brazil cases, you're definitely joining the league of prolific case authors! : )
@daviddleon912725 күн бұрын
Hello, Good Morning.. 💪⚡💻📊📉
@nemoyatpeace25 күн бұрын
Really great collaboration. My lambda file is more of a blank file with named lambdas, but I think I'm going to work to improve it to look more like Brittany's, the clear descriptions and samples for each function are great. Now we just need a way to have lambdas that are added to all files!
@DimEarly20 күн бұрын
Yeah, honestly it reminded me that I should do a better job of the same thing myself! My lambda file is also mostly blank, and I have to keep reminding myself how the parts I don't use as often work... Copying a blank sheet from your lambda file (which you can pin to your taskbar) is a passable way to get them all into a new workbook... but it would be nice if you could have something like the personal macro workbook that's always available by default.
@marvinkoller527326 күн бұрын
I am so glad that a caliber like you shares his knowledge. A big thank you to you!
@redchakra0329 күн бұрын
it's me or you videos are very laggy ?🤔
@christoslefkimiotis9889Ай бұрын
Hi I am trying to write a lambda function to calculate the margin of a debt, based on a specific leverage ratio, so I am trying to find a lambda to avoid circularity...Hope you continue this series of videos.
@wanderleihuttelАй бұрын
The new functions regex are good to solve text and number separations.
@Nicolas-gt9qfАй бұрын
Also with VBA you can only see the names of the arguments you can pass to the function with the use of Ctrl + Shift + A which is not very ergonomic for the end user, well you can technicaly also use the Fx API but it's not great either. Or you have to open the VBA editor to see what the function wants. Great video.
@timothyshueАй бұрын
Another way of doing this would be to go down the formula route and use a combination of FILTER, GROUPBY and PIVOTBY.
@cashewABCDАй бұрын
Classic Internet - Spirit of Sharing Knowledge, Helping one another. Great stuff Britney and Diarmuid.
@gdecomeАй бұрын
Excellent ! ... besides the case itself, you have the challenge of a foreign language without translator. Very well done!
@yashshukla7131Ай бұрын
This was great, wiith multiple screens it was made simpler by your explanations. Also kudos on teaching new syntaxes. Thanks.
@sledgehammer-productionsАй бұрын
What a joy to watch you exchange ideas about lambda's and competition approach. I'm curious: are you allowed to bring your lambda's to the live-competition? Diarmuid mentions that you have 15 seconds to prepare your lambda's, but how does that go? Small suggestion for a next time with a screenshare: as I'm on a laptop now, no big screen, so I had some trouble seeing the formula's, and because only one screen no opportunity to simultaneous look at the Excel file.
@nemoyatpeace25 күн бұрын
You can bring formulas in with you.
@DimEarly19 күн бұрын
I just realized I never answered this… I think what I meant about the 15 seconds was that you’re usually trying to save a very small amount of time if you’re using a generic prepared LAMBDA - e.g. making a dice lookup wouldn’t take long anyway. So if the LAMBDAs you’re using bring any friction with them, it would be easy for them not to be worth it. You can spend as long as you want preparing LAMBDAs ahead of time, but obviously the more specific you make them, the less likely they are to help on any given case. I sometimes also try to write a last-minute one in the time between seeing the case animation (which you get access to ~10 mins before you have to start) and starting. If you get it right, that can be a big help… but I often prepare something that ends up being worthless!
@xwalkermАй бұрын
@Diarmuid, thanks for the video, managed to get part way there on my own, but I couldn't work out how to pass 2 values back in the Reduce/(Scan) formula. I see that you used ({0,""} as a way of combining the 2 values.. later on, you went on to use the @ symbol, and I have searched the web, but can't find anything that explains the use in this context.. LET(toSplit,@a+v, Could you explain the significance of it. I am trying to break this down and evaluating the results at each step, so I'm using SCAN in place of reduce so I can see the resulting array. when I type "toSplit,@a+v" Excel encourages me to go with "toSplit,@a+@v"
@felipercarvalhoАй бұрын
Oh, my God! This video is VERY funny! You are amazing. Congratulations!
@abde5241Ай бұрын
Hi , thanks for your video. I saw that you are giving some financial modelling courses with full stack modeler. Can I still enroll ?
@DimEarlyАй бұрын
Yes, enrollment is still open. It's hosted on the Maven training platform, and you can sign up here: maven.com/full-stack-modeller/financial-modelling-with-modern-excel The first cohort kicks off at the end of July (it's possible we'll run it again in the future, but TBD...).
@abde5241Ай бұрын
@@DimEarly I enrolled already, thanks for this event. I can't wait to learn more on excel especially lambda which can be a good alternative to VBA.
@adjiedzikryАй бұрын
Can you share the sample case? I wanna try it by myself
@DimEarlyАй бұрын
You can download it for free from their website here: fmwcbrasil.com.br/case-rodada-inaugural/
@andrew_ngaiАй бұрын
Thanks for sharing :) 14:10 interesting point there, I think to make that a reality people will need to document their lambdas (to counter the benefit of transparency when you can see exactly how things work), which isn't a hard thing to do but requires discipline
@DimEarlyАй бұрын
Totally agree! I'll be talking more about documentation later in the series... I've seen some pretty interesting approaches people have taken to it.
@KO1967Ай бұрын
Thanks for the video. It made me take a look at a complex custom function written in VBA and consider converting it to a LAMBDA function. Long story short, on 10,000 rows the LAMBA version takes 10% as much time to calculate as the VBA version. That's huge.
@DimEarlyАй бұрын
That's great - congrats!
@MagicOfMushroomsАй бұрын
Great stuff!
@deepakdalal4580Ай бұрын
Look forward to this series as we get more n more complex and complicated Lambdas. Thank you
@45rewy28Ай бұрын
Another amazing, super informative video - appreciate it, as always!