Formulas in Spreadsheets

Formulas are a powerful tool that allow you to perform calculations, analyze data, and automate tasks in Simul8’s internal spreadsheets.

You can enter a formula by typing it directly into the relevant cell or by selecting a cell and typing it into the formula bar under the ribbon.

To see a full list of available formulas, and read a description of them, select a cell and click the ‘f_x’ button on the formula bar.

You can also browse the available formulas by going to the Formulas tab and selecting the different categories.

Formulas begin with an equal sign ( = ). For example, to add two numbers in cells A1 and B1, you would enter the formula =A1+B1.

Simul8 supports standard mathematical operators:

• Addition: +
• Subtraction: -
• Multiplication: *
• Division: /
• Exponentiation: ^

Use these operators to perform various calculations within your formulas.

Cell references allow you to include the values from specific cells in your formulas. Understanding these references helps you create flexible formulas that adjust as you copy them to different cells. Putting a dollar sign in front of a reference – called an absolute reference - means it will not dynamically change if the formula is dragged or copied to another cell. For example:

• Relative references: A1, B2, C3
• Absolute references: $A$1, B$2, $C3
• Range references: A1:C3
• Combination: A1:C3, D1

Simul8 provides a wide range of built-in functions for specific calculations. Some common functions include:

• SUM: =SUM(A1,B1,B2,D1)
• AVERAGE: =AVERAGE(B1:B10)
• MAX: =MAX(C1:C8)
• MIN: =MIN(D1:D6)
• IF Statement: =IF(C1>10, “Yes”, “No”)

Click on the ‘f_x’ button on the formula bar to search the full list of built-in functions and to read a brief description of each. Alternatively browse the different categories under the Formulas tab

Formulas update every time you open a spreadsheet or if you change the value of a cell and then select another cell.

Selecting the option to ‘Recalculate formulas at runtime’ will cause the formulas to additionally recalculate every time a cell value is changed while the simulation is running. You may want this to happen if a cell that is referenced in a formula is updated during a run (using Visual Logic, for example) and you want this to recalculate so the new value can be used elsewhere.

Note: This will mean Simul8 is performing these calculations much more frequently and will therefore impact the speed of a run. For this reason, this option is turned off by default.

To control exactly when the spreadsheet recalculates the formulas, you can also use the Visual Logic command – ‘Manually recalculate spreadsheet formulas.

You can use the command ‘Automatically recalculate spreadsheet formulas’ – to toggle the ‘Recalculate formulas at runtime’ option on or off. This is particularly useful if you know you will be editing many cells but do not need to recalculate the formulas for each update. You can toggle the option off, update all the required data and then turn it back on to update the formulas once, after all of the changes have been made.

To make cell references easier, you can change the name of cells to something more recognizable. You can then refer to cells by this given name in formulas.

• Use parentheses (curly brackets) to control the order of operations.
• Click and drag the small square in the bottom-right corner of a cell containing a formula to autofill the formula in other cells.

• Formatting Spreadsheets
• Buttons on Spreadsheets
• Attach Spreadsheets to Work Items
• Internal Spreadsheet Tutorial