Thank you Kenji for the test. I have finished the test in 7 min. In the future i want you to make a more video like this. it will help our Excel knowledge. Love you man.
This exercise is great but I thought of a better solution for Q5 which uses SUMIF: =SUMIF(B6:B16,F6,D6:D16). If Sarah was to work part-time in one department and part-time in another, then she would occur twice in the table, so you would need to sum for the two occurences. XLOOKUP would only reference the first occurence. In your example, Xlookup works fine, but only because Sarah only occurs once in the table. Also if instead you wanted to know the revenue for a department like Sales (3 occurences), then SUMIF would be better.
i learnt something new with the CTRL+H trick, but you still have the space after your sentence, so a cleaner formula would be TEXTBEFORE. In cell D6 enter =TEXTBEFORE(C6," (").
Awesome video 🤝 Here's an additional solution for your bonus question that works when you need to erase text after or before specific characters. So, you can use the same function REPLACE (CTRL+F) and use (* to delete whole text after it finds last character "( " , or if you want to delete text before certain character you can use logic *( to delete all text before last character in this case "(". Best regards, Stevan
Great video and a diverse array of fundamental Excel questions. Thank you Kenji! For the BONUS QUESTION, I'd go by the formula *=LEFT($C6,SEARCH("(",$C6)-2)* since the parentheses appear only at the end of company names in column C.
I used =LEFT(C6,FIND("(",C6)-1) in a new column for the bonus question, had no idea it was possible the other way! Will definitely be using that one going forward
@KenjiExplains, I've been watching your videos for some time now, I was able to complete all the questions without following along with the video, however I will watch now to get other ideas on how to solve. Thank you!
Hi, I'm better than I thought! For the bonus question to leave the original data intact, should someone need the stuff in parentheses later, I would have used SUBSTITUTE(). Off topic, autofill is magic at learning by example. You type in what transformations you want for the first few rows and autofill does the rest.
So an excel test I had the other week involved using LEFT to pull a code from a larger file name, using those codes for a v look up on a 2nd sheet. A pivot table for client costs, and some basic maths formula
With the bonus question: would the extra space cause any issues e.g. when sorting? My plan was to find-replace “ (” to some other symbol not used, then Text to Columns, splitting via that symbol, and only retaining the left column
For the bonus I probably would have used textsplit or text to columns. But Learning the wildcard option in find and replace it great. I can see myself using that for quick results. Thanks!
Neat trick on the bonus question. Shouldn't you add a space in front of the open parenthesis though? Otherwise if you try to do a lookup for the name, you wont get a match because of the space at the end that isnt instantly noticeable
Actually the Michael Jordan and Scottie Pippen in the Chicago Bulls, and John Lennon and Paul McCartney tandem in the Beatles winning formula indirectly infused a glimpse of what's gonna be ahead of time that works.
Hi Kenji, what if I have in my Excel only IFS function (not the IF), how should I put it then? Because with IFS, there are not enough ifs...if you understand that :D
Thanks for new formulas I've just learned usually done for corporate departmental earnings calculation and presentation. It's like having a peek into the corporate world of meticulous data accuracies. Can AI in a specific department be programmed to update or correct data and files in real time for numerous data files for for personnel reviews complete with displays and presentations to proof check any probable errors and having to update newly entered data's in real time as an assessment tools to asses AI performance and to double check also for any human errors aside from supervisorial reviews and checks in Data and files segregation by systematic orderings of entered data's pre prorammed as such like having second opinion like in medical check ups and transforming AI solutions as a human assistance partner in real time accurate information enterings for very large bulk of data files only at the same time as a tool mechanism of pre programmed data storage facilitator.
Good morning Kenji, I have a formula that use to link different sheets, but when i new information to the main sheet, the Links become dead, how do maintain the link with additional information that changes the location from original sheet. Again the main is a master list with different information, but i have to isolate a column to another sheet. Thank you in advance
Hey Kenji, i hope you are doing well, just a quick question for Q5, when i put the same formula my excel doesn't show the correct answer but it shows the last revenue at D16, is it because if have old excel or is there something else wrong?
