Type |
Explanation |
Output |
NUMBERS |
0 |
|
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 |
|
|
|
|
|
|
|
|
|
|