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!
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.
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.
|FUNC(a,b)||Functions (see below)|
List of functions
The formula box provides access to many functions that can be useful
|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|
Add a £ symbol to the A column values
Total a row and add a £ symbol
Find the average of a set of columns
Add 20% tax to a column