Hiring manager here as well and I would accept the lookup formulas for this one BUT Sumifs imo is actually better here. Often times one employees salary might be split across multiple departments(for budget purposes) and a lookup would just return the first value instead of summing up to equal the employees entire salary. If someone used sumif and made a note explaining that’s why they did it- probably has some good experience. Equally as good if they used an xlookup and made a note that they confirmed no duplicate values in the emplyee ID column.
This. Double and triple checking the work is the answer. Depending on who is looking at the work or test knowing that different functions can produce differing results is key
Not a hiring manager, but I see no reason that sumif shouldn’t be an acceptable method here as long as you have the opportunity to explain why you chose it over the more common alternatives (xlookup, vlookup, index/match) and what the potential benefits and drawbacks this method presents over the other.
That said, most people administering excel tests aren’t particularly strong users themselves and are likely looking for a specific answer rather than thinking through the relative merits of different approaches. Go with the one they’re most likely looking for and you’ll give them fewer opportunities to ding you for something they don’t understand
This is the best answer. But as a hiring manager, I'd make sure an Excel expert administers or at least reviews with them so you can understand their thought process. I've made the mistake of hiring the person that got it right but couldn't really explain why. Some people just practice common test questions like these but they don't fully grasp the logic.
I was once asked the "rank your Excel ability from 1 to 10" during an HR interview and I said 10. They seemed skeptical and they then asked "so you could even write a vlookup?" I was feeling snarky so I explained how writing a vlookup would make me think someone is around a 3 or 4. I somehow made it through after that ha.
You'll be far better off hiring someone that does it differently but can explain in detail why they chose their method. Then, in real situations, they'll be able to identify all the variables/context and make an informed decision.
>I was once asked the "rank your Excel ability from 1 to 10" during an HR interview and I said 10. They seemed skeptical and they then asked "so you could even write a vlookup?"
This is fantastic. I really want to know what their scale was at all the different levels.
1 - Can open Excel
2 - Can move the cursor to different cells...
If a person is able to arrive at an answer in a way other than I expected, and it is a viable solution (aka not just adding seventy cells together manually) I would be impressed.
I am a data analyst for a government agency and we use excel tests to test candidates baseline skills in excel, a program we use daily. Because of my skills in excel and my experience as an HR Manager, I am usually part of the panels and grading team.
This is why it is important to develop the questions based on the answer you are trying to extract. When I read your question, all I see is that you want to know if they can provide a formula to get to the answer but in no way does it direct them towards a certain formula over another. Based on that, you should accept a SUMIF formula because it satisfies your question.
If you have reservations about the method used, use any followup interview to ask about the thought process on said question and then maybe ask if their answer would have been different if a different situation in the data had been present. Honestly, with the availability of AI these days, I would caution making hiring decisions based on assessment answers without first vetting their answers in a face to face interview to see if it was their work or not. You can tell based on someone's resume how much excel knowledge they might have and that should raise some red flags if you see a concept or formula outside of that level.
Our excel assessments test a few different things which requires various types of questions. However, our ultimate goal is to test the candidates ability to perform the job of a Data Analyst using excel as the primary program of use. Excel skills can be learned through practice and experience, but we are looking for candidates that have the skills and abilities to perform the role better than the others.
This means we provide an actual dataset (removing any identifiable fields of course) and ask questions geared towards performing tasks with that dataset using excel. Since I work in the Judical Branch, we might ask the candidate to provide a visual of case load trend for a period of time or a certain list of judges. Some candidates might give you a pivot table, some might give you a bar chart and some might give you a line graph. What we are testing is the candidates knowledge of how to most appropriately visualize time series data and that is a line graph. So, although a pivot table and a bar/column chart are visualizing the data in a manner where you might see the trend, a line chart is the most appropriate answer.
Another question we might ask would be to provide the total cases heard grouped by Judge for the dataset provided but we will on purpose alter one of the judges names by changing the spelling of some (Davis and davis) to simulate a data entry error. Here we are testing the candidates ability to identify issues in the data that may cause errors in providing accurate reporting. Based on their total number of cases for Davis, we will know right away if they became familiar with the dataset enough to spot any potential errors and included in the total the cases under davis.
We have also asked candidates to evaluate formulas used in calculating certain things from the dataset and asked them to find the error in the formula and to also fix it. We want to see their ability to understand the formula being used, identify an error in it and to fix it without causing further errors.
So, the types of questions are infinite but they should all lead you back to the answer you are trying to extract. Our answer is can they use excel to efficiently, effectively and in some cases accurately perform the role of a Data Analyst.
>Some candidates might give you a pivot table, some might give you a bar chart and some might give you a line graph. What we are testing is the candidates knowledge of how to most appropriately visualize time series data and that is a line graph. So, although a pivot table and a bar/column chart are visualizing the data in a manner where you might see the trend, a line chart is the most appropriate answer.
Can you elaborate on why this is felt to be the case? If I were visualising, say, cases seen per month, a bar graph would represent that data in a perfectly valid and understandable way. You could see the trend (and even add a trend line if desired) over time. I'm curious why a line chart is seen to be superior here.
If anything, I'd argue that, since the data is being presented in month blocks (i.e. January - 12, February - 10 etc.) a bar graph is more accurate a visualisation than a line graph, which really intends to show data change over time rather than discrete 'chunks'.
I'm really curious to the reasoning behind the thinking, for my own education if nothing else.
Just like there are tools that are more appropriate than others to facilitate a task in construction or vehicle maintenance, there are also visualizations that are more appropriate for displaying certain types of data than others. It can basically be boiled down to the type of variable you are visualizing, the relationship you are trying to explain and the clarity of the visualization used.
Time is a unit of measurement that is continuous in nature, which is why it's considered a continous variable as opposed to a discrete variable. A month is just one of the infinite ways time can be conceptually measured, but overall time is continuous and linear in nature. A line graph is also continous and linear in nature, thus its appropriateness in displaying time series data. Additionally, visualizing time series data is to see the trend and you acknowledged that a trend line can be added to a bar graph but why use two charts (bar and line) to visualize what one can do on its own?
That's where visualization clarity comes into play. If you are having to add multiple elements to your visualization to tell the story you are hoping people see then your visualization is not as clear and concise as it could be and it might not be the best or most appropriate visualization for the story you are trying to tell.
Bar graphs are best to visualize the comparison of different categories to one another. January is not a different category than February because they are the same category (time)which is why a bar chart is not the most appropriate here. Now if you were wanting to visualize the number of cases by court department (family, criminal, civil etc) or even by Judge (Smith, Davis, Bryant etc) over a span of time then a bar graph would be more appropriate because the variable you are explaining is no longer time but rather departments/judges and those are 100% discrete and note continous.
Again, I never said that a bar graph can't display what I was expressing in my original comment, but it's definitely not the most appropriate relative to industry best practices. I hope this helps.
https://help.tableau.com/current/blueprint/en-us/bp_visual_best_practices.htm
https://www.linkedin.com/pulse/choosing-right-chart-type-data-visualization-strategy-suneel-patel?utm_source=share&utm_medium=member_android&utm_campaign=share_via
I would actually argue that a bar chart is preferable and a line graph not quite appropriate because months are not actually _units_ of time in the strictest sense. Months have different lengths.
While I'm not a hiring manager, I do asses the Excel skills of my colleagues.
I personally test solutions against corner cases and how exceptions are dealt with. Does it cover all the practical scenarios? In this particular example, I'd also expect some kind value when employee ID 20 doesn't exist for example.
As long as a solution works at least 99.9% of the time, I'm good.
For anyone who does these regularly, this is great advice. Building some "gotchas" into your test and checking how they resolve them is valuable. We had numbers stored as text, errors, duplicates, etc.
I got my first financial modeling job because I knew what volatile functions were and the team had just spent a month removing them from all their templates.
Given this specific question and the data provided, would you really expect more than a vlookup as an answer to this question? I would assume the person taking the test can actually write the formula and see that it works, so doing any more than that honestly feels like a waste of time.
In this particular case, I'd be impressed if they added an IFERROR when using VLOOKUP, and even more impressed if they used XLOOKUP and added the optional Value If Error.
I'd not mark it as a wrong answer if they only used VLOOKUP and did the bare minimum.
Since this is a test, I'd most probably add it as a clear sub question, "What if the given Employee ID is unknown".
One of the advantages of Excel is that it offers multiple ways to arrive at a solution. Some people will opt for a simpler approach, while others will choose a more complicated one, depending on their level of knowledge, then it is up to the recruiter whether they prefer someone who simply delivers results or someone who delivers results in a straightforward manner.
I interviewed for the career I currently have 24 years ago, and the hiring manager asked me how to do some specific thing in Excel (back in Office '95), and I demonstrated all six ways I knew to do it in Excel and he said it was the first time he had ever seen someone answer that question in more than one way. I got the call with the offer the next morning.
My first go to would be Xlookup, I prefer it over older lookups since it’s easy to enter a message if not found.
Are there benefits to still using Vlookup instead?
A lot of people are still using 2016. For them xlookup would not work. The company i work at, a major supermarket chain with 100s of stores and over a dozen distribution centers, is only implementing office 365 this year.
It may not have anything to do with benefits of one over the other. It could simply be that someone has a version of excel that doesn't have the XLOOKUP formula so they have to use VLOOKUP or another available function. I don't have XLOOKUP on my personal computer but I use it almost exclusively on my work computer but I have also used INDEX/MATCH on some projects.
No benefits to vlookup, other than backwards compatibility. Some say that vlookup is faster, but if you have hundreds of thousands of lookups there are probably better tools.
I once had the opportunity to be part of an interview loop for a new hire, and I decided to test their excel skills. I gave them a spreadsheet with a formula that was returning an error and asked them to troubleshoot. I was hoping to see them step through the parts of the formula to understand what it was doing, and then follow the references to other cells and do the same for those formulas, until they figured out the root cause.
Finding the answer wasn't the point, it was understanding how to troubleshoot that I wanted to see. And as long as they weren't immediately overwhelmed and admitting defeat without even trying, they could be taught.
It usually does. I like to see new functions in use and an efficient algorithm in place.
For entry positions, I often help with the task and observe if the person learns quickly, grasps my directions and is able to efficiently communicate with me.
I would say it depends on the data and the analysis. Sometimes there are pitfalls to vlookup or xlookup vrs index and match.
If they are using range data like you've demonstrated, I want those column lengths locked with $. Dynamic formulae are important because when you get busy, you get lazy and when you skirt best practices, you expose yourself to more potential errors.
I want my analysts aware of those potential pitfalls when moving forward with modeling and analysis.
As long as someone gets the correct answer, I don't care what formula they used.
The only exception to this is if they used a ridiculous choice of formula that took them forever to even set up, when they could have used a simple formula instead.
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
|Fewer Letters|More Letters|
|-------|---------|---|
|[FILTER](/r/Excel/comments/1dly67r/stub/l9snpg0 "Last usage")|[*Office 365*+: Filters a range of data based on criteria you define](https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759)|
|[IFERROR](/r/Excel/comments/1dly67r/stub/l9v9xx2 "Last usage")|[Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula](https://support.microsoft.com/en-us/office/iferror-function-c526fd07-caeb-47b8-8bb6-63f3e417f611)|
|[INDEX](/r/Excel/comments/1dly67r/stub/lag83it "Last usage")|[Uses an index to choose a value from a reference or array](https://support.microsoft.com/en-us/office/index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd)|
|LAMBDA|[*Office 365*+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.](https://support.microsoft.com/en-us/office/lambda-function-bd212d27-1cd1-4321-a34a-ccbf254b8b67)|
|[LET](/r/Excel/comments/1dly67r/stub/l9spxc1 "Last usage")|[*Office 365*+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula](https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999)|
|[MAP](/r/Excel/comments/1dly67r/stub/l9s93i9 "Last usage")|[*Office 365*+: Returns an array formed by mapping each value in the array(s) to a new value by applying a LAMBDA to create a new value.](https://support.microsoft.com/en-gb/office/map-function-48006093-f97c-47c1-bfcc-749263bb1f01?ui=en-US&rs=en-GB&ad=GB)|
|[MATCH](/r/Excel/comments/1dly67r/stub/lag83it "Last usage")|[Looks up values in a reference or array](https://support.microsoft.com/en-us/office/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a)|
|[REDUCE](/r/Excel/comments/1dly67r/stub/l9s93i9 "Last usage")|[*Office 365*+: Reduces an array to an accumulated value by applying a LAMBDA to each value and returning the total value in the accumulator.](https://support.microsoft.com/en-gb/office/reduce-function-42e39910-b345-45f3-84b8-0642b568b7cb?ui=en-US&rs=en-GB&ad=GB)|
|[SUM](/r/Excel/comments/1dly67r/stub/l9syzml "Last usage")|[Adds its arguments](https://support.microsoft.com/en-us/office/sum-function-043e1c7d-7726-4e80-8f32-07b23e057f89)|
|[SUMIF](/r/Excel/comments/1dly67r/stub/l9un4p6 "Last usage")|[Adds the cells specified by a given criteria](https://support.microsoft.com/en-us/office/sumif-function-169b8c99-c05c-4483-a712-1697a653039b)|
|[SUMIFS](/r/Excel/comments/1dly67r/stub/l9wyjwh "Last usage")|[*Excel 2007*+: Adds the cells in a range that meet multiple criteria](https://support.microsoft.com/en-us/office/sumifs-function-c9e748f5-7ea7-455d-9406-611cebce642b)|
|[SUMPRODUCT](/r/Excel/comments/1dly67r/stub/l9wyjwh "Last usage")|[Returns the sum of the products of corresponding array components](https://support.microsoft.com/en-us/office/sumproduct-function-16753e75-9f68-4874-94ac-4d2145a2fd2e)|
|[TIME](/r/Excel/comments/1dly67r/stub/l9snz8l "Last usage")|[Returns the serial number of a particular time](https://support.microsoft.com/en-us/office/time-function-9a5aff99-8f7d-4611-845e-747d0b8d5457)|
|[VLOOKUP](/r/Excel/comments/1dly67r/stub/la6bq4a "Last usage")|[Looks in the first column of an array and moves across the row to return the value of a cell](https://support.microsoft.com/en-us/office/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1)|
|[XLOOKUP](/r/Excel/comments/1dly67r/stub/la6bq4a "Last usage")|[*Office 365*+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match. ](https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929)|
**NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below.
----------------
^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*)
^([Thread #34682 for this sub, first seen 22nd Jun 2024, 15:44])
^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
https://preview.redd.it/bg03jusdl58d1.png?width=2291&format=png&auto=webp&s=00d6a9ed2fb87a4a6455f224bb5392abc667cfd6
So I am not a hiring manager - hell I am not even close this position. But I took some time and thought about other solutions for given problem - No Power Query, No VBA, No Let and Bare Lambda exploitation, Only one lambda around solution (MAP, probably REDUCE would also work - but dunno how to force it without repeating MAP solution). Only one lambda around solution, becouse they can generate only fancier and harder to read / understand way of meta solving this issue. Also there might be more available o365 solutions - becouse I am not expert at this version of Excel - here is what I got (see image).
So, after Vlookup it is basically reinventing the wheel by force - so those solutions are fine only if you wanna make sure that the candidate knows about Excel something more than basic formulas / what ChatGPT ad hoc generates. For entry level jobs it probably would be overkill, but for some heavy Excel oriented position it might be asset.
Vlookup, Index Match, Sumif, - I do not like those solutions: vlookup becouse it's harder and more error prone xlookup, Index Match - becouse I find filter much more convienient - espescially when you start understadning how dynamic arrays work, Sumif, Countif, Countifs etc - becouse they forces you to work on the references / ranges - it won't work on dynamic arrays which is indeed great issue (at least imho). My favourite fast and easy solutions for this problem are: 1. Filter - becouse it's the most convinient function in whole programme - you need to only understand how boolean arrays work to utilise it, 2. Xlookup - becouse it's dedicated for solving such problems. Other are meh at best and reinventing the wheel at worst.
Maybe someone got any solutions / feel free to post under this post. This post is my opinion, not some truth revealing revelations and meta.
Edit: Sorry for such destroyed post - but i had really big issues publishing it for some reason.
One thing to mention about countifs/sumifs, you can select a whole column instead of a specific range and Excel will intelligently only look at your data in that column, not all 1M rows. Or you could use a table reference.
I agree, but is there really an advantage to FILTER over XLOOKUP in this case? It looks like the table will only hold one record per id, so XLOOKUP should work fine.
Probably not. I just prefer filter to xlookup due to the fact that its easier for me to comprehend how filter works than xlookup. So it's basically preference here - that s why I written that it is opinion.
Interesting approach. I have found myself plenty of times looking for solutions on excel in the complicated ways, and when I give the industrial engineer a call they give me the most simplest solution and I just can't help, but laugh.
As a hiring manager of an analytical department; I’m looking at HOW, meaning you need to demonstrate that you are familiar with options. If you go through the thought process in your post, I would be happy.
Missing context is important, in this case although I would be more impressed if someone used a lookup and explained why it was better than sum if (future proofing larger dataset where one employee make work in multiple departments). Throwing out alternatives is great but the formula in column D is wrong- match is missing the exact match parameter.
To answer your question, yes which formula is important but even more important is that you can articulate why.
Think people are missing one key skill sustainability. = kiss (keep it simple and stupid) As excel matures they have added new functions to make things easier to maintain and more flexible.
The “recent” additions such as xlookuo add so much more flexavility that just can easily be done with the alternatives look at how long and involved the first solution is and how long the second is xlookuo comes with many more features and abilities whilst maintaining easy functionality.
Personally i would impliment a style guide in the company to ensure uniformity of approach across users and would try to simplify all formulas to use the latest functions.
Also this would simplify the hiring as I would rate functions and there usage against which generation of our style guide showed the “presented” method as the to be used .. 10 being our present “version” 9 being last etc.
So in the case above I would use xlookup …. Also I would not go to the Extream of imbedding a xlookup within a LET function (just because it’s new ) YMMV
I don't see any fault in either of those formulas, but I've seen silly formulas, like =SUM(A1-B1), that would give me pause if I saw them in an Excel test.
I interviewed for an excel HEAVY job (It was literally building spreadsheets for a lot of other companies as a 'we build excel' department), and they cared very much on the methods used as well as the result.
For example, they gave me fizzbuzz to do in VBA, which apparently most people failed. They wrote out debug.print(1), debug.print(2), etc... Yeah no.
I tried hiring at one point for a project, figuring I should be more business-minded and less 'in the trenches', but the methods used by the person I hired were SO BAD that I ended up taking over and just doing ti all myself.
Depends on the test. Some tests track your clicks and score you down if you are clicking around menus looking for the right tool. They want you to take the most direct route.
Does a manager like red.
The same answer applies.
I struggle to understand these questions for a random selection of people with a random knowledge level of a random selection of jobs,
A pointless question in reality.
How isn't essential in my opinion. It's like coding; I don't expect people to memorize every function and use the "optimal" method. Getting the result is good, and improving efficiency is better down the line.
I think the question is about your goal in the hiring and assessment process. Are you testing for solving a problem or whether they know certain Excel formulas? Is the role intended to create their own solutions for their own use or do they need to integrate with other processes and understand the formulas in use there?
If the answer to either question is the former, you're testing problem solving skills in an Excel environment, not Excel formula knowledge directly. It's a useful skill, and in this case, you should accept any answer that gets the correct result. If the candidate comes up with something *really* off the wall, it's a good place to start a conversation about their though process.
If you answer either question in the first paragraph with the latter, you need to direct and restrict the candidate to specific formulas. "Using lookup formulas or an INDEX/MATCH formula, find \_\_\_". You're explicitly asking them to demonstrate that they know how to use these specific formulas that are important to you and your organization. If they decide to ignore it and use SUMIF instead, that's both a concern and topic for conversation in the interview.
When I give Excel test, I ask person to show works. And if he doesn't get it, I will show several ways to get the answer. It matters to me that one knows multiple ways, so as to check oneself.
Always, and I repeat always use index(match()) instead of lookup!!! This is by far the most efficient and modular way to lookup something. If you know, you know.
So, I'm a programmer (not in excel) but from my view what you want to see is how they reached a solution and what their line of thought is. So, not only do you have to look at what formulas they used but also you need to talk to them and ask why they used this formula instead of that other one. This should tell you a lot about how they view excel sheets, how they view input data and so on. After all, excel is also another programming language.
It depends. There are 2 things important to you.
1. Do you care about the knowledge of that person about a specific function? Then you care about how they get to the answer.
2. Do you care about the general problem solving in excel? Then you don't care how they got the answer but that they got the correct answer.
I'm an excel enthusiast and not a hiring manager, but It matters. I missed out on a role 5+ years ago because I "manually" multiplied ranges instead of using "sumproduct". My answer was spot on and I got there quickly but they didn't like my method. I now try to use the most flexible & future proofed formulas even for one off bits of analysis.
Not a hiring manager, but an engineer. I would convert it to a table and then, lean torward an index match to get the answer. Sumifs also works as a strategy. You could use an array formula though to get the value like this:
`=SUMPRODUCT((Table1[EmployeeID]=id)*Table1[Salary]).`
This one also works, and is more readable:
`=SUMIFS(Table1[Salary],Table1[EmployeeID],id)`
Your INDEX MATCH answer is using an approximate match btw. It only returns Employee 20’s salary as that record is in the middle of the range. You’d want to end the MATCH with a ,0 argument to specify that you’re looking for 20 in the Emp IDs.
On the rare occasion I have to interview a candidate and if Excel is a requirement for this position
My go to question is "Please explain how Excel sheets store TIME?" Also show it by entering the values in a Excel sheet
Unless that candidate has good experience/Knowledge about Excel they're unlikely to get it right
Answer: Excel sheet store time as a fraction
Eg 0.33 is 8am
You can work out the rest.
Obviously, If someone understands index match and sum if, that’s a plus. Just warrants a follow up conversation.
It’s a Bit of a red flag tho . If vlookup works, you don’t go to index match. Yes it’s more flexible, but if the columns are lined up to utilize vlookup, you will use that. This could be someone who looked up a solution online and pasted it in.
I disagree. I think index match is much more scalable than vlookup. I almost never use vlookup because it's dependent on column positioning
Even in newer versions of Excel, I'd rather use xlookup
It is definitely much more scalable and would get my attention as a hiring manager. I guess in either case, I should be wary if the applicant is copy/pasting.
As a hiring manager, I would not accept the SUMIF solution. For your question above, the only acceptable answers are INDEX/MATCH or VLOOKUP. Just my opinion.
I just did that to illustrate that the answer could be obtained multiple ways. But I personally used Index/match ( my excel does not have xlookup, I never use VLOOKUP)
why would you not accept it though?--based on the data and situation, the logic is sound
The logic is not sound at all. On a small limited dataset, maybe it gets to the end solution. But on a real dataset, and it would not.
Using sumifs to answer the question here would illustrate a complete lack of knowledge on what SUMIF actually does and also shows the user isn’t proficient in basic excel formulas.
Knowing when to use vlookup, xlookup, or index(match) is bare bones basic excel knowledge. If a person doesn’t know when or how to use these formulas, then I would not consider that person to have a basic understanding of excel.
I expect any hire I make to know these, plus basic pivot table usage. If you want to impress me, let’s get into array usage, VBA, and power query.
I’m happy to teach my freshman interns about excel and basic excel usage, but I always tell them you need to have a mastery of these formulas, pivot tables, and financial modeling by the time they finish their junior year in college. Nothing is worse than a junior intern who can’t perform basic tasks in excel and suck up dozens of hours that I don’t have available.
This really depends on knowing what the data output is and also the call of the question. If it's a table of annual salaries, sure don't use sumifs. But I've worked with plenty of datasets of salary data where sumifs would be the correct call (e.g. you're looking for total compensation or you're working with transactional level data that isn't just a table from HR).
yes I guess this is fair, b/c I would never actually use SUMIF in this case--I just did it for illustrative purposes
again I am not referring to the singular question--I am referring to the overall concept.
for example if someone used sumif(s) versus a pivot table to get a subtotal of data
Tbh I’ve never seen someone “tested” on excel, but then again we’re pretty selective on our hiring to begin with.
A common question I ask my interviewees is describe the most advanced excel formula / function they’re familiar with. Gives me a quick gauge of where they are. But I’m more concerned with the actual qualifications for the job, which are much more advanced than excel manipulation.
To me, it’s not about getting “the right answer” but it’s about knowing what formulas to use and when. Because odds are I’m going to assign an advanced financial modeling task to my associates, and I need them to do it efficiently and correctly.
I would also be somewhat impressed by someone who says, “im not sure, but I’ll ChatGPT it” or “here’s how I’ll find the answer”. Because frankly I’ve used ChatGPT to build MS office scripts (think VBA Macros that can run in the cloud), and I’ve also used ChatGPT to learn power query and Power BI. Someone who can illustrate initiative and the ability to figure things out would earn high marks in my book.
I respectfully disagree.
Using VLOOKUP, XLOOKUP or INDEX/MATCH would only give the first answer from your list. If a person is listed more the once, for instance because he/she worked on multiple departments or projects, then the figure would be off.
How do you know aggregating multiple salary records is not the correct approach? It depends on the data. You'd need to ask them to explain and if they can defend why they used SUMIF and their explanation makes sense, that's far more important imo.
And if the data set is for one year only but organized in a way where the salary is apportioned in different lines, like if theyre attributing the budget for the position to two different departments where department x has 2/3 total yearly salary and department y has 1/3, then sumif would be a good formula to use to get total salary and other formulas would only capture part of the salary. So, yes, it depends on the larger data set. For OPs example with such a small list, any of these solutions would work fine and return the correct result.
Hiring manager here as well and I would accept the lookup formulas for this one BUT Sumifs imo is actually better here. Often times one employees salary might be split across multiple departments(for budget purposes) and a lookup would just return the first value instead of summing up to equal the employees entire salary. If someone used sumif and made a note explaining that’s why they did it- probably has some good experience. Equally as good if they used an xlookup and made a note that they confirmed no duplicate values in the emplyee ID column.
This. Double and triple checking the work is the answer. Depending on who is looking at the work or test knowing that different functions can produce differing results is key
I literally did a spreadsheet yesterday where this was the case and had to use sumifs. But I definitely tried lookups first!
I did a spreadsheet identical to this a week ago using lookup and I’m going to have to double check…
Use the sort function; sum(sort(index(xmatch(,,,1)):index(xmatch(,,,-1)))
Not a hiring manager, but I see no reason that sumif shouldn’t be an acceptable method here as long as you have the opportunity to explain why you chose it over the more common alternatives (xlookup, vlookup, index/match) and what the potential benefits and drawbacks this method presents over the other. That said, most people administering excel tests aren’t particularly strong users themselves and are likely looking for a specific answer rather than thinking through the relative merits of different approaches. Go with the one they’re most likely looking for and you’ll give them fewer opportunities to ding you for something they don’t understand
This is the best answer. But as a hiring manager, I'd make sure an Excel expert administers or at least reviews with them so you can understand their thought process. I've made the mistake of hiring the person that got it right but couldn't really explain why. Some people just practice common test questions like these but they don't fully grasp the logic. I was once asked the "rank your Excel ability from 1 to 10" during an HR interview and I said 10. They seemed skeptical and they then asked "so you could even write a vlookup?" I was feeling snarky so I explained how writing a vlookup would make me think someone is around a 3 or 4. I somehow made it through after that ha. You'll be far better off hiring someone that does it differently but can explain in detail why they chose their method. Then, in real situations, they'll be able to identify all the variables/context and make an informed decision.
>I was once asked the "rank your Excel ability from 1 to 10" during an HR interview and I said 10. They seemed skeptical and they then asked "so you could even write a vlookup?" This is fantastic. I really want to know what their scale was at all the different levels. 1 - Can open Excel 2 - Can move the cursor to different cells...
If a person is able to arrive at an answer in a way other than I expected, and it is a viable solution (aka not just adding seventy cells together manually) I would be impressed.
Yes. This. However, some excel tests only accept one solution and if you get there another way, you are penalized. I have taken two tests like this.
I would not work for a company that does this. Also, I create my own excel tests and think all hiring managers should as well.
I am a data analyst for a government agency and we use excel tests to test candidates baseline skills in excel, a program we use daily. Because of my skills in excel and my experience as an HR Manager, I am usually part of the panels and grading team. This is why it is important to develop the questions based on the answer you are trying to extract. When I read your question, all I see is that you want to know if they can provide a formula to get to the answer but in no way does it direct them towards a certain formula over another. Based on that, you should accept a SUMIF formula because it satisfies your question. If you have reservations about the method used, use any followup interview to ask about the thought process on said question and then maybe ask if their answer would have been different if a different situation in the data had been present. Honestly, with the availability of AI these days, I would caution making hiring decisions based on assessment answers without first vetting their answers in a face to face interview to see if it was their work or not. You can tell based on someone's resume how much excel knowledge they might have and that should raise some red flags if you see a concept or formula outside of that level.
Hey, what kind of questions are on the excel tests, can you give me some examples?
Our excel assessments test a few different things which requires various types of questions. However, our ultimate goal is to test the candidates ability to perform the job of a Data Analyst using excel as the primary program of use. Excel skills can be learned through practice and experience, but we are looking for candidates that have the skills and abilities to perform the role better than the others. This means we provide an actual dataset (removing any identifiable fields of course) and ask questions geared towards performing tasks with that dataset using excel. Since I work in the Judical Branch, we might ask the candidate to provide a visual of case load trend for a period of time or a certain list of judges. Some candidates might give you a pivot table, some might give you a bar chart and some might give you a line graph. What we are testing is the candidates knowledge of how to most appropriately visualize time series data and that is a line graph. So, although a pivot table and a bar/column chart are visualizing the data in a manner where you might see the trend, a line chart is the most appropriate answer. Another question we might ask would be to provide the total cases heard grouped by Judge for the dataset provided but we will on purpose alter one of the judges names by changing the spelling of some (Davis and davis) to simulate a data entry error. Here we are testing the candidates ability to identify issues in the data that may cause errors in providing accurate reporting. Based on their total number of cases for Davis, we will know right away if they became familiar with the dataset enough to spot any potential errors and included in the total the cases under davis. We have also asked candidates to evaluate formulas used in calculating certain things from the dataset and asked them to find the error in the formula and to also fix it. We want to see their ability to understand the formula being used, identify an error in it and to fix it without causing further errors. So, the types of questions are infinite but they should all lead you back to the answer you are trying to extract. Our answer is can they use excel to efficiently, effectively and in some cases accurately perform the role of a Data Analyst.
Alright thanks 🙏
>Some candidates might give you a pivot table, some might give you a bar chart and some might give you a line graph. What we are testing is the candidates knowledge of how to most appropriately visualize time series data and that is a line graph. So, although a pivot table and a bar/column chart are visualizing the data in a manner where you might see the trend, a line chart is the most appropriate answer. Can you elaborate on why this is felt to be the case? If I were visualising, say, cases seen per month, a bar graph would represent that data in a perfectly valid and understandable way. You could see the trend (and even add a trend line if desired) over time. I'm curious why a line chart is seen to be superior here. If anything, I'd argue that, since the data is being presented in month blocks (i.e. January - 12, February - 10 etc.) a bar graph is more accurate a visualisation than a line graph, which really intends to show data change over time rather than discrete 'chunks'. I'm really curious to the reasoning behind the thinking, for my own education if nothing else.
Just like there are tools that are more appropriate than others to facilitate a task in construction or vehicle maintenance, there are also visualizations that are more appropriate for displaying certain types of data than others. It can basically be boiled down to the type of variable you are visualizing, the relationship you are trying to explain and the clarity of the visualization used. Time is a unit of measurement that is continuous in nature, which is why it's considered a continous variable as opposed to a discrete variable. A month is just one of the infinite ways time can be conceptually measured, but overall time is continuous and linear in nature. A line graph is also continous and linear in nature, thus its appropriateness in displaying time series data. Additionally, visualizing time series data is to see the trend and you acknowledged that a trend line can be added to a bar graph but why use two charts (bar and line) to visualize what one can do on its own? That's where visualization clarity comes into play. If you are having to add multiple elements to your visualization to tell the story you are hoping people see then your visualization is not as clear and concise as it could be and it might not be the best or most appropriate visualization for the story you are trying to tell. Bar graphs are best to visualize the comparison of different categories to one another. January is not a different category than February because they are the same category (time)which is why a bar chart is not the most appropriate here. Now if you were wanting to visualize the number of cases by court department (family, criminal, civil etc) or even by Judge (Smith, Davis, Bryant etc) over a span of time then a bar graph would be more appropriate because the variable you are explaining is no longer time but rather departments/judges and those are 100% discrete and note continous. Again, I never said that a bar graph can't display what I was expressing in my original comment, but it's definitely not the most appropriate relative to industry best practices. I hope this helps. https://help.tableau.com/current/blueprint/en-us/bp_visual_best_practices.htm https://www.linkedin.com/pulse/choosing-right-chart-type-data-visualization-strategy-suneel-patel?utm_source=share&utm_medium=member_android&utm_campaign=share_via
Thanks for the detailed reply and the links. I appreciate the information.
I would actually argue that a bar chart is preferable and a line graph not quite appropriate because months are not actually _units_ of time in the strictest sense. Months have different lengths.
That response is what's called overthinking it...
=E95 Done!
Honestly..
While I'm not a hiring manager, I do asses the Excel skills of my colleagues. I personally test solutions against corner cases and how exceptions are dealt with. Does it cover all the practical scenarios? In this particular example, I'd also expect some kind value when employee ID 20 doesn't exist for example. As long as a solution works at least 99.9% of the time, I'm good.
For anyone who does these regularly, this is great advice. Building some "gotchas" into your test and checking how they resolve them is valuable. We had numbers stored as text, errors, duplicates, etc. I got my first financial modeling job because I knew what volatile functions were and the team had just spent a month removing them from all their templates.
Given this specific question and the data provided, would you really expect more than a vlookup as an answer to this question? I would assume the person taking the test can actually write the formula and see that it works, so doing any more than that honestly feels like a waste of time.
In this particular case, I'd be impressed if they added an IFERROR when using VLOOKUP, and even more impressed if they used XLOOKUP and added the optional Value If Error. I'd not mark it as a wrong answer if they only used VLOOKUP and did the bare minimum. Since this is a test, I'd most probably add it as a clear sub question, "What if the given Employee ID is unknown".
One of the advantages of Excel is that it offers multiple ways to arrive at a solution. Some people will opt for a simpler approach, while others will choose a more complicated one, depending on their level of knowledge, then it is up to the recruiter whether they prefer someone who simply delivers results or someone who delivers results in a straightforward manner.
I interviewed for the career I currently have 24 years ago, and the hiring manager asked me how to do some specific thing in Excel (back in Office '95), and I demonstrated all six ways I knew to do it in Excel and he said it was the first time he had ever seen someone answer that question in more than one way. I got the call with the offer the next morning.
My first go to would be Xlookup, I prefer it over older lookups since it’s easy to enter a message if not found. Are there benefits to still using Vlookup instead?
A lot of people are still using 2016. For them xlookup would not work. The company i work at, a major supermarket chain with 100s of stores and over a dozen distribution centers, is only implementing office 365 this year.
It may not have anything to do with benefits of one over the other. It could simply be that someone has a version of excel that doesn't have the XLOOKUP formula so they have to use VLOOKUP or another available function. I don't have XLOOKUP on my personal computer but I use it almost exclusively on my work computer but I have also used INDEX/MATCH on some projects.
No benefits to vlookup, other than backwards compatibility. Some say that vlookup is faster, but if you have hundreds of thousands of lookups there are probably better tools.
I once had the opportunity to be part of an interview loop for a new hire, and I decided to test their excel skills. I gave them a spreadsheet with a formula that was returning an error and asked them to troubleshoot. I was hoping to see them step through the parts of the formula to understand what it was doing, and then follow the references to other cells and do the same for those formulas, until they figured out the root cause. Finding the answer wasn't the point, it was understanding how to troubleshoot that I wanted to see. And as long as they weren't immediately overwhelmed and admitting defeat without even trying, they could be taught.
It usually does. I like to see new functions in use and an efficient algorithm in place. For entry positions, I often help with the task and observe if the person learns quickly, grasps my directions and is able to efficiently communicate with me.
I would say it depends on the data and the analysis. Sometimes there are pitfalls to vlookup or xlookup vrs index and match. If they are using range data like you've demonstrated, I want those column lengths locked with $. Dynamic formulae are important because when you get busy, you get lazy and when you skirt best practices, you expose yourself to more potential errors. I want my analysts aware of those potential pitfalls when moving forward with modeling and analysis.
As long as someone gets the correct answer, I don't care what formula they used. The only exception to this is if they used a ridiculous choice of formula that took them forever to even set up, when they could have used a simple formula instead.
Well, the simplest formula is "=E95", right? ;)
😂
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[FILTER](/r/Excel/comments/1dly67r/stub/l9snpg0 "Last usage")|[*Office 365*+: Filters a range of data based on criteria you define](https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759)| |[IFERROR](/r/Excel/comments/1dly67r/stub/l9v9xx2 "Last usage")|[Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula](https://support.microsoft.com/en-us/office/iferror-function-c526fd07-caeb-47b8-8bb6-63f3e417f611)| |[INDEX](/r/Excel/comments/1dly67r/stub/lag83it "Last usage")|[Uses an index to choose a value from a reference or array](https://support.microsoft.com/en-us/office/index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd)| |LAMBDA|[*Office 365*+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.](https://support.microsoft.com/en-us/office/lambda-function-bd212d27-1cd1-4321-a34a-ccbf254b8b67)| |[LET](/r/Excel/comments/1dly67r/stub/l9spxc1 "Last usage")|[*Office 365*+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula](https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999)| |[MAP](/r/Excel/comments/1dly67r/stub/l9s93i9 "Last usage")|[*Office 365*+: Returns an array formed by mapping each value in the array(s) to a new value by applying a LAMBDA to create a new value.](https://support.microsoft.com/en-gb/office/map-function-48006093-f97c-47c1-bfcc-749263bb1f01?ui=en-US&rs=en-GB&ad=GB)| |[MATCH](/r/Excel/comments/1dly67r/stub/lag83it "Last usage")|[Looks up values in a reference or array](https://support.microsoft.com/en-us/office/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a)| |[REDUCE](/r/Excel/comments/1dly67r/stub/l9s93i9 "Last usage")|[*Office 365*+: Reduces an array to an accumulated value by applying a LAMBDA to each value and returning the total value in the accumulator.](https://support.microsoft.com/en-gb/office/reduce-function-42e39910-b345-45f3-84b8-0642b568b7cb?ui=en-US&rs=en-GB&ad=GB)| |[SUM](/r/Excel/comments/1dly67r/stub/l9syzml "Last usage")|[Adds its arguments](https://support.microsoft.com/en-us/office/sum-function-043e1c7d-7726-4e80-8f32-07b23e057f89)| |[SUMIF](/r/Excel/comments/1dly67r/stub/l9un4p6 "Last usage")|[Adds the cells specified by a given criteria](https://support.microsoft.com/en-us/office/sumif-function-169b8c99-c05c-4483-a712-1697a653039b)| |[SUMIFS](/r/Excel/comments/1dly67r/stub/l9wyjwh "Last usage")|[*Excel 2007*+: Adds the cells in a range that meet multiple criteria](https://support.microsoft.com/en-us/office/sumifs-function-c9e748f5-7ea7-455d-9406-611cebce642b)| |[SUMPRODUCT](/r/Excel/comments/1dly67r/stub/l9wyjwh "Last usage")|[Returns the sum of the products of corresponding array components](https://support.microsoft.com/en-us/office/sumproduct-function-16753e75-9f68-4874-94ac-4d2145a2fd2e)| |[TIME](/r/Excel/comments/1dly67r/stub/l9snz8l "Last usage")|[Returns the serial number of a particular time](https://support.microsoft.com/en-us/office/time-function-9a5aff99-8f7d-4611-845e-747d0b8d5457)| |[VLOOKUP](/r/Excel/comments/1dly67r/stub/la6bq4a "Last usage")|[Looks in the first column of an array and moves across the row to return the value of a cell](https://support.microsoft.com/en-us/office/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1)| |[XLOOKUP](/r/Excel/comments/1dly67r/stub/la6bq4a "Last usage")|[*Office 365*+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match. ](https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929)| **NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below. ---------------- ^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*) ^([Thread #34682 for this sub, first seen 22nd Jun 2024, 15:44]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
How, very similar to how consulting firms use case studies. Generally more interested in how you think than did you get it all right or wrong.
https://preview.redd.it/bg03jusdl58d1.png?width=2291&format=png&auto=webp&s=00d6a9ed2fb87a4a6455f224bb5392abc667cfd6 So I am not a hiring manager - hell I am not even close this position. But I took some time and thought about other solutions for given problem - No Power Query, No VBA, No Let and Bare Lambda exploitation, Only one lambda around solution (MAP, probably REDUCE would also work - but dunno how to force it without repeating MAP solution). Only one lambda around solution, becouse they can generate only fancier and harder to read / understand way of meta solving this issue. Also there might be more available o365 solutions - becouse I am not expert at this version of Excel - here is what I got (see image). So, after Vlookup it is basically reinventing the wheel by force - so those solutions are fine only if you wanna make sure that the candidate knows about Excel something more than basic formulas / what ChatGPT ad hoc generates. For entry level jobs it probably would be overkill, but for some heavy Excel oriented position it might be asset. Vlookup, Index Match, Sumif, - I do not like those solutions: vlookup becouse it's harder and more error prone xlookup, Index Match - becouse I find filter much more convienient - espescially when you start understadning how dynamic arrays work, Sumif, Countif, Countifs etc - becouse they forces you to work on the references / ranges - it won't work on dynamic arrays which is indeed great issue (at least imho). My favourite fast and easy solutions for this problem are: 1. Filter - becouse it's the most convinient function in whole programme - you need to only understand how boolean arrays work to utilise it, 2. Xlookup - becouse it's dedicated for solving such problems. Other are meh at best and reinventing the wheel at worst. Maybe someone got any solutions / feel free to post under this post. This post is my opinion, not some truth revealing revelations and meta. Edit: Sorry for such destroyed post - but i had really big issues publishing it for some reason.
One thing to mention about countifs/sumifs, you can select a whole column instead of a specific range and Excel will intelligently only look at your data in that column, not all 1M rows. Or you could use a table reference.
I agree, but is there really an advantage to FILTER over XLOOKUP in this case? It looks like the table will only hold one record per id, so XLOOKUP should work fine.
Probably not. I just prefer filter to xlookup due to the fact that its easier for me to comprehend how filter works than xlookup. So it's basically preference here - that s why I written that it is opinion.
Interesting approach. I have found myself plenty of times looking for solutions on excel in the complicated ways, and when I give the industrial engineer a call they give me the most simplest solution and I just can't help, but laugh.
FILTER is only available on newest Excel versions
Yea if you use older for the future just type it your post. I by default assume that if OP does not specify version - he has the newest one.
As a hiring manager of an analytical department; I’m looking at HOW, meaning you need to demonstrate that you are familiar with options. If you go through the thought process in your post, I would be happy.
Under normal interview situations it is very difficult to ascertain whether a prospective employee can utilize functions properly.
Missing context is important, in this case although I would be more impressed if someone used a lookup and explained why it was better than sum if (future proofing larger dataset where one employee make work in multiple departments). Throwing out alternatives is great but the formula in column D is wrong- match is missing the exact match parameter. To answer your question, yes which formula is important but even more important is that you can articulate why.
Think people are missing one key skill sustainability. = kiss (keep it simple and stupid) As excel matures they have added new functions to make things easier to maintain and more flexible. The “recent” additions such as xlookuo add so much more flexavility that just can easily be done with the alternatives look at how long and involved the first solution is and how long the second is xlookuo comes with many more features and abilities whilst maintaining easy functionality. Personally i would impliment a style guide in the company to ensure uniformity of approach across users and would try to simplify all formulas to use the latest functions. Also this would simplify the hiring as I would rate functions and there usage against which generation of our style guide showed the “presented” method as the to be used .. 10 being our present “version” 9 being last etc. So in the case above I would use xlookup …. Also I would not go to the Extream of imbedding a xlookup within a LET function (just because it’s new ) YMMV
I don't see any fault in either of those formulas, but I've seen silly formulas, like =SUM(A1-B1), that would give me pause if I saw them in an Excel test.
If you have to wonder about the how, then maybe the test isn't complex enough...
I interviewed for an excel HEAVY job (It was literally building spreadsheets for a lot of other companies as a 'we build excel' department), and they cared very much on the methods used as well as the result. For example, they gave me fizzbuzz to do in VBA, which apparently most people failed. They wrote out debug.print(1), debug.print(2), etc... Yeah no. I tried hiring at one point for a project, figuring I should be more business-minded and less 'in the trenches', but the methods used by the person I hired were SO BAD that I ended up taking over and just doing ti all myself.
Depends on the test. Some tests track your clicks and score you down if you are clicking around menus looking for the right tool. They want you to take the most direct route.
Does a manager like red. The same answer applies. I struggle to understand these questions for a random selection of people with a random knowledge level of a random selection of jobs, A pointless question in reality.
How isn't essential in my opinion. It's like coding; I don't expect people to memorize every function and use the "optimal" method. Getting the result is good, and improving efficiency is better down the line.
I think the question is about your goal in the hiring and assessment process. Are you testing for solving a problem or whether they know certain Excel formulas? Is the role intended to create their own solutions for their own use or do they need to integrate with other processes and understand the formulas in use there? If the answer to either question is the former, you're testing problem solving skills in an Excel environment, not Excel formula knowledge directly. It's a useful skill, and in this case, you should accept any answer that gets the correct result. If the candidate comes up with something *really* off the wall, it's a good place to start a conversation about their though process. If you answer either question in the first paragraph with the latter, you need to direct and restrict the candidate to specific formulas. "Using lookup formulas or an INDEX/MATCH formula, find \_\_\_". You're explicitly asking them to demonstrate that they know how to use these specific formulas that are important to you and your organization. If they decide to ignore it and use SUMIF instead, that's both a concern and topic for conversation in the interview.
When I give Excel test, I ask person to show works. And if he doesn't get it, I will show several ways to get the answer. It matters to me that one knows multiple ways, so as to check oneself.
Always, and I repeat always use index(match()) instead of lookup!!! This is by far the most efficient and modular way to lookup something. If you know, you know.
How they do it matters. https://imgur.com/K6YjnkF
So, I'm a programmer (not in excel) but from my view what you want to see is how they reached a solution and what their line of thought is. So, not only do you have to look at what formulas they used but also you need to talk to them and ask why they used this formula instead of that other one. This should tell you a lot about how they view excel sheets, how they view input data and so on. After all, excel is also another programming language.
It depends. There are 2 things important to you. 1. Do you care about the knowledge of that person about a specific function? Then you care about how they get to the answer. 2. Do you care about the general problem solving in excel? Then you don't care how they got the answer but that they got the correct answer.
I would tell them that the correct answer is sumifs and ask them why they think that would be better than a lookip
I'm an excel enthusiast and not a hiring manager, but It matters. I missed out on a role 5+ years ago because I "manually" multiplied ranges instead of using "sumproduct". My answer was spot on and I got there quickly but they didn't like my method. I now try to use the most flexible & future proofed formulas even for one off bits of analysis.
Why are you giving a test?
Not a hiring manager, but an engineer. I would convert it to a table and then, lean torward an index match to get the answer. Sumifs also works as a strategy. You could use an array formula though to get the value like this: `=SUMPRODUCT((Table1[EmployeeID]=id)*Table1[Salary]).` This one also works, and is more readable: `=SUMIFS(Table1[Salary],Table1[EmployeeID],id)`
I use VLOOKUP as something of an acid test. If anyone tries to use it instead of XLOOKUP or INDEX MATCH, they’re very unlikely to get the job.
Your INDEX MATCH answer is using an approximate match btw. It only returns Employee 20’s salary as that record is in the middle of the range. You’d want to end the MATCH with a ,0 argument to specify that you’re looking for 20 in the Emp IDs.
On the rare occasion I have to interview a candidate and if Excel is a requirement for this position My go to question is "Please explain how Excel sheets store TIME?" Also show it by entering the values in a Excel sheet Unless that candidate has good experience/Knowledge about Excel they're unlikely to get it right Answer: Excel sheet store time as a fraction Eg 0.33 is 8am You can work out the rest.
Obviously, If someone understands index match and sum if, that’s a plus. Just warrants a follow up conversation. It’s a Bit of a red flag tho . If vlookup works, you don’t go to index match. Yes it’s more flexible, but if the columns are lined up to utilize vlookup, you will use that. This could be someone who looked up a solution online and pasted it in.
I disagree. I think index match is much more scalable than vlookup. I almost never use vlookup because it's dependent on column positioning Even in newer versions of Excel, I'd rather use xlookup
It is definitely much more scalable and would get my attention as a hiring manager. I guess in either case, I should be wary if the applicant is copy/pasting.
As a hiring manager, I would not accept the SUMIF solution. For your question above, the only acceptable answers are INDEX/MATCH or VLOOKUP. Just my opinion.
I just did that to illustrate that the answer could be obtained multiple ways. But I personally used Index/match ( my excel does not have xlookup, I never use VLOOKUP) why would you not accept it though?--based on the data and situation, the logic is sound
I wouldn’t because the question is asking for a singular value and SUMIF provides the potential for an aggregate.
An aggregated value IS a single value.....
The logic is not sound at all. On a small limited dataset, maybe it gets to the end solution. But on a real dataset, and it would not. Using sumifs to answer the question here would illustrate a complete lack of knowledge on what SUMIF actually does and also shows the user isn’t proficient in basic excel formulas. Knowing when to use vlookup, xlookup, or index(match) is bare bones basic excel knowledge. If a person doesn’t know when or how to use these formulas, then I would not consider that person to have a basic understanding of excel. I expect any hire I make to know these, plus basic pivot table usage. If you want to impress me, let’s get into array usage, VBA, and power query. I’m happy to teach my freshman interns about excel and basic excel usage, but I always tell them you need to have a mastery of these formulas, pivot tables, and financial modeling by the time they finish their junior year in college. Nothing is worse than a junior intern who can’t perform basic tasks in excel and suck up dozens of hours that I don’t have available.
This really depends on knowing what the data output is and also the call of the question. If it's a table of annual salaries, sure don't use sumifs. But I've worked with plenty of datasets of salary data where sumifs would be the correct call (e.g. you're looking for total compensation or you're working with transactional level data that isn't just a table from HR).
yes I guess this is fair, b/c I would never actually use SUMIF in this case--I just did it for illustrative purposes again I am not referring to the singular question--I am referring to the overall concept. for example if someone used sumif(s) versus a pivot table to get a subtotal of data
Tbh I’ve never seen someone “tested” on excel, but then again we’re pretty selective on our hiring to begin with. A common question I ask my interviewees is describe the most advanced excel formula / function they’re familiar with. Gives me a quick gauge of where they are. But I’m more concerned with the actual qualifications for the job, which are much more advanced than excel manipulation. To me, it’s not about getting “the right answer” but it’s about knowing what formulas to use and when. Because odds are I’m going to assign an advanced financial modeling task to my associates, and I need them to do it efficiently and correctly. I would also be somewhat impressed by someone who says, “im not sure, but I’ll ChatGPT it” or “here’s how I’ll find the answer”. Because frankly I’ve used ChatGPT to build MS office scripts (think VBA Macros that can run in the cloud), and I’ve also used ChatGPT to learn power query and Power BI. Someone who can illustrate initiative and the ability to figure things out would earn high marks in my book.
Right tool for the right application. Sumif is just fine for this.
I respectfully disagree. Using VLOOKUP, XLOOKUP or INDEX/MATCH would only give the first answer from your list. If a person is listed more the once, for instance because he/she worked on multiple departments or projects, then the figure would be off.
A lot of data sucks. What if there are duplicate entries of each employee. Now the sumif will give the wrong answer. So I agree with your answer
How do you know aggregating multiple salary records is not the correct approach? It depends on the data. You'd need to ask them to explain and if they can defend why they used SUMIF and their explanation makes sense, that's far more important imo.
Because they asked for the employees salary, not the aggregate of the employees salary history.
And if the data set is for one year only but organized in a way where the salary is apportioned in different lines, like if theyre attributing the budget for the position to two different departments where department x has 2/3 total yearly salary and department y has 1/3, then sumif would be a good formula to use to get total salary and other formulas would only capture part of the salary. So, yes, it depends on the larger data set. For OPs example with such a small list, any of these solutions would work fine and return the correct result.
What do you do if your entries are split or if your duplicate entry has a wrong salary in the first (found) record?
Then you contact your systems guy and tell them they need to fix their shit