Error checking: Split up complex formulas (megaformulas) | Vose Software

Error checking: Split up complex formulas (megaformulas)

See also: Checking model behavior introduction, Model design introduction

It is probably a good idea to avoid complex formulas (megaformulas) in risk analysis models unless you can thoroughly test them.

Megaformulas, especially those performing array calculations, can certainly make a model run faster and keep its size much smaller, but they introduce an extra risk of error, and they are difficult for other reviewers and users to check. If you do use a megaformula, try first putting together a separate spreadsheet with each sub-calculation so that you can check your logic, and make reference to it in your reporting so that others can retrace your steps. If the megaformula performs a function that you need often, consider creating a custom function using Visual Basic for Applications, which can be documented and need then only be thoroughly tested the once.

Read on: Compare with known answers



Monte Carlo simulation in Excel. Learn more

Spreadsheet risk analysis modeling


Adding risk and uncertainty to your project schedule. Learn more

Project risk analysis


Enterprise Risk Management software (ERM)

Learn more about our enterprise risk analysis management software tool, Pelican

Enterprise risk management software introduction