the xlookup is great but the way you've used it assumes that each brand has a unique GM. If there were two brands with the same GM %, then xlookup will by default only populate your list with the first brand
Great video. I’d maybe have used an IFS function for the last one to reduce the nesting which always trips me up. It would be 3 tests: if it’s less than the resignation month then full amount, if it’s equal to the month then they get partial amount, and if it’s greater than the month then it’s 0. I like your method too though- and I learned a lot from this. Thanks
Definitely this one. I would use Kenji's nesting only if I'm using an earlier version of Excel. Otherwise using IFS would be a clearer and simpler solution to the task. Thanks for the great video, Kenji!
Just remember that not all versions of Excel suppose IFS and XLOOKUP. If the hiring manager knows that, they may purposely put you on Excel 2019 or 2016 just to see if you can still handle it without making excuses. They might even do it without even realizing if they are at a company that simply hasn't felt the need to upgrade. I've worked at Amazon and UPS, and they never updated their Office suite anywhere near the time that they were released.
but u have microsoft 365 subscriptions for companies like 10-20$ per user per month. You get updates automaticaly. Really weird such big companies dont pay subscriptions to have most up-to-date software? its like pennies. Interesting...@@B3Band
I'm a fan of array functions. For Q1, I'd use orginal amount times (1+r)^SEQUENCE to return 1 row and x columns where x is the number of periods. Then reference all subsequent steps to that array. Though, since not everyone is on 2019/365 just yet, my method may cause issues with compatibility...
For Q3 I prefer to calculate DAYS(resignation,month_start)/DAYS(month_end,month_start) and then apply a max of 1 and min of 0. Multiply this by the salary amount and done 😃
For the last If function. Should you not actually count the business days, rather than all days? So, if a month has 20 business days, but I quit on the 3rd of the month, I could have worked between 1 or 3 days. (Between $250 and $750 on a $5000 salary) In your scenario, you pay me a fraction of the overall salary, which does not account for that. So, the correct formula to use for that is: =NETWORKDAYS(B3,B4), where B3 is the starting date and B4 is the ending date
Just use a Filter( Sortby( Filter() ) ) for question 2. Inner Filter() removes all columns except the two you want. Sortby() the GM. Outer Filter() where any row with GM less than the nth GM is removed.
"Date" is a number format in excel that show you "the number of days since January 1st of 1900". So, you can use inside the if "date_of_resig" cell minus "month_col_name" to get the days worked. And this, divided by day(eomonth(month_col_name, 0)). So, if(date_of_resig > month_col_name -1, (date_of_resig - month_col_name -1)/day(eomonth(month_col_name,0)),). -1 because if you resign 1st of month you worked one day
Question 2 - x.lookup / index match wont work if you have the same value in multiple cells so the formula is incorrect. Try using INDEX(SORT(B6:F17,6, -1), SEQUENCE(5), {1,5}). In the future when they'll implement new functions TAKE and CHOOSECOLS, it'll be even more intuitive
You are truly doing a great job.. especially for people like me who are at the entry level of career after ACCA.. learning alot from your videos.. keep making more videos MAY ALLAH BLESS YOU!!
Nice video Kenji, but had a thought though that came to my mind regarding question 3. Here in my country due to Labour Law legal definition of pro-rated salaried working days, calculation might not be as simple, as some employment work on a 5 days/week and some 6 days/week. As such, what is considered daily rate needs to take into account how many days he/she would actually be required to work that month (e.g. until 7th of the month employee A may need to work 5 days, employee B may need to work 6 days eventhough both resigns on the same date. Salary payment would only be up to those number of working days). Not sure how it is in other countries, just something to think about.
this is more complicated. you have to take into account the days each date signifies from sunday to monday, and you probably have to count holidays too. what day each employee will work also must be specified. you probably have to work on this month by month like project management table. maybe ask someone who works at payroll on how they do it
Fascinating stuff. I am curious how you would approach that last question if some of the employees had not resigned (i.e. resignation date blank for the employees).
thanks for such productive instruction but my question is how to eliminat the weakend from the calculation (in short our salary is divided only on working days based on that we do calculation )@Kenji Explains
Hey Kenji, great video again! I was wondering if in Q2 there were 2 exact same percentages, would XLOOKUP show error or the first that comes of the two?
Good question! It would just show the first I think...there are alternatives to it but it would just require multiple formulas which I didn't want to get into in the video XD
11:42 "Also make sure they're on Excel and not on a different platform, which is obviously a lot easier to do" Not gonna lie, that's so funny to hear 🤣
So that if function and the referrences and the true or false etc can be used as an indirect equation because it's made to be precise numerically speaking with a twist on how science things behave per point in time like mapping microthings area for example and mapping science things area per painted or approximated area to create area equation like macro and micro separate focus and equation per area with behavioral diagnosis of it's numericals if compounds or energies range created when clumped.
Wouldn't you use the WORKDAY function in the last example to make sure they are getting the right percentage based on actual days worked? For example, April 17, 2022 is a Sunday... assuming a standard 5-day work week M-F, they shouldn't getting paid for the weekends.
On question 3 all the fields ended on the 22nd. Not sure if I'm thinking too hard but that would of made me wonder if everyone got paid on the 22nd normally and would need to be their days counted from the 23rd
what if i use function CHOOSECOL, CHOOSEROW WITH SORT for the exercise 2. And in the exercise 3, should we exclude weekend, i mean we should use NETWORKDAYS function instead. Thanks
The Q3 isn't correct as the function takes into consideration all days in the month however this analysis should consider only working days. Last time I checked no country in the world pays for weekends if you aren't working or if your contract doesn't say so
In this video you calculate salary for resigning employees during the month What formula will be used if someone is joining during the month . Means we cant calculate salaries of all employees including new employees who joined during month
Question 2 was a lot easier than Question 1, IMO. Now, I've only had 1 'excel interview test' (intermediate/new grad), but I had a couple of days to do it. Is that common? Or would a 'test' like the one in this video be something you'd complete on the spot?
I've had some due in a couple days (especially for startup roles) and also some on the spot for big tech (30min-ish) where you need to explain your thought process as well. I guess both are possible!
For a university student wanting to specialize in quantitative finance is your complete finance & valuation course necessary for the quantitative field?
One solution I can think off is to use some logical expression to check if the top brand isn't like the next match from the xlookup. Not sure ho actually Excel behave in that instance, does it go for the first match or throws an error? And I was wrong. From searching online I found that Xlook will return only the first matching instance and not the rest. People use this instead : For reference, =INDEX([array], SMALL(IF([search value] = [search column], ROW([search column])-1,""), ROW(1:1)))
Hi Kenji! I was wondering whether those paid courses on the link has a lifetime access or a limited one (e.g only accessible in one year, etc). Thanks!
This was incredibly helpful!... But in regards to Q2 - when you are creating the XLOOKUP formula for the brand when sorting - is there a workaround when there are two lookup values that are the same? If you apply this formula when there are two brand with the same value, it just puts the same brand in twice, rather than the two different brands that have the same value.
I had received an email for Warsaw Goldman Sachs, pre recorded interview. For internal auditing position, any advice? Excel skills are really basic and never done auditing in my life only some bookeping and partial balance sheets and partial pre compilating taxes, but things that are really basic.
Hey there! the prerecorded interviews are usually more generic so they don’t assume previous knowledge of the subject(audit/accounting in this case). So you can expect more behavioral questions. That said, the questions may have changed since my colleagues and I did it so be prepared for both!
@@KenjiExplains above compiling some income statements and balance sheet from high school and university I don’t know how is it in audit. Beyond that I think I made a mistake writing “economics and accounting” bachelor and not “economics and innovation” which is its real name. But it’s just a translation mistake. Thank you very much
@Kenji Explains Hi Kenji can I ask you if Goldman was selective or was giving everybody a chance of doing hirevue? Are chances bigger to get hired once a candidate received a hirevue pre recorded to do or chances remain the same? I'm really nervous but I think it's common given the reason.
Hey Kenji, I might be landing a BA job soon (I'm in my last few months of high school), and I completed this test. I haven't practiced much excel, apart from what I remember from school and searching for formulas throughout the test, so I'm wondering what level of experience this test was; is it entry level for a job or pretty good for most situations? Thanks for the test file!
*When it comes to investing, I'm so ignorant about it, I hereby ask; What's your say or thought for anyone with over $20k looking for the best ways to make good returns off it? I will appreciate any help here on how to do this*
@Lucy Bernardi Interesting, I've always been fascinated with investing and I need to start now. Could you possibly give more information on your Investment Professional and how I can reach out to her?
Hi Kenji, great video!!! Anyway can I use your worksheets on my video? I'll give credit to you, just it seems very good if I could localize this material. I also make an Excel video on my channel. Feel free to check that out
I got my job as a financial analyst. This helped so much, thank you from the bottom of my heart.
Literally practiced your excel interview test the day before my interview, and got the job as a result. 10/10 Recommended channel.
the xlookup is great but the way you've used it assumes that each brand has a unique GM. If there were two brands with the same GM %, then xlookup will by default only populate your list with the first brand
Good point! You can use a combination of index + sort + sequence, but it's gonna get a bit messy haha
Was thinking the same. I would add a column with a rank function and then use the unique rank reference with the xlookup.
Great video. I’d maybe have used an IFS function for the last one to reduce the nesting which always trips me up. It would be 3 tests: if it’s less than the resignation month then full amount, if it’s equal to the month then they get partial amount, and if it’s greater than the month then it’s 0. I like your method too though- and I learned a lot from this. Thanks
Good point!
Definitely this one. I would use Kenji's nesting only if I'm using an earlier version of Excel. Otherwise using IFS would be a clearer and simpler solution to the task.
Thanks for the great video, Kenji!
Just remember that not all versions of Excel suppose IFS and XLOOKUP. If the hiring manager knows that, they may purposely put you on Excel 2019 or 2016 just to see if you can still handle it without making excuses. They might even do it without even realizing if they are at a company that simply hasn't felt the need to upgrade. I've worked at Amazon and UPS, and they never updated their Office suite anywhere near the time that they were released.
but u have microsoft 365 subscriptions for companies like 10-20$ per user per month. You get updates automaticaly. Really weird such big companies dont pay subscriptions to have most up-to-date software? its like pennies. Interesting...@@B3Band
Beginner here: In this last example, does this formula reflect or differentiate work days and weekends?
I'm a fan of array functions. For Q1, I'd use orginal amount times (1+r)^SEQUENCE to return 1 row and x columns where x is the number of periods. Then reference all subsequent steps to that array.
Though, since not everyone is on 2019/365 just yet, my method may cause issues with compatibility...
Every video is full of leaning. Thanks a lot , Kenji for a wonderful explanation.
He seems to be sitting straight to me. 🤣
Hi Kenji, I am always looking forward for your videos every time am on you tube. I am learning a lot from you brother keep up the good works.
Thank you! I appreciate the kind comment Joseph :)
I like to use Alt + Enter between different parts of a nested IF formula, as it really helps make it easier to read
Nice thanks for the suggestion!
oh nice, I never thought of that before. But I use it frequently to [Enter] inside a cell when typing long texts
For Q3 I prefer to calculate DAYS(resignation,month_start)/DAYS(month_end,month_start) and then apply a max of 1 and min of 0. Multiply this by the salary amount and done 😃
I was thinking the excat same but maybe with an additional adjustment for business days in the month for the prorata component.
You have really helped me a lot
I always look for your videos anytime I come to utube.
Keep it up and continue the good job
For the last If function. Should you not actually count the business days, rather than all days?
So, if a month has 20 business days, but I quit on the 3rd of the month, I could have worked between 1 or 3 days. (Between $250 and $750 on a $5000 salary)
In your scenario, you pay me a fraction of the overall salary, which does not account for that.
So, the correct formula to use for that is: =NETWORKDAYS(B3,B4), where B3 is the starting date and B4 is the ending date
dude ur actually a legend. Concise and succinct too.
Thank you for watching!
This is definitely gonna come in handy in my next interview! Thank you!!!
Great to hear!
The Datedif function could help with the difference between the two dates. Nice one Kenji
Good point!
Very much enjoy watching your videos and looking forward for more to come! Keep up the great work!
Thank you for the nice comment Benjamin!
This is very informative video. Thank You Kenji for sharing your knowledge and skills.
The most important stuff that I have ever learned hats off to you boss 🤠💖💖💖
Cheers!
Just use a Filter( Sortby( Filter() ) ) for question 2.
Inner Filter() removes all columns except the two you want.
Sortby() the GM.
Outer Filter() where any row with GM less than the nth GM is removed.
"Date" is a number format in excel that show you "the number of days since January 1st of 1900". So, you can use inside the if "date_of_resig" cell minus "month_col_name" to get the days worked. And this, divided by day(eomonth(month_col_name, 0)). So, if(date_of_resig > month_col_name -1, (date_of_resig - month_col_name -1)/day(eomonth(month_col_name,0)),). -1 because if you resign 1st of month you worked one day
very creative solving the last one..impressive
Thank you hope you liked it ✌️
The dynamic holding period is a very practical model feature 👍
Yes sir!
Nice and loaded, keep it up Mr Kenji
Question 2 - x.lookup / index match wont work if you have the same value in multiple cells so the formula is incorrect. Try using INDEX(SORT(B6:F17,6, -1), SEQUENCE(5), {1,5}). In the future when they'll implement new functions TAKE and CHOOSECOLS, it'll be even more intuitive
Good point thanks for the suggestions!
I just tried your suggestion, sadly my Excel 2016 cannot support sort and sequence, is there any other way please? thank youuu
🏦 Learn finance & valuation: www.careerprinciples.com/courses/finance-valuation-course
🧑💻 Easier excel interview test: ruclips.net/video/50X2DDPWXoY/видео.html
Great! Im learning new things. Thanks Kenjie!
For number 3, for better optics and visual checking, i would put the 2nd nested if in the second line. Alt+Enter
Good point thanks for the suggestion!
Many thanks! The video is useful!!!! Keep making the great content👍🏻
You are truly doing a great job.. especially for people like me who are at the entry level of career after ACCA.. learning alot from your videos.. keep making more videos
MAY ALLAH BLESS YOU!!
Thank you for the nice comment Basit! More to come :)
Hi Kenji, Great Video - RE: for Salaries - the full story for budgeting / forecasting is both start and stop dates.....do you have a video for this?
Would be great addition to the videos if the shortcuts pressed are displayed somewhere on the screen
Nice video Kenji, but had a thought though that came to my mind regarding question 3.
Here in my country due to Labour Law legal definition of pro-rated salaried working days, calculation might not be as simple, as some employment work on a 5 days/week and some 6 days/week. As such, what is considered daily rate needs to take into account how many days he/she would actually be required to work that month (e.g. until 7th of the month employee A may need to work 5 days, employee B may need to work 6 days eventhough both resigns on the same date. Salary payment would only be up to those number of working days).
Not sure how it is in other countries, just something to think about.
this is more complicated. you have to take into account the days each date signifies from sunday to monday, and you probably have to count holidays too. what day each employee will work also must be specified. you probably have to work on this month by month like project management table. maybe ask someone who works at payroll on how they do it
Fascinating stuff. I am curious how you would approach that last question if some of the employees had not resigned (i.e. resignation date blank for the employees).
Great Video. Could you make a video to explain relative & absolute references? I always get confused. Thanks
Hi Kenji, Singaporean here. I have a way to shorten the IF formula in the Advanced Excel Interview question
IF(MONTH(F$5)
The last formula was great to learn. Thank you!
Glad it was helpful!
On 3, I use max(min((eomonth-date)/(eomonth()-eomonth(,-1)+1)),1),0)* figure.
Conceptually it’s just what’s the % of month max 1, min 0.
Keep going kenji!
Cheers!
Thanks for this upload!
I learned a lot in your excel course!
Appreciate it!
If one doesn't have an XLookup function try this =index($B$6:$B$17,match(I15,$F$6:$F$17,0)). For sequence function just use google sheets.
Can you do a video about LET and LAMBDA functions?
Super helpful tips!! Thanks
Glad it was helpful!
thanks for such productive instruction but my question is how to eliminat the weakend from the calculation (in short our salary is divided only on working days based on that we do calculation )@Kenji Explains
Hey Kenji, great video again! I was wondering if in Q2 there were 2 exact same percentages, would XLOOKUP show error or the first that comes of the two?
Good question! It would just show the first I think...there are alternatives to it but it would just require multiple formulas which I didn't want to get into in the video XD
amazing man
11:42 "Also make sure they're on Excel and not on a different platform, which is obviously a lot easier to do"
Not gonna lie, that's so funny to hear 🤣
So that if function and the referrences and the true or false etc can be used as an indirect equation because it's made to be precise numerically speaking with a twist on how science things behave per point in time like mapping microthings area for example and mapping science things area per painted or approximated area to create area equation like macro and micro separate focus and equation per area with behavioral diagnosis of it's numericals if compounds or energies range created when clumped.
Thanks!
Wouldn't you use the WORKDAY function in the last example to make sure they are getting the right percentage based on actual days worked?
For example, April 17, 2022 is a Sunday... assuming a standard 5-day work week M-F, they shouldn't getting paid for the weekends.
Q2: Xlookup for Brand works only if all values are unique. I would use rank and then xlookup from that. But maybe there are better ways?
Good point!
Due to subtitles can't see what short cut keys you telling, other than everything is very helpful and knowledgeable for next level..
On question 3 all the fields ended on the 22nd. Not sure if I'm thinking too hard but that would of made me wonder if everyone got paid on the 22nd normally and would need to be their days counted from the 23rd
Great video. Very practical advice :)
Thank you 👊
what if i use function CHOOSECOL, CHOOSEROW WITH SORT for the exercise 2. And in the exercise 3, should we exclude weekend, i mean we should use NETWORKDAYS function instead. Thanks
Hello Kenji, Do you offer personal excel tutoring ?
The Q3 isn't correct as the function takes into consideration all days in the month however this analysis should consider only working days. Last time I checked no country in the world pays for weekends if you aren't working or if your contract doesn't say so
In this video you calculate salary for resigning employees during the month
What formula will be used if someone is joining during the month .
Means we cant calculate salaries of all employees including new employees who joined during month
6:10 What will happen if there are two or more brands with equal percentages?
Babe wake up, new Kenji video
hahahah love to see it
how would formula in Q3 looked like if we were to count only working days?
Great video, Is Microsoft excel the main program used in the corporate finance field or do some company’s use google sheets? Thanks
Hey! It's Excel that's by far most popular. Google sheets is often used by startups though
Question 2 was a lot easier than Question 1, IMO. Now, I've only had 1 'excel interview test' (intermediate/new grad), but I had a couple of days to do it. Is that common? Or would a 'test' like the one in this video be something you'd complete on the spot?
I've had some due in a couple days (especially for startup roles) and also some on the spot for big tech (30min-ish) where you need to explain your thought process as well. I guess both are possible!
@@KenjiExplains Intriguing! Thank you for your response.
For a university student wanting to specialize in quantitative finance is your complete finance & valuation course necessary for the quantitative field?
Great video
Thank you!
for Q2, what will you use if you have 2 or more items with the exact same margins?
One solution I can think off is to use some logical expression to check if the top brand isn't like the next match from the xlookup. Not sure ho actually Excel behave in that instance, does it go for the first match or throws an error?
And I was wrong. From searching online I found that Xlook will return only the first matching instance and not the rest. People use this instead : For reference, =INDEX([array], SMALL(IF([search value] = [search column], ROW([search column])-1,""), ROW(1:1)))
Shorter alternative for last problem - IFS(MONTH(F$5)$D6,0)
Hi Kenji! I was wondering whether those paid courses on the link has a lifetime access or a limited one (e.g only accessible in one year, etc). Thanks!
Hey there! It’s lifetime access (see the FAQ section on the landing page)
For Q1: why did you used 1+ the rental income growth rate?
This was incredibly helpful!... But in regards to Q2 - when you are creating the XLOOKUP formula for the brand when sorting - is there a workaround when there are two lookup values that are the same?
If you apply this formula when there are two brand with the same value, it just puts the same brand in twice, rather than the two different brands that have the same value.
how did he fill the entire table in the third problem?
I had received an email for Warsaw Goldman Sachs, pre recorded interview. For internal auditing position, any advice? Excel skills are really basic and never done auditing in my life only some bookeping and partial balance sheets and partial pre compilating taxes, but things that are really basic.
Hey there! the prerecorded interviews are usually more generic so they don’t assume previous knowledge of the subject(audit/accounting in this case). So you can expect more behavioral questions. That said, the questions may have changed since my colleagues and I did it so be prepared for both!
@@KenjiExplains above compiling some income statements and balance sheet from high school and university I don’t know how is it in audit. Beyond that I think I made a mistake writing “economics and accounting” bachelor and not “economics and innovation” which is its real name. But it’s just a translation mistake.
Thank you very much
@Kenji Explains Hi Kenji can I ask you if Goldman was selective or was giving everybody a chance of doing hirevue? Are chances bigger to get hired once a candidate received a hirevue pre recorded to do or chances remain the same?
I'm really nervous but I think it's common given the reason.
If you ever had an interview question asking about excel, please respond with the question and the job role!
i just sort (ALT D S) everything when asked about getting the highest to lowest figures or vice versa in a set of data aheheh😅
Hey Kenji, I might be landing a BA job soon (I'm in my last few months of high school), and I completed this test. I haven't practiced much excel, apart from what I remember from school and searching for formulas throughout the test, so I'm wondering what level of experience this test was; is it entry level for a job or pretty good for most situations? Thanks for the test file!
Hey! If you can do this on your own you should be covered for most work situations. Very impressive as a high school student!
@@KenjiExplains thank you very much sir!
*When it comes to investing, I'm so ignorant about it, I hereby ask; What's your say or thought for anyone with over $20k looking for the best ways to make good returns off it? I will appreciate any help here on how to do this*
@Lucy Bernardi Interesting, I've always been fascinated with investing and I need to start now. Could you possibly give more information on your Investment Professional and how I can reach out to her?
No matter what I do I can't get the last formula to work. Any ideas? =IF(MONTH(F$5)
IF(MONTH(F$5)
sir i am not able to understand anything, specially formula how to apply
check out this video (the one you watched is an advanced excel test): ruclips.net/video/50X2DDPWXoY/видео.html
Hi Kenji, great video!!! Anyway can I use your worksheets on my video? I'll give credit to you, just it seems very good if I could localize this material. I also make an Excel video on my channel. Feel free to check that out
Hi Adam thank you for the comment. Unfortunately, no you cannot use my worksheets in your videos.
@@KenjiExplains no worries, thanks for your clarification.
How you have done the 3:06 thing?
format cell to Percentage.
@@RBMndzi mean dragging the cells
why im watching this if Im a cs major???
$250 course...yikes...well, at least there's a $50 discount
Hahaha yeah there are 1000s of courses on the internet that are free. You’re truly a sucker to buy that
So people are learning these things to do a job.They wanted to be a slave.Go for business if have you any kind of wisdom