Formulas and functions

You can add functions to the spreadsheet in order to perform calculations, for example. Different formulas are available to you, depending on whether you are editing a model of the Spreadsheet type, a results table of a query, or an ad hoc analysis.

AND

Logical AND operation.

AND requires at least one argument. You can use single arguments and/or scope arguments.

Example

Value 1

Value 2

AND

FALSE

FALSE

FALSE

FALSE

TRUE

FALSE

TRUE

FALSE

FALSE

TRUE

TRUE

TRUE

=AND (TRUE;TRUE) returns the following:

AND

=AND(A1>100;A2>100) returns TRUE.

AND

ARIS_ATTRIBUTE

Indicates an attribute.

ARIS_ATTRIBUTE requires an API name of an attribute as input and displays the name of the attribute in the cell.

Example

The following formula outputs the name of the ARIS attribute Name. Required input is the API name.

=ARIS_ATTRIBUTE("AT_NAME")

ARIS_ATTRIBUTE

For result tables for queries or ad-hoc analyses, replace ARIS with QUERY or ANALYSIS in the formula.

ARIS_ATTRIBUTE_VALUE

Outputs an attribute value.

ARIS_ATTRIBUTE_VALUE requires an ARIS item and an attribute as input. You can use single arguments and/or scope arguments.

Example

For a model of type Spreadsheet, the following formula outputs the attribute value of the ARIS item that is displayed in cell A2 and whose Name attribute is specified in cell B2.

=ARIS_ATTRIBUTE_VALUE(A2;B2)

=ARIS_ATTRIBUTE_VALUE(ARIS_ITEM("GUID");ARIS_ATTRIBUTE("API name of the attribute"))

ARISATTRVALUE funtion

For result tables for queries or ad-hoc analyses, replace ARIS with QUERY or ANALYSIS in the formula.

ARIS_ITEM

Indicates an ARIS item.

An item is an:

  • ARIS model

  • ARIS object

  • ARIS connection

ARIS_ITEM requires a GUID with quotation marks as an argument. The input is entered in the following form:

=ARIS_ITEM("GUID")

Example

For a model of type Spreadsheet, the formula outputs the name of the ARIS item and the associated symbol.

The cell of the connection is selected. The formula outputs the name and the connection symbol.

=ARIS_ITEM("ff14b3a1-438e-11e2-2aa2-e33baa0a29cc")

ARIS item

For result tables for queries or ad-hoc analyses, replace ARIS with QUERY or ANALYSIS in the formula.

ARIS_ITEM_API_NAME

Returns the API name of an ARIS item.

An item is an:

  • ARIS model

  • ARIS object

  • ARIS connection

ARIS_ITEM_API_NAME requires an ARIS item as an argument.

Example

In this example, the API name of the item in cell A1 is output in cell B1.

=ARIS_ITEM_API_NAME(A1)

s_tc_item_api_name.gif

For result tables for queries or ad-hoc analyses, replace ARIS with QUERY or ANALYSIS in the formula.

ARIS_ITEM_DEF_SYMBOL

Returns the name of the default symbol for an ARIS object.

ARIS_ITEM_DEF_SYMBOL requires an ARIS item as an argument.

Example

In this example, the default symbol names for the items of cell A1 and A2 are displayed in cell B1 and B2.

=ARIS_ITEM_DEF_SYMBOL(A1)

=ARIS_ITEM_DEF_SYMBOL(A2)

ARIS_ITEM_DEF_SYMBOL

For result tables for queries or ad-hoc analyses, replace ARIS with QUERY or ANALYSIS in the formula.

ARIS_ITEM_GUID

Returns the GUID of the ARIS item.

ARIS_ITEM_GUID requires an ARIS item as input. This can be a cell containing an ARIS item, or the result of a calculation that returns an ARIS item.

Example

For a model of type Spreadsheet, the following formula returns the GUID of the ARIS item that appears in cell A1.

=ARIS_ITEM_GUID(A1)

ARIS-ITEM_GUID

For result tables for queries or ad-hoc analyses, replace ARIS with QUERY or ANALYSIS in the formula.

ARIS_ITEM_PATH

Returns the path to an ARIS item.

ARIS_ITEM_PATH requires an ARIS item as input.

Example

