F1 points in Excel
Last fall, I was experimenting with array formulas in Microsoft Excel, and I created this simple Formula 1 points calculator. In the workbook, you enter race results in one worksheet, and you see a second worksheet with instantly tabulated and sorted driver points, and a third with instantly tabulated and sorted constructor points. (Of course I could have programmed this in a variety of other languages on other platforms, but the goal here was to investigate Excel).
What are array formulas? Unlike normal worksheet formulas, which work on a cell-by-cell basis, array formulas apply to a rectangular region of cells. Their main use is to enable functions that returns multiple values; for instance, a regression function might return a slope and an intercept, which would be returned in two adjacent cells.
In my investigations, I discovered that you can return an array from a Visual Basic for Applications function that can be applied to a region just as a built-in worksheet array function can. In my F1 points calculator, I have written two such formulas, one to pick out the unique values from a list, and another to do a bubble sort over a column in a region.
To use this sheet in Excel 2002, you must set macro security to at most "medium" (in the Tools menu, pick Macros... and then Security). Earlier versions of Excel have "medium" security on by default. If you are untrusting, you can open the spreadsheet with macros disabled, look at them in the VBA editor to reassure yourself that they are not malicious, and then reopen the sheet with macros enabled.
10:42:08 AM 
|
|
