How to use Formulas in your RowShare tables?

RowShare formulas are based on the value of the other cells of the same row. Add a Formula column and specify the formula in this column’s settings. Yes, the formula is set by column and not by cell, which means that the formula will always be the same in a given column.

Start by adding a new column to your table, and click Formulas, then decide if your formula will show text or numbers. After having named your column and clicked on Add, and scroll to the bottom to enter the formula:

Formulas reference other column names between single quotes : 'Column A' + 'Column B'

A formula can also reference a column that is itself a formula. This lets you split a calculation into several successive columns, as you would in a spreadsheet.

When a formula becomes hard to read, split it: one column per calculation step, the last one assembling the result. Intermediate columns can be hidden.

In addition to referencing other columns, formulas can include operators and functions.

Operators

You can find below the most common operators.

Examples :
- 'Product A' + 'Product B' to add the amounts in columns Product A and Product B
- 'First Name' & " " & 'Last Name' to display the first name and last space split by a space

4 standard mathematic operations: +, -, *, /
operator "&" to concatenate text
Comparison operators: >, <, >=, <=, =, !=, <> (the last two ones are equivalent and mean "not-equal")
(bool1) And (bool2), (bool1) && (bool2), And (bool1, bool2): boolean operator that returns true if both expressions are true
(bool1) Or (bool2), (bool1) || (bool2), Or (bool1, bool2): boolean operator that returns true if at least one expression is true
Not (bool) or ! (bool) : returns the opposite of a boolean. true returns false and false returns true

RowShare formulas leverage Microsoft Power Fx. Please read https://learn.microsoft.com/en-us/power-platform/power-fx/operators to learn more these operators and more. Only a subset of Power Fx is available in RowShare: see What does not exist, below.

Functions

RowShare offers additional functions to manipulate numbers, text, dates and introduce logical operations. Here is a selection of the most commonly used.

As RowShare formulas leverage Microsoft Power Fx, you can find the comprehensive list of functions here: https://learn.microsoft.com/en-us/power-platform/power-fx/formula-reference-overview. Some functions do not apply to RowShare such as location-based functions.

Numbers

Max/Min(Number1, Number2, …) returns the largest/smallest value
Sum(Number1, Number2, ...) returns the sum of numbers
Average(Number1, Number2, ...) returns the arithmetic mean
Abs(Number) returns the absolute value of a number
Round(Number, DecimalPlaces) rounds to the specified number of decimal places
RoundUp(Number, DecimalPlaces) rounds away from zero
RoundDown(Number, DecimalPlaces) rounds toward zero
Sqrt(Number) returns the square root
Rand() returns a random decimal ≥0 and <1
RandBetween(Lower, Upper) returns a random integer between bounds (inclusive)
Text(Number, Format) formats Number with predefined formats. List of formats available here.

Time

Today() returns the current date (no time)
Now() returns the current date and time
Date(Year, Month, Day) returns a date built from parts
DateAdd(DateTime, NumberOfUnits, Unit) returns DateTime shifted by the given units
DateDiff(StartDateTime, EndDateTime, Unit) returns the difference between two dates/times in units
DateValue(Text[, LanguageTag]) converts text to a date value
Year(DateTime) returns the year component
Month(DateTime) returns the month number (1–12)
Day(DateTime) returns the day of month (1–31)
Weekday(DateTime[, StartOfWeek]) returns the day-of-week number (1–7)
Text(Date, Format) formats Date with predefined formats. List of formats available here.

Time units are: TimeUnit.Seconds, TimeUnit.Minutes, TimeUnit.Hours, TimeUnit.Days, TimeUnit.Months, TimeUnit.Quarters, TimeUnit.Years. Default time unit is TimeUnit.Days.

There is no end of month function. To get the last day of the month of a date, start from the first day of that month, add one month, then subtract one day:
DateAdd(DateAdd(Date(Year('My date'), Month('My date'), 1), 1, TimeUnit.Months), -1, TimeUnit.Days)

