Working on a question submitted in an on-line forum the other day, I stumbled upon a phenomenon that threatened to do my head in.
The asker of the question had attached her workbook to the post, created with Excel 2007. As of writing this, I am working mainly with Excel 2003 and the Excel 2010 beta version. I created a solution for the question that involved Sumproduct(). I used Excel 2010 beta, tested it, and it worked fine, so I posted the spreadsheet back into the forum.
The asker still was not happy. She claimed that the formula did not calculate right. We went back and forth a few times, until I finally fired up my unloved copy of Excel 2007 and — whoa! — here was a completely different result for the Sumproduct formula.
How could that be?
I dug around a bit, asked a few people, ran a few tests and finally concluded: Excel 2007 produces wrong results with Sumproduct, depending on the syntax used, and if the precedents are volatile.
I’ve written up my findings in this article: