A Real-Life Excel Test from a Job Interview: Can You Pass??

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.

Found this today, and have a couple inputs on using dates.

Don't divide by some value of number of days in a year. Instead, use DATE(TODAY()) to return the date as integer, or cut decimal part of today's date with INT(TODAY)).

You could also use DATEDIF function to count the number of full years: DATEDIF(DATEVALUE(Birthdate),INT(TODAY(),"Y")
Replace INT(TODAY()) with a literal date in quotes or a cell containing the date (without decimals). If the cell has a literal date instead of a date serial, convert it to date serial with DATEVALUE()

Thank you

Pivot tables help a lot, but sometimes formulas give errors. Thanks for showing me another perspective

If were to do it, ill format it to table, then I can add slicers. It works like a charm.

I think a LookUp is your best bet for question 7

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

In question 8, I think you can do a countif(and()) formula, 1st criteria more than 18, 2nd less than 19.

for the age cant you do a countif ("cell of Birthdate - today()=18) ???

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

Should be transformed into a table

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.

Could anyone tell me if this test is still somewhat indicative of Excel tests for job applicants today? I liked the video, but this test will be 12-13 years old now, so I'm not sure if it's a good example of what kind of questions I can expect (have to take an Excel test in a couple days)

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.

I think. Communication skills should be as important , there is lot of people that can even speak English , and that it’s frustrating. Don’t hired people with No English .

Thanks didn’t tot of using pivot as fastest solution. Gotta test now wish me luck and will update you. Thanks

Pivottable is excellent... as long as you don´t need to do things totally automatic. We use to have loads of pivot table with my last employer and i needed to make them automatic to extract information from them. And that turned out not to be really a very good way

What's the point of using pivot tables when you've got the filter anyway?

The INT (integer) and TRUNC functions will round the ages to the correct numbers you desire. Great video!

Not sure if anyone suggested but for question 8 you wanted to use FLOOR not ROUND which would have essentially dropped the decimal places.

With dates I would have avoided all of that though as there are so many weird rules and calculated the start and end dates of the range of date of births for an 18 year old (Using your date of 2020-01-31 so the range would have been Jan 31 2002 to Jan 30th 2003) , offhand I'll be honest I don't know how that would be done in excel automatically but I don't imagine that being too difficult. It is a bit of a cheat but it anchors the age as of that date if you hard code them assuming there isn't an easy way to calculate those points in Excel.

I come more from the SQL world and this avoids the extra column and will usually remain extremely fast on large data sets especially if the column is indexed.

Pivot tables are by far the excel feature responsible for the most excel errors.

I really wanted to see the formulas....but your screen shots didn't always show..but thank you!

test for marketers

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

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.

for question number 8 , you use Datedif function to determine the ages .

- Datedif(date birth, today's date, " y" )

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.

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

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.

Couldn't you use Floor function for age

7 th question ==> index,match

For the age group , you can group them in the pivot table. effectively resulting in the total quantity

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.

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

You're too young to be going senile i.e. the 14 or 15 count answer.

61 year old aspiring data analyst here. I am currently a teacher but after 32 years, I just don't think I can teach any more. It probably doesn't help that I'm autistic and that interacting with people is always inherently stressful. I'd rather work with data.

Loved the video. It was useful to hear your thought processes. Thank you.

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.

for the age, this worked for me and I am using Google sheets =ROUNDDOWN((TODAY()-F2)/365.25,0)

Pivots are cool but not always the best solution :)

For getting the ages we want we can simply use " =INT(YEARFRAC(D2,TODAY())) " where cell d2 holds the date of birth.

For question 7, use conditional formatting and under the "includes text" you could put "8"

I wonder if used the spelling F7 from excel to find the misspelled.

The problem is we know the tools, but not using too much with projects, then we get rusty, and they this surprised testing catching us off guard without warming up.

You are too cute, vibrant and sassy. All the blessings for you.

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

This is hilarious (the bad way)

😍Love it!

For the age from date of birth use the datedif function

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.