For a model of type Spreadsheet, the following formula returns the path to the ARIS item of cell A1.

=ARIS_ITEM_PATH(A1)

ARIS_ITEM_PATH

For result tables for queries or ad-hoc analyses, replace ARIS with QUERY or ANALYSIS in the formula.

ARIS_ITEM_TYPE

Returns the type of an ARIS item.

ARIS_ITEM_TYPE requires an ARIS item as input.

Example

For a model of type Spreadsheet, the following formula returns the type of an ARIS item of cell A1.

=ARIS_ITEM_TYPE(A1)

ARIS_ITEM_TYPE

For result tables for queries or ad-hoc analyses, replace ARIS with QUERY or ANALYSIS in the formula.

ARIS_ITEM_TYPE_NUM

Returns the type number of an ARIS item.

ARIS_ITEM_TYPE_NUM requires an ARIS item as input.

Example

For a model of Spreadsheet type, the following formula returns the type number of the ARIS item of cell A1.

ARIS_ITEM_TYPE_NUM=(A1)

ARIS_ITEM_TYPE_NUM

For result tables for queries or ad-hoc analyses, replace ARIS with QUERY or ANALYSIS in the formula.

ARIS_LOCKED_BY_USER

Indicates whether or not an ARIS item is locked by a user. The user is displayed in the Spreadsheet model.

An item is an:

  • ARIS model

  • ARIS object

ARIS_LOCKED_BY_USER requires a cell reference of the models or objects as an argument. The input is entered in the following form:

=ARIS_LOCKED_BY_USER(cell reference)

Example

For a model of type Spreadsheet, the formula outputs the name of the user who has locked models or objects. The cell of the object is selected.

=ARIS_LOCKED_BY_USER(A2)

ARIS_LOCKED_BY_USER

ARIS_WRITE_ATTRIBUTE_VALUE

Specifies the attribute value of an object.

ARIS_WRITE_ATTRIBUTE_VALUE requires as input an ARIS item, an attribute, and the value that the attribute is to receive. You can use single arguments and/or scope arguments.

Example

For a model of type Spreadsheet, the following formula sets the attribute value of the ARIS item that is displayed in cell A1 and whose attribute is specified in cell B1 to acceptable.

=ARIS_WRITE_ATTRIBUTE_VALUE(A1;B1;"acceptable")

For result tables for queries or ad-hoc analyses, replace ARIS with QUERY or ANALYSIS in the formula.

SPECIFY_ARIS_ATTRIBUTE

AVERAGE

Returns the average.

AVERAGE requires at least one argument. You can use single arguments and/or scope arguments.

Example

AVERAGE(A1:A5) returns the average of the values saved in the cells A1 to A5.

CHOOSE

Returns a value based on an index.

Indexing means the position of an item in a list. If for example, you have a list with the values 1,3,5,7,13, the item at position three has the value five.

CHOOSE requires at least two arguments. The first argument represents the index of the value that the corresponding item is selected from.

Input: CHOOSE(Position of the item, Item1..Item2)

Example

CHOOSE(1;2;4;6;8;19) returns 2

CHOOSE(3;2;4;6;8;19) returns 6

CONCATENATE

Concatenates strings that are separated by a semicolon.

CONCATENATE

COUNT

Calculates the number of arguments. You can use single arguments and/or scope arguments.

Example

=COUNT(A1;12;5) returns 3, because three arguments are transferred.

=COUNT(A1:A3;"Text") returns 4, because four arguments are transferred.

=COUNT(A1:A5) returns 5.

DATE

Converts three numeric arguments into a date.

The input of the arguments is made in the following form: =DATE(Year;Month;Day), also =DATE(YYYY;MM;DD)

Example

=DATE(2012;12;5) returns the date 5.12.2012.

DATE function

DAYS

Returns the number of days between two dates.

The date cannot be entered directly as an argument, but must be present in cells or be calculated by a formula that returns a value of the Date type. Two arguments are required.

The input of the arguments is made in the following form: =DAYS(Date;Date).

Example

=DAYS(A1;A2) returns 22 days.

Days

IF

Checks a condition and returns a value depending on the result.

IF requires three arguments. The input is entered in the following form:

IF(Condition;Condition met;Condition not met).

Example

When you enter =IF(A1>A2;1;0) you receive TRUE.

