Excel and Analytica

Many modelers use Analytica instead of spreadsheets because they find Analytica's Influence diagrams make for more transparent models, Intelligent Arrays provide greater flexibility and power, and/or they want to treat uncertainty using Analytica's Monte Carlo tools. But, many organizations are not ready to give up all their spreadsheets. So they use Analytica in combination with Excel -- for example, users may submit input settings and data on a spreadsheet to be imported into Analytica. And the model writes results to a spreadsheet to review, share, and analyze results in Excel.

Analytica plays nice with Excel. There are several ways to read inputs from a spreadsheet into Analytica and write results back to a spreadsheet:

  • Usually, the simplest way is to use a set of standard Analytica functions that read from and write to Excel spreadsheets. These can open and close a spreadsheet, read from or write to a cell, cell range, named range, or tab. They can read and write formulas and formats as well as simple numbers or text values.
  • These functions work equally well in the Analytica Cloud Player. When opening a spreadsheet from ACP, it can prompt the user to upload a spreadsheet to ACP. Similarly, when closing a spreadsheet, it can prompt the user to download a spreadsheet with the results. For details see Spreadsheets in ACP.
  • You can link cells or ranges in Excel with Analytica variables with OLE linking. OLE supports "live" updates: Any changes to the spreadsheet propagate automatically to the Analytica model and vice versa. If they are both are open, the update propagates immediately. If not, the update propagates when you next open the closed file (Analytica model or Excel spreadsheet).This is a standard Microsoft technology for creating live links among Microsoft Office products, including MS Excel and Word.
  • You may also access Excel from Analytica using COM Integration (Microsoft Component Object Model), another widely used method for communicating between Windows applications. Analytica's COM functions let you access just about all features of Excel and other Microsoft Windows applications. See COM and Excel examples for how this works.

Converting a spreadsheet into Analytica

If you have have a spreadsheet that is too cumbersome, opaque, or inflexible, you may wish to translate it into Analytica. This is a great way to learn Analytica for a new user. It's also a powerful way to verify the validity of a spreadsheet by comparing the results of the Analytica model with the spreadsheet. This process often surfaces errors that were hidden in the spreadsheet.

We have yet to find an Excel calculation that can't be converted into Analytica. Usually, there is considerable benefit in clarity and compactness. If you plan to convert a spreadsheet into Analytica, you should consult Excel to Analytica Translation, a step-by-step guide for the process. Also see Excel to Analytica Mappings which gives the Analytica function or expression equivalent for most Excel functions.

Excel to Analytica.png

See also


You are not allowed to post comments.