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

 

ModelRisk

Monte Carlo simulation in Excel. Learn more

Tamara

Adding risk and uncertainty to your project schedule. Learn more

Navigation

FREE MONTE CARLO SIMULATION SOFTWARE

For Microsoft Excel

Download your free copy of ModelRisk Basic today. Professional quality risk modeling software and no catches

Download ModelRisk Basic now

FREE PROJECT RISK SOFTWARE

For Primavera & Microsoft Project

Download your free copy of Tamara Basic today. Professional quality project risk software and no catches.

Download Tamara Basic now
-->