**What are Formulas?****How are they different from spreadsheet formulas? ****Types of Formulas **

## Formulas

You can represent a single column type in a different way or compute between two or more different column types (Text or Number) using Formulas.

It may involve numerical operations, logical operations, functions and text operations that operate on columns.

## How are they different from spreadsheet formulas?

Unlike spreadsheet formulas, that operate on individual cell (A2, B3), ** formulas in Stackby operate on each column (defined by column name).** It functions like a database, and columns are first class citizen instead of cells.

*Please note that Formulas are still in beta!*

## Types of Formulas

- Numerical operators & Functions
- Logical operators & Functions
- Date and Time Functions

A note about formula syntax:

- All functions are denoted in upper case and the parameters required for them are surrounded by round brackets ( )
- All Optional parameters are surrounded by square brackets [ ]
- All column type parameters are surrounded by curved braces { }

#### Numerical operators & Functions

*Numerical operators:*

- Numeric operators are used with logical functions
- Numerical operators return a numeric value
- We support 4 numerical operators

`'+' : Adds two numerical values`

Example: {Column Name} + 3

`'-' : Subtracts two numerical values `

Example: {Column Name} - 3.00

`'*' : Multiply two numerical values`

Example: {Size}*{Price}

`'/' - Divide two numerical values `

Example: {Weight}/{Height}

Numerical functions:

`ABS(value) : Returns the absolute value.`

AVERAGE(number1, [number2, ...]) : Returns the average of the numbers.

CEILING(value, [significance]) : Returns the nearest integer multiple of significance that is greater than or equal to the value. If no significance is provided, a significance of 1 is assumed.

COUNT(number1, [number2, ....]) : Count the number of numeric items.

COUNTA(textOrNumber1, [number2, ....]) : Count the number of non-empty values. This function counts both numeric and text values.

COUNTALL(textOrNumber1, [number2, ....]) : Count the number of all elements including text and blanks.

EVEN(value) : Returns the smallest even integer that is greater than or equal to the specified value.

EXP(power) : Computes Euler's number (e) to the specified power.

FLOOR(value, [significance]) : Returns the nearest integer multiple of significance that is less than or equal to the value. If no significance is provided, a significance of 1 is assumed.

INT(value) : Returns the greatest integer that is less than or equal to the specified value.

LOG(number, base=10) : Computes the logarithm of the value in provided base. The base defaults to 10 if not specified.

MAX(number1, [number2, ...]) : Returns the largest of the given numbers.

MIN(number1, [number2, ...]) : Returns the smallest of the given numbers.

MOD(value1, divisor) : Returns the remainder after dividing the first argument by the second.

ODD(value) : Rounds positive value up the the nearest odd number and negative value down to the nearest odd number.

POWER(base, power) : Computes the specified base to the specified power.

ROUND(value, precision) : Rounds the value to the number of decimal places given by "precision." (Specifically, ROUND will round to the nearest integer at the specified precision, with ties broken by rounding half up toward positive infinity.)

ROUNDDOWN(value, precision) : Rounds the value to the number of decimal places given by "precision," always rounding down, i.e., toward zero. (You must give a value for the precision or the function will not work.)

ROUNDUP(value, precision) : Rounds the value to the number of decimal places given by "precision," always rounding up, i.e., away from zero. (You must give a value for the precision or the function will not work.)

SQRT(value) : Returns the square root of a nonnegative number.

SUM(number1, [number2, ...]) : Sum together the numbers. Equivalent to number1 + number2 + ...

VALUE(text) : Converts the text string to a number.

#### Logical Operators & Functions

*Logical operators:*

- Logical operators are used with logical functions
- Logical operators return a TRUE or FALSE value when compared with the data in cells. This value is then used by logical functions for further processing.
- We support 6 logical operators

`'>' : Greater than`

Example: {ColumnName} > 15

'<' : Less than

Example: {ColumnName} < 15

'>=' : Greater than or Equal to

Example: {ColumnName} >= 15

'<=' : Less than of Equal to

Example: {ColumnName} <= 15

'==' : Equal to

Example: {ColumnName} == 15

'!=' : Not Equal to

Example: {ColumnName} != 15

*Logical functions:*

`AND(logical1, [logical2, ...]) : Returns true if all the arguments are true, returns false otherwise. `

Example: AND({Employee}, {Employer})

BLANK() : Returns a blank value.

Example: IF({Value} > 1000000, "Value less than 1000000", "Value more than 1000000")

ERROR() : Returns the error value.

Example: IF({Grade} == "F", ERROR(), "OK")

FALSE() : Logical value false.

IF(<Condition>, <exp1>, <exp2>) : Returns 'exp1' if the logical argument is true, otherwise it returns 'exp2'.

