@@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.
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.
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
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.
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)
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.
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.
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.
@@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.
@@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?
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.
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.
How my approach for Q4 =XLOOKUP(1,(CriteriaRange1=Criteria1)*(CriteriaRange2=Criteria2),ReturnColumn) Been using this to replace Index & Match function
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 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.)
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.
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
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.
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.
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.
@@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”.
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.
@@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))
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!
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!
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.
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!
🤯 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 :)
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…
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.
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.
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!
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.
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.
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.
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.
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 :(
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.
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!
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!
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.
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?
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 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!!!!
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 :)
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.
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.
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!
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
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.
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)
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
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
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.
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.
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.
Nice I didn't know about the "&" hack for Xlookup. That's super cool that will actually help me out a bit! I use Python increasingly for my data needs, but I still use google sheets/excel regularly for smaller data sets! That will help!
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.
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.
@@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.
To be fair, it's bad practice to round-up to a whole number and then show xxx.0, which suggests a decimal place of accuracy of the number, that has just been removed by rounding.
I got 2 of 4 questions correct, 3 I would have solved in some other way instead of Goal seeker. 4th really taught me my current understanding about Excel.😅
I consider myself a capable user, (Decent with Formulas, VBA, and Power Query) not necessarily a master but good enough that anyone who doesn't really know how to use excel could think I was ended up getting 3/4 didn't know u could do SUMIF like that, normally i'd just use helper columns
Hi @KenjiExplains - In the second Test of Dates the datedif formula does not calculate the no.of months correctly for the first date in your example (for instance the dates selected were 6/1/24 to 8/30/24 which is actually three months but the formula shows it as 2 months which is incorrect. Kindly recheck.
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…
Q3 I initially thought to use the Solver add-in as I’ve never really messed around with What-If Analysis. Are there scenarios you guys can think of where it would be better to use What-If? Better to use Solver?
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.
@@counterleo for reporting yes, pivot table but if you want to be operating that table for linking to other tables and things like that, I guess it is better to have formulas.
damn, i thought to be intermediate, but i underestimate the skills. But actually for me the trickier one is the easiest one as i would have filtered all columns through shortcut (showing i can save time) sort it by lowest and manually go for highlting the, but also conditional formatting is very ideal, and maybe you end up saving more time
Kenjie, is your course more an excel course or more a finance course? Would you say it is a good way to learn and remember all the capabilities of excel or should I take an excel specific course for that? If so, what excel specific course would you recommend? Thank you. Love you're teaching style! Cheers!
Hey there, it’s definitely an excel course. It has a focus on real-work tasks that’s why it’s called for business & finance. If you’re interested, you can get it here: www.careerprinciples.com/courses/excel-for-business-finance
🔥 Take our Excel for Business & Finance Course: www.careerprinciples.com/courses/excel-for-business-finance
gotta Q, when Microsoft release copilot, are the knowledge will be still useful?
@@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.
This is amazing
Can I take this course with no prior knowledge on excel?
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.
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
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.
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
@@HateSpeechMoreLikeBasedSpeechpower query goes brrrr
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)
I would have used the SUMIFS and the SUMPRODUCT formulas.
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.
Please I would like to learn more from you,I really love your experience
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.
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.
@@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.
@@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?
Halfway through question 4 was super simple for me, but I didn't know I could do xlookup with two criteria; thanks!!
news to me too. Very cool!
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.
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.
9:29 &b
How my approach for Q4
=XLOOKUP(1,(CriteriaRange1=Criteria1)*(CriteriaRange2=Criteria2),ReturnColumn)
Been using this to replace Index & Match function
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.
This is what I had in mind too, I never knew that the Goal Seek option was even there!
@@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.)
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.
about question 4 , i knew sumifs... but Xlookup with the " & " ... wow... just magic
thak you very much
I love the way you explain, straight to point! Indeed you are heaven sent to newbies like me❤
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
The last formula of using the xlookup is awesome 👍, I was thinking about using the formulas (sum + filter)
Thanks for sharing 🙂
I wish I had these videos available when I was doing interview prep. Nice work Kenji!
Thank you!
Hi Michael
Hope you are Good, please I would like to learn more from you because I know you would have experience concerning an interview
Dang that first question was the hardest would have never known about it without this video.
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.
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.
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.
@@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”.
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.
@@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))
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!
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!
I would use SUMPRODUCT on Q4. I think it's safer because it would work with multiple input of the same month and country.
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.
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!
🤯 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 :)
Ditto! My brain says no way does & work like that…guess now I’ll have to try it for myself. 🤷♂️
Best and most clear vids on Excel by far. Thanks Kenji-san.
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 🙂
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…
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.
Thank you for showing the short keys. I keep forgetting f4 so you showing it really helps
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.
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!
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.
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.
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.
Check out this updated Excel Test video for 2024: th-cam.com/video/E4t43Bx7XvY/w-d-xo.html
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.
To be fair, in the 2nd exercise I'm 100% accepting project B, screw the formula.
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 :(
It's not a stupid request if you don't know how to work something out! Fingers crossed Kenji sees this for you bud ❤
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.
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!
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!
th-cam.com/video/FRu48zy-Djk/w-d-xo.html
The real test of Question 2 is whether or not you realize how silly it would be to actually reject Project B.
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.
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.
If you needed months, why dind't you do (month(array)). Excel goes really easy these days with such formulas.
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!
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.
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?
Bro, I think you are missing the point of this exercise lol. It's testing excel skills, not financial acumen.
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.
this might be true 25 years ago but nowadays self training is more than adequate haha
@@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!!!!
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 :)
You could also use SUMPRODUCT on #4.
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.
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.
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!
You are such an Educator angel.❤❤❤❤❤❤
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.
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
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.
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)
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
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
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.
Well done, Kenji. Thank you for this especially xlookup's multip[le criteria. Scored 3/4.
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.
I would definitely accetp the Project B. That's a big margin, but then the exercise was more on excel kill not on qualitative assessment
Perfect score. This was too easy
I did 3 out of 4 correct and had the first Xlookup function
I did not know that sumif can have multiple criteria hehe
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.
Nice I didn't know about the "&" hack for Xlookup. That's super cool that will actually help me out a bit! I use Python increasingly for my data needs, but I still use google sheets/excel regularly for smaller data sets! That will help!
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.
You can use =IFS on problem 2
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.
Just try it, everything work, except the CTRL + R at the end, it is now CTRL + D today.
Thanks for making such informative videos
Thanks for watching!
@@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.
To be fair, it's bad practice to round-up to a whole number and then show xxx.0, which suggests a decimal place of accuracy of the number, that has just been removed by rounding.
I will accept Project B, from the second question, everyday of the week
I got 2 of 4 questions correct, 3 I would have solved in some other way instead of Goal seeker. 4th really taught me my current understanding about Excel.😅
Amazing stuff, thanks for this
honest question. why round up formula and not just Decrease Decimal?
You are a great, great teacher. I hope you know that. Thank you.
I consider myself a capable user, (Decent with Formulas, VBA, and Power Query)
not necessarily a master but good enough that anyone who doesn't really know how to use excel could think I was
ended up getting 3/4
didn't know u could do SUMIF like that, normally i'd just use helper columns
ahahaha good intro! Good practice for us newbies into the interview environment. Thanks Kenji!
Need this type of more videos. so much love for this work.
Okay young man, share this with my secretary and assistants who are involved in such things
Hi @KenjiExplains - In the second Test of Dates the datedif formula does not calculate the no.of months correctly for the first date in your example (for instance the dates selected were 6/1/24 to 8/30/24 which is actually three months but the formula shows it as 2 months which is incorrect. Kindly recheck.
Hello Kenji, could you make a video explaining the use of f4 ?
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…
Very easy
Would love to take up your course, too expensive for me. But I hope people take it up, you are amazing.
Thanks for the tutorial. For some reason, I don't understand the cell locking part with the F4 key. I need to understand how it can be done
42! There you go, when do I start?
For Q1, there are duplicate values in column E. I think those have to be counted as 1.
In Q2 the revenue and profit columns should be absolute.
I have used sumifs and sumproduct instead of your xlookup as some user might don't have this in their formula bar.
The 4th example, I guess I would just do a pivot table, but I can see how this is useful.
Really useful for my interview prep!!! Cheers
Is this the typical difficulty for excel interview questions for data analyst roles?
I don't even use excel but this was a good video.
I'm learning a lot! thanks!
Q3 I initially thought to use the Solver add-in as I’ve never really messed around with What-If Analysis. Are there scenarios you guys can think of where it would be better to use What-If? Better to use Solver?
Thank you Kenji. Great stuff
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.
What kinds of jobs could someone with a 4/4 get?
My awenser would be just: I'll find how to do it on Yt. Next one please
had no idea you could put an & iin the xlookup formula like thatl.
But on the last test it will be best to use sumifs than ookup formula just to be safe.
use sumproduct for multi criteria lookup for #4
hey do you mind if I put this in my portfolio?
in task five i used sumproduct formula and sumifs.
Need more videos like this one.
3.5/5 xlookup is tricky for me. Great explanation here, thank you!
It is, but the real world you would just use a pivot table I guess, takes 10 seconds
@@counterleo for reporting yes, pivot table but if you want to be operating that table for linking to other tables and things like that, I guess it is better to have formulas.
damn, i thought to be intermediate, but i underestimate the skills. But actually for me the trickier one is the easiest one as i would have filtered all columns through shortcut (showing i can save time) sort it by lowest and manually go for highlting the, but also conditional formatting is very ideal, and maybe you end up saving more time
Kenjie, is your course more an excel course or more a finance course? Would you say it is a good way to learn and remember all the capabilities of excel or should I take an excel specific course for that? If so, what excel specific course would you recommend? Thank you. Love you're teaching style! Cheers!
Hey there, it’s definitely an excel course. It has a focus on real-work tasks that’s why it’s called for business & finance. If you’re interested, you can get it here: www.careerprinciples.com/courses/excel-for-business-finance