Defining a calculator

You can define multiple web calculators in single Excel spreadsheet file. Here’s how:

1. Set up new sheet

1. Create new sheet with name def_function

2. Copy the header (columns A to Z) from the following example file below (do not mind text in the cells, only data structure matters).

2. Define a calculator

Web calculator is defined on the first row of def_function worksheet, columns A – H (black background).

Column	Column name	Description
A	ID*	Identifies calculator, used: • in calculator URL when shared: your-domain.kalkulio.com/calculators/calculator-id • when calling calculator via API. Required.
B	Name*	Displayed in calculators view. Required.
C	Description	Displayed in calculators view.
D	Group	Calculator can be assigned to a group for your better orientation when you have many calculators. Groups can be found in web calculators view when you log in into your account.
E	Icon	Leave empty.
F	Icon color	Leave empty.
G	Interface	Enter „service“ (without parenthesis).
H	Role visibility	Cell can be set as visible according to role assigned to user. User role can be set by administrator in Settings/Users.

3. Define active input & output cells

Define one I/O cell per each row. I/O cells listed in web calculator definition are called Active I/O cells.

When I/O cell is not listed in web calculator definition, currently saved value in spreadsheet is applied. For better control we recommend to list all I/O cells instead and hide them instead of not listing them at all.

Basic settings

ID* (column I)

Required

Label text (column J)

Text displayed next to the input/output cell. Overrides Default I/O label text.
Default I/O label text is defined in cell comment tag. Default I/O label text aplies when this setting is left empty in web calculator definition.

Help text (column K)

Text displayed under the input/output cell. Overrides Default I/O help text.
Default I/O help text is defined in cell comment tag. Default I/O help text aplies when this setting is left empty in web calculator definition.

Option list (column L)

If an option list should be displayed to select cell value, enter its option list ID. Option list ID can be found on def_index_filter sheet, column A.

Default value (column M)

Default value is used, when I/O cell is set to invisible (column V of calculator definition).
When there is option list linked to I/O cell, default value must be defined in that list. Default value will not be used when filtered out.

Advanced settings

Protocol lists all input and output cells of calculator. Kalkulio can send this data another party that can process it. For instance: to save it to a database, generate a document, etc.

Protocol group label (column N)

On protocol page, I/O cells can be arranged into groups. Group label is a name of a group.

Protocol visibility (column O)

Accepted values:

• Blank – visible
• 1 – visible
• 0 – invisible

Visual settings

Required? (column P)

Accepted values:

• 1 – required
(I/O cell must be filled out with value otherwise web calculator returns an error)

• 0 – not required
(I/O call can be left blank Blank – not required: I/O call can be left blank)

• Blank – required

Visibility to roles (column Q)

Leave empty.

Displayed on page* (column R)

Web calculator can consists of multiple pages.
Maximal number of pages is 10.
Define on which page cell should be displayed (although it may be still set as invisible).
I/O cell can be displayed only once per web calculator.

Accepted values:

• 1 – 10
• Blank – I/O cell is displayed on the first page.

Page & protocol order (column S)

Accepted values:

• Blank – order according to order that I/O cell appears in web calculator definition
• Any positive number – The lowest number is 1, displays at the top.

Panel label text (column T)

Panels visually group I/O cells. If you want to include I/O cells in a panel, enter panel label (name). Leave empty if you don’t want to display I/O cell in a panel.

Panel color (column U)

2 values expected separated by space:

1st value – color of panel heading background
2nd value – color of heading text Values can be defined as:
• HEX – e.g. #000000 for black
• RGB – rgb(0,0,0) for black

You can use this tool to pick the color: https://htmlcolorcodes.com/color-picker/

Visibility (column V)

Accepted values:

• Blank – visible
• 1 – visible
• 0 – invisible
• TRUE – visible
• FALSE – invisible

There are 2 types of conditions that can be used:

1) Simple condition Condition based on value of an input cell

Syntax:
I/O cell ID==Calculation value
(I/O cell will be displayed when I/O cell ID equals Calculation value)

I/O cell ID!=Calculation value
(I/O cell will be displayed when I/O cell ID does not equal Calculation value)

2) Formula condition You can use any of supported functions to define the condition.

Accepted outcomes of formula:
• TRUE – visible
• FALSE – invisible

Editability (column W)

Accepted values:

• Blank – editable
• 1 – editable
• 0 – uneditable
• TRUE – editable
• FALSE – uneditable

There are 2 types of conditions that can be used:

1) Simple condition
Condition based on value of an input cell
Syntax:

I/O cell ID==Calculation value
(I/O cell will be editable when I/O cell ID equals Calculation value)

I/O cell ID!=Calculation value
(I/O cell will be editable when I/O cell ID does not equal Calculation value)

2) Formula condition
You can use any of supported functions to define the condition.
Accepted outcomes of formula:

• TRUE – editable
• FALSE – uneditable

Label width (column X)

Defines the width of label displayed next to I/O cell.

Accepted values:
• 1 – 12

Value summed with the Cell width must equal 12.

Cell width (column Y)

Defines the width of I/O cell.

Accepted values:
• 1 – 12

Value summed with the Label width must equal 12.

Panel width (column Z)

Defines the width of panel.

Accepted values:
• 1 – 12