Difference between revisions of "Excel and Analytica"

m
 
(3 intermediate revisions by one other user not shown)
Line 1: Line 1:
 
[[Category: Excel to Analytica mappings]]
 
[[Category: Excel to Analytica mappings]]
  
−
Many modelers use Analytica instead of spreadsheets because they find Analytica's [[Influence diagram]]s 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 and other spreadsheets. Even if you prefer to build models in Analytica, you may want to link it to spreadsheets. You may want to import input settings and data from a spreadsheet. Or you may want to save model results to a spreadsheet for others to review, share, or generate graphs.   
  
−
Analytica plays nice with Excel. There are several ways to read inputs from a spreadsheet into Analytica and write results back to a spreadsheet:
+
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 [[Excel spreadsheets read and write|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.
+
* Usually the simplest is to use standard Analytica functions that [[Excel spreadsheets read and write|read from and write to Excel spreadsheets]]. These functions can open a spreadsheet, read from or write to a cell, cell range, named range, or worksheet. 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]].
+
* In [[Analytica Cloud Platform]], these spreadsheet functions can prompt a user to upload or download a spreadsheet from or to their computer. 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.  
+
* [[OLE linking]] lets you can link a cell or range in Excel with a corresponding Analytica variables. Any changes to the spreadsheet propagate automatically to the Analytica model, and vice versa. If both Analytica model and spreadsheet are open, each update to one propagates immediately to the other. If not, the update propagates when you next open the closed file (Analytica model or Excel spreadsheet). OLE 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.
+
* [[COM Integration]] (Component Object Model) also lets you link Link from Analytica to Excel.  It is another widely used Microsoft-developed method for communicating between Windows applications.  Analytica's COM functions let you access just about all features of Excel and other Microsoft Office applications. See [[COM and Excel examples]] for how this works.
 +
* You can also use [[Database access|ODBC]], a widely used standard for reading and writing relational databases, to access Microsoft Excel.  
  
−
== Converting a spreadsheet into Analytica ==
+
== Convert 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.
+
If you have have a spreadsheet that is too cumbersome, opaque, or inflexible, you may wish to translate it into Analytica. You will end up with a model that is much clearer and simpler. The Analytica file is often 10 to 40 times smaller than the original Excel file -- even though you have added information in the form of visual influence diagrams and documentation, This dramatic reduction in size and complexity reflects a corresponding reduction in the effort to understand, test, communicate, and maintain a model. Translating a spreadsheet is also a powerful way to find inconsistencies or errors by comparing the results of the spreadsheet and the Analytica model. The translation process often surfaces errors that were previously 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.  
+
See the [[Excel to Analytica Translation]] for a step-by-step guide to convert a spreadsheet into Analytica. We have yet to find an Excel calculation that can't be converted into Analytica.   
 +
 
 +
[[Excel to Analytica Mappings]] shows the Analytica function or expression equivalent for most Excel functions.  
  
 
:[[File:Excel to Analytica.png|400px]]
 
:[[File:Excel to Analytica.png|400px]]

Latest revision as of 08:58, 20 June 2022


Analytica plays nice with Excel and other spreadsheets. Even if you prefer to build models in Analytica, you may want to link it to spreadsheets. You may want to import input settings and data from a spreadsheet. Or you may want to save model results to a spreadsheet for others to review, share, or generate graphs.

There are several ways to read inputs from a spreadsheet into Analytica and write results back to a spreadsheet:

  • Usually the simplest is to use standard Analytica functions that read from and write to Excel spreadsheets. These functions can open a spreadsheet, read from or write to a cell, cell range, named range, or worksheet. They can read and write formulas and formats as well as simple numbers or text values.
  • OLE linking lets you can link a cell or range in Excel with a corresponding Analytica variables. Any changes to the spreadsheet propagate automatically to the Analytica model, and vice versa. If both Analytica model and spreadsheet are open, each update to one propagates immediately to the other. If not, the update propagates when you next open the closed file (Analytica model or Excel spreadsheet). OLE is a standard Microsoft technology for creating live links among Microsoft Office products, including MS Excel and Word.
  • COM Integration (Component Object Model) also lets you link Link from Analytica to Excel. It is another widely used Microsoft-developed method for communicating between Windows applications. Analytica's COM functions let you access just about all features of Excel and other Microsoft Office applications. See COM and Excel examples for how this works.
  • You can also use ODBC, a widely used standard for reading and writing relational databases, to access Microsoft Excel.

Convert 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. You will end up with a model that is much clearer and simpler. The Analytica file is often 10 to 40 times smaller than the original Excel file -- even though you have added information in the form of visual influence diagrams and documentation, This dramatic reduction in size and complexity reflects a corresponding reduction in the effort to understand, test, communicate, and maintain a model. Translating a spreadsheet is also a powerful way to find inconsistencies or errors by comparing the results of the spreadsheet and the Analytica model. The translation process often surfaces errors that were previously hidden in the spreadsheet.

See the Excel to Analytica Translation for a step-by-step guide to convert a spreadsheet into Analytica. We have yet to find an Excel calculation that can't be converted into Analytica.

Excel to Analytica Mappings shows the Analytica function or expression equivalent for most Excel functions.

Excel to Analytica.png

See also

Comments


You are not allowed to post comments.