String

Len(Text) returns the number of characters in a string
Left(Text, NumberOfCharacters) returns the beginning characters of a string
Right(Text, NumberOfCharacters) returns the ending characters of a string
Mid(Text, StartPosition[, NumberOfCharacters]) returns a substring starting at a position
Lower(Text) converts all letters to lowercase
Upper(Text) converts all letters to uppercase
Trim(Text) removes extra spaces and trims leading/trailing spaces
Substitute(Text, OldText, NewText[, InstanceNumber]) replaces matching text (optionally only the nth match)
Find(FindText, WithinText[, StartingPosition]) returns the position of a substring (case-sensitive)
StartsWith(Text, Prefix) returns whether Text begins with Prefix
Split(Text, Separator) splits Text into a single-column table of substrings
Concatenate(Text1, Text2, …) or Text1 & Text2 joins strings together
IsMatch(Text, Pattern[, Options]) tests if Text matches a pattern (regex-like)
Coalesce(Text1, Text2, …) returns the first non-blank string

Logic

If(Condition1, Result1[, Condition2, Result2, …[, DefaultResult]]) returns the first Result whose Condition is true
Switch(Expression, Match1, Result1[, Match2, Result2, …[, DefaultResult]]) returns the Result for the first equal Match
With(Variables, Formula) defines one or more named values, then evaluates Formula using them
And(Condition1, Condition2, …) returns true if all conditions are true
Coalesce(Value1, Value2, …) returns the first non-blank value
Or(Condition1, Condition2, …) returns true if any condition is true
Not(Boolean) returns the logical negation of a Boolean

With avoids repeating the same expression in several places of a formula. For example:
With({total: 'Quantity' * 'Unit price'}, If(total > 1000, total * 0.9, total))
The total is computed only once, and a change only has to be made in one place.

Tests on values

IsBlank(Value) returns true if the value is blank (null)
Blank() returns a blank value (useful to clear a field)
IfError(Value, Fallback[, Value2, Fallback2, …]) returns Fallback if an error occurs while evaluating Value
IsMatch(Text, Pattern[, Options]) returns true if Text matches the pattern (regex-like)

An empty cell is Blank(). Blank() does not behave the same way everywhere: in a calculation, 0 + Blank() returns 0, but in a comparison, Blank() = 0 is false. To detect an empty cell, use IsBlank rather than a comparison.

IsBlank never returns true on a Formula column: a numeric formula with no result returns 0, not Blank(). Keep IsBlank for the columns users fill in, and test the value itself, for example = 0, on calculated columns.

Examples:
- to specify in a cell 1 if another cell is positive, and -1 if it is null or negative, use: If('COL1' > 0, 1 , -1). You could also have used Abs('COL1') to get the same result.
- to show the first name, unless there is no first name, in this case, show the last name: Coalesce('First Name','Last Name')

It is possible to add comments, useful for complex formulas. In formulas, lines starting with // are ignored and can therefore be used as comments.
Example :
// If the VAT column is checked, we multiply by 1.2
'Price' * if('VAT',1.2,1)

What does not exist

A formula only sees the columns of its own row. Functions that walk through a table or a list of values are not available: no ForAll, no Filter, no LookUp, no Concat.

Sum, Max, Min and Average work on the values passed as arguments, within the same row. Sum('Amount') returns the amount of the current row, not the sum of the Amount column.

No formula can add up several rows, nor the rows of child tables. To get a total across several rows, analyzing your table displays it on screen, and RowMerge or Zapier let you reuse it elsewhere.

Important Notes

Formulas do not function if column names include one of the following characters:
. (dot)
\ (backslash)
[ (opening square bracket)
] (closing square bracket)
You can show formulas as percentage or colored text: choose the column type you want, and then check "Formula" in its settings.

If a column name contains an apostrophe, double it in the formula. The column What is the client's name? is therefore written 'What is the client''s name?'.