How to Pass Microsoft Excel Job Test: Questions and Answers

a lot of times you might be required to complete indeed excel assessment test this might be necessary if you're applying for a full time part-time consulting or temporary position as part of indeed excel test you would need to be able to demonstrate your excel skills and answer specific questions on features and functionality of the application hi there this is vadim mikhailenko and in this video i'll share with you how to pass an employment assessment test we put together this video to help you better understand typical questions asked as part of excel assessment test answers and solutions that will help you pass the test rationale behind the calculations how to use formulas and functions what is the correct syntax to use where to find help tips and tricks and also where to find answers quickly in this video i will share with you sample questions we see on the test i will have some questions for you to try and i'll show you tips tricks and hacks on how to get ready and pass the test i will also share with you some additional test resources that might benefit you and might help you to get ready for the test quickly here is the sample question you might get as part of the test which function would be used to add various payment amounts in a worksheet with three different criteria and you might be presented with four different choices countyfast samifas some if or none of the above do you think you know the answer let me tell you a little bit about myself my name is vadim mikhalenka and i have mba and master's degree in computer science i have been helping people for more than 25 years to get hired and find jobs i have founded how to analyze data.net website with only intent to help people get employment this site helped a lot of people and i'm pretty sure it will help you as well and now let's jump straight to the questions answers and solutions a lot of times during excel assessment test you might be asked to determine which function should be used to perform particular calculations what you see on the screen is an example of the types of questions that might be asked which function would calculate a number of rooms that sleeps two or more and is available to book you are presented with the grid of values from excel as well as four choices some if some ifs countifs all of the above incorrect do you think you know the answer let's see if we can get to the correct answer together a lot of times during questions like this you don't even have to know the syntax of the function you just need to think logically because we're being asked to calculate a number of rooms the only function that is valid choice here is function count fs because that's the only function which calculates the count and some function calculates the sum of values which would be an incorrect choice here from my observation the choice similar to choice d is rarely correct on the test because most of the time employer is looking for you to find a specific value and choice d is just the easy way out for those of you actually interested in performing the calculations let's look at excel range with the data to see how we can figure out the answer based on what we're being asked we need to determine the number of rooms that slips more than two people so let's filter out those first and remove the rooms that only sleep one person which returns us 14 records but in addition to being able to sleep more than two people we're only interested in rooms that are not already booked so let's filter out all the rooms that's already booked by leaving the ones that only have no in this column which returns us six records now let's do the calculations using the formula to use count fs formula we just need to paste the value count fs select the range in the column e and select the value which would be no in our case as well as select the table sleeps column and enter the condition that the room should sleep more than two people and when we hit enter you see that the value is 6 which matches our original filtering let's go back to the original question and recap choices a b and d are incorrect and the correct choice is c which uses count to fast formula and now you even know how the formula should look like hopefully you've got this one right sometimes your knowledge of excel formulas and functions is tested by presenting you with the specific syntax and asking you what would be the end result of this what you see on the screen is an example of this type of question what result would be displayed in the highlighted cell which is d2 if the following statement was entered there you are presented with the nested if statement and you also presented with four different choices yes not applicable no and solicitor do you think you know the answer to answer this type of questions you need to have a good understanding of if statement and as you can see the if function in excel has three arguments two of which are optional the first argument is logical test the second and third arguments are optional and second argument returns the value if the logical test is true and third argument returns the value if logical test is false so before we plug in the formula and get to the final value let's understand what's actually happening let's use cell b10 to enter formula as a string and to do that you need to start formula with apostrophe and this allows us to go through this nested logic step by step let's look at the first if in this formula the first if's condition is prompting us if c2 equals yes let's find the value of c2 c2 is equals yes and if you remember from the syntax of the if formula if c2 equals yes the yes value will be returned so we have the yes value as output of the first if statement now let's look at the second if statement which represents the entire condition is b2 equals solicitor and the answer is yes the value in b2 cell equals solicitor and because this condition is true the value from the calculated nested if statement will be returned and by now we know that this value is yes so the final value from this nested if statement should be yes let's test it and if we enter the same formula into cell b11 but without apostrophe and hit enter the return value is yes going back to the original question the correct answer which matches the yes answer is a hopefully you've got this one right and in case you need more practices like this with similar questions from real tests make sure to check out the description of this video to download my pdf ebook a lot of students ask me how can i learn quickly let's look at all different options based on recent scientific research one of the most effective ways to learn we recommend to our students is a mixed mode learning you use variety of different resources during mixed mode learning and alternate between them to ensure better comprehension of the material for example if you're trying to get ready for excel assessment test you can start by watching youtube videos then practice the steps in microsoft excel read related topics in the ebook and then experiment with variety of questions during practice test very frequently you might be asked formula related question as part of your excel assessment test for example the question that we see on the screen asks which function should be entered into a cell b10 to calculate all products marked with yes in the expired column and refrigerator in the stored column and then you're presented with the snippet data snippet that we see on the screen contains three columns in the first column a we see the list of products in the second column b we see variety of values some of them have yes and no some of them show expired and in the third column different types of the products and some of the types are stored refrigerator and pantry there are four choices with the samples of the formulas that could be used to answer this question there are two samples with count if s formula one choice contains sumifs formula and another choice contains if s formula so which one do you think is correct answer here let's work together to get to the solution if you look inside microsoft excel i put the table that we saw in the snippet right in the excel spreadsheet i also put the possible values that were presented as part of the question in the right side of the screen i used the little trick here and started the formula with apostrophe this way excel doesn't think that this is a formula and doesn't start calculations now it is always a good idea to reread the question just to make sure you understand what is being asked and when you reread the question you might notice that you've been asked to calculate all products but what does it mean all products does it mean count of all products does it mean some of all products or does it mean that they're asking you to return just the range of products with all the data associated with the product as you might have learned the test providers are not trying to help you and most of the time specialize in making questions tricky now logically speaking you cannot return sum of products if you look at the choices presented here you can reasonably assume that you are asking to calculate count of products that meet certain criteria and there are two criterias that are presented yes in the expired column and refrigerator in the stored column and it might be coming clear to you that there are two data tables built in one the today's date and then the date as well as checker john with employee id number is a one table another set of data or range as you can call it in excel would be down below and this range will have three columns which you can highlight in a different color temporarily just to make sure that you can understand the difference so once we decode the question it becomes clear that they are asking us to return the count of products from this column that meet criterias in expired column and then stored column based on this information we can reasonably exclude choices b and c because some fs functions as well as efs functions do not return counts and the only choices that will work based on our understanding of question would be choices a and d so which one is right among a and d typically when you have two choices presented with very similar set of values it is different in the syntax of the formula between choice a and choice d and if we look at the syntax of countifs formula you will see that countyfs formula supports multiple criterias and ranges versus count if formula which only supports one range and one criteria so the correct answer here is choice a because choices b and c not just logically cannot calculate the value but also have incorrect excel formula syntax and for the same reason choice d is incorrect as well because it has incorrect excel formula syntax and when you put the correct formula into the cell b10 you will see that the final number of products that meet criteria is two this was a tough question hopefully you've got this one right let me show you smart ways to get prepared for the test one of the smartest way to prepare is to find out who your provider is that will be conducting an assessment test and use outlines and simple questions from the provider the providers might be indeed.com skill check ikm connexa shl and a lot of others once you know who the provider is you can go to their website find out sample questions and practice with simple questions you can also practice linkedin assessment test questions on the topic that you will be assessed linkedin test is free it gives you exposure to the sample questions and also you can get a badge that will enhance your profile for potential employers you can also research the topic you're trying to prepare for online and download ebooks to get ready for the test hands-on experience is very important you can find relevant training and follow along to do hands-on exercises using practice videos on the training if you have a question or ran into an obstacle during your practice exercises try posting questions in the comment sections of the video channel owners or members of the channel monitor the questions and respond to the inquiries and last but not least consider subscribing to relevant youtube channels i encourage you to consider subscribing to online training for everyone we have a great community of people helping each other to prepare and pass the test a lot of times during assessment test you might see questions asking you which argument is not part of the particular function well we're looking at one of those questions which of the following is not an argument you would find in some if function and you're presented with four different choices sum range criteria well you if true or range do you think you know the answer there are multiple ways you can get to the answer unless you don't know it i'll show you two most efficient ones at least from my standpoint so if you type the formula following the equal sign it shows you some arguments that are used by this particular formula as soon as you open the parenthesis so some if formula uses range criteria and then sum range arguments as part of the formula another way to get to the answer might be for you to put the cursor on top of the function and press f1 button in the windows keyboard it brings up excel's help and because we put the cursor right on top of the function or inside the function it brings in context sensitive help here in this help article you can find the syntax for some if formula and read some additional information about it because the question is asking which of the following is not an argument you must have figured answer by now and the correct answer is c value if true which is not part of the syntax of sum if formula which only contains range criteria and sum range arguments hopefully you've got this one right can i ask you to do me a favor if you know someone who will benefit from this material and looking for the job please share this content with them i really appreciate it this will help them find the jobs quickly thank you very much now let's continue and get you ready for the assessment test one of the most frequent questions we see on the test is the question where you need to find the true statement among variety of wrong statements we're looking at exactly this type of question which one of the following statements is true and you have four different choices some if is used to count cells based on specified ranges and criteria if statement has to be used first in a worksheet before you can use some efs or count to fast functions countifs is used to count the number of cells specified by a given set of conditions or criteria and last but not least none of the options listed above are correct which one do you think is right one of the tricks about answering these types of questions if you find at least one question which is false it means the choice d is also incorrect and in our case there are two choices that are incorrect let's look at each one of those incorrect choices in more details one of the tricks to answer these types of questions is to understand the definition of the function inside microsoft excel for example if at any point of time i type f1 on windows keyboard it will bring up excel's help and here you can find definition of for example some if function obviously you can also google it and find the definitions even faster but what we learn here when we click on the sum if function if you haven't known this already is that this function finds the sum of values and choice a tells you that some efs function counts cells based on specific range and criteria which is incorrect because some fast functions sums the values based on the specified ranges and criteria choice b is also incorrect because there is no such limitation that if statement has to be used first in the worksheet before you can use some fs or count to fast functions which automatically makes choice d incorrect as well so the only true statement here is choice c count if s is used to count the number of cells specified by a given set of conditions or criteria this was a tricky question but you see a lot of these types of questions on the test hopefully you've got this one right let's talk about best practices on how to get ready for employment assessment test if this option is available and you have a choice try to schedule assessment tests in the morning when you have highest levels of energy get a good night's sleep before the test and please do not take the test if you're tired since a lot of questions require your top mental energy performance during the test read each question carefully ideally more than once questions are designed to be tricky and each and every detail in the question might be important if you have a choice try to answer easy questions first this would allow you to leave harder questions for the end but you will get easy answers in and you will get the points for them try to validate your answers with more than one method for example if you do an excel assessment test you can do manual calculation you can try to use formulas for calculations use calculator or use pivot tables to validate your answers and last but not least try not to guess the answers as some providers deduct points for incorrect selections very frequently your knowledge of excel formulas and functions is tested by questions like this which statement of nested if functions is correct and you're presented with four different choices three of these choices presented contain syntax errors and one choice is correct do you think you know the answer the best way to answer the question around excel formulas and functions is to understand the syntax of formulas and functions in question for example if you type if in excel you see that there are multiple arguments that are part of this function you can also ask for help by typing f1 on the keyboard and that brings up excel article around the particular formula or function here you can see some of the examples as well as the full syntax now let's look at some tricks the test creators use to hope that you will answer these types of questions incorrectly let's look at choice b instead of if function it's if s which is completely different function in microsoft excel so obviously choice b is incorrect let's look at choice c and as you can see they hid an extra equal sign in a nested if which is not a valid syntax and in very similar way they hit the column sign in choice d so the correct choice here is choice a and the lesson learned for you is to answer these types of questions you need to understand the syntax and look for syntax errors in all choices presented to you hopefully you've got this one right and now it's your turn please make sure to post your answer in the comment section below this video so i can give you the grade here's the question for you to answer which of the following is the correct formula to calculate the weighted average score in cell c8 as shown below and there are four choices here choice a use average formula choice b use sum product formula with the arguments presented on the screen choice c is also used sumproduct formula but was the different set of arguments and choose d use average formula which one do you think is the correct choice here please make sure to post your answer in the comments section of this video so i can give you the grade let's look at the question which tests your knowledge of microsoft excel charts and graphs the graph 1 was created from the range a1 through c4 what are the steps you need to take to convert it to graph 2. you're presented with two screenshots one of the data in the graph for graph one and second one same data but with the graph two there are four choices hit select data and then switch row and column choice b right click the left most columns in their title and hit delete choice c change the range of the graph to a2 through c4 and then choice d change the number format of range a1 through a4 and range b1 through c1 to text and reinsert the chart which one do you think is right to better understand answer to this question let's jump right to microsoft excel and take a look at the data the reason there are four sets of bars here in this chart even though we only have two sets of data is because everything is formatted as the number you see here product titles you see the header of column b and column c are formatted as the number in excel when inserting the chart did not correctly detect the values and even though we wanted to present products and then b1 and b2 cell values as headers in the chart they were detected by microsoft excel as actual values that should be calculated inside the chart because headers were included in the calculation you see this product section which has one value as one which matches b1 cell and then you have second value s2 which matches c1 cell so how would you convert this chart into the other chart that was presented as part of excel test question the correct choice here is choice d we need to change the number format of the range a1 through a4 and range b1 through c1 to text and reinsert the chart let's go ahead and do it i created a duplicate of this data that we see in the original question right in the columns e through g and you see it also has all the values and numbers so first step is we need to convert this range into text to do that we need to select the range and then we need to select the text so the first point was accomplished second point is a little trickier in order for us to represent the number as text we would need to start this number with apostrophe and once we click enter you see this green triangle in the upper left corner which indicates that this number is actually a text we'll do the same thing for the second number in the cell g1 now as we done with the conversion we can reinsert the chart and you see once we reinserted the chart it correctly represents the values it only has values for product 1 2 and 3 and no longer has values for the header of the data tree cap the correct choice is d you need to change the number format of the ranges a1 through c3 and ranges b1 through c1 to text and reinsert the chart answer a is insufficient as the legend will still say series 1 and series 2 instead of 1 and 2. answer c will lead you to an error and cannot be done and answer d is incorrect and will yield a completely different chart so hopefully you've got this one right and if you didn't don't get frustrated but these are the types of questions that you frequently see on excel intermediate and advanced excel tests people contact me and ask what's changed during coveted 19 let's look at the changes in employment assessment test process that have happened recently one of the biggest changes is the fact that a lot of people work remotely which means a lot of hiring also happening remotely provider might ask you to install special software to monitor your desktop activities they might also ask you to enable your camera to see what you're doing during the test another big shift we see is that the questions become more relevant to the position for example if you're applying for account and or bookkeeper job make sure you know how to import the data do profit and loss reports calculate the expenses and calculate the annual statements these are the types of questions that you might see for that specific position researching the company and researching the test provider always works for our students once you know who the provider is you can try to go to their website to see the sample questions to get an idea what kind of questions you're going to get on the test it is always a good idea to refresh your hands-on skills and practice before the test this gives you necessary boost of confidence and allows to pass the test with the higher grade if you didn't pass the test make sure to reflect after it take notes and develop an action plan to see what the next steps might be to get to the job let's look at the tricky excel assessment test question which we frequently see as part of the tests company a is looking at four possible activities and will accept them if the irr is 10 or above as appeared in the cell e2 what is the formula utilized in cell c2 which can also be copied down to cells c through c5 to produce the required results there are four choices here all use if formula and there is a table here which represents the activities what do you think is the right answer let's see if we can nail this question together let's jump to excel and take a look at the data that is presented here first of all what is irr irr is the internal rate of return so you have some activity and you calculate return on investment for that particular activity it is internal return on investment so it's calculated a little bit differently but the idea is the same we do not need to calculate irr here but it's good to understand because this is a confusing this data might be confusing if you do not understand what irr means now let's look at the data i calculated accept status here and this is how the formula looks like we have to use absolute reference here for the cell e2 because as we move and start to project the data and expand it if we need to reflect this formula for all of the activities then the value of e2 if we don't use absolute value was also start shifting so let me demonstrate for example right now because we use absolute reference if i am going to expand this formula it will be correct it will calculate it correctly but if for example this formula wouldn't be absolute value it would be just a relative value and we accept it it will still calculate it correctly for the cell c2 but as soon as i start expanding it it will just calculate it incorrectly because it will take the value from this cells and here the value is 0 so it's always look looks acceptable because 0 will always be less than any one of these values in b3 b4 b5 so let's recap the correct answer here is a because in this case of the a it uses absolute references case b is incorrect because it uses relative references and answer d is incorrect because there's an error in the syntax of the formula here so hopefully you've got this one right it's a tricky question some of the answers do lead to the correct results for one cell but because question itself is asking what is the formula utilized in cell c2 which can also be copied down to cell c3 through c5 that's the part that confuses you and that leads to only one correct answer here on the list and now it's your turn please make sure to post your answer in the comment section below this video so i can give you the grade here is the question for you to answer you need to display last day of the month based on the month's id in the year which formula should be entered in the cell g2 to accomplish this objective and you have four choices eo months and the range b2 through f2 and then g1 e month and then dollar sign b dollar sign two comma g1 choice c e months and then dollar sign b dollar sign two comma b two plus g one and then choice d month and in parentheses dollar sign g dollar sign two plus g one which one do you think is the right answer make sure to post your answer in the comments section of this video so i can give you the grade

Channel: Online Training for Everyone

Views: 25,061

Rating: 4.8421054 out of 5

Keywords: vadim mikhailenko, online training for everyone, indeed test assessments, indeed test answers, indeed assessment test answers, indeed aptitude test, indeed excel test, indeed assessment test answers accounting, indeed assessment test answer key, indeed aptitude test answers, indeed excel assessment test, indeed excel assessment test answers, excel for job application, excel interview test, excel test questions and answers, excel interview questions, excel formulas

Id: ONkj_wkDxLo

Length: 30min 14sec (1814 seconds)

Published: Wed Nov 25 2020