Well dang it, what was the Excel challenge? Of course we all want to try it :)

Would love your thoughts! I linked to it in the post, but here's a direct link: http://smooth-mist-1423.heroku.com/

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

My fault: When I was actually hiring I used an XLS, but this evening when I was writing the post I made a trivial change to the file and accidentally saved it as an XLSX. I just pushed an updated version. Mea Culpa.

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

I got all of them except the last one. Does anyone know how to apply a formula to a range of cells and get back a range of results? (i.e. map)

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

It's an array formula which means instead of pressing ENTER, press CTRL+SHIFT+ENTER

  I tried to mitigate this by telling people not to
  bother writing a cover letter.

Aren't cover letters a dying art anyway?

So what happens if they don't have Excel?

OpenOffice.org