[Andy Abel]

You can find some examples here. There is also excel file with Kalkulio definition available for download.

[Andy Abel]

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).

Product_pricing_calculator_v03Download

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

[Andy Abel]

Calculator is a set of input, output, warning and error tagged cells. They can be formatted, grouped into panels and displayed on one or multiple pages.

[Andy Abel]

What is filtering?

Filtering gives us an answer to this question: which options in option list B should be available when specific option in option list A is selected?

Options of option list B can be filtered by option selected in option list A.

Filtering is great to provide users only with desirable combination of options.

How to define filtering?

You can set up filtering in def_index_filter sheet of your spreadsheet.

On the first row, starting with column G, enter IDs of option list you want to filter by. Then in cells under each option list ID you can define filtering. To better understand filtering, let’s examine an example below.

Example

Sem vložiť embed číselníkov material a material type
Sem vložiť obrázok definície filtrovania číselníkov

There are two input cells: Material and Material type. Option list linked to Material type input cell is filtered by option list linked to Material input cell.

Let’s look at cell H11. There is a value: 1. It means following:

„When option with ID of 1 (Wood) from option list material_list (Material) is selected, then option on current row (Oak) will be available in the option list type_list“.

If we would leave cell H11 empty, Oak would be available in Material type input field without any dependency on option list material_list.

Good to know

1. One option list can be linked to many input cells.
2. Option list is filtered in every input cell that it is linked to.
3. When all options of an option list are filtered out, input cell with this option list is set as invisible. Input cell remains without any value, thus any visibility or editability conditions based on this input cell won’t function.

[Andy Abel]

1. Set up new sheet

1. Create new sheet with name def_index_filter
2. Copy the header from the following example file: Product_pricing_calculator.xlsx or define it manually (do not mind text in the cells, only data structure matters).

2. Define an option list

Option list is defined only on its first row (not on the rows with its options).

Option list ID* (column A)	Identifies option list, used when linking option list to input cell
Option list name* (column B)	Can be used for your description
Option list value type (column C)	Defines value type of Option calculation value. Use: • STRING for texts • NUMERIC for numbers • BOOLEAN for 1/0 value If left blank, value is considered as STRING.

3. Define options

Each row represents one option.

Option ID* (column D)	Identifies option, used when setting up filtering
Option calculation value* (column E)	Value Kalkulio sends to the input cells and uses for calculation
Option display text* (column F)	Value Kalkulio dislays in web calculator form

Sem vložiť obrázok viacerých vydefinovaných option listov
Sem vložiť obrázok prezentujúci rozdielne hodnoty Value a Display

[Andy Abel]

How to tag input and output cells in spreadsheet? Just insert a this tag into a cell comment:

@K{“input”:{“id”:”your_id_goes_here”,”label”:“Your label goes here”,“help”:”Your help text goes here”}}

Sem vložiť obrázok bunky, ktorá má zobrazený komentár s I/O tagom

Cell type
use input or output

Cell ID
ID is used when setting up which input & output cells should be a part of web calculator and when calling the calculator using API

Cell label text
Text displayed next to the input/output cell. This cell label text is a default text for every calculator that uses this input/output cell. It can be redefined for every calculator in Calculator definition.

Cell help text
Text displayed under the input/output cell. Cell help text is optional. This cell help text is a default text for every calculator that uses this input/output cell. It can be redefined for every calculator in Calculator definition.

You can create multiple web calculators from single excel file. Each calculator can use different input & output cells. So you can have more tagged input & output cells that you currently need for specific calculator.

[Andy Abel]

Tagged cells

Tagged cells are cells that you can use in your web calculator. Kalkulio app communicates only with tagged cells: either sends data to the spreadsheet calculation (input cell) or receives data from it (output cell).

Active cells

You can create multiple calculators from one spreadsheet. Each calculator can use different tagged cells. If you use tagged cell in calculator (you define it in definition of your calculator), we call this cell an active cell.

Active cell ignores value saved in spreadsheet and takes value either from:

• user input in form
• default value defined in Calculator definition.

If you do not use tagged cell in your calculator, it is called inactive cell. For inactive cell, calculator will use value saved in spreadsheet.

Visible cells

Visible cells are visible in the form of web calculator. There are 2 types:

1. input cells – cells that users enter data to,
2. output cells – cells with outcomes of calculation.

Invisible input cells are great, if you want to override values saved in spreadsheet with the values from calculator definition and don’t want to display them in the form.
Invisible output cells are handy when sending outcomes using API.

[Andy Abel]

Kalkulio supports following spreadsheet formulas:

ABS
ACOS
ACOSH
ADDRESS
AND
AREAS
ASIN
ASINH
ATAN
ATAN2
ATANH
AVEDEV
AVERAGE
CEILING
CHAR
CHOOSE
CLEAN
CODE
COLUMN
COLUMNS
COMBIN
CONCATENATE
COS
COSH
COUNT
COUNTA
COUNTBLANK
COUNTIF
DATE
DAY
DAYS360
DEGREES
DEVSQ
DGET
DMAX
DMIN
DOLLAR
DSUM
ERROR.TYPE
EVEN
EXACT
EXP
FACT
FALSE
FIND
FIXED
FLOOR
FREQUENCY
FV
GEOMEAN
HLOOKUP
HOUR
HYPERLINK

IF
INDEX
INDIRECT
INT
INTERCEPT
IPMT
IRR
ISBLANK
ISERR
ISERROR
ISLOGICAL
ISNA
ISNONTEXT
ISNUMBER
ISREF
ISTEXT
LARGE
LEFT
LEN
LN
LOG
LOG10
LOOKUP
LOWER
MATCH
MAX
MAXA
MDETERM
MEDIAN
MID
MIN
MINA
MINUTE
MINVERSE
MIRR
MMULT
MOD
MODE
MONTH
NA
NOT
NOW
NPER
NPV
ODD
OFFSET
OR
PERCENTILE
PI
PMT
POISSON
POWER
PPMT

PRODUCT
PROPER
PV
RADIANS
RAND
RANK
RATE
REPLACE
REPT
RIGHT
ROMAN
ROUND
ROUNDDOWN
ROUNDUP
ROW
ROWS
SEARCH
SECOND
SIGN
SIN
SINH
SLOPE
SMALL
SQRT
STDEV
SUBSTITUTE
SUBTOTAL
SUM
SUMIF
SUMPRODUCT
SUMSQ
SUMX2MY2
SUMX2PY2
SUMXMY2
T
TAN
TANH
TEXT
TIME
TODAY
TRANSPOSE
TREND
TRIM
TRUE
TRUNC
UPPER
VALUE
VAR
VARP
VLOOKUP
WEEKDAY
YEAR

[Andy Abel]

There are 2 types of input cells:

1. text input cells – user inputs characters and numbers (string, numeric),
2. cells with option lists – user selects an option from predefined list.

Option lists guide the user and do not allow him to enter wrong value.

[Author]

Posts