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

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 |

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**

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