# Calculation

**Formulas and functions**

**Calculation** with LinkNotions

Formulas and functions

… Functions: IF, AND, AVERAGE, NOW

… List of functions for LinkNotions

…. List of functions available in Excel

When to use a visual spreadsheet?

We know that we can calculate the value of a notion from the values of one or more other notions.

The calculation of the value is done by formulas using functions.

Example:

Notion C = Notion A x Notion B (the function used in this formula is multiplication)

or:

Surface = Width x Length

The formulas are written in the “Main Expression” field **(1)** of a notion that you will find in three places:

- The “Calculations” window
**(2)**. Click on a notion to activate it. - The “Calculation overview”
**(3)**. Click the “Show / hide calculation overview” button in the toolbar. - The “Notion Properties” window. Double-click the notion and click “Modify”
**(4)**.

Here is a diagram that shows the use of the formula for calculating the surface.

## The field “Main expression“

The content of the field “Main Expression” can be

- an amount
- a formula
- a text

but writing is subject to strict rules.

Example showing these 3 registration possibilities

You will find amounts in the notions

- A1 “empty plane” (click on the notion A1 in the above interactive diagram to see the amount “296” in the field “Main expression“)
- A2 “Gasoline (Liters)”
- A3 “Driver”
- A4 “Passenger”
- A5 “Baggage”
- C5 “Maximum total weight”

You will find calculation formulas in the notions

- A2 “Essence” (click on the notion A2 in the above interactive diagram to see the formula in the field “Main expression“)
- B3 “Total weight”
- C4 “Available passenger weight”

You will find a text in the notion

- B5 “Take-off allowed” and “Airplane too heavy” which are displayed according to the total weight of the aircraft. (click on the notion B5 in the above interactive diagram to see the text in the field “Main expression“)

### Enter an amount:

The inscription is done in the window “Calculations” which is under the diagram.

Enter the amount without space. For example 2000000, but do not enter 2.000.000 or 2 000 000.

If you enter 2000000, the software will show 2 000 000 (with spaces) in the concept. Try in the interactive diagram above.

To enter a decimal place, use the dot. For example: Two decimal thirty-five is entered 2.35 (and not 2.35). The comma is used to separate arguments in a function. For example, to calculate the average of 3 numbers:

AVERAGE (1.50, 10.011, -5000)

### Enter a formula:

The inscription is done in the window “Calculations” which is under the diagram.

Remember that a value of a notion can be calculated from one or more values of one or more notions.

The basic operations are the addition (+), the subtraction (-), the multiplication (*), the division (/), the remainder of a division (%) (7% 2 = 1).

The exponent is written with the circumflex accent “^”. So, ten exponent two (10²) is written 10 ^ 2 in a formula.

The square root is written SQRT (). For example, the square root of cent (√100) is written as SQRT (100) or sqrt (100).

Comparison operations: =, <> (different), <,>, <=,> =

The logical operations are interesting for the conditions: AND (), OR (), NOT (), XOR (), for example the condition to be able to calculate the density is that the “Volume” and the “Mass” are positive: “AND (obj.volume> 0, obj.mass> 0) ”

But you can use a multitude of other functions. Try in the interactive diagram above.

The available functions are to a large extent those available in Excel. Here is a list that includes these calculation functions.

Some of these functions are not really useful in LinkNotions because they apply to cell domains (eg A1 to B3). Such domains do not really exist in LinkNotions. Thus, in Excel one could use the formula “SUM (A1: B3). In LinkNotions it does not work. One could, however, indicate the concepts one after the other. So we can write the formula “SUM (A1, A2, A3, B1, B2, B3). One can also combine functions. For example to calculate the average of two sums one could write “AVERAGE (SUM (A1, A2, A3), SUM (B1, B2, B3)).

Here is a list of the functions available in Excel.

Here are the most important functions with a calculation example in LinkNotions.

**Enter a text :**

The inscription is done in the window “Calculations” which is under the diagram.

This text will most often be information or a decision. For example: “The amounts are very small”; “Please consult a doctor”; “Buy the product”; “Increase speed,” etc.

