# 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,

- 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, save the file to commit the smart columns and you can then 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 |

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

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)