Can You Pass This Excel Interview Test?

Подписаться 518 тыс.

Просмотров 792 тыс.

50% 1

Try this Excel Interview test to see your level!
👉 Take our Excel for Business & Finance Course: www.careerprinciples.com/cour...
🆓 DOWNLOAD Free Excel file for this video: view.flodesk.com/pages/644287...
This Excel Interview Test has a total of 4 questions going from easy to hard. First we use conditional formatting to find the bottom 10 values. Second, we use a combination of an IF and an AND statement to accept or reject a project based on two criteria. Third, we use the goal seek tool on Excel under what if analysis to reach 5000 in profit. Finally, we use 2 different formulas to find the revenue in France for each month. For this we first use a SUMIFS with relative references. Second we use the XLOOKUP combined with the ampersand (&) so we can filter using multiple criteria.
LEARN:
👉 Excel for Business & Finance Course: www.careerprinciples.com/cour...
🔥Power BI for Business Analytics: www.careerprinciples.com/cour...
📈 The Complete Finance & Valuation Course: www.careerprinciples.com/cour...
🚀 All our courses: www.careerprinciples.com/courses
SOCIALS:
🎬 My Company RU-vid Channel: / @careerprinciples
📸 Instagram - careerprinc...
🤳 TikTok - / career_principles
🧑‍💻 LinkedIn - / careerprinciples
▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬
Chapters:
0:00 - Question 1 (Easy)
1:17 - Question 2 (Intermediate)
4:15 - Question 3 (Advanced)
6:30 -Question 4 (Expert)
Disclaimer: I may receive a small commission on some of the links provided at no extra cost to you.

Опубликовано:

9 июн 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 426

@KenjiExplains Год назад

🔥 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 11 месяцев назад

Can I take this course with no prior knowledge on excel?

@bardia8225 8 месяцев назад

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

@pyromanga Год назад

@@HateSpeechMoreLikeBasedSpeechpower query goes brrrr

@Rohwi Год назад

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 10 месяцев назад

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 10 месяцев назад

@@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?

@maridelregala8742 Год назад

I love the way you explain, straight to point! Indeed you are heaven sent to newbies like me❤

@mikenkk1 Год назад

Halfway through question 4 was super simple for me, but I didn't know I could do xlookup with two criteria; thanks!!

@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

@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

@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!

@mythals 9 месяцев назад

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!

@mhmdseadawy8228 Год назад

The last formula of using the xlookup is awesome 👍, I was thinking about using the formulas (sum + filter) Thanks for sharing 🙂

@KenjiExplains 3 месяца назад

Check out this updated Excel Test video for 2024: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-E4t43Bx7XvY.html

@hikari1690 Год назад

Thank you for showing the short keys. I keep forgetting f4 so you showing it really helps

@owrsrlt9062 3 месяца назад

about question 4 , i knew sumifs... but Xlookup with the " & " ... wow... just magic thak you very much

@surajbeloshe4441 Год назад

Need this type of more videos. so much love for this work.

@camlex6310 Год назад

Really useful for my interview prep!!! Cheers

@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

@Cavcurlz 8 месяцев назад

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!

@MT-kr8cn 10 месяцев назад

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!

@sixpepper Год назад

Haven’t taken excel training in years. I didn’t forget anything thankfully.

@Soritaaa Год назад

ahahaha good intro! Good practice for us newbies into the interview environment. Thanks Kenji!

@tomjun8945 8 месяцев назад

Very helpful video! Thank you for your content as always. Love your channel.

@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 )

@dasfreutmich1 9 месяцев назад

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

@d4ve19 4 месяца назад

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.

@solomonbhandari-young4154 Год назад

Thank you Kenji. Great stuff

@emanuele2014 10 месяцев назад

You are a great, great teacher. I hope you know that. Thank you.

@arnashaikh1800 Год назад

Amazing. Please make more videos like this I got the first three right. But the last one taught me something new..🎉

@gabrieliusbalandis Год назад

The & on the xlookup was very useful, will definetly use it in the future

@industryrule-4080 Год назад

I’m an Excel newbie and got the first two right. That last one was 🤯 for me.

@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

@thomasstorbugt6829 6 месяцев назад

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

@musiqchakra 10 дней назад

this is so helpful thanks for creating and i look forward to the next one! blessings!

@Yorknie 7 месяцев назад

Dang that first question was the hardest would have never known about it without this video.

@roberth.9558 2 месяца назад

Well done, Kenji. Thank you for this especially xlookup's multip[le criteria. Scored 3/4.

@hodlmaximalist 8 месяцев назад

I work as an investment analyst and this actually taught me a lot 😂

@26iain Год назад

To be fair, in the 2nd exercise I'm 100% accepting project B, screw the formula.

