MATHEMATICS, SIMULATION AND MODELLING
Mathematica Link for Excel
30 October 2006
Reviewed by Felix Grant
The convenience and flexibility of a three-dimensional cellular array as a plugboard platform when building conceptual constructs, particularly for exploratory or prototyping purposes, is undeniable. So is the power of a dedicated mathematics engine. Mathematica Link for Excel, recently updated to release 3, offers one way to the best of both worlds.
Viewed from Excel, the link appears as a toolbar and/or top level menu (a toolbar installs by initial default, changes are made in a settings dialog). The gamut of existing Mathematica functions is immediately available from Excel’s normal formula bar, compound functions can be defined and called, and pure functions can be used as well. Functions can be declared in one cell but applied to a list, with results delivered over a defined range of Excel cells. Mathematica graphics, far beyond anything that Excel can deliver, can be generated and placed within the worksheet. Macros can be written in Mathematica and run within the Excel worksheet, and fragments can be evaluated in a clipboard, a small analogue of the Mathematica notebook.
Functions are called in Mathematica syntax, but broken up into Excel strings and packaged within a bracketed EVAL function. For example, logarithm of 7 to base 3 would be =Log(7,3) in Excel, and Log[3,7] in Mathematica, but =EVAL("Log","3","7") as an Excel call to Mathematica. This means that anyone already familiar with Mathematica needs to remember a new way of writing syntax while Excel users need to learn it. Either way there is an invaluable function browser, analogous to Excel’s own, which helps find the required function then guides the way through point and click assembly of the necessary formula line. It’s also helpful that dialogs are nonmodal so you can dap in and out of the worksheet at will without having to close what you are doing and restart.
EVAL is one of five MathematicaLink functions, the others being CALC (forces a function to recalculate when told to do so by Excel), DATA (specifies that an argument is Excel native), EXPR and RULE (used for building Mathematica expressions and rules respectively).
Viewed the other way, from Mathematica, ExcelLink provides a set of nearly 60 functions, which control an Excel data source or target. Most of these are of the form EXCEL* (spreadsheet handling, at different levels of ease versus specificity) or $EXCEL* (user interaction) although there are three odd ones for specific purposes as well. The shorthand EXCEL* forms are intelligent, reading if only a range is given but writing if values are appended. Control is smoother this way around, and even more extensive. There are probably things you can’t do to Excel from within Mathematica, but I haven’t run up against them in a month’s practical exploration.
All in all, a delight to use.