Array formula bug on B2 version of Excel Services.

I got an email from Dan Ciruli describing a bug he was experiencing with an array-returning UDFs. In essence, the UDF Dan wrote was being called over and over again for a single recalculation.

After reproing this on the server, I went to talk to Duane and, as fate would have it, he knew about the bug and even fixed it the night before.

The bug would occur when a cell would reference a range that was contained a call to an array formula. Take for example the following workbook in Excel:

A3:C5 contains an array formula utilizing the RandBetween formula. As you can see, it shows the same formula in all cells (note that if this was not an array formula, but rather, just copied cells, each cell in the A3:C5 range would have show a different value).

If this workbook (sans the formula in B1) was to be opened on the server, it would have shown a similar result (i.e. it may not have shown 5 in all cells, but they would all show the same number).

Once the formula in B1 is added (it simply referneces A4), the client still behaves the same, however, the server would start showing potentially different values in each cell!

This happens with UDFs as well, and may have puzzling results - suddenly the workbook takes ages to calculate because the UDF does some heavy lifting.

The takeaways from this are:

1. You can usually solve this bug by rearranging your workbook to have referencing cells in different sheets or below the array formula range.
2. The fix will be present in the B2 Techincal Refresh release.
3. This may still happen for some extreme cases. Make sure your workbook behaves as you expect it to.

Comments

• Anonymous
  September 28, 2006
  Instead of re-hashing information I've found elsewhere I figured a pre-reqs post would be good.
  One...