# 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 (Google sheets, Excel etc). In order to allow you to edit and insert rows/columns in multi gigabyte files with zero latency, the file is kept firmly on disk and streamed as you view it, because of this - there are limitations imposed on the smart functions which you would not normally expect:

- The formula can only lookup values in the current row. Each row is processed individually and thus other rows are not referenceable.
- 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 column(s) the calculated value in each cell will be written to the csv.
- You can then reopen the file to delete 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

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 |

Trim(string) | Removes whitespace either side of the string |

Lowercase(string) | Converts the string to lowercase |

Uppercase(string) | Converts the string to uppercase |

Reverse(string) | Reverses the string |

Startswith(string, pattern) | Checks if the string starts with the pattern returns 1 or 0 |

Endswith(string, pattern) | Checks if the string ends with the pattern returns 1 or 0 |

Contains(string, patter) | Checks if the string contains the pattern returns 1 or 0 |

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 |

Conditional Functions

Conditional Functions

Function | Description |
---|---|

IF(condition, a, b) | Checks the condition and returns A if true or B otherwise |

Lookup Functions

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:

##### Checking a string contains Fish

IF(CONTAINS(A,'Fish'), 'Contains Fish', 'No Allergy')