These texts are very useful with the conditions. In the above example with the decision to take off the ULM aircraft according to its weight, the decision displayed in the notion “Decision” (B5) is

“Authorized take-off” if the total weight is less than or equal to 450 kg

“Plane too heavy” if the total weight is more than 450 kg

The inscribed text can be any text, but it must be presented as an evaluable formula, otherwise you will get an error message.

Concretely this means that you have to put the text in single quotes or double quotes. The important thing is to use single quotes instead of typographic quotes (which Word also automatically).

‘This is possible’ (this is the apostrophe that must be used)

,This is not possible’

„This is not possible”

“This is possible” (on a Swiss French keyboard, please use the shift + 2).

Try in the interactive diagram above.

**To reference a notion in a formula**

You can use :

- the references of the notions
- the variable numbers of notions
- the fixed numbers of the notions
- the subnames for formulas (in the case of several values in a notion)

Here is the diagram with the calculation of the surface of a ground which illustrates all the possibilities above:

**The references of the notions**

(use the interactive diagram above by clicking on the notions and modifying the entries in the “Calculations” window)

You can give each notion a reference that you can imagine. In the case of the calculation of the surface, we could for example assign the reference “leng” to the Notion “Length” and the reference “wid” to the Notion “Width”.

The formula becomes:

Surface = leng*wid

**The reference of the notion is to be entered in the field “Reference for formulas” (1) to refer the main value and in the “Subname for formulas” field (2) to refer a sub-name of a notion.**

Note: The reference can only contain letters (uppercase or lowercase), numbers, and the “_” sign. You can not use: é; è; space ; …

Advantages of the method:

- You can choose a very short reference
- You can choose a reference that you will remember easily

Disadvantages

- It will be necessary to retain the reference or to seek it in the notion
- You must be careful not to use the same reference for two different notions.

**Variable numbers of notions**

The variable number is the place occupied by the notion in the diagram. Since in LinkNotions you can move notions, this number is necessarily variable.

In our example (if the Notion “Length” is in C4 and the Notion “Width” in C5), the formula becomes:

Area = C4 * C5 (or c4 * c5) (see the arrow in the diagram below)

**This is the method we prefer and recommend.** It is easy and fast.

In LinkNotions, each notion is automatically assigned an ID number when it is created.

So: If you create the Notion A, the software assigns it (for example) the ID number id1. This number can not be changed and remains assigned to the notion, even when it is moved.

To know the ID number, simply place the cursor above the notion. The tooltip that appears contains the ID number of the notion **(1)**. You will also find it in the Info window of each notion **(2)**.

This way of referencing is also convenient because the ID number is very short. It is composed of two letters “id” and a number.

**Subnames for formulas** (in the case of several values in a notion)

In the image below the notion “Field” (1) which is in A2 has two sub-names “Length” and “Width” (2). It also has a main sub-name “Surface” (3).

The value (1000) of the main subname is calculated using the value of the sub-names (50 x 20).

To refer sub-names in the formula, we must assign each sub-name a subname for formulas (4).

We have assigned the subname for formulas “long” to the subname “Length” and the subname for formulas “larg” to the subname “Width”.

To refer a sub-name of a notion one must refer the notion, followed by a point followed by the short sub-name. Example: “id1.long”

The formula for the main subname gets: id1.long * id1.larg (5). id 1 is the nr. of the notion “Terrain”.

Note that instead of nr. fixed to refer the notion “Terrain”, we could have taken his nr. variable (A2) or its reference “ter” (6).

**The error log**

If there is an error in a formula, the software reports it: the error register turns from green to red.

In the field under the error log, you get information about the causes of the error. In this case the cause is division by 0.

“Unable to evaluate id1 / id3 expression from Notion Notion C = A / B, value:. Error Code: # DIV / 0!”

Click on “Error Log” to get the contents of the “Calculations” window

Another example of an error type is the circular reference. This is the case when the notion A is calculated using the notion B and the notion B using the notion A.

Note that each notion that is referenced in a formula in other notions must have a value (amount or text), otherwise the error register button turns red.

As the explanations given are quite frugal, we suggest you to be careful when registering your formulas and recalculate after each small step. In this way you know that it is at the last step (the last inscription) that you have made a mistake.