| Type | Explanation | Output |
| NUMBERS | | |
| Profit | Shows the percentage profit on a sale (tick “Show as percentage”) | 10% |
| ([Price]-[Cost])/[Cost] | |
| Markup | Gives a price from a cost and a percentage markup | $120.00 |
| [Cost]*(1+[Markup]) | |
| Commission | Gives the commission due on a sale (based on a commission %) | $25.00 |
| [Sale]*[Commission] | |
| Formatting | Formatted with $ curency, comma thousand seperator and 2 decimal places | $1234.56 |
| TEXT([Sales],”$#,###.00″); | |
| Negative numbers in brackets | (95.99) |
| TEXT([Sales],”#,###.00;(#,###.00)”); | |
| OPERATORS | | |
| & | Concatanate (put two text values or fields together)
4 & “3″ | 43 |
| ^ | Power (e.g. [Field]^2 = Squared)
4^3 | 64 |
| /, +, -, * | Divide, Add, Subtract, Multiply | |
| RELATIONAL OPERATORS | | |
| = (Equal to) | > (Greater than) | >= (Greater than or equal to) |
| <> Not equal to) | < (Less than) | <= (Less than or equal to) |
| DATE AND TIME | | |
| Time only | TEXT([DateTimeField],”hh:mm:ss”) | 01:21:51 |
| Weekday | TEXT([DateField],”dddd”) | Wednesday |
| TEXT([DateField],”ddd”) | Wed |
| Month | TEXT([DateField],”mmmm”) | October |
| TEXT([DateField],”mmm”) | Oct |
| Year | TEXT([DateField],”yyyy”) | 2012 |
| TEXT([DateField],”yy”) | 12 |
| Combinations | TEXT([DateField],”mmmm dd, yyyy” | October 17, 2012 |
| Fiscal Year | Shows which fiscal year a date falls in (1st October) | |
| FY & IF(DATE(YEAR([Date]), 10, 1)>[Date], YEAR([Date]), YEAR([Date])+1) | FY 2012 |
| Season | Shows which season a date falls in. Takes into account one month offset from quarter. | |
| CHOOSE(INT((MOD(MONTH(When)+1,12)/4))+
1,”Winter”,”Spring”,”Summer”,”Autumn”) | Spring |
| Quarter | Shows which quarter a date falls in | |
| Q & INT((MONTH([Date])-1)/3)+1 | Q1 |
| Q & INT((MONTH([Date])-1)/3)+1 & “-” & YEAR([Date]) | Q1-2012 |
| Week Number | Shows the week number (US style) | |
| ROUNDDOWN(([Date]-DATE(YEAR([Date]),1,1)+
WEEKDAY(DATE(YEAR([Date]),1,1))-WEEKDAY([Date])+1)/7,0)+1 | 5 |
| Week Commencing | Shows the date of the first day of the week (useful for grouping by week) | |
| [Date]-WEEKDAY([Date])+1 | 3/4/2012 |
| Day/Night | Shows whether time is day or night | |
| IF(AND(HOUR([Time])>6,HOUR([Time])<18),”Day”,”Night”) | Day |
| AM/PM | Shows whether a time is AM or PM | |
| IF(HOUR([Time]) < 12,”AM”,”PM”) | PM |
| OTHER | | |
| Modified | Shows whether an item has been modified since creation | |
| IF([Modified] > [Created], “Changed”, “Original”) | Changed |
| Marks out of ten | Gives general comments on a mark out of ten | |
| CHOOSE(INT([Marks]/3),”Bad”,”Poor”,”Good”,”Great”) | Great |
| Random String | Chooses a string at random, based on the time (in seconds) | |
| CHOOSE(MOD(TEXT(Created,”s”),2)+1,”String A”,”String B”, “String C”) | String C |
| TEXT | | |
| TEXT (Value, Format) | Converts Value to a Text value, using Format | 2012|04 |
| TEXT([Created], “yyyy|mm”) | |
| REPT (Text, Number) | Repeats Text the given Number of times | HelloHelloHello |
| REPT(“Hello”,3) | |
| FIXED (Num, Dec, NoCommas) | Returns Number with the given number of decimals as text (commas optional) | |
| FIXED(2044.23,1,TRUE) | 2044.23 |
| FIXED(2044.23,0,FALSE) | 2,044 |
| LEN (Text) | The length of Text | 4 |
| LEN(“Hola”) | |
| LEFT (Text, Number) | Return X characters from the left | |
| LEFT(“The Quick Brown Fox”, 5) | The Q |
| RIGHT (Text, Number) | Return X characters from the right | |
| RIGHT(“The Quick Brown Fox”, 5) | n Fox |
| MID (Text, Num1, Num2) | Returns Number2 characters from the middle of Text, starting at Number1 | |
| MID(“The Quick Brown Fox”, 4, 15) | Quick Brown |
| SEARCH (Text1, Text2, Num) | Returns the index of Text1 within Text2,starting the search at index Number | |
| SEARCH(“Banana”, “Banana Banana”, 4) | 8 |
| LOWER (Text) | Text in lower case | |
| LOWER(“Hello”) | hello |
| UPPER (Text) | Text in upper case | |
| UPPER(“Hello”) | HELLO |
| PROPER (Text) | Capitalize first letter of each word | |
| PROPER(“good morning”) | Good Morning |
| TRIM (Text) | Removes spaces from the start and end | |
| TRIM(” Hello “) | Hello |
| CLEAN (Text) | Returns Text without non-printable characters added by clipboard or similar | |
| CLEAN(“String1? String2??”) | String1 String2 |
| REPLACE (T1, N1, N2, T2) | Replaces Number2 characters starting at Number1 from Text1 with Text2 | |
| REPLACE(“Hello”,2,4,”i”) | Hi |
| CONCATENATE (T1, T2, …) | Combines the string values together into one string | |
| CONCATENATE(“A”,” Fine “,”Morning”) | A Fine Morning |
| DOLLAR (Number, Decimals) | Converts number to currency text, with the given number of decimals | |
| DOLLAR(11.267,2) | $11.27 |
| EXACT (Text1, Text2) | Checks if two text values are identical, returns boolean | |
| EXACT(“Hello”,”hello”) | False |
| MATH | | |
| SUM (Number1, Number2, …) | Returns the total of all Numbers and number-like values | |
| SUM(0, 2, “26″, 100, TRUE) | 128 |
| MINA (Number1, Number2, …) | Gets the smallest of the numbers, including non-number values | |
| MINA(0, 2, “26″, 100, “MyString”, TRUE) | 0 |
| MIN (Number1, Number2, …) | Gets the smallest of the numbers, including Text fields containing numbers | |
| MIN(0, 1, “26″, 100) | 0 |
| MAXA (Number1, Number2, …) | Gets the largest of the numbers, including on-number values | |
| MAXA(0, 2, “26″, 100, “MyString”, TRUE) | 100 |
| COUNTA (Value1, Value2, …) | Counts all values, including empty text (“”), ignoring empty columns | |
| COUNTA(5, 0,TRUE) | 3 |
| COUNT (Num1, Num2, …) | Averages the Numbers, ignoring non-Number values | |
| COUNT(5, 0,TRUE) | 2 |
| AVERAGEA (Num1, Num2, …) | Averages the Numbers, non-Number values are interpreted | |
| AVERAGEA(5,0, TRUE) | 2 |
| AVERAGE (Num1, Num2, …) | Averages the Numbers, ignoring non-Number values | |
| AVERAGE(10, 0, “”, “0″) | 5 |
| VALUE (Text) | Converts Text to a Number, Date or Time, according to its format | |
| VALUE(“00:05″) | 00:05 |
| TRUNC (Number) | Returns Number with decimals removed | |
| TRUNC(14.999999) | 14 |
| SQRT (Number) | Returns the square root | |
| SQRT(25) | 5 |
| SIGN (Number) | Returns -1 for negative numbers, 1 for positive, and 0 when 0 | |
| SIGN(-5.2786) | -1 |
| ROUNDUP (Num1, Num2) | Rounds Number1 to Number2 decimals, always rounding up | |
| ROUNDUP(22.0001, 0) | 23 |
| ROUNDDOWN (Num1, Num2) | Rounds Number1 to Number2 decimals, always rounding down | |
| ROUNDDOWN(122.492, 1) | 122.4 |
| ROUND (Number1, Number2) | Rounds Number1 to Number2 decimals | |
| ROUND(221.298, 1) | 221.6 |
| PI () | Returns Pi to 15 decimal places | |
| PI() | 3.14159265358979 |
| ODD (Number) | Rounds Number up to the nearest odd number | |
| ODD(1.5) | 3 |
| MOD (Number1, Number2) | Returns the remainder of Number1 divided by Number2 | |
| MOD(5, 4) | 1 |
| EVEN (Number) | Rounds Number up to the nearest even number | |
| EVEN(0.5) | 2 |
| ABS (Number) | Makes a number positive if it is negative | |
| ABS(-1) | 1 |
| LOGICAL | | |
| AND (Condition1, Condition2) | Returns True if both conditions are True | |
| AND(4>=3,3>2) | True |
| OR (Condition1, Condition2) | Returns True if either condition is True | |
| OR(4>=3, 3<2) | True |
| NOT (Condition1) | Returns the opposite to the condition | |
| NOT(1=1) | False |
| | |
| CHOOSE(Num, Val1, Val2, …) | Returns the value corresponding to the number. Up to 29 values can be used. | |
| CHOOSE(2, “A”, “B”, “C”, “D”) | B |
| IF(Condition, Val1, Val2) | If Conditon is true, return Value1, otherwise return Value2 | |
| IF([Modified] > [Created], “Changed”, Original) | Changed |
| ERROR & TYPE CHECKING | | |
| ISTEXT (Value) | Returns True if Value is Text | |
| ISTEXT(99) | False |
| ISNUMBER (Value) | Returns True if Value is a Number, oherwise False | |
| ISNUMBER(99) { | True |
| ISNONTEXT (Value) | Returns True if Value is not text or is empty, False otherwise | |
| ISNONTEXT(99) | True |
| ISNA (Value) | Returns True if Value returns error #N/A, otherwise False | |
| ISERR(#N/A) | True |
| ISLOGICAL (Value) | Returns True if Value returns a logical value (True or False), False otherwise | |
| ISLOGICAL(FALSE) | True |
| ISERR (Value) | Returns True if Value returns an error (except #N/A), otherwise False | |
| ISERR(#REF!) | True |
| ISBLANK (Value) | Returns True if Value is empty, otherwise False | |
| IF(ISBLANK([Attendee]) | Needs Attendee |
| FORBIDDEN COLUMNS | | |
| Lookup columns | Not supported | |
| [ID] | Only works on column addition/update, will not work from then on | |
| [Today] and [Me] | Only available in default columns |
