🔥 Take our Excel for Business & Finance Course: www.careerprinciples.com/courses/excel-for-business-finance
@meliheser01 Жыл бұрын
gotta Q, when Microsoft release copilot, are the knowledge will be still useful?
@jean-francoisquesnel5607 Жыл бұрын
@@meliheser01 no, 99% of both excel and Word skills will lose all value. 90% of programming skills will also be useless. Proofreading however will greatly increase in value, as will fact checking and tone analysis.
@solomonbhandari-young4154 Жыл бұрын
This is amazing
@oscarumana7934 Жыл бұрын
Can I take this course with no prior knowledge on excel?
@bardia822510 ай бұрын
your xlookup function only works because there are 1 unique France for each month. If you had multiple "France" for each month, only SUMIFS and using PIVOT table would work.
@ianklimisch4139 Жыл бұрын
I would consider myself an advanced excel user, and had no idea I could add a 2nd criteria to Xlookup using & - I would have used index match! Great video
@adrianstensrud Жыл бұрын
Index match with multiple criteria is a bit more complicated though, but for sure, I would too. Never got into Xlookup as I never really needed it, and the naming criteria makes me sick after all of the years where people still uses VLOOKUP instead of Index Match.
@HateSpeechMoreLikeBasedSpeech Жыл бұрын
You can, but it's very computationally expensive for anything more than a couple dozen records. Your best bet is to combine the two criteria in a separate column first, or you're constantly going to be waiting for the sheet to calculate
Personally I prefer another method where you lookup 1 in the result array of a product of the two conditions. for example =XLOOKUP(1,(A1:A4=D1)*(B1:B4=D2),C1:C4,"",0)
@walterbierkens Жыл бұрын
I would have used the SUMIFS and the SUMPRODUCT formulas.
@supportive_comment Жыл бұрын
During my interview with my current employer, I was given small tasks in Excel to 'prove' my qualifications. It's very similar, in fact, to this video. However, the thing my employer did to test me, which I think was harder to do, was decipher already written formulas. He gave me 3 different preexisting data tables with formulas already in them. He then told me to explain what the formuals were doing. The first 2 were easy enough, but the last one was absolutely atrocious. What got me the job was when I started deleting his formula and rewrote it in a cleanliner/more cohesive way.
@oluwadarelekan1289 Жыл бұрын
Please I would like to learn more from you,I really love your experience
@iz5772 Жыл бұрын
The lack of ability by other employees to decipher formulas, pivots and other structures i developed, is o e of the reasons I'm irreplaceable. Thus is a lot i learned while back, to sometimes make the calculations more complex than they should be.
@Lesardah Жыл бұрын
Agreed. I'd hire someone like you over anyone who solved the actual formula. I always prefer someone who can look beyond their current task and ask a question like "What is this formula actually trying to do" rather than simply solving the given task. These types of folks usually move a little slower on the frontend, but save GOBS of time overall in trimming down old and obsolete business practices. Too many times I've heard the "because we've always done it this way" in response to why something exists. Wish there was a way to screen for people who think the way you do. I'm a working IT Manager (like, actually working with end-users as well as managing a small department over three locations). I don't have time or talent when it comes to hiring people - I've just been lucky to have an awesome HR department.
@arthurdent2900 Жыл бұрын
@@iz5772 "Thus is a lot i learned while back, to sometimes make the calculations more complex than they should be." I'd get rid of anyone I found with this attitude because they clearly but themselves above the team and the business. When I started programming back in 1966 (IBM assembler code on 1400 series) I had this attitude but was taken to one side by my boss who explained things about the real world.
@AmpersAndAI Жыл бұрын
@@arthurdent2900 You were bullied into submission by your supervisor and now you lack the courage to step out of the box your were verbally coerced into. Very unfortunate... Is your ex boss still alive? What is his attitude nowadays?
@d4ve197 ай бұрын
For question 3 I would add in "Required profit" to cell B9 and "$5000" to cell C9. Cell C11 is then a simple formula: =roundup(C9+C8)/(C6-C7) It's now dynamic and you can change the required profit to see how many sales it would need.
@mattb8135 Жыл бұрын
Awesome tips! Here are a couple notes on Question 3 and 4: For Question 3, you can use Solver, rather than Goal Seek. Using Solver, you can create a parameter on the 'Units' sold to be an integer. That way you don't have to do the roundup formula at the end. For Question 4, you can use sumproduct for the second formula. =SUMPRODUCT(--($B$11:$B$22 = $B7), --($C$11:$C$22 = C$5), $F$11:$F$22). Then copy/paste across (Or Shift right and Ctrl R as you mention). Unfortunately, I would have to count points against someone for using any 'lookup' type of formula when summing numbers (even an Xlookup).
@beezmemoire9376 Жыл бұрын
Is there a way you can show the exact formula so those not so familiar can better understand it? Thanks! 😊
@counterleo Жыл бұрын
True, in this dataset the couple CountryDate is unique so it is fine to find only the first match, but if there could be multiple rows for France and January arguably the question would want the sum of all matches not just the first one. The sum is conceptually superior. But then SUMIFS is the only elegant option, SUMPRODUCT is clever use of Boolean arithmetics but isn’t very readable.
@mattb8135 Жыл бұрын
@@counterleo Once you get used to sumproduct, it's not too bad. In certain analytics departments, sumproduct is heavily used and preferred to sumifs as sumproduct is at least 1.5x more efficient at calculating on large data sets (or at least this used to be the case).
@mattb8135 Жыл бұрын
@@beezmemoire9376 The Solver is a tool in excel (add-ins --> solver). I did show the sumproduct formula above. basically you put in a criteria like shown for B11:B22 and then equals to what you are trying to map to (in this case, b7). You can repeat this as many times as you want, just put a comma, then 2 dashes, then open parentheses, and do another check. when you get to the what you are actually trying to sum (like the 'sum range' in sumifs) you don't do the double dashes or parentheses (F11:F22 in my example above).
@adrianstensrud Жыл бұрын
@@mattb8135 Would also recommend cleaning up the formula a bit: =SUMPRODUCT( --($B$11:$B$22 = $B7), --($C$11:$C$22 = C$5), $F$11:$F$22 )
@darkdudironaji Жыл бұрын
The third one I made a cell for "profit goal" and set it to $5000. If you do a little vit if algebra you'll realize you can get the number by dividing (fixed cost + goal profit)/(price per shirt - cost per unit). Use round up on that. Now you can change the goal profit, price per shirt, fixed cost, and cost per unit and it will adjust itself.
@Dan_Gilpin Жыл бұрын
This is what I had in mind too, I never knew that the Goal Seek option was even there!
@danieltemelkovski982812 күн бұрын
@@Dan_Gilpin A quick bit of algebraic manipulation is suitable here, but if you want to optimize across multiple criteria, you'd be best off going with Solver. (Goalseek can only optimize for one variable.)
@mikenkk1 Жыл бұрын
Halfway through question 4 was super simple for me, but I didn't know I could do xlookup with two criteria; thanks!!
@arte7solАй бұрын
news to me too. Very cool!
@meritocraticmafia9824 Жыл бұрын
Learn something new every time you upload. I didn't know xlookup can do 2 lookups at once. I only knew index & match for Q4. So glad this channel exists.
@counterleo Жыл бұрын
Actually it doesn't do 2 lookups at once. It's a workaround. Under the hood, the & sign basically creates an invisible virtual column that consists of Country and Date just written back to back (concatenated). Same with the criterion. And then you match that virtual criterion to your virtual column. Whenever Excel finds a row where "CountryDate" equals "FranceJan-23" it will pick it 😂 Implicit requirement that the couple "CountryDate" is unique in the dataset, otherwise it will only pick the first one, whereas SUMIFS would sum all matching entries.
@aggravatedassociate8237 Жыл бұрын
9:29 &b
@muhamadazfar6788 Жыл бұрын
How my approach for Q4 =XLOOKUP(1,(CriteriaRange1=Criteria1)*(CriteriaRange2=Criteria2),ReturnColumn) Been using this to replace Index & Match function
@owrsrlt90626 ай бұрын
about question 4 , i knew sumifs... but Xlookup with the " & " ... wow... just magic thak you very much
@thomasstorbugt68299 ай бұрын
This was freaking cool. Im about to start a job where excel skills are usefull. Never have i ever really used excel and found it kinda tedious. But wow its so cool. I like programming and this kinda got my interest ticking. Thanks for the video
@hodlmaximalist10 ай бұрын
I work as an investment analyst and this actually taught me a lot 😂
@caramireddych14 күн бұрын
1.Highlight in red 10 Lowest 2.2 Conditions -IF(And 3.Break Even sales (Qty)-Use Goal seek 4.2 Different Formulas with 2 conditions as Horizontal & vertical- SumIFS( and Xlook up (& ) for second condition.
@maridelregala8742 Жыл бұрын
I love the way you explain, straight to point! Indeed you are heaven sent to newbies like me❤
@Kalachoff Жыл бұрын
Question 3 I would use an adaptive formula that will always work, even if you change the Assumptions table: =ROUNDUP((C8/(-C7+C6))+(5000/(C6-C7)),0) 🙂 Thanks for your videos! @Kenji Explains Greetings from Moscow)
@robertcasey1708 Жыл бұрын
i would replace the 5000 with a link to another tab's calculation for the fixed costs, as numbers hardcoded in formulas is not best practice
@Kalachoff Жыл бұрын
@@robertcasey1708 Exactly. My bad. Hate fixed numbers as well. :)
@LTJchrisRBF9 ай бұрын
that could be simplified a lot, to =ROUNDUP((C8+5000)/(C6-C7),0)
@Kalachoff9 ай бұрын
@@LTJchrisRBF Hey! Not bad! I didn't check your formula, but it seems I missed some lectures on algebra :) But you still forgot about the fixed 5k :) (I know I'm a terrible person) :)
@LTJchrisRBF9 ай бұрын
@@Kalachoffit’s in there c8 + 5000
@michaelquach6655 Жыл бұрын
I wish I had these videos available when I was doing interview prep. Nice work Kenji!
@KenjiExplains Жыл бұрын
Thank you!
@oluwadarelekan1289 Жыл бұрын
Hi Michael
@oluwadarelekan1289 Жыл бұрын
Hope you are Good, please I would like to learn more from you because I know you would have experience concerning an interview
@mhmdseadawy8228 Жыл бұрын
The last formula of using the xlookup is awesome 👍, I was thinking about using the formulas (sum + filter) Thanks for sharing 🙂
@Hokiebird428 Жыл бұрын
I got 3 out of 4, but question 4 actually wasn’t the question I missed. I am actually extremely familiar with using lookup or match with multiple criteria, and in fact I believe there is a third viable formula you could use. However I have never used the What-If Analysis feature before, let alone the Goal-Seek tool. Maybe the test administrator could guide me to it like in the video, because I understood how it worked almost instantly. But I don’t know if that would have counted for a point or not. EDIT - A WORD OF CAUTION AGAINST USING A CONCATENATED ARRAY THAT I JUST EXPERIENCED THIS MORNING: If you are looking for criteria that are represented by numbers (for example I was looking to find a match for 1&21) you may find a false match for 12&1! Both evaluate to 121, so the match with the smaller row number will be selected, which was giving me an incorrect result! I find it much safer to use conditional statements multiplied together when I’m working with multiple criteria.
@ScorpiusZA. Жыл бұрын
For me question 1 to 3 was fine. Knew it before he started. On Q4, the first one, Sumifs, I got, but I would use a formula different to the Xlookup. Interesting how all sorts of ways to get to the same result.
@adamwolach Жыл бұрын
You may know this but for those who don't, sumproduct with three arrays evaluated to 1/0 does this easily. =sumproduct(array1, array2, array 3). Array1 would be countryrange=country, if you wrap it in parenthesis with --() it becomes 1/0: --($B11:$B22=$B6). Similarly the date lookup is --($C11:$C22=C$5). lastly the revenue is just the revenue array. To make life even easier, name the arrays. Then your sumproduct will look like this: =sumproduct(--(country=$B6),--(month=C$5),revenue) In essence, this runs a bunch of AND statements on each row in the multiplied arrays, so it will return 1 * 1 * revenue if everything matches, and return 0 if any criteria don't match, then adds them all up.
@Hokiebird428 Жыл бұрын
@@adamwolach This is was precisely what I meant when I said there was a third viable formula, as well as when I said “multiplying conditional statements together”.
@saltesc Жыл бұрын
You won't have and shouldn't. Define the dataset and build the formula once, done. Giving defined names to cells, your formula bar should just have... =ROUNDUP((FixedCosts+SaleTarget)/SaleProfit),0) And that way if you get hit by a bus on the way to work, the work experience kid can read exactly what you did and life goes on...not for you, the business. You can load that up wherever you want for any product you want. Targets and costs can change and it'll just keep showing the correct figure. Job done once in a minute, never do it again. Querying the data and it'll be showing the correct adjusted figure when the file opens. That's literally all that Excel feature is doing, but you don't have to fiddle around with it every time. Always understand these kind of Excel features. Never use them unless one-off and it's fine to make a quick mess because the workbook is going to get deleted in a minute after.
@saltesc Жыл бұрын
@@adamwolach I'd have tables with 'colHeader' defined and paste this formula in every cell... =SUM(FILTER( Sales[Revenue], (Sales[Country]=[@Country]) * (Sales[Date]=colHeader), 0)) And then if the manager wants any other variables like sales over 1000, just slap another line in... =SUM(FILTER( Sales[Revenue], (Sales[Country]=[@Country]) * (Sales[Date]=colHeader) * (Sales[Quantity]>1000), 0))
@Yorknie9 ай бұрын
Dang that first question was the hardest would have never known about it without this video.
@jessilink280615 күн бұрын
Fun video for self-assessment! My two formulas were Xlookup using the & (I learned this months ago and it's one of my favorite FAVORITE functions in excel to-date!) and then a filter formula using two criteria in the filter by using the asterisks functionality in the criteria "($B$11:$B$22=$B7)*($C$11:$C$22=C$5)". Thanks for the engaging content!
@blackshirtsd9916 Жыл бұрын
My friend at work is a whiz at excel. He created a formula at an old job that made us run thru jobs faster, which pretty much eliminated a job altogether. Though I don’t want to eliminate jobs where I work, I do work with 95% all licensed engineers, and I’m just a guy with a cubicle making autoCAD/Revit drawings for them. Though I would like to elevate my excel game to make our jobs easier and/or impress my boss so I can start making the big bucks!
@vk2him Жыл бұрын
As an analyst, I agree with other comments about Q2 but I see if from a different perspective and I contend that Kenji may have gotten it wrong himself. Extrapolating the minimum requirements of $500k revenue and $25k profit will result in a 5% margin (25/500). From Kenji's answer, Projects B and C are rejected due to them being less than $500k revenue, however Project B has an incredible 78% margin (over 15 times better than the minimum) and Project C's margin is 15.5% which is over three times better than the Minimum requirement.
@chrisadeniyi418 сағат бұрын
this makes sense! how would you have approached it?
@vk2him4 сағат бұрын
@@chrisadeniyi4 I would have added an extra cell in H8 that calculated the target margin is 5% . I'd keep the column E formulae but I would then add an extra column in F5 down calculating the margin for each row and highlighting those with a margin > 5%. I would then Add one more column with the Decision to be either "Reject" or "Accept based on margin". Technically, Kenji's answer is correct if the hiring manager just wants to see if you know how to write formula, my answer is arguably correct if they want someone who can provide a full analysis so better decisions can be made
@ChrisAdeniyi39 минут бұрын
@@vk2himsuperb! very detail oriented process 👏🏿 and it’s a better way to achieve the desired outcome
@carroux4050 Жыл бұрын
Regarding Question 4 just use a Pivot Table with a filter. Since I learned about Pivot Tables back in 2009 it made so many things way easier 🙂
@thorstenl.49286 ай бұрын
Only, that its not the Solution to the question. The task is to use two different formulas. This is not about this special use case but about how you understand and tackle the task and how you unterstand the way excel formulas work and if you can think around the corner…
@iambilly Жыл бұрын
🤯 I did not know about the "&" for the xlookup formula, I thought you could only nest xlookup to search multiple criteria Thank you Sir, i sincerely appreciate you sharing :)
@jacksons1010 Жыл бұрын
Ditto! My brain says no way does & work like that…guess now I’ll have to try it for myself. 🤷♂️
@industryrule-4080 Жыл бұрын
I’m an Excel newbie and got the first two right. That last one was 🤯 for me.
@sayedyahiya Жыл бұрын
I like this kind of videos. Thank you for putting these videos. Xlookup formula will not work if you have multiple values for the month. I will use for 2nd formula as below =SUMPRODUCT((Country=“France”)*(Date=“Jan-23”)*Revenue) Here Country, Date & Revenue are the ranges France & Jan-23 are dynamic values
@wicem4 Жыл бұрын
He made it work no ? I didn't know x lookup so I'd like to understand more what you're saying
@evensteven9111 Жыл бұрын
In his example there was one and only one record for each Country and Month, which is fine. But say you have multiple records for the same date and country, xlookup will return only the first match to encounter. sumif will do it correctly. So using xlookup is not a good example really, since there is a silent assumption made on how the underlying data is structured.
@wicem4 Жыл бұрын
@@evensteven9111 thanks. That was very clear !
@anramahoney1471 Жыл бұрын
Your videos have helped me so much during this week! Wonderful work explaining things in a simple manner. I can't thank you enough!😁🌼
@oluwadarelekan1289 Жыл бұрын
Hi anra
@infinifty Жыл бұрын
In question 2 i did =IF(C10>$H$6,IF(D10>$H$7,"Accept","Reject"),"Reject") Basically used If 2 times for successive checks. AND is much cleaner, tho.
@ltoledotc Жыл бұрын
I would use SUMPRODUCT on Q4. I think it's safer because it would work with multiple input of the same month and country.
@adamwolach Жыл бұрын
This is the way. It's also good to put your criteria in parenthesis with '--' before, like this --(x=y), which converrts the true/false output to 1/0 output making matrix multiplication smoother.
@moshemoses6249 Жыл бұрын
I had SUMIF and SUMPRODUCT as well. Like with many things, there's multiple ways of getting to the right answer. As long as one gets there within the constraints applied and time required, then it's all good!
@KenjiExplains6 ай бұрын
Check out this updated Excel Test video for 2024: kzfaq.info/get/bejne/e5qkZ5Zz3Z28po0.html
@excellover71724 күн бұрын
I got all correct except the 2nd formula where & was used to add 2nd criteria. Thank you very much!
@kloppskop41492 ай бұрын
cool, like the test. didnt know about the goal seek tool. for last task when using xlookup for multiple criteria i like to use this format as i find it easy to visualise: xlookup(1,(range1=criteria1)*(range2=criteria2),return range) so in your example it would be: xlookup(1,($B$11:$B$22=$B7)*($C$11:$C$22=C5),$F$11:$F$22) helps when the data is in a table too.
@GL1TCH_8816 күн бұрын
Best and most clear vids on Excel by far. Thanks Kenji-san.
@NuhaMuneer9 ай бұрын
I am an IGCSE ICT and A-level IT in a Cambridge school. And proudly I can say I could solve all of the without seeing the answers! Thank you for this video. It motivating me to do more challenges and improve myself more.
@N3v3rm1nd916 ай бұрын
All of these were really easy. Excel just feels extremely simple if you're doing any sort of technical work day-to-day. Will continue to take a while to phase out as those in functional areas continue to resist change but eventually Excel will be a thing of the past.
@Kerbeygrip Жыл бұрын
I used to set an interview test when looking for trainers in an IT centre. It was to recreate an IDENTICAL table in EACH of the Office suite, including identical formatting. This was 25 years ago without instant tools. Of many candidates I found ONE. The only one who had been formally trained, not self- taught. To get the best out of software, and not make catastrophic mistakes, is to get a formal training.
@ttsait Жыл бұрын
this might be true 25 years ago but nowadays self training is more than adequate haha
@Kerbeygrip Жыл бұрын
@@ttsait my recent experience of self taught (in schools) is that I still receive documents which have been created “visually” , which when moved or imported “jump about” and need a global formatting to be of any use! The basics are missing! I am sure your courses are great. We just need more people to train!!!!
@26iain Жыл бұрын
To be fair, in the 2nd exercise I'm 100% accepting project B, screw the formula.
@MT-kr8cn Жыл бұрын
great to find you! 🙏🙏🙏, you are a blessing, being now unemployed, I cant afford paying a Microsoft certificate training session. As long as I am updated, with the newest version and features. This helps a lot! looking forward to Word, Outlook, ppt..Thanks a lot!
@Real28 Жыл бұрын
Something to note about CTRL-SHIFT-(ARROW KEY) I remember when I learned this, changed my working life. But know this: it ONLY goes until a blank cell. If you have blanks, it'll stop there. Just keep tapping the arrow of the direction you're going to bypass it.
@danieltemelkovski982812 күн бұрын
ctrl + end/ctrl + shft + end bypasses blanks and takes you to the last row of a block of adjacent cells. if you are trying to highlight columns, you can then just use the left arrow to go back to the column/s you want to highlight if ctrl/shft/end takes you too far right.
@mythals11 ай бұрын
Thank you for explaining these concepts in a way that is open and encourages learning, your user friendly approach helps me understand and implement these with my work - thank you!
@evanfriedland67709 ай бұрын
The profit question can also be done algebraically. If you know profit = (price - cost) x quantity - 15000, then you can solve for the quantity at profit 5000 as quantity = (5000 + 15000)/(price - cost) which gives the same answer as above :)
@uh_me9 ай бұрын
The real test of Question 2 is whether or not you realize how silly it would be to actually reject Project B.
@sixpepper Жыл бұрын
Haven’t taken excel training in years. I didn’t forget anything thankfully.
@Mike__B Жыл бұрын
Man they'd rather accept a $30k estimated profit just because the revenue estimate is over $550k over one that has an estimated profit of $350k even though the revenue is only $450k, while I passed the Excel test on that one I definitely would have pointed out that they're absolutely nuts for rejecting proposals that are 78% profit!
@centerpunch Жыл бұрын
Regarding question 2, if I were hiring, I'd be looking for the applicant that could do the excel work, but who would also see beyond the numbers on the screen, and would actually question the question. Obviously, the criteria for acceptance should be changed. Any intelligent business would not reject Project B, instead they would choose it as the first project to accept.
@chrismoule7242 Жыл бұрын
This. I had the advantage that I was a qualified accountant and not a qualified IT [though I was the go-to IT person in my section] and when I saw that level of profit I immediately thought the chosen criteria were not good business.
@hikari1690 Жыл бұрын
Thank you for showing the short keys. I keep forgetting f4 so you showing it really helps
@Greendotz6 ай бұрын
I had no idea F4 was a shortcut to lock columns (and could be cycled between hard-lock, horizontal and vertical lock). Absolute game chager
@lythsian Жыл бұрын
I'm old school, I don't know these new functions, my results would have seemed more clunky but it would have got the job done. For the 3rd it's a calculation formula. For the last sumifs I didn't know and I didn't know xlookup. My degree was 20+ years ago and I don't work professionally in any IT based positions but I've kept my mind sharp. For the last I'd have done an if and if set up and second the same rather than nesting the and but otherwise. The good thing is it's showing me for all I know I can still know more. I'm sure an updated course could bring me back up to speed. Thanks for the video.
@mikehaniszewski904Ай бұрын
I am not a Wiz at Excel, but I have been managing businesses for over 25 years, and I think you might have missed the point of question 2. The question is not asking about the static numbers, but more about the profit margin. The "minimum requirement" example of $500K revenue with a $25K profit shows a profit margin of 5%. A better statement to evaluate the proposed projects would be: =IF(C2/B2>=0.05, "Accept","Reject"). Why would any business reject a project with a predicted 77% margin?
@danieltemelkovski982812 күн бұрын
Bro, I think you are missing the point of this exercise lol. It's testing excel skills, not financial acumen.
@tremolony49247 ай бұрын
Question 3 Logic: Profit per shirt sold = (Price per shirt - cost per shirt) Total Sales Needed = (Target Profit + Fixed Cost) Units to Sell = (Total Sales Needed / Profit per shirt) Whole Units to Sell = RoundUp(20,000 / 21.49) Whole Units to Sell = 931
@JustTony72 Жыл бұрын
Ahhh excel. Only got 3. Didn't know goal seeker or solver existed and was going to write the formula by hand.
@daylen5776 ай бұрын
In the third case it would be much more efficient to use a formula instead; our target is C8 + 5000 >= Units * (C6 - C7), or target = C8 + 5K / (C6 - C7) which would resolve to 20,000 / 21.49 = 931 units sold to hit the target. This would give you not only a target, but also a very easy template to then use later on
@edravtv436711 ай бұрын
In q3, we can calculate it using the Cost-Volume-Profit formula : (fxc+p)/cm per unit. (15k+5k)/(29.99-8.50) = 930.7 or 931 units.
@madhunethaposa14947 ай бұрын
For last question, Xlookup doesn't give the sum for selected criteria rather it will through the first matching result. In the table we have only one entry for each month for france so the answer was matching otherwise not.
@Lemurai Жыл бұрын
My engineering program had an excel mastery course as part of the major, but at my first job out of college, they were more concerned with my trig skills over excel, even though many of us use this program 90% of the day logging data lol! It def pays to know this.
@lucadesanctis563 Жыл бұрын
I know it's a stupid request, but I would like a video on how to use relative and absolute reference because I still can't get how they work sometime :(
@_sootyfoot Жыл бұрын
It's not a stupid request if you don't know how to work something out! Fingers crossed Kenji sees this for you bud ❤
@Darkslide820 Жыл бұрын
I think the easiest way to remember it is that the $ locks whatever it's next to. Hit F4 to flip through the various $ combinations and say out loud (lock everything, lock the row, lock the column, lock nothing, lock everything, etc). I still do this myself as I'm formula building to make sure I apply the $ correctly.
@KenjiExplains Жыл бұрын
Hey Luca, don't think I'll make a video on this as it's quite a short topic but on the whole I'd remember that the $ sign locks whatever it's next to. Best of luck!
@judejamison2970 Жыл бұрын
Just wanna chime in that the default is a relative reference. If you write a formula in cell C2 that references B2, when you drag or copy that formula into C3, it's going to reference B3. You use the dollar sign to change this, placing it in front of the B (lock the column), the 2(lock the row), or both(lock everything), as explained above. Good luck!
@ernestrufus Жыл бұрын
kzfaq.info/get/bejne/fLilZ5ur3pOomp8.html
@Cavcurlz10 ай бұрын
I would have used ab index match for the last one but I haven’t been taught the xlookup, it’s amazing what excel can do!
@matteomontanari2960 Жыл бұрын
I used the SUMIFS recently and it was an absolute nightmare because i was dealing with data about dates... yours was way easier because you just had to confront text. I had to set a condition to determine if a date was before or after another one. Again, a nightmare.
@lompeluiten2 ай бұрын
If you needed months, why dind't you do (month(array)). Excel goes really easy these days with such formulas.
@dasfreutmich1 Жыл бұрын
Really loved this exercise. Thanks much. Also the sample excel file helps a ton to speed up the process if you want to do it yourself. One note though for those using excel online, the What If Analysis option is not available there.....
@julius434618 ай бұрын
4:59, I can't believe there is a tool for this 🙂. I paused first to try and get the number of sales myself, and this is how I got it: (Fixed cost + 5000) / (Price per shirt - Cost per unit)
@kd6lor Жыл бұрын
I will give myself a 3.5. I didn't know about anding two variables or arrays in the xlookup function. Learn something new everyday.
@geerliglecluse5297 Жыл бұрын
Regarding question 3, using the Goalseeker function leaves unanswered whether a candidate understands how to do a break-even / profit calculation in the first place, which is arguably the more important question, I would say. In other words, what's the point of being good at using Excel functions if one doesn't understand how something should be calculated correctly in the first place?! With respect to question 4 I would use a pivot table. The data set in this example is small, in reality this kind of questions usually pertain to large sets of data. Pivots are easier to check for errors and also easier to change. Once users of information get a question answered, they tend to ask follow-up question and in many other cases these are monthly exercises where the number of records change, while the nature of the data stays the same (in which case you have to change all the ranges using formulas mentioned here, while pivots automatically take care of the number of rows)
@mohdabdulmateen5159Ай бұрын
You are such an Educator angel.❤❤❤❤❤❤
@jeremiahjohnson8864 Жыл бұрын
Haven't got ot use the xlookup and goalseek but everything else i knew. Always have to keep learning with excel. Excel is a very powerful tool when you know how to use it
@alexpol6035 Жыл бұрын
Ok so I would like to point out some problems: 1. Highliting 10 lowest orders should be done with sorting - that way you will sort the table in a meaningfull way. Then you should highlight them manually in order to that use filters to show this 10 results. But if you think about this problem - it has no meaning couse there is no business need to show exactly 10 items, instead you should either sort data or convert it to graph. Lastly your "solution" showed more then 10 results because it also highlighted simmilar quantities i.e. all 100s 2. Proposed solution is also short sighted. Instead you should create 3 coloumns: a) is revenue higher then 500k yes/no b) is profit higher then 25k yes/no are both statements correct yes/no. That way you can easylly spot why your end result is yes or no - is there a problem with revenue or profit and they use this answers as options to sort and filter the table. But once again the example is rediculous there is no use case for such analytics and you should atleast filter revenue criteria as "less then 500k" not "more then 500k" couse less is better 3. You have provided temporary solution to this question. There is no business case in this question. The case should be when do you breake even and how much you will profit each quarter after you breake even. So the question will have information about production quantities and the answer will be a graph which will tell you predicted profit. 4. Formulas are legit but way to hard for business to handle. If they would need to check simmilar info for Mexico they wont be able to do it, then they will do either waste time and wait for you to fix it or make something more simmilar, like filtering the country. You should of used graph (again) with an option to select a country as an imput and that way your business user will be able to check all the data himself. Conclusion - formulas are legit, use cases are not, all this examples are artificial and solutions are overengineered.
@terry_the_terrible Жыл бұрын
1.Automatically highlighting the 10 lowest orders has its uses. Especially in other branches of a company which for example would only be concerned with supervision and control like QA or KPIs and are not interested in sorting by number of units. On the subject of complexity Conditional Formatting is fast and easy and 60 yr old grandmas use it all the time. I see no downside on why you wouldn't want to show off your skill in a job interview
@terry_the_terrible Жыл бұрын
2. Your solution for 2 only works if your company allows you to have more columns than necessary. In big companies, you need to have a standard amount of columns if you want to be able to freely share tables between units and using 2 to 3 extra columns when the intent is to only have ONE column with "Accept" or "Reject" is wasteful. Once again, controllers who already have to keep track of 30-40 columns of criteria can't afford to waste space when dealing with large amounts of data. At the end of the day, this question is just a test of your skills with the IF formula with arbitrary criteria's by your manager. If you don't see the point of automated IF formulas then you don't have enough sales volume.
@terry_the_terrible Жыл бұрын
3. Goal Seek and advanced Goal Seek tools are valid tools that are commonly used in accounting for projections. They're much more reliable than doing algebra by hand and sometimes it's just not worth it to make a new table when you're asked a question in a board meeting. Just because you're only concerned with break even and sales volume doesn't invalidate the other test uses and Goal Seek is often usedthe Scenario Finder to quickly switch numbers in an existing document. I can personally attest that it's a lifesaver when doing projections and simulations. Especially since the final numbers can be on different sheets at the end of very complex calculations.
@terry_the_terrible Жыл бұрын
4.Youre talking about a very human solution that lets small users manipulate a graph or maybe a pivot table to manually filter the data that they need. Fair enough it works. But just so you know, large companies like banks, stock exchanges, data centers or such receive very large data sheets with very redundant and repetitive info and the job of excel grunts is often to filter 30 of these per day with the exact same steps and parameters. I've seen several of these tables in the documents that I had to fix and the joy of that solution is that you just need to copy paste the data to get the tiny amount of info you need. It's definitely a powerful, very specific and very hard thing to do that managers want but that most candidates can't do and that's why we call that a marketable skill.
@terry_the_terrible Жыл бұрын
Dude, at the end I see that you do understand excel and have a fair bit of knowledge in business accounting but that's an excel test not a business test. The average business doesn't need complex excel skills and that's fine but don't assume that no one needs advanced excel skills just because you don't. Yes it's true that a poorly understood formula is more dangerous than a slow manual process that can't be fucked up but that's like saying humans should never drive because they can already walk. You need to understand the cases where you can use them and maste their use. The world is large and there's always a new trick to learn. Give it a try and you'll see. I'd suggest conditional formatting first. Did you know that it can automatically highlight duplicate invoices in you table? It saves so much time and anguish.
@arnashaikh1800 Жыл бұрын
Amazing. Please make more videos like this I got the first three right. But the last one taught me something new..🎉
@Skystrike70 Жыл бұрын
3.5/4 I did not come up with a second way to do the last question, good brain exercise if you have not thought about excel in a bit.
@Soritaaa Жыл бұрын
ahahaha good intro! Good practice for us newbies into the interview environment. Thanks Kenji!
@surajbeloshe4441 Жыл бұрын
Need this type of more videos. so much love for this work.
@nigelsmith721 Жыл бұрын
The biggest question is why was Project B rejected?
@paimonismyemergencyfood433222 күн бұрын
I didnt know i was a expert level excel user🙂🙂 i thought sumit, xlookups are very beginner level thing
@adesinaoluwasanmi57932 күн бұрын
Amazing stuff, thanks for this
@roberth.95584 ай бұрын
Well done, Kenji. Thank you for this especially xlookup's multip[le criteria. Scored 3/4.
@kickiniitbak5 ай бұрын
I have very limited knowledge of excel and I don’t use it for my job. I have no idea what I’m doing here but I still watched the whole thing 😂
@user-bi5jr1fp2p Жыл бұрын
Okay young man, share this with my secretary and assistants who are involved in such things
@GuyFromJupiter Жыл бұрын
It's funny that even though I would consider myself a relatively advanced Excel user there are still features of it that I have never used (like Goal Seek, I've never once used that). I guess it's because as much as I like using Excel it's still just a handy tool to me as opposed to something really necessary to do my job. I don't use it to solve problems or analyze data, I usually just use it to take care of otherwise very tedious tasks.
@Ravi-sf7sh7 ай бұрын
Cute. I got everything right except the 4b part.. Never knew you could add multiple criteria for xlookup. thanks for this
@LuckyTux Жыл бұрын
Perfect score. This was too easy
@wrandy593 Жыл бұрын
I manage a large quantity of inventory and use chatgpt to generate the excel formulas I need. They are even explained concisely so that I can remember them and the function later.
@markbaker1596 Жыл бұрын
Question 2, I know this is just on the formula but I would expect an analyst to query the minimum requirements at a glance for Project B. If they moved on without having any comments I wouldn’t consider it a pass.
@musiqchakra2 ай бұрын
this is so helpful thanks for creating and i look forward to the next one! blessings!
@pi_llow Жыл бұрын
Interviewing for excel skills is kind of wild considering how easy of a technical pick up it is
@solomonbhandari-young4154 Жыл бұрын
Thank you Kenji. Great stuff
@camlex6310 Жыл бұрын
Really useful for my interview prep!!! Cheers
@gabrieliusbalandis Жыл бұрын
The & on the xlookup was very useful, will definetly use it in the future
@way2kul4any1 Жыл бұрын
You could also use SUMPRODUCT on #4.
@ChristineIT2 ай бұрын
Great explanations- thank you!
@vartikaupadhyay9733 Жыл бұрын
Thanks for making such informative videos
@KenjiExplains Жыл бұрын
Thanks for watching!
@vartikaupadhyay9733 Жыл бұрын
@@KenjiExplains hi I've just completed my articleship and i want to go so long in finance, through your video I create my resume and applied some to best summer analyst program. Till now there is no response from their side but I'm trying some better will come surely. Till now I want to work on my skill and also I've many more to ask and need somewhere your guidance where I can contact to u.
@angelamacasa9956Ай бұрын
I'm learning a lot! thanks!
@Stags28 Жыл бұрын
Would you consider the use of sumproduct with the date as a boolean to be a "different" formula than sumifs? In principle, they're the same, but it's certainly a different function...
@counterleo Жыл бұрын
Clever use of booleans as coefficients. I’d give it credit
@joshclark4272 Жыл бұрын
You can use =IFS on problem 2
@jtmh31 Жыл бұрын
The SUMIFS is not an expert skill. HR could argue that two or more Criteria Ranges and Criteria Values would make it such. If the company has either Excel 365 or Office 2021, that could be done as a spill formula using a dynamic array. Simply select both cells for France, and it would work.
@adsgame87064 ай бұрын
I am not an expert but for the Q4 I initially used this function =VLOOKUP($B$6&C$5,$B$11:$F$22,5,TRUE) and it turns out the first answer $33,430 (France, Jan-23) is correct but when i drag it to the right all the answers (France, Feb-23 & Mar-23) turn out to be the same $33,430. I am wondering if that's how Vlookup works or if i just wrong in the formula. Thanks in advance for anyone who may answer this question
@SylviusTheMad Жыл бұрын
As someone who learned Microsoft Access before he ever saw Excel (~25 years ago), I don't understand why people use Excel as much as they do. Many of these complicated actions would be easier in a database than a spreadsheet. Data cleaning, in particular. Data should never be stored in a spreadsheet.
@emanuele2014 Жыл бұрын
You are a great, great teacher. I hope you know that. Thank you.
@joseC20138 ай бұрын
I think im buying the course. I felt so behind in all of them. I remember excel from my School Of Business college days but man that was just to get some hw and projects done.
@thorstenl.49286 ай бұрын
It should be mentioned that the two solutions on task 4 do not do the same. And also that this is not two criteria in one lookup or two lookups. It is one lookup with one calculated criteria. The difference is important to know. However there are tons of approaches for this task: xlookup against 1 (with a logical comparison on multiple criteria), sumifs, sum, maxifs, max, index/match, xlookup (as shown in the video/I would not suggest this solution), sumproduct… Its more about if you understand what excel does then how to fill in a formula…
@theswankypotato Жыл бұрын
On the last one the xlookup will only return a correct result if there's only one entry for France in each month. If there is a second entry you'll want to stick with the sumifs. If i had an interview on this, I think I'd have better results explaining that than I would by actually running an xlookup.
@xApplesss Жыл бұрын
Great content, Kenji!
@tomjun894511 ай бұрын
Very helpful video! Thank you for your content as always. Love your channel.
@Luigi1500Ай бұрын
Just try it, everything work, except the CTRL + R at the end, it is now CTRL + D today.