@xApplesss Год назад

Great content, Kenji!

@jayantsood6346 Год назад

Need more videos like this one.

@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!

@Tzwrpap Год назад

Hello Kenji, could you make a video explaining the use of f4 ?

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

@Alexexplains-9095 Год назад

Nice Kenji,Thank you.

@Ravi-sf7sh 4 месяца назад

Cute. I got everything right except the 4b part.. Never knew you could add multiple criteria for xlookup. thanks for this

@catalinabecu3753 5 месяцев назад

Thank you this was very helpful :)

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

@tayyabyarkhan1048 4 месяца назад

Xlookup part was really great to learn ❤❤❤😊

@rafaelasantana7695 Месяц назад

Amazing content! Thanks!

@ThinkThings_1 8 месяцев назад

Thanks for sharing such a great video

@N3v3rm1nd91 4 месяца назад

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.

@shouryateja Год назад

Would love to take up your course, too expensive for me. But I hope people take it up, you are amazing.

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

@alinas5521 2 месяца назад

amazing! thank you!

@madhunethaposa1494 4 месяца назад

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.

@AD19938 Год назад

THANK YOU!

@LuckyTux Год назад

Perfect score. This was too easy

@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))

@linusnilsson2377 5 месяцев назад

I knew everything except & in excel lookup good one

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

@Greendotz 4 месяца назад

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

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

@user-jk5gg8mv6n 9 месяцев назад

Thank you so much

@marie-vb3vg 8 месяцев назад

thank you for the video! does anyone know how to build the profitability table?

@khurramkhan5328 Год назад

Very very cool ... thank you

@joseC2013 6 месяцев назад

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.

@tarasherlock876 Год назад

The 4th example, I guess I would just do a pivot table, but I can see how this is useful.

@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. 🤷‍♂️

@kloppskop4149 10 дней назад

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.

@giladdrillich2350 Год назад

Very easy

@m.w.6099 Год назад

Great stuff! So much fun and interesting these ‘exams’! I have a question about Q4; why in both answers when you refere to C5 you don’t lock the row like C$5 but keep it all open and use C5? And in the second answer when you use the xlookup, is it also possible to use a second xloookup function in this formula instead? Great btw to see the formula you use with the &, didn’t know this was possible! Thanks and keep teaching, you’re a natural.

@B3Band Год назад

Because you don't need to. The formula moves across, not down. You certainly could do it, but if you're trying to demonstrate mastery, then knowing whether or not you actually need to lock rows is a good thing to mention in your interview.

@henrikhjerppe8804 Год назад

@@B3Bandbut for the reference in B6, he left the row unlocked, as if he was preparing to copy the formula down as well as across. Say, if he wanted to add more country rows to a comparison. Just full locking B6 would have spared two hits of the F4 key.

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

@JohnZyski 4 месяца назад

Very helpful.

@MrPetrochelli 4 месяца назад

thx it was really usefull..

@giovannihernandez3208 Год назад

3.5/5 xlookup is tricky for me. Great explanation here, thank you!

@counterleo Год назад

It is, but the real world you would just use a pivot table I guess, takes 10 seconds

@giovannihernandez3208 Год назад

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

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

@sabinatopcic5812 27 дней назад

Great video! Could you maybe explain this scenario: you have a project budget, and you need to move 5% from one budget line to another.? How to accomplish that? I had this task on one of my job tests. Thank you in advance!

@mattmedema6426 7 месяцев назад

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.

@amitl2308 6 дней назад

the last one i did with sumifs and also if sumproduct

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

@Nwakaibeya Год назад

Great stuff.

@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!

@adewaleonipede1861 Месяц назад

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

@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 !

@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.4928 3 месяца назад

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…

@user-bi5jr1fp2p Год назад

Okay young man, share this with my secretary and assistants who are involved in such things

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

@way2kul4any1 Год назад

You could also use SUMPRODUCT on #4.

@b10highlights13 Год назад

Great video

@flourishwithMJ 11 месяцев назад

Thanks

@Kalachoff 11 месяцев назад

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 10 месяцев назад

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 9 месяцев назад

@@robertcasey1708 Exactly. My bad. Hate fixed numbers as well. :)

@LTJchrisRBF 7 месяцев назад

that could be simplified a lot, to =ROUNDUP((C8+5000)/(C6-C7),0)

@Kalachoff 6 месяцев назад

@@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) :)

@LTJchrisRBF 6 месяцев назад

⁠⁠@@Kalachoffit’s in there c8 + 5000

@dylan_song Год назад

Thank you so much! This helped me test my current Excel knowledge!

@ajiebooks 2 месяца назад

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

@paoponce1863 7 месяцев назад

THANK YOUUU

@republikadugave420 9 месяцев назад

Man this is super easy...

@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