Smart Columns

Published On February 26, 2019

Introduction

Smart columns are a great way for you to quickly calculate sums on your entire dataset, and have them just as editable as any other column!

Caveats

There are a few things you should bear in mind,

Operators

The formula syntax supports these basic operations, the syntax is parsed in a left to right approach (not BODMAS). Please use brackets to achieve desired functionality.

Operator
Description
+
Add
-
Subtract
/
Divide
*
Multiply
%
Modulus
&
Concatenate (Strings) 
(
Start group
)
End group
A-Z
Reference column
12.3
Decimal
FUNC(a,b) Functions (see below)

List of functions

The formula box provides access to many functions that can be useful

Decimal Functions
Function Description
CONCAT(a,b) Appends b onto a
LEFT(a,x) First x characters of a
RIGHT(a,b) Last x characters of a
SUBSTITUTE(a, b, c) Replaces all instances of b with c located in a
ADD(a,b) Adds b to a
SUBTRACT(a,b) Subtracts b from a
MULTIPLY(a,b) Times a and b
DIVIDE(a,b) Divides a by b
MOD(a,b) Remainder of a by b
MAX(a,b) Greater of a or b
MIN(a,b) Lesser of a or b
ROUND(a,x) Rounds a to x decimal places
POW(a, b) Takes a to the power of b
IF(a, b, c) Checks a is true, returns b otherwise returns c
COL(x) Returns the Xth column
AND(a,b) Checks if a and b are both true
OR(a,b) Checks if a or b are either true
XOR(a,b) Checks if one of a or b is true
EQUALS(a,b) Checks if a and b are the same value
NOTEQUALS(a,b) Checks if a and b are different values
NOT(a) Checks if a is true, returns false, vice versa
GT(a,b) Checks if a is greater than b
LT(a,b) Checks if a is less than b
String Functions
Concat(string1, string2) Appends string2 onto string1
Left(string, No characters) First X characters of the string
Right(string, No characters)
Last X characters of the string
Substitute(string, old, new)
Replaces all instances of old with new located in string
Len(string)
Returns the number of characters in a string
Conditional Functions
IF(condition, true, false) Returns the true value or false value depending on the condition equalling 1
Lookup Functions
Col(index) Returns the value in the specified column

Examples

Add a £ symbol to the A column values

\"£\"&A 

Total a row and add a £ symbol

\"£\"&A+B+C 

Find the average of a set of columns

(A+B+C)/3 

Add 20% tax to a column

A*1.2
Tutorials
Back