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