The Benefits of Arbitrary Questions

Interesting. I use Excel pretty often am and good with it but there's nothing compelling enough for me to upgrade from my several-year-old license. The linked .xlsx file isn't readable in the Excel I own.

So you are having a hard time finding qualified people, and you say that using Excel is not an important part of the job description, but you chose to arbitrarily filter out everyone who doesn't own a recent version of Excel.

Does this really ensure only the best and the brightest will apply?

(Spoiler: http://www.zamzar.com/ is an online tool which allows conversion from xlsx to xls; and the 6 questions are not hard, the hardest part is dealing with xlsx.)

1. What is the average amount owed?

    =AVERAGE('Raw Data'!D2:D252)
    $547.55

2. How many music customers are there?

    =COUNTIF('Raw Data'!B2:B252,"Music")
    103

3. What is the due date of the customer in zip code 11680?

    =SUMIFS('Raw Data'!E2:E252,'Raw Data'!C2:C252,"=11680")
    4/4/2012

4. If you divide the highest zip code by the lowest zip code, what is the remainder?

    =MOD(MAX('Raw Data'!C2:C252),MIN('Raw Data'!C2:C252))
    7782

5. How many theater customers have a May 26 due date?

    =COUNTIFS('Raw Data'!B2:B252,"Theater",'Raw Data'!E2:E252,DATEVALUE("May 26, 2012"))
    2

6. Considering all customers, find the standard deviation of dividing the customer's zip code by their number.

    {=STDEV('Raw Data'!C2:C252/(1*MID('Raw Data'!A2:A252,LEN("Customer "),LEN('Raw Data'!A2:A252)-LEN("Customer ")+1)))}
    85.98374041

A few notes:

- #3's answer cell should have date format (otherwise it will show up as 41003) - #4's answer cell should have number format (for some reason it defaults to currency) - The curly brackets on #6 denote CTRL+SHIFT+ENTER for an array formula

There's still room for some improvement. The range 2:252 and the string "Customer " are hard-coded into the formulas. These could be removed with some additional code but this would increase the length of the formula by a good amount.

So, how many did I get right? :)