Today I learned that I know how to format Excel, but know nothing about Pivot tables and they are really cool! LOL! Thank you for a great video! I noticed that you created this video right before our lives changed in 2020. I am watching two years later and preparing for a test for a job that I really want. Wish me luck! xoxo
I would recommend that the very first step be to format the data first as a table. Then add totals (table design). Using the filter button for searching/filtering, one can easily use count function in the totals drop down to get counts (inclusive and exclusive using filtering) for several of the questions.
Great video - I enjoy watching people do things they are really good at, and learning from them. I'm not near your level of expertise in excel so it was interesting to hear you working through the problems out loud while also demonstrating. Excel can be daunting for those of us who don't quite grasp the extent of its power, but you make it look easy (and fun). More like this!
To those new to Excel or any other analytics tools or languages (SQL etc) for that matter - please don’t stress out. It may seem like a lot at first but you’ll get it...if you keep at it. Keep building my friends :)
YES! This was an interview for a job that required 2+ years of experience, not an entry-level position. I'd already been working with Excel for 2 years before taking this test. Not all day every day, but I did have some experience already.
I have an assessment test this coming week for a job even though I told a panel during my interview that I did not have much experience in excel. I am a nervous wreck and I don't even know if I can pass it. Any tips because I'm terrified. 😨
Depends on where you are applying. I know for sure in many Engineering and Accounting positions these are all extremely simple questions at the most basic level. Im not trying to be mean or scare anyone, Id just rather you get a reality check from some guy on the internet than at a job interview.
Loved this presentation Ann. Today I learned about pivot tables for the first time - I could have used them so often in the past if I had bothered to find out! I spent two hours practising as I was going through your video - you have such an engaging style. Now I feel much more confident for my excel assessment this week, thank you.
To get the age I think the easiest way was to use the datedif function with the today function nested inside and "y" for years and it would have returned to the year without the decimals.
pivot tables the best way to go. I'm now understanding pivot tables more after watching this video. I'm in Spreadsheet class preparing for tests . Thank You please post more videos
That age formula is too tough for me ☹️. Thank for the amazing video and it really helped me in understanding it. You really did this such a funny manner . Loved it
On the question for age ... there must be a function that simply truncates the ages to zero decimal points ... so 18.3 = 18 ...28.67 = 28 Once you do that then your pivot table gives you the answer quicky
Great video! For the 18 age question, I would have used =INT((TODAY-G2)/365.25) and that would have truncated the answer to the year without rounding and copied it down then used pivot table to get the rest of the answer.
I understand how you got 33 for the final question, but remember that 1 "Parkside" was spelled incorrectly, so the desired answer would really be 34 as the question doesn't preclude misspellings.
Technically speaking you can't even divide by 365.25 to get the years in a timespan, because it assumes that all years are smoothly 365.25 days long, and not how it actually is - with 3 years shorter and 1 longer. This can introduce a small mistake depending on what kind of year it was at the start and end of the calculation. It only makes a difference in very rare cases when the numbers sort of work out against you. Basically you're assuming that the average is 365.25, which is correct in general, but might not be in some specific situation, eg. if the span of time both starts and ends with a 366 day year and has 3 normal years in between, the average is 365.4., so then if you use 365.25, you will get an answer that is slightly off, and maybe the fact that it's slightly off makes some IF statement somehwere not work properly, etc. EDIT: So I think the rock solid approach would be to actually split days, months and years from the date and work with that.
I'd think that Index/Match would be a great candidate for the question about 8 children. You could put the formula in G116: =INDEX(E2:E115,MATCH(G117,G2:G115,0)) and then type 8 into G117.
@@Kevn_96 Hi Kevin, If you wanna do this with vlookup formula, you have to drag the "Number Of Children" column between neighborhood and race columns. Unless you drag this column as I told you, vlookup doesn't work in this case.
This is one of those few occasions imo where the "bad" solution (filters) is still better than the "better" solution (formulas). After all, what if the question writer was wrong and there were multiple people with 8 kids? Xlookup and index-match are only going to search for one value. Sounds nitpicky, but in a real world scenario this could actually be a very significant oversight.
I think the best is to do both formulas AND pivot tables. It's like driving manual first before going automatic. I would not hire someone who relies solely on pivot tables without knowing any formulas
Love the video. Thoroughly enjoyed it. Thanks for posting. The Pivot tables are a great method to answer the questions. 10 years ago, we didn't have Power Query. But Power Query would make some of the last questions very easy.
If you're worried about spaces in blanks you can select them, control F to hit replace, in find what just hit space, in replace what leave it empty. it will delete all spaces in the selected cells. For the number of kids one a quicker way would be selecting the number of kids column. Control F, 8 to find specifically 8 and search across for associating information.
Thankyou Ann, I enjoyed watching this just for fun. I was going through the same various answers in my head so that was good to see what you showed. One other thing to watch out for is leading and trailing spaces along with the text in a cell. I would just like to add that what really impressed people was that I vary rarely use the mouse. I have memorised most of the keyboard shortcuts and this enables a far greater speed. I found that I had to explain what it was that I was doing as the 'watcher' would try to follow mouse actions which were not being used. I forced myself to learn these by hiding the mouse around the back of the PC. One trick is to announce (quietly to yourself) what the shortcut is as you are typing it. eg, If you are calling a Pivot Table, just think 'ALT D P' as you type it. I taught this same method to all new staff and they loved it.
Love your videos, thank you! I often grapple with the ages and often do it not the right way... From a quick look at the blank zip code, you can infer that it is a 20019 address. The correct answer would then be that 3 people don't live in that Zip code.
Question 7: You can use the combination of Index and Match formula where Match give you the reference of adjacent row number from "No of Children" column and Index will give you the value of "Race" column from that row. =Index(A1:Z200, Match(8, no of Children column like E1:E200,0), Match("Race column name", A1:Z1, 0))
To calculate the age you could: Assuming the birthdate is in cell A1 formatted correctly as date, in cell B1 you could use =INT((TODAY()-A1)/365) and you get the current age of the person. The INT formula will round DOWN to the nearest integer - meaning that a person who is 30.93 years old would still be 30 years old
Basically the pivot table is the ultimate cheat code for people without knowledge of excel formulas or a programming language mindset. The first thing I did was remembering all the different formulas and connect them in my head only to find out that you just had to create a pivot table for all these problems. Now I’m wondering why I learned formulas to begin with? Kind of unnecessary and a waste of time
For question 5 (determining how many blank fields there are): Could you use the TRIM function to make sure any blank cells are actually blank (no spaces) and then use a pivot table to analyze the column? I'm new to working with excel but hoping to land a relevant job by the start of next year, at the very least. Thanks for the video(s)!! Massive appreciate to anyone who uses their free time to educate the world :)
One easy method is to apply an auto filter and then use the drop down to see if there are apparent blanks and include them with the actual blanks. After filtering using this method, you will see the total record count in the lower left of the screen.
So basically I need to master pivot tables. Gotcha because I am a formula girl and I would have never used a Pivot Table. Everytime I used them it never turned out right.
25:00 ish. Int instead of round. This will round down to the nearest integer. RoundDown can also be used. Due to the type of output being looked for though, int would likely be better since that is the form it is typically represented in.
There is no distinct count in pivot tables. You are referring to a power pivot via Excel data model. Completely different thing and not required to know unless you are applying for a data engineer position as its an expert feature.
I learnt Excel when I had to write a system with a report showing the loss of Alcohol in a Brewery. Many tanks and the report needed to show the concentration of alcohol (or the potential of the sugars to turn into alcohol). Used Excel to workout what the right answers would be, as a crosscheck. I once signed up for an 80 mile cycle. I decided that I would be able to walk for 8 hours easily and cycling at 10mph is almost the same as walking. As long as I ensured that I didn't finish in less that 8 hours then I'd be able to complete the journey. I used Excel to create way points so that I'd get an idea of where I should be on the course and would not go faster than 10mph. I completed the 80 miles
Excellent! I love planning my long trips in Excel as well. Scenic rest points and alternate routes in case bathrooms are needed or a cafe or museum, in the case of traveling. What role were you in for the Brewery reporting position? You noted you wrote a system, so it sounded like a coding-focused role. I'm finishing my supply chain post-grad program, but I think aside from business and operational concepts... analytics and communication will be the most needed skills to succeed day to day. Understanding data,and auditing it as well as knowing how to interpret the results. Transferable skills across industries.
Question 1 - the best way is to select Column A header.. that auto selects the entire row and then look at the count in the lower right hand of the screen. A single mouse click = answer. No formulas, no highlighting, etc.
Your answer to question 2 is incorrect. the one row with a blank zip code is still in fact part of 20019, just the field is missing in the data, but the zip can be inferred from the neighbourhood column (kenilworth courts is always 20019 in other rows). so the correct answer is 3, not 4. I'd have expected you to clean the data like this if i were the director hehe.
You went the hard way in age calculation but it was simply to take integer after date subtraction. Intv=int(Decimal Age). After that count if intv is 18.
Question for you: what tasks in Excel would you consider to be basic excel, and when does it become "intermediate"? Thanks very much! Great video and very informative!
Great question! Depends on the industry/sector for sure, and even on the specific job role. For example, I used to think pivot tables were advanced. Now, I consider them to be beginner (because they let you bypass formulas, which are harder than simply dragging and dropping).
Thanks for posting, but as someone who's used Excel on the job only a few times, I wish you would've explained things like what a pivot table is or how to access Count A.
I’m sorry Vic but if your excel knowledge isn’t the best I recommend to learn the fundamentals step by step before watching some kind of interview questions as these are too complicated for beginners. This video was perfect for intermediate level users as it showcased the real thinking process in a situation like a test. Don’t let yourself get intimated by things you didn’t learn yet! Keep improving
Why didn't you use pivot tables during your job interview? Also, thanks for the instructional video. I'm taking my excel exam on Tuesday for an entry-level data analyst and I hope that the test would be similar to this.
Question 4 : if there’s only one typo that means it’s should be the only single value, so you just have to look for the duplicate values and color them in red for instance, the non colored value will be the typo ;)
I appreciate the effort but as a beginner, I just got too frustrated especially halfway through the end. Probably would help a lot if it's just straight to the point and stated the best way and not the bad way as we (speaking for myself) are here trying to learn the right way as Excel seems intimidating enough. Thanks.
You might like finding an "Excel for beginners" tutorial on RUclips first, and then come back and look for "Excel tests" to assess your progress. Best of luck on your learning journey!! :)
I like this video for 1 reason, why waste time with formulas when you can just Pivot Table everything. From a database standpoint, I used to think why waste time on Excel when I an just write an SQL
A good reminder for me was that if specific data doesn't exist in the original data that I can add columns to create the data to use in a pivot table. Don't know enough about Power Query yet to see if there's a solution there.
For the ages: did you try the "Floor" function? Or YEAR(TODAY()-Date)-1900 and transform the field to number (because YEAR gives an answer between 1900-1999 for some reason, unless it's an option I forgot to check) And your if formula if you don't round the ages: =IF(AND(years>=18,years
Who else is watching this thinking... just when I thought I knew excel. Lol
Omg SMH
Lol same
I’m crying 😂 I have an assessment tomorrow for a job
🤣😂
@@atlienacct5868 Goodluck mate. How was it?
So basically Pivot Table is the answer to all of life's questions 😂
Lol 😂 yup! It’s good to know
When I took Excel they never taught us pivot tables, glad to see its a short cut.
This video helped me ace an excel test and get a new job! Thanks so much for making it!
I've got one tomorrow, wish me luck!
@@toivokarttunen8187 update
@@toivokarttunen8187 update?
Today I learned that I know how to format Excel, but know nothing about Pivot tables and they are really cool! LOL! Thank you for a great video! I noticed that you created this video right before our lives changed in 2020. I am watching two years later and preparing for a test for a job that I really want. Wish me luck! xoxo
I would recommend that the very first step be to format the data first as a table. Then add totals (table design). Using the filter button for searching/filtering, one can easily use count function in the totals drop down to get counts (inclusive and exclusive using filtering) for several of the questions.
Use formulas and pivot to survive or forget excel😂
Such a good way..Thanks for helping 😊
Great video - I enjoy watching people do things they are really good at, and learning from them. I'm not near your level of expertise in excel so it was interesting to hear you working through the problems out loud while also demonstrating. Excel can be daunting for those of us who don't quite grasp the extent of its power, but you make it look easy (and fun). More like this!
To those new to Excel or any other analytics tools or languages (SQL etc) for that matter - please don’t stress out. It may seem like a lot at first but you’ll get it...if you keep at it. Keep building my friends :)
YES! This was an interview for a job that required 2+ years of experience, not an entry-level position. I'd already been working with Excel for 2 years before taking this test. Not all day every day, but I did have some experience already.
I have an assessment test this coming week for a job even though I told a panel during my interview that I did not have much experience in excel. I am a nervous wreck and I don't even know if I can pass it. Any tips because I'm terrified. 😨
@@zlatte2594 This test is intermediate to advanced level. You won't get this kind of test. You will get a basic Excel test.
Depends on where you are applying. I know for sure in many Engineering and Accounting positions these are all extremely simple questions at the most basic level. Im not trying to be mean or scare anyone, Id just rather you get a reality check from some guy on the internet than at a job interview.
What about using rounddown() formula instead of round() at question no 8 and 9? This way the ages will be always rounded correctly
Didn't even know that existed! Thanks for teaching me about rounddown. :)
Loved this presentation Ann. Today I learned about pivot tables for the first time - I could have used them so often in the past if I had bothered to find out! I spent two hours practising as I was going through your video - you have such an engaging style. Now I feel much more confident for my excel assessment this week, thank you.
You might not be a master at excel, but you definitely the best Teacher 👍🙏
The INT (integer) and TRUNC functions will round the ages to the correct numbers you desire. Great video!
Thank you for posting this...its a huge help for recent graduates
To get the age I think the easiest way was to use the datedif function with the today function nested inside and "y" for years and it would have returned to the year without the decimals.
Number one thing I'd say is put the data in a table. For the "blank" cells, I might use a LEN function and check for cells where TRIM(LEN(cell))
pivot tables the best way to go. I'm now understanding pivot tables more after watching this video. I'm in Spreadsheet class preparing for tests . Thank You please post more videos
I'll have my exam next Monday, thank you for sharing, it was very useful!
Thank you for uploading this. It really helped.
I wouldn't pass this ever on the first try.
That age formula is too tough for me ☹️. Thank for the amazing video and it really helped me in understanding it. You really did this such a funny manner . Loved it
Super, I hope I will do well on my upcoming Excel test. Keep fingers crossed !:)
On the question for age ... there must be a function that simply truncates the ages to zero decimal points ... so 18.3 = 18 ...28.67 = 28 Once you do that then your pivot table gives you the answer quicky
Yes! I recently learned about =rounddown.
You can use "Group" and "insert slicer" for the pivot table to answer some of the questions faster.
Great video! For the 18 age question, I would have used =INT((TODAY-G2)/365.25) and that would have truncated the answer to the year without rounding and copied it down then used pivot table to get the rest of the answer.
I understand how you got 33 for the final question, but remember that 1 "Parkside" was spelled incorrectly, so the desired answer would really be 34 as the question doesn't preclude misspellings.
I think the misspelled cell may have suppose to been "Paradise" not "Parkside".
Thank you so much I really needed this I followed along with my own project, and I learned a lot. I'm using pivot tables all the time now!
This is really helpful, every moment of learning has it own impact in life and this tutorial has made it own in my life too.
Thank you, I will definetley walk away with some new ways to do things. And you keep me entertained!
Technically speaking you can't even divide by 365.25 to get the years in a timespan, because it assumes that all years are smoothly 365.25 days long, and not how it actually is - with 3 years shorter and 1 longer. This can introduce a small mistake depending on what kind of year it was at the start and end of the calculation. It only makes a difference in very rare cases when the numbers sort of work out against you.
Basically you're assuming that the average is 365.25, which is correct in general, but might not be in some specific situation, eg. if the span of time both starts and ends with a 366 day year and has 3 normal years in between, the average is 365.4., so then if you use 365.25, you will get an answer that is slightly off, and maybe the fact that it's slightly off makes some IF statement somehwere not work properly, etc.
EDIT: So I think the rock solid approach would be to actually split days, months and years from the date and work with that.
Yes, agreed! Thanks for thinking through the nuances.
I'd think that Index/Match would be a great candidate for the question about 8 children. You could put the formula in G116:
=INDEX(E2:E115,MATCH(G117,G2:G115,0)) and then type 8 into G117.
could you do this with Vlookup?
@@Kevn_96 Hi Kevin,
If you wanna do this with vlookup formula, you have to drag the "Number Of Children" column between neighborhood and race columns. Unless you drag this column as I told you, vlookup doesn't work in this case.
@@excelgun8915 But Xlookup would.
This is one of those few occasions imo where the "bad" solution (filters) is still better than the "better" solution (formulas). After all, what if the question writer was wrong and there were multiple people with 8 kids? Xlookup and index-match are only going to search for one value. Sounds nitpicky, but in a real world scenario this could actually be a very significant oversight.
@@jakerockznoodles exactly, noting wrong with using filter, so much faster
I think the best is to do both formulas AND pivot tables. It's like driving manual first before going automatic. I would not hire someone who relies solely on pivot tables without knowing any formulas
This was really helpful. Thank you so much!
Love the video. Thoroughly enjoyed it. Thanks for posting. The Pivot tables are a great method to answer the questions. 10 years ago, we didn't have Power Query. But Power Query would make some of the last questions very easy.
Watching this ahead of a work sample I will have to do for an analyst interview tomorrow... super helpful!!!
I think a LookUp is your best bet for question 7
the summary - Pivot Table is the best way to answer every question)))
For question 7, use conditional formatting and under the "includes text" you could put "8"
If you're worried about spaces in blanks you can select them, control F to hit replace, in find what just hit space, in replace what leave it empty. it will delete all spaces in the selected cells.
For the number of kids one a quicker way would be selecting the number of kids column. Control F, 8 to find specifically 8 and search across for associating information.
Thankyou Ann, I enjoyed watching this just for fun. I was going through the same various answers in my head so that was good to see what you showed. One other thing to watch out for is leading and trailing spaces along with the text in a cell.
I would just like to add that what really impressed people was that I vary rarely use the mouse. I have memorised most of the keyboard shortcuts and this enables a far greater speed. I found that I had to explain what it was that I was doing as the 'watcher' would try to follow mouse actions which were not being used. I forced myself to learn these by hiding the mouse around the back of the PC. One trick is to announce (quietly to yourself) what the shortcut is as you are typing it. eg, If you are calling a Pivot Table, just think 'ALT D P' as you type it. I taught this same method to all new staff and they loved it.
Thanks for the video. Commentary really helped. Never realised how much you can do with pivot tables lol
Thanks for the answer levels and the honest commentary. We want to develop an excel test to prevent future hiring catastrophies. This is so helpful.
Hope this helps! This was for a position for a Bachelor's degree + 2 years of experience. Not entry level, but fairly beginner.
Love your videos, thank you! I often grapple with the ages and often do it not the right way...
From a quick look at the blank zip code, you can infer that it is a 20019 address. The correct answer would then be that 3 people don't live in that Zip code.
Easy , I wish I could get a SQL Query test like this.
Pivot tables help a lot, but sometimes formulas give errors. Thanks for showing me another perspective
Thanks a lot for your effort, I had watched a lot of learning pivot tables but didn't really get it until now , it's really was useful
Question 7: You can use the combination of Index and Match formula where Match give you the reference of adjacent row number from "No of Children" column and Index will give you the value of "Race" column from that row.
=Index(A1:Z200, Match(8, no of Children column like E1:E200,0), Match("Race column name", A1:Z1, 0))
could you do this with Vlookup?
@@Kevn_96 Not unless you shift the "number of children" column to the left first. X lookup or just Index/Match saves you the trouble
TOTAL Hilarie Burton vibes. Love her energy. Would want a coworker like you any day 😔
This is really helpful, thanks for posting this.
To calculate the age you could:
Assuming the birthdate is in cell A1 formatted correctly as date, in cell B1 you could use =INT((TODAY()-A1)/365) and you get the current age of the person. The INT formula will round DOWN to the nearest integer - meaning that a person who is 30.93 years old would still be 30 years old
You are too cute, vibrant and sassy. All the blessings for you.
Basically the pivot table is the ultimate cheat code for people without knowledge of excel formulas or a programming language mindset. The first thing I did was remembering all the different formulas and connect them in my head only to find out that you just had to create a pivot table for all these problems. Now I’m wondering why I learned formulas to begin with? Kind of unnecessary and a waste of time
Might depend on when you learned Excel. Pivot tables weren't added until Excel 2013. I started using Excel in 2008. So I had to learn formulas, darn!
Can you show us the right path to learn excel. I'm so confused with all these videos available in the internet
For question 5 (determining how many blank fields there are):
Could you use the TRIM function to make sure any blank cells are actually blank (no spaces) and then use a pivot table to analyze the column? I'm new to working with excel but hoping to land a relevant job by the start of next year, at the very least. Thanks for the video(s)!! Massive appreciate to anyone who uses their free time to educate the world :)
One easy method is to apply an auto filter and then use the drop down to see if there are apparent blanks and include them with the actual blanks. After filtering using this method, you will see the total record count in the lower left of the screen.
I have to take an Excel test today and this makes me feel more confident. Thank you. I hope this is the kind of test I get.
Howd it go? I am taking one Wednesday
@@A3L2A3N How did yours go?
So basically I need to master pivot tables. Gotcha because I am a formula girl and I would have never used a Pivot Table.
Everytime I used them it never turned out right.
In question 8, countifs() could do with greater than 18 and less than 19 age.
I do wish you had typed in a way so that we could see what the formulas consisted of, and the way you added data to them.
25:00 ish. Int instead of round. This will round down to the nearest integer. RoundDown can also be used. Due to the type of output being looked for though, int would likely be better since that is the form it is typically represented in.
In question 8, I think you can do a countif(and()) formula, 1st criteria more than 18, 2nd less than 19.
for question number 8 , you use Datedif function to determine the ages .
- Datedif(date birth, today's date, " y" )
For the age group , you can group them in the pivot table. effectively resulting in the total quantity
8:27 - that count will also include duplicates if they exist, so you'll need to count "distinct" values, its an option in pivot tables.
There is no distinct count in pivot tables.
You are referring to a power pivot via Excel data model. Completely different thing and not required to know unless you are applying for a data engineer position as its an expert feature.
I learnt Excel when I had to write a system with a report showing the loss of Alcohol in a Brewery. Many tanks and the report needed to show the concentration of alcohol (or the potential of the sugars to turn into alcohol). Used Excel to workout what the right answers would be, as a crosscheck.
I once signed up for an 80 mile cycle. I decided that I would be able to walk for 8 hours easily and cycling at 10mph is almost the same as walking. As long as I ensured that I didn't finish in less that 8 hours then I'd be able to complete the journey. I used Excel to create way points so that I'd get an idea of where I should be on the course and would not go faster than 10mph.
I completed the 80 miles
Excellent! I love planning my long trips in Excel as well. Scenic rest points and alternate routes in case bathrooms are needed or a cafe or museum, in the case of traveling.
What role were you in for the Brewery reporting position? You noted you wrote a system, so it sounded like a coding-focused role.
I'm finishing my supply chain post-grad program, but I think aside from business and operational concepts... analytics and communication will be the most needed skills to succeed day to day. Understanding data,and auditing it as well as knowing how to interpret the results. Transferable skills across industries.
Thanks Ann, it's so helpful.
Great content, loved every minute of it.
Pivot tables are by far the excel feature responsible for the most excel errors.
Whaaat are you being sarcastic? It's so easy to make mistakes with formulas. Pivot tables are just drag-and-drop.
Question 1 : you can just select all the column by clinking in the letter above and then just take the count minus 1 for the header ;)
Best way to find out an age is the yearfrac formula!
If were to do it, ill format it to table, then I can add slicers. It works like a charm.
Question 1 - the best way is to select Column A header.. that auto selects the entire row and then look at the count in the lower right hand of the screen. A single mouse click = answer. No formulas, no highlighting, etc.
You have to remember that sometimes the computer where you will do the test, might set up the bottom visible formulas results into invisible
Your answer to question 2 is incorrect. the one row with a blank zip code is still in fact part of 20019, just the field is missing in the data, but the zip can be inferred from the neighbourhood column (kenilworth courts is always 20019 in other rows). so the correct answer is 3, not 4. I'd have expected you to clean the data like this if i were the director hehe.
"If you can't explain it to a six year old, you don't understand it yourself"
Rvery where I turn the convos about data and analytics is convoluted.
I really wanted to see the formulas....but your screen shots didn't always show..but thank you!
You can download the Excel file I used in this video. :)
Very informative and style of teaching is so cool. Impressed
Thanks didn’t tot of using pivot as fastest solution. Gotta test now wish me luck and will update you. Thanks
You went the hard way in age calculation but it was simply to take integer after date subtraction. Intv=int(Decimal Age). After that count if intv is 18.
Floor and ceiling function would help with the ages. This is where a background in discrete math is useful.
discret math? LOL. Anyway what floor and ceiling does?
Super helpful to prepare for my assessment test! Thanks :)
Pivots are cool but not always the best solution :)
So, just excel the pivot table to excel at Excel😂
moral of the story use pivot tables or formulas
lol YESSSSSSSSSSSSSSSSSSSSS
@@AnnKEmerymoral of the story check your work.
you could possibly use an xlookup for #7
Question for you: what tasks in Excel would you consider to be basic excel, and when does it become "intermediate"? Thanks very much! Great video and very informative!
Great question! Depends on the industry/sector for sure, and even on the specific job role. For example, I used to think pivot tables were advanced. Now, I consider them to be beginner (because they let you bypass formulas, which are harder than simply dragging and dropping).
Thanks for posting, but as someone who's used Excel on the job only a few times, I wish you would've explained things like what a pivot table is or how to access Count A.
I've got lots of other videos on those concepts. Hope you find them useful!
I’m sorry Vic but if your excel knowledge isn’t the best I recommend to learn the fundamentals step by step before watching some kind of interview questions as these are too complicated for beginners. This video was perfect for intermediate level users as it showcased the real thinking process in a situation like a test. Don’t let yourself get intimated by things you didn’t learn yet! Keep improving
i cant download the spreadsheet excel, can you fix the link please, there is only a picture there
Why didn't you use pivot tables during your job interview?
Also, thanks for the instructional video. I'm taking my excel exam on Tuesday for an entry-level data analyst and I hope that the test would be similar to this.
I didn't learn pivot tables in college or grad school, so I simply hadn't encountered them yet 10 years ago.
Question 4 : if there’s only one typo that means it’s should be the only single value, so you just have to look for the duplicate values and color them in red for instance, the non colored value will be the typo ;)
could you not also use the spelling feature?
I appreciate the effort but as a beginner, I just got too frustrated especially halfway through the end. Probably would help a lot if it's just straight to the point and stated the best way and not the bad way as we (speaking for myself) are here trying to learn the right way as Excel seems intimidating enough. Thanks.
You might like finding an "Excel for beginners" tutorial on RUclips first, and then come back and look for "Excel tests" to assess your progress. Best of luck on your learning journey!! :)
the data is only showing as an image? anywhere to find the actual file?
Thank you for the video. I watch your video to prepare for Excel & SQL test for analyst position
How did it go?
I wonder if used the spelling F7 from excel to find the misspelled.
I like this video for 1 reason, why waste time with formulas when you can just Pivot Table everything. From a database standpoint, I used to think why waste time on Excel when I an just write an SQL
can you share the dataset? and i dont know if its the video resolution thats affecting the visibility of the video
Indeed there's a formula for Q7. "There is one participant that has 8 children. What is their race/ethnicity? "
=INDEX(E2:E115,MATCH(8,G2:G115,0))
could you do this with Vlookup?
A good reminder for me was that if specific data doesn't exist in the original data that I can add columns to create the data to use in a pivot table. Don't know enough about Power Query yet to see if there's a solution there.
7 th question ==> index,match
you did really well
For the ages: did you try the "Floor" function?
Or YEAR(TODAY()-Date)-1900 and transform the field to number (because YEAR gives an answer between 1900-1999 for some reason, unless it's an option I forgot to check)
And your if formula if you don't round the ages: =IF(AND(years>=18,years
Would you give the full formula? I don't understand what you mean.
Pretty Cool.
😍Love it!
Thank you