- Exp1/Exp2 can either be values, text, or other conditions

- You can also add more IF statements in exp1/exp2

- Can also be used to check if a cell is blank/is empty.

Example:

- IF({GPA} > 2.5, "Good", "Bad")

- IF({Date} = BLANK(), "Please enter date", "Date entered")

- IF({Sky} == "Clear", IF({Temp} < 30, "Great weather!", "I'm burning!"), "Disappointing Weather.")

ISERROR(expr) : Returns true if the expression causes an error.

Example: ISERROR(SQRT(-99))

NOT(boolean) : Reverses the logical value of its argument.

Example: NOT(Total > 50)

OR(logical1, [logical2, ...]) : Returns true if any one of the arguments is true.

Example: OR({Fire}, {Smoke})

TRUE() : Logical value true.

Example: IF({Value} > 50, TRUE(), FALSE())

XOR(logical1, [logical2, ...]) : Returns true if an odd number of arguments are true.

Example: XOR(TRUE(), {Complete}, {Incomplete})

#### Text Functions

`CONCAT() : Concatenates all the column values together `

Example: CONCAT({Column1},{Column 2},{Column 3})

Note: If you need space between columns, you need add it like below

CONCAT({Column1}," ",{Column 2}," ",{Column 3})

#### Data and Time Functions

`NOW () : Returns the current time and day.`

Example: DATE_ADD(NOW(), 5, "days")

TONOW() : Displays relative time to now.

Example : TONOW({Deadline})

FROMNOW() : Displays relative time from now.

Example: FROMNOW({Deadline})

CREATED_TIME() : Returns the creation time of the current record.

Example: CREATED_TIME()

DATE_ADD() : Adds a "count" units to Datetime, such as DATE_ADD(Datetime, count, unit)

Example: DATE_ADD(Datetime, 5, "days")

DATE_SUB() : Substracts a "count" units to Datetime such as DATE_SUB(Datetime, count, unit)

Example: DATE_SUB(Datetime, 5, "days")

DATE_DIFF() : Returns the difference between datetimes in specified units such as DATE_DIFF(From, To, units)

Example: DATE_DIFF({Deadline}, NOW(), "days")

DATETIME_FORMAT(): Formats a datetime into a specified string such as DATETIME_FORMAT(Datetime, format string)

Example: DATETIME_FORMAT(NOW(), "YYYY-MM")

DATETIME_PARSE() : Parses the datetime string with optional format and locale such as DATETIME_PARSE(Datetime, format, local)

SET_LOCALE() : Sets a specific locale for the datetime, such as SET_LOCALE(Datetime)

Example: SET_LOCALE(NOW(), "ru")

SET_TIMEZONE() : Sets a specific timezone for a datetime such as SET_TIMEZONE(Datetime, timezone)

Example: SET_TIMEZONE(NOW(), "Australia/Sydney")

MONTH() : Returns the month part of the datetime such as MONTH(date)

Example: MONTH({Completion date})

YEAR() : Returns the year part of the datetime such as YEAR(date)

Example: YEAR({Completion date})

DAY() : Returns the day part of the datetime such as DAY(date)

Example: DAY({Completion date})

TIMESTR() Formats a datetime into a time-only string (HH:mm:ss) such as TIMESTR(Datetime)

Example: TIMESTR(NOW())

DATESTR() : Formats a datetime into a string (YYYY-MM-DD) such as DATESTR(Datetime)

Example: DATESTR(NOW())

HOUR() : Returns the hour part of the datetime such as HOUR(date)

Example: HOUR({Completion date})

MINUTE() : Returns the minute part of the datetime such as MINUTE(date)

Example: MINUTE({Completion date})

SECOND() : Returns the second of the datetime such as SECOND(date)

Example: SECOND({Completion date})

WEEKDAY() : Returns the day of the week as an integer between 0 (Sunday) and 6 (Saturday)

Example: WEEKDAY({Date})

WORKDAY_DIFF() : Counts the number of working days between startDate and endDate. Working days exclude weekends and an optional list of holidays, formatted as a comma-separated string of ISO-formatted dates such as WORKDAY_DIFF(startDate, endDate, [holidays])

Example: WORKDAY_DIFF({Assignment date}, {Due Date}, "2019-09-04,2019-10-09,2019-11-10")

ISAFTER() : Checks if date A is after B such as ISAFTER(A, B)

Example : ISAFTER({Deadline},NOW())

ISBEFORE() : Checks if date A is earlier than B such as ISBEFORE(A, B)

Example: ISBEFORE({Deadline}, NOW())

ISSAME() : Checks if the datetimes are the same up to some unit such as ISSAME(A, B, units)

Example: ISSAME({Deadline}, NOW(), "hour")