Formulas are a place to perform calculations, reference existing values, and create new values. They are very similar to the spreadsheet formulas you know, but they are capable of more.
Formulas can be in a lot of different places, like dynamic expression and spreadsheet cells. They are signified by an equals sign surrounded by a blue box.
This document covers what formulas do in more detail. For an introduction to basic calculations, see Calculations in-depth in Getting Started with Blockpad.
Formulas can reference and create values, and there are different types of values in Blockpad. Some behave more similarly than others, and some are used only in specific circumstances.
Number values are just that – numbers. Blockpad stores all numbers the same way, whether they are entered as an integer, decimal, or fraction.
In Blockpad, numbers can have units assigned to them. A number with no units is considered to have “null” as the unit.
25, 0.25, 1/4, 1/4 in, and 5 lbf/in^2 are all considered number values.
A string is a set of text characters. In Blockpad, the words string and text value are used interchangeably – they mean the exact same thing.
“Hello, I am a string”, “233”, and “$14 ABCDEF ??” are all considered text values.
A Boolean value is a logical value. There are only two – true and false.
In Blockpad, Boolean values have no meaning as a number and numbers have no meaning as a logical value. This is different than many other spreadsheet and programming languages, where some numbers can mean true or false and true or false can be used as a number.
Date values specify a date and time.
Some calculations can be done with date values. A date value subtracted by a date value gives a number value with time units. Number values added or subtracted from date values will give a date value.
2019.04.27, 1274.03.07, and 2010.02.07.21.45.00 are all date values.
An array is a matrix of values. Each item in an array can be a different value type, and all value types are allowed, including functions and arrays. Arrays can be 1-dimensional or 2-dimensional.
1-dimensional arrays have only one row or only one column. In Blockpad, these are sometimes treated similarly.
2-dimensional arrays have multiple rows and multiple columns.
When arrays composed of only numbers are used in calculations, they are treated a little differently. In these cases, a 1-dimensional array is called a vector and a 2-dimensional array is called a matrix.
Note: Values are not created inside of an array. The array itself is a value. So, value names cannot be assigned inside of an array. [a = 1+1, b = 2*2]
will give an error in Blockpad.
A function is a pre-set calculation that takes values as inputs and gives a value as an output. Blockpad has many built-in functions in the library, like sin(), sum(), etc.
In Blockpad, functions are considered to be values and behave very similarly to other types of values, like numbers and strings.
In Blockpad, you can easily create and use your own functions inside of formulas. To do so, you specify the function variables and the operations to be done to those variables, using operators and existing functions.
For example, x => 5*sqrt(x) + x^2 is a function where x is the function variable.
Units are almost always used with number values. They are assigned to number values and affect how calculations are performed with unit tracking, unit conversion, and checking for fundamental dimension compatibility.
Only built in units are available at this time.
Every formula result is saved into Blockpad as a value. Whether the value is calculated or only typed in, and whether or not the value is named, it is stored in the Blockpad file, in the value container the formula is located in.
A literal is a value typed directly into a formula, as opposed to referencing a value stored in Blockpad. Examples of literals for number values are 42 and 4.2. Examples of literals for strings are “Good Morning!” and “What do you mean?”. Each value type has different rules for entering values, which are covered below.
In Blockpad, values are often displayed differently than they are entered. Some display settings and options are mentioned in the list below, but this should not be considered exhaustive.
The value display settings are controlled through value format, which can be accessed through the button in the toolbar or in the properties window.
Numbers can be entered using typical decimal format. 1234, 1234., 12.34, .1234, 0.1234, 0.0001234, 0.123400 are all legitimate number literals.
Numbers can also be entered as fractions with whole numbers (e.g. 3 2/3). When this is done, Blockpad treats the fraction as a number value, not as a calculation.
To enter a fraction, the basic input is (whole number) space (numerator)/(denominator)
.
The whole number part must be an integer, and the numerator and denominator must be whole numbers. Otherwise, the formula will treat it as a calculation instead of a literal. To enter a fraction without a whole number, type in 0 for the whole number part.
To enter a number with a unit, type the number literal, a space, and then the unit. Only a unit stored in Blockpad will work, but Blockpad will often autocorrect by guessing intent.
Because of the order of operations, there may be times that Blockpad doesn’t interpret units the way you’d expect, especially with combined units. For example, if 60 mile/ 60 mile/hr
is typed in, then Blockpad will interpret hr as a value name and not as a unit. If there is no value named hr, this formula will produce an error message.
These cases can be handled by parentheses around the number-unit literal. So, Blockpad will interpret 60 mile/(60 mile/hr)
the way you’d expect and give 1 hr as the answer.
This is only a concern when typing in literals. When a number value is referenced, the number and the unit are treated as a cohesive pair.
There is a special case of number-unit literals where multiple units of decreasing size are used to describe one value. When this is done, the number entered is treated as a single value, not a calculation.
Below are some units that can be entered this way.
The basic input for these unit combinations is [number_1 unit_1 number_2 unit_2 number_3 unit_3 etc]. 5 ft 6 in, 32 deg 5 min 20 sec, 10 day 3 hr 30 min, and 15 min 22 sec are all legitimate number literals.
If the value is negative, only the first number needs a negative sign. Also, there might be limitations on the kinds of number literals you can type in, depending on the units.
There are many options to display numbers in Blockpad, but the value format does not affect the number stored and used in calculations.
You can show numbers as decimals and choose the number of decimal places or significant figures to display.
Numbers can also be shown as a fraction. You specify the maximum denominator and Blockpad will round the display number to the closest fraction with that denominator (but still stores the value in the same way).
To enter a string in a formula, quotation marks or apostrophes are used to signal the start and stop. So “this is text” and ‘this is also text’ are both legitimate string literals in Blockpad.
To have a quotation mark or apostrophe inside of the string, type a backslash in front of the punctuation mark you want to show.
This is only necessary for the punctuation mark that you use to start and end, but you can always use the backslash for whichever. All the strings below are stored as __He said, “We're going on an adventure.”__.
“He said, \”We're going on an adventure.\””
‘He said, “We\'re going on an adventure.”’
“He said, \”We\'re going on an adventure.\””
‘He said, \”We\'re going on an adventure.\”’
Of course, using the backslash gets a bit confusing. It’s often easier to type text into a Value cell (or value field), and then reference that cell for the text value.
Because the text isn’t entered in a formula, it doesn’t need to be entered the same way.
Boolean values can only be entered in formulas one way – all lower case true or all lower case false.
Boolean values can be displayed in many ways – True/False, T/F, Yes/No, Pass/Fail, and many more including custom options.
Date values are entered using periods in the form yyyy.mm.dd.hh.mm.ss
The year, month, and day must all be specified, otherwise Blockpad will see the entry as a number value (2019 or 2019.04 are both number values). Seconds, minutes, and hours may be left off, but everything must be in the correct order. If something is left off, Blockpad will treat that part as 0 (the beginning of the hour, minute, or second). So, 1903.12.17 is the same as 1903.12.17.00.00.00 and 1969.07.20.20.17 is the same as 1969.07.20.20.17.00.
If the month, day, hour, etc. is only one digit long, a leading zero can be entered, but it isn’t required. For example, 1901.9.4.8.15 is the same as 1901.09.04.08.15.
Arrays are entered using brackets [], commas (,), and semi-colons (;). Brackets specify the start and end of an array. Commas separate members of one row into columns, and semi-colons (;) specify the end of a row.
For example, [11, 22, 33, 44] is a 1x4 horizontal array, [22; 33; 44; 55] is a 4x1 vertical array, and [11, 22, 33; 4, 5, 6; 77, 88, 99] is a 3x3, 2-dimensional array whose first row is [11, 22, 33].
If there is a number missing in the literal, then the array is still valid, but the space is left empty. For example, [1,2;3] is a 2x2 array, but with the bottom right value missing.
Blockpad considers a value to be there, but empty. Note this is different than a zero in that place. This array cannot be used in math calculations, because it is not all numbers.
Functions can be entered one of two ways. The in-line method is covered here. The second requires a name for the function and is covered below.
You will probably find using the named-function method to be more natural to use, so it is encouraged to learn, possibly before the in-line syntax.
To enter an in-line function, the function variables are specified, then the calculation using those variables is specified.
The basic syntax is
(FunVar1, FunVar2, …, FunVarN) => FunVar3^2 + 2*FunVar1+…FunVarN
The function variables are specified in parentheses, separated by commas. => separates the function variables from the calculation. The calculation is typed in like any other calculation but using the function variables.
For example, (A, B) => A^2 + B^2
is an in-line function that adds the squares of two numbers.
If an in-line function only has 1 variable, then the parentheses around it can be dropped. For example, x => x^2 + 2
is legitimate syntax for an in-line function.
Units are typically only used with number values, and that is covered above.
Every Formula result is saved as a value, whether the result is calculated or simply entered in. To name that value, the name is typed into the left of the formula, followed by an equals sign, with the calculation or input value at the end.
ValueName = (calculations)
For example, in the formula Length = 3 m + 500 mm
the formula result (3.5 m) is stored as a value, and the name of that value is Length.
This applies to most value types, including strings, arrays, and functions.
Functions can be named using the syntax above. For example Fun_Example = x => x^2 +2*x
will save the function with the name Fun_Example.
However, there is a more natural option that's more like how functions are used. The syntax for this is different than in-line functions, covered above.
In this method, the basic syntax is
FunctionName(FunVar1,FunVar2,…, FunVarN) = FunVar1+FunVar2+…+FunVarN
Where the right side of the formula is the calculation the formula will perform, using the function variables.
For example, the formula Hypotenuse(a, b) = Sqrt(a^2 + b^2)
creates a function named Hypotenuse, which finds the hypotenuse of a right triangle, given the lengths of its two sides.
Formulas can reference values for use in calculations. Blockpad uses a system of value names and value containers to reference any value in the file.
To reference a value in a formula, the value name and location is written, but some or all of the location can be implied depending on where you are referencing from. See Value containers for more on this.
This is true for all types of values, including functions and arrays.
As mentioned above, the value name and location are used to reference a value. Blockpad has simple tools to insert the appropriate reference name for you - clicking and copy reference.
To reference a value by clicking, first open the formula that will reference the value, navigate to the value to be referenced, and then click on that value. The value name and context appropriate location will be inserted into the formula.
To reference a value using copy reference, first go to the value to be referenced, then right click and select copy reference. Now open the formula that will reference the value and paste the reference inside. The context appropriate reference name will be inserted into the formula.
Because functions are values, clicking and copy reference work to insert the function name in a formula. You can also just type it in, like any other value.
To use the function calculation and give inputs, parentheses at the end of the function name are used, in the same way that parentheses are used in normal built-in functions. This is a function call.
So to use the Hypotenuse function defined above, the formula looks like this Hypotenuse(3 in, 4 in) = 5 in
.
There are some contexts that require a function as an input. In these cases, only the function name and location are used to reference a function, there are no parentheses typed at the end.
Units are almost always used in context with number literals. See above for more details.
When a value is renamed, references to it are updated to show the new name, they are not broken.
For example, say there is a formula Hobbit4 = "Peregrin Took"
and another formula referencing that value Hobbit4 == "Peregrin Took" = true
.
If you change the value name of the first formula to Hobbit3, then the reference in the second formula will auto-update to Hobbit3 == "Peregrin Took" = true
.
Values with no name can be referenced using the click or copy reference functionality. When this is done, Blockpad auto-generates a name for that value.
Referencing spreadsheet cells and table cells are a special case. If a value inside of a cell does not have a name, then it can still be referenced using the cell reference (the location of the cell in the spreadsheet e.g. B7, AA24, or T345). If a value in a cell is not named, this is what clicking or copy reference will insert.
If a value in a cell is named, then that value can be referenced using either the value name or the cell reference. However, clicking and copy reference will produce the value name. To enter the cell reference, it must be typed in.
If the value name is used to reference a value in a cell, then it acts the same as a cell reference with dollar signs (e.g. $E$12). It can also be thought of as a name in a report, not in a spreadsheet. If the cell reference is used, then it is treated the same as any other cell.
Blockpad uses a system of value containers to organize values and reference them. It isn’t necessary to understand this system – clicking and copy reference will do the work for you. However, it can be useful to understand.
The Value containers section in Getting Started with Blockpad is another useful reference.
In Blockpad, values are stored in value containers inside of the file. Value containers are the frames and sections in a file. Just like frames and sections, value containers can hold more value containers inside.
You can think of them like boxes. The file is a big box that holds all the value containers. Immediately inside the file box are all of the top level frame boxes. Inside the top level frame boxes there can be values and more boxes. In the illustration below, blue names are the names of the boxes and green names are values.
When you create a value in a specific frame or section, that value is stored inside of that value container.
For example, say there is a top level frame named Report1, and a section inside named Section1. If the formula Length = 4 ft
is written inside of Section1, then Length is stored in the Section1 value container, inside of Report1, inside the file.
To reference a value, dot notation is used to specify the value container(s) and the value name. This starts with the highest container (the top level frame) and goes down the sub containers until the value is reached. So, to reference Length in a formula, Report1.Section1.Length is typed.
This full reference name is not always needed. When referencing a value, the required reference name starts with the container after the first shared parent between the container holding the value and the container that the value is referenced from.
For example, say there is also a table in Report1 named Table1, and we want to reference Length from there. The full name and location can be used, Report1.Section1.Length, but this isn’t necessary. It’s only necessary to start after the first container they are both in, Report1.
So, the shortest reference name is Section1.Length. This can be used in a formula in Table1 Area = (3 ft)*Section1.Length
.
Going back to boxes, you can think of the first shared container as the first box that both containers are in (the container that holds the value and the container where you are writing the formula).
Most frames and sections are automatically closed containers. They hold all the values inside, and to reference those values from outside, location must be specified. However, value containers can be “open containers”, where they share their values with the next highest container.
To do so, the Capture Values property in the properties window is changed from yes to no.
When this is done to a value container (frame or section), the container becomes like an open box inside of the system. So, if this is done to a sub frame or Section, the values are treated like they are stored in the container above. If this is done to a top level frame, then the values are shared with the whole file.
To specify values that are in an open container, you don’t specify the open container in the reference name. Going back to our example, if Section1 has “Capture Values” set to no, then the full reference name is Report1.Length, and if we want to reference Length from Table1, then the required reference name is just Length.
This is shown below. Also in this illustration, Report2 has “Capture Values” set to no, so it is shown as an open box.
Calculations in Blockpad are very similar to other spreadsheet formulas: there are references, literals, functions, and operators. In many cases, a formula in Blockpad is the exact same as other spreadsheet formulas.
However, there is a little more to Blockpad calculations, because Blockpad formulas are capable of more things.
Operators perform specific operations on values in a formula, like addition, subtraction, or division. There are a number of operators in Blockpad, and some are different from normal spreadsheet formulas.
Category |
Symbol |
Operator |
Meaning |
Example |
Math |
- |
Negate |
Makes the following value negative. |
-27 |
Math |
^ |
Exponent |
Raises the left value to the power of the right. |
2^3 |
Math |
n/a |
Unit Multiply |
Assigns a unit to the preceding number value. Implied when a unit follows a number. |
5 ft |
Math |
* |
Multiply |
Multiplies. |
7*6 |
Math |
/ |
Divide |
Divides. |
33/11 |
Math |
+ |
Add |
Adds. |
4+39 |
Math |
- |
Subtract |
Subtracts. |
43-4 |
Math |
to |
Unit To |
Converts the number value on the left to the units on the right. |
5 ft to in |
Test |
== |
Equals |
Tests if equal. |
42==42 |
Test |
!= |
Not Equals |
Tests if not equal. |
42!=42 |
Test |
< |
Less Than |
Tests if less than. |
33<34 |
Test |
> |
Greater Than |
Tests if greater than. |
33>34 |
Test |
<= |
Less Than/ Equal to |
Tests if less than/equals to. |
33<=34 |
Test |
>= |
Greater Than/ Equal to |
Tests if greater than/equals to. |
33>=34 |
Access |
. |
Member Access |
Accesses a specific value reference from a value container. |
Report1.Section2.Width_3 |
Access |
: |
Chain |
Yields a range reference given two cell references. |
A3:B12 |
Function |
n/a |
Function Call |
Performs a function's calculation procedure with given inputs. Signified by parentheses after the function name, with the inputs inside. |
Average(1,2) Sin(30 deg) |
Formulas calculate in a specific order. Different operators take precedence over others, and operators with the same precedence calculate left to right. Parentheses always override any precedence, so operations grouped inside of parentheses always evaluate first, with normal order of operations holding true inside.
The table below shows which operators have precedence over others and which operators share precedence, going from top to bottom.
Symbol |
Operator |
: |
Chain |
. n/a |
Member access Function call |
- |
Negate |
^ |
Exponent |
n/a |
Unit multiply*** |
* / |
Multiply Divide |
+ - |
Add Subtract |
to == != < > <= >= |
Unit to Equals Not equals Less than Greater than Less than/ equal to Greater than/ equal to |
When a combined unit is assigned to a number, * and / are evaluated for the units first, before they are assigned to the number.
For example, if 20 m/s^2
is entered, (m/s^2) is evaluated first, before being assigned to the number.
Unit multiply is still higher priority than * and / though. For example, the formula 20 m/s^2 * 3 s
is the same as (20 m/s^2)*(3 s)
When multiple test operators are used in one formula, Blockpad returns true only if all tests are true. Essentially, Blockpad interprets multiple test operators with a logical and.
For example, if 1 < 2 < 3
is entered, Blockpad evaluates this the same as and(1<2,2<3)
. So, 1<2 and 2<3 must both be true for the formula to return true.
This is opposed to normal left to right evaluation. 1 < 2 < 3
-> (1<2)<3
-> true < 3
. Interpreting like this would give an error in Blockpad, since boolean values are never treated as numbers.
There are some situations where this order of operations does not give what you’d expect, especially with units. This is because Blockpad is unsure whether to treat something as a unit or a value reference.
For example, in the formula 5 m/10 m/s
, one would expect this to be interpreted as (5m)/(10m/s))
, but instead Blockpad sees the s as a value reference that hasn’t been created and will give an error.
When this happens, parentheses around the full number/unit entry will always work. So for the example above, 5 m/(10 m/s)
will give the expected answer of 0.5 s.