When you enter =IF(A1>A2;TRUE;FALSE) you receive the following:

IF

When you enter =IF(A1>10;"red";IF(A1>5;"yellow";IF(A1>2;"green";"black"))) you receive the following:

IF

IS_CONNECTED_BY

Returns the specified string if source object and target object are connected via the respective connection type

IS_CONNECTED_BY requires as input at least one ARIS source object, an ARIS target object, the API name of the connection type, and the string to be displayed if this relationship exists at the definition level. You can use single arguments and/or scope arguments.

Example

The following formula outputs the string OK, if the source object is connected to the target object via the connection activates with the API name CT_ACTIV_1.

=IS_CONNECTED_BY(A1;B1;"CT_ACTIV_1";"OK")

IS_CONNECTED_BY

MAX

Returns the maximum value in a list of arguments. MAX requires at least one argument. You can use single arguments and/or scope arguments.

Example

=MAX(A1;A4:A6) returns 123.

MAX

MIN

Returns the minimum value in a list of arguments. MIN requires at least one argument. You can use single arguments and/or scope arguments.

Example

=MIN(A1;A4:A6) returns 7.

MIN

NETWORKDAYS

Returns the work days between two dates.

NETWORKDAYS requires two arguments and returns the work days assuming that Saturday and Sunday are days off.

Example

WORKDAYS

NOT

Logical inversion.

NOT requires the argument True or False.

NOT(TRUE) or NOT(1) returns FALSE and NOT(FALSE) or NOT(0) returns TRUE.

Example

NOT(TRUE)

NOT(TRUE)

NOT(FALSE)

NOT(FALSE)

NOW

Returns the current time.

NOW does not require any arguments.

The input is entered in the following form:

=NOW()

The NOW() function refreshes the time each time a calculation is carried out in the spreadsheet.

OR

Logical OR operation.

OR requires at least one argument.

Example

Value 1

Value 2

OR

FALSE

FALSE

FALSE

FALSE

TRUE

TRUE

TRUE

FALSE

TRUE

TRUE

TRUE

TRUE

SQRT

Returns the square root of a number.

SQRT requires an argument.

Example

=SQRT(4) returns two.

STANDARDIZE

Normalizes to one hundred percent.

STANDARDIZE requires three arguments and normalizes a value that lies between a minimum and maximum value. The calculation is made as follows:

The first argument represents the value, the difference of the two following ones the range that is normalized to one hundred percent.

(Value - Minimum) / (Maximum - Minimum)

STANDARDIZE is mainly used for a cell with conditional formatting to highlight the maximum value reached by not coloring the cell at all, coloring it partially, or coloring it completely.

Example

STANDARDIZE(2;0;10) equates to (2-0)/(10-0) and results in 0,2

STANDARDIZE(7;3;10) equates to (7-3)/(10-3) and results in 0.571

SUM

Returns the sum of argument values.

SUM requires at least one argument.

Example

=SUM(2;3) returns five.

SWITCH

Checks a condition and reacts according to the defined conditions.

SWITCH requires at least three arguments: a condition and a value that is displayed when the condition is met, and a default value that is displayed when none of the conditions listed is met.

It is also possible to link conditions with the IF formula. This however, would require a longer expression.

Example

=SWITCH(A1>10;"red";A1>5;"yellow";A1>2;"green";"black")

SWITCH

In comparison, the expression when IF is used:

=IF(A1>10;"red";IF(A1>5;"yellow";IF(A1>2;"green";"black")))

TIME

Returns a time.

TIME requires three arguments.

The input is entered in the following form:

=TIME(Hour;Minute;Second)

Example

=TIME(2;23;59) returns 2:23:59

TIME

The input of =TIME(25;23;59) returns 1:23:59.

The input of =TIME(2;23;61) returns 2:24:01.

TODAY

Returns the current date.

TODAY does not require any arguments.

The input is entered in the following form:

=TODAY()

The TODAY() function refreshes the date each time a calculation is carried out in the spreadsheet.

XOR

Logical XOR operation.

XOR requires at least one argument.

Example

A1

A2

XOR

FALSE

FALSE

FALSE

FALSE

TRUE

TRUE

TRUE

FALSE

TRUE

TRUE

TRUE

FALSE