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
The editor does not function like a traditional grid based editor in that your file is not kept in memory, this allows you to edit and insert rows/columns in multi gigabyte files with zero latency, however there are limitations imposed on the smart functions:
- The formula can only lookup values in the current row.
- Smart columns evaluate from left to right. If you want to reference another smart column, the referenced column should be to the left. This is to ensure there are no circular dependencies.
- Deleting a source column will result in a #REF lookup error.
- Saving the file will commit the smart columns, only then can you delete the source columns.
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 (equivalent of +) |
SUBTRACT(a,b) | Subtracts b from a (equivalent of -) |
MULTIPLY(a,b) | Times a and b (equivalent of *) |
DIVIDE(a,b) | Divides a by b (equivalent of /) |
MOD(a,b) | Remainder of a by b (equivalent of %) |
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
Function | Description |
|---|---|
Concat(string1, string2) | Appends string2 onto string1 (equivalent of &) |
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 |
Regex(string,pattern,group) | Return the selected regex group from a pattern match |
IsMatch(string, pattern) | Returns true if the regex pattern was found in the string |
Reverse(string) | Reversed the string |
Conditional Functions
Function | Description |
|---|---|
IF(condition, a, b) | Checks the condition and returns A if true or B otherwise |
Lookup Functions
Function | Description |
|---|---|
Col(index) | Returns the value in the specified column for each row (equivalent of A, B, C etc) |
Examples
Prefix a £ symbol to A column values
\'£\'&A
Find the Sum of columns A B and C, and prefix a £ symbol
\"£\"&A+B+C
Find the average of columns A, B and C
(A+B+C)/3
Calculate column A with an additional 20% tax (rounded)
ROUND(A*1.2, 2)
Extract the first name from a first/last pair using regex
REGEX(B,'^(.+)\\s(.+)',1)
Print even or odd if there are an even or odd number of characters in a column
IF(LEN(B) % 2, 'Even', 'Odd')
Swapping Columns A and B
You can add two new columns C and D,
C will reference B
B
and D will reference A:
A
Now save the file to commit both smart columns
Once the file reloads you can delete Colums A and B to leave only the reversed columns:
