Validation Rule: Formulas

#EN #Excel #Vaidation

Updated over a week ago

Table of content


Validation rule to use

Use the traditional calculations while making sure to put the whole formula in between parentheses ("(" and ")").

Possible calculation tools:

  • Sum (+)

  • Substraction (-)

  • Multiplication (*)

  • Division (/)

  • Numeric Values

  • Field Names

  • Column Names

  • Additional Parentheses

  • Descriptive Statistics

Validation rules available

Standard Calculation

Allows to do any calculation as long as it is between two parentheses.

Example: (Field_1 + Field_2)
Example: ((Field_1 + Field_2) / 2)
Example: (Field_1 *5)

Descriptive Statistics

Allows to do a pre-programmed calculation to get a specific value from a column in a grid. The predefined calculations are:

Type

Description

Keyword to Use

Rule to Use

Sum

Gives the sum of the values in a targeted column.

num

sum(COLUMN_NAME)

Average

Gives the average of the values in a targeted column.

num

avg(COLUMN_NAME)

Count

Gives the number of cells filled in a targeted column.

num

count(COLUMN_NAME)

Minimum

Give the lowest value within a targeted column.

num

min(COLUMN_NAME)

Maximum

Gives the highest value within a targeted column.

num

max(COLUMN_NAME)

Median

Gives the value in the middle of all the values in a targeted column.

num

med(COLUMN_NAME)

Mode

Gives the values that are the most popular within a targeted column.

result

mode(COLUMN_NAME)

Range

Gives a value from the calculation of the highest value minus the lowest value in a targeted column.

num

range(COLUMN_NAME)

Standard Deviation

Gives the measure of the amount of variation or dispersion of a set of values in a targeted column.

num

sd(COLUMN_NAME)

Example: (sum(Column_1))
Example: (avg(Column_2))
Example: (count(Column_3))

Example: (min(Column_4))

Example: (max(Column_5))

Example: (med(Column_6))

Example: (mode(Column_7))

Example: (range(Column_8))

Example: (sd(Column_9))

Date/Time Calculation

Allows to do a calculation to add or remove a numeric value to a date or a time field.
The date (or time) field must always be first in this kind of calculation.

  • +d: Will add days to a date field

  • +M: Will add months to a date field

  • +y: Will add years to a date field

  • +m: Will add minutes to a time field

  • +h: Will add hours to a time field

Example: (Date_field +d Field_2)
Example: (Date_field +d 60)

Timespan Calculation

Allows to do calculate a date/time difference between two date and/or time fields.
⚠️Must be used with the Timespan keyword.

  • Date - Date: # day(s)

  • Time - Time: # hour(s) # minute(s)

  • Date/Time - Date/Time : # day(s) # hour(s) # minute(s)

Example: (Date_field1 - Date_field2)
Example: (Time_field1 - Time_field2)
Example: (Datetime_field1 - Datetime_field2)

Keywords available with this validation rule

Did this answer your question?