How to pass an analytics job test – Part II – MS Excel.

          Even with the rise of use of R, Python, SAS and other more scientific analytical tools, Microsoft Excel remains the most popular data analysis tool. While we have gone over a solution for an analytics job test in SQL last month , you are much more likely to encounter a job test in Microsoft Excel for your next analytics opportunity. While I would personally argue that this particular test is actually better solved with SQL, the employer believes that the applicants instead need to apply their Excel skills to demonstrate their proficiency and acumen. As before, we should start by asking questions about the problem at hand and trying to get as much clarification as needed or state our assumptions. However, since spreadsheets are less forgiving from the presentation point of view than the databases, I would strongly recommend that we would also take a few minutes to format any workbooks provided by the prospective employer. Chances are they would recognize your level of professionalism by looking at clean and presentable file. Your stylistic preferences might be different, but as a minimum I would remove gridlines, add filters/format as tables larger datasets, freeze panes, and add at least one to two colors to the otherwise monochrome layout.

          As you can see from the test file , potential employer is clearly in the healthcare vertical. Reading through instructions and notes, we need to retrieve commissionable flag and effective dates [CommissionableDates tab] for carrier/state combinations also found in the [Policies tab.] If the policy has commissionable flag set to “Yes” then it’s commissionable, if the flag is set to “No” it is commissionable, as long as its effective date is less than the or equal to last effective date. If the record is not found then we cannot determine if the policy is commissionable and need to assign a value of N/A.

Data tables for an Excel job test.

          It wouldn’t take us a long time to realize that we have 3,533 policies, and only 99 records with commissionable dates. Assuming this data is in fact accurate, I would also show the summary below to demonstrate all records that cannot be matched between these data sets:

          I propose two versions of the solution, each of which would require multiple helper columns: 1) A simple solution using the VLOOKUP function, with a slight touch of sophistication in the form of the MATCH function. 2) While the first solution gets the job done, we can go the extra mile to impress our prospective employer and undertake the INDEX/MATCH route instead. Do you have any other ideas?

1. Solution using VLOOKUP/MATCH functions.
          Since our match requires multi-column look up (a combination of Carrier and State columns), and VLOOKUP is limited to single column lookup, we need to create a helper column in the [CommissionableDates tab.] Please note that the second methodology with INDEX/MATCH function combination does not have this limitation and we would not require this specific column. Below is the concatenation formula I created to combine these fields in cell A4:

 =B4&"_"&C4 

          I would then proceed by creating 3 additional help columns in the [CommissionableDates tab.], we could retrieve Commissionable flag, Last Effective Date and then determine actual commissionable logic – Commisionable_Final.

          Initial Commissionable look up can be performed by concatenating Carrier and State column to be used in VLOOKUP function against the expanded range from the [CommissionableDates tab.], which now also concatenates these fields. Since we don’t want to guess the relative position of the column we would like to retrieve matched value from, I propose using the MATCH function, which would do just that based on the column name – please make sure that both tabs have the same column headings in the relevant range. I noticed that this data was not overly clean, and am using the TRIM function to eliminate one space prefix. Finally, IFERROR function can help us deal with unexpected errors. Combining all these steps, we have:

Cell E4:

 =IFERROR(TRIM(VLOOKUP(B4&"_"&C4,CommissionableDates!$A$3:$E$102,MATCH(E$3,CommissionableDates!$A$3:$E$3,0),)),"")

Last Effective Date utilizes similar logic, but is retrieving date, instead of the commissionable flag:

Cell F4:

 =IFERROR(VLOOKUP(B4&"_"&C4,CommissionableDates!$A$3:$E$102,MATCH(F$3,CommissionableDates!$A$3:$E$3,0),),"") 

          The last column helps us put it all together based on the business rule specified. If the policy has a commisionable flag set to “Yes”, we would assign a value of “Yes” here, if the commisionable flag is “No” the we look at the last effective date and if it is equal to or later than the effective date, then our flag should be “Yes”, otherwise “No”. If the record is not found then it’s not available: “N/A”
Cell G4:

 =IF($E4="YES","YES",IF(AND($E4="NO",$D4<=$F4),"YES",IF($E4="NO","NO","N/A")))  

This formula can be simplified quite a bit with the new IFS function, but we are not sure if the employer subscribes to Office 365.

          We are now ready to present our findings. This step is quite simple, we simply need to utilize the COUNTIF function to function number of occurrences of commissionable, non commissionable, and not available policies at hand by matching the appropriate condition with the final commisionable flag from the previous step.

Cell C3:

 =COUNTIF(Policies!$G$3:$G$3536,"YES") 

Cell C5:

 =COUNTIF(Policies!$G$3:$G$3536,"NO") 

Cell C7:

 =COUNTIF(Policies!$G$3:$G$3536,"N/A") 

          To make your life easier, here is the Excel version of this test file solution .

2. Solution with INDEX/MATCH functions combination.
          As you can guess, formulas in the [Summary tab.] stay intact, we no longer need the help column in the [CommissionableDates tab.], and only need to focus on the [Policies tab.] to implement our solution. Furthermore, Commisionable_Final column logic stays intact, using the same Nested If logic.
We now need to convert VLOOKUP/MATCH formulas into INDEX/MATCH array formulas. Applying multi-column lookup logic:
Cell E4:

 {=TRIM(IFERROR(INDEX(CommissionableDates!$B$3:$E$103,MATCH(1,(CommissionableDates!$B$3:$B$103=B4)*(CommissionableDates!$C$3:$C$103=C4),0),3),"NOT FOUND"))}

Please keep in mind that for this formula to work it has to be entered as an array formula – using CTRL+SHIFT+ENTER key combination instead of regular ENTER.
Cell F4:

 {=IFERROR(INDEX(CommissionableDates!$B$3:$E$103,MATCH(1,(CommissionableDates!$B$3:$B$103=B4)*(CommissionableDates!$C$3:$C$103=C4),0),4),"NOT FOUND")}

          Congratulations, you have reached the end of the final solution for this test.

          Do you agree with these solutions, or would you solve this exercise differently? Could you share other analytics job test exampless?