Syntax

	excelFormula($formula)

Description

Creates an Excel formula that can be used in a cell. The function takes an Excel formula as a string and wraps it in a special format that is recognized and processed by the Excel creator. Formulas enable the creation of dynamic and interactive Excel tables in which values are automatically calculated based on other cell contents.

Parameters

Return value

Type: Map

A special map object that contains the formula information for processing by excelCell().

Examples

Simple summation formula

	excelCell(row: 5, col: 3, content: excelFormula("SUM(B3:D3)"))

Output: Cell with formula that calculates the sum of cells B3 to D3.

Conditional formula

	excelCell(row: 2, col: 4, content:
    excelFormula("IF(B2>1000, \"Überschritten\", \"Im Rahmen\")"),
    style: {"color": "blue"})

Output: Cell with conditional formula that displays "Exceeded" if B2 > 1000, otherwise "In range".

Complex calculations

	excelCell(row: 10, col: 5, content:
    excelFormula("AVERAGEIFS(D2:D100, B2:B100, \">=2024-01-01\", C2:C100, \"Marketing\")"),
    style: excelStyle(numberFormat: "0.00"))

Output: Two cells with the current date and time, suitably formatted.

Statistical formulas

	excelCell(row: 0, col: 0, content: excelFormula("TODAY()"),
    style: excelStyle(numberFormat: "DD.MM.YYYY"))

excelCell(row: 0, col: 1, content: excelFormula("NOW()"),
    style: excelStyle(numberFormat: "DD.MM.YYYY HH:MM"))

Output: Cell with conditional formula that displays "Exceeded" if B2 > 1000, otherwise "Within range".

Complex calculations

	excelCell(row: 10, col: 5, content:
    excelFormula("AVERAGEIFS(D2:D100, B2:B100, \">=2024-01-01\", C2:C100, \"Marketing\")"),
    style: excelStyle(numberFormat: "0.00"))

Output: Cell with complex calculation of the average value based on several conditions.

Statistical formulas

	excelCell(row: 15, col: 2, content: excelFormula("COUNTIF(A2:A100, \">1000\")"),
    style: excelStyle(background: "#FFF3CD"))

excelCell(row: 16, col: 2, content: excelFormula("MAX(C2:C50)"),
    style: excelStyle(background: "#D4EDDA"))

excelCell(row: 17, col: 2, content: excelFormula("MIN(C2:C50)"),
    style: excelStyle(background: "#F8D7DA"))

Output: Three cells with statistical calculations (number, maximum, minimum) and color highlighting.

Notes on use

• No equals sign: The formula should be specified without a leading "=" (e.g. "SUM(A1:A10)" instead of "=SUM(A1:A10)")
• Excel syntax: Use the standard Excel formula syntax with English function names
• Cell references: Cell references are specified as usual in Excel (e.g. "A1", "B2:C5")
• Text in formulas: Text values in formulas must be enclosed in double quotation marks (e.g. "Hello")
• Combination with styles: Formulas can be combined with all available styles

Supported formula types

• Mathematical functions: SUM, AVERAGE, COUNT, MAX, MIN, ROUND, etc.
• Logical functions: IF, AND, OR, NOT, etc.
• Text functions: CONCATENATE, LEFT, RIGHT, MID, UPPER, LOWER, etc.
• Date and time functions: TODAY, NOW, DATE, YEAR, MONTH, DAY, etc.
• Search and reference functions: VLOOKUP, HLOOKUP, INDEX, MATCH, etc.
• Statistical functions: AVERAGEIFS, COUNTIFS, SUMIFS, etc.