Calculated Column Cheat Sheet
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$130.00 [Cost]*(1+[Markup])
Commission Gives the commission due on a sale (bad on a commission %)$20.00 [Sale]*[Commission]
Formatting Formmatted with $ curency, comma thousand perator and 2 decimal places$1,234.56 TEXT([Sales],"$#,###.00");
Negative numbers in brackets(95.99)
TEXT([Sales],"#,###.00;(#,###.00)");
Date and Time
Time only TEXT([DateTimeField],"hh:mm:ss")08:35:21
Weekday TEXT([DateField],"dddd")Tuesday TEXT([DateField],"ddd")Tue
Month TEXT([DateField],"mmmm")September TEXT([DateField],"mmm")Sep
Year TEXT([DateField],"yyyy")2011 TEXT([DateField],"yy")11
Combinations TEXT([DateField],"mmmm dd, yyyy"September 17, 2011
AM/PM Shows whether a time is AM or PM AM IF(HOUR([Time]) < 12,"AM","PM")
Day/Night Shows whether a time is day or night Day IF(AND(HOUR([Time])>6,HOUR([Time])<18),"Day","Night")
Wk commencing Shows the date of the first day of the week (uful for grouping by week)1/2/2011 [Date]-WEEKDAY([Date])+1
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 Quarter Shows which quarter a date falls in
"Q" & INT(MONTH([Date])/4)+1Q1
"Q" & INT(MONTH([Date])/4)+1 & "-" & YEAR([Date field])Q1-2010 Season Shows which ason a date falls in. Takes into account one month offt from quarter.
CHOOSE(INT((MOD(MONTH(When)+1,12)/4))+1,"Winter","Spring","Summer","Autumn")Spring 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 2011
Shows which fiscal year a date falls in (6th April)
IF([Date]<DATE(YEAR([Date]),4,1), RIGHT(YEAR([Date])-1,2) & "/" &
RIGHT(YEAR([Date]),2), RIGHT(YEAR([Date]),2) & "/" & RIGHT(YEAR([Date])+1,2))10/11/2011 Other
Ref. number Creates a fixed length reference number from a Number PLT00324 "PLT" & REPT("0",5-LEN(TEXT([Number]))) & TEXT([Number])
Random team Choos a team at random, bad on the time (in conds)Team B CHOOSE(MOD(TEXT(Created,"s"),2)+1,"Team A","Team B")
Marks out of ten Gives general comments on a mark out of ten Good CHOOSE(INT([Marks]/3),"Bad","Poor","Good","Great")
Modified Shows whether an item has been modified since creation
IF([Modified] > [Created], "Changed", "Original")Changed
Operators Description Example {Result} +, -, *, /Add, Subtract, Multiply, Divide
^Power (e.g. [Field]^2 = Squared)4^3 {64}
&Concatanate (put two text values or
fields together)
"4" & "3" {"43"}, [Field1] & [Field2]
Relational Operators
= (Equal To)> (Greater than)>= (Greater than or equal to)
<> (Not equal to)< (Less than)<= (Less than or equal to)
Text Description Example {Result}
LEN (Text)The length of Text LEN("Hello") {5}
LEFT (Text, Number)Return X characters from the left LEFT("I quite like camping", 6) {"I quit"} RIGHT (Text, Number)Returns X characters from the right RIGHT("I quite like camping",4) {"ping"}
MID (Text, Num1, Num2)Returns Number2 characters from the
middle of Text, starting at Number1MID("I quite like camping", 9, 11) {"like campin"}
SEARCH (Text1, Text2, Num)Returns the index of Text1 within Text2,
starting the arch at index Number
SEARCH("Banana", "Banana Banana", 4) {8} LOWER (Text)Text in lower ca LOWER("uRGEnT") {"urgent")
UPPER (Text)Text in upper ca UPPER("uRGEnT") {"URGENT")
PROPER (Text)Capitalize first letter of each word PROPER("uRGEnT TICket") {"Urgent Ticket") TRIM (Text)Removes spaces from the start and end TRIM(" some text ") {"some text"}
保洁员管理制度
CLEAN (Text)Returns Text without non-printable
characters added by clipboard or similar
CLEAN("Line1?? Line2??") {"Line1 Line2"}
REPLACE (T1, N1, N2, T2)Replaces Number2 characters starting at
Number1 from Text1 with Text2
REPLACE("Hello",2,4,"i") {"Hi") CONCATENATE (T1, T2, ...)Puts the values together into one string CONCATENATE("A","B","C") {"ABC"}
DOLLAR (Number, Decimals)Converts a number to a currency text, with
the given number of decimals
DOLLAR(12.567,2) {"$12.57"}
EXACT (Text1, Text2)Are the two text values identical?EXACT("Hi","hi") {Fal}
FIXED (Num, Dec, NoCommas)Returns Number with the given number of
decimals as text (commas optional)FIXED(1057.26,1,TRUE) {"1057.3"}, FIXED(1057.26,0,FALSE) {"1,057"}
REPT (Text, Number)Repeats Text the given Number of times REPT("Hi",3) {"HiHiHi"}
TEXT (Value, Format)Converts Value to a Text value, using
Format (e 'TEXT Formats' overleaf)
TEXT([Created], "yyyy|mm") {"2011|03"} Date/Time Description Example {Result}
YEAR (Date)Returns the year number from a date YEAR([Created]) {2011}
MONTH (Date)Returns the month number from a date MONTH([Created]) {3}
WEEKDAY (Date)Returns the weekday number from a date
or datetime (0 is Sunday)
WEEKDAY([Created]) {2}
DAY (Date)Returns the day number from a date DAY([Created]) {29}
HOUR (DateTime)Returns the hour (us 24h clock)HOUR([Created]) {13}
MINUTE (DateTime)Returns the minute from a datetime MINUTE([Created]) {51}
SECOND (DateTime)Returns the conds from a datetime SECOND([Created]) {26}
DATE(Year, Month, Day)Creates a date from the 3 numbers DATE(2011, 3, 29) {3/29/2011}
DATEDIF (Date1, Date2, Unit)The difference between two dates (Unit:
"Y" Years, "M" Months, "D" Days, and
more)
DATEDIF("3/29/2011","2/1/2011","M") {1}
DATEVALUE (Text)Converts Text to a Date DATEVALUE("3/29/2011") {3/29/2011} TIME (Hour, Minute, Second)Creates a time from the 3 numbers TIME(19,45,21) {1/1/1900 7:45:21 PM} TIMEVALUE (Text)Converts Text to a time value TIMEVALUE("19:45") {1/1/1900 7:45 PM} Math Description Example {Result}
ABS (Number)Makes a number positive if it is negative ABS(-1) {1}, ABS(1) {1}
EVEN (Number)Rounds Number up to the nearest even
number
EVEN(0.5) {2}, EVEN(-1) {-2}演讲稿作文
FACT (Number)Returns the factorial of a number ("x!" on a
calculator)
FACT(5) {5*4*3*2*1 = 120)
MOD (Number1, Number2)Returns the remainder of Number1 divided
by Number2
MOD(5, 4) {1}, MOD(2010, 20) {10}
ODD (Number)Rounds Number up to the nearest odd
number ODD(1.5) {3}, ODD(-2) {-3}
Calculated column reference
Math (cont.)Description Example {Result}
PI ()Returns Pi to 15 decimal places PI() {3.14159265358979} ROUND (Number1, Number2)Rounds Number1 to Number2 decimals ROUND(327.598, 1) {327.6}
ROUNDDOWN (Num1, Num2)Rounds Number1 to Number2 decimals,
always rounding down.
ROUNDDOWN(327.598, 1) {327.5}
ROUNDUP (Num1, Num2)Rounds Number1 to Number2 decimals,
always rounding up.
ROUNDUP(52.0001, 0) {53}
SIGN (Number)Returns -1 for negative numbers, 1 for
positive, and 0 when 0SIGN(-5.9786) {-1}, SIGN(20678) {1}, SIGN(0) {0}
SQRT (Number)Returns the square root SQRT(64) {8}
TRUNC (Number)Returns Number with decimals removed TRUNC(37.999999) {37}
VALUE (Text)Converts Text to a Number, Date or Time,
according to its format
VALUE("57") {57}, VALUE("00:05") {00:05}
AVERAGE (Num1, Num2, ...)Averages the Numbers, ignoring non-
小学五年级解方程
Number values AVERAGE(10, 0, "", "0") {5}, AVERAGE(5, 0, TRUE) {2.5}
AVERAGEA (Num1, Num2, ...)Averages the Numbers, non-Number
values are interpreted AVERAGEA(10, 0, "", "0") {2.5}, AVERAGEA(5, 0, TRUE) {2}
COUNT (Num1, Num2, ...)Averages the Numbers, ignoring non-
Number values COUNT(10, 0, "", "0") {2}, COUNT(5, 0, TRUE) {2}
COUNTA (Value1, Value2, ...)Counts all values, including empty text (""),
ignoring empty columns COUNTA(10, 0, "", "0") {4}, COUNTA(5, 0, TRUE) {3}
MAX (Number1, Number2, ...)Gets the largest of the numbers, including
Text fields containing numbers
MAX(0, 2, "76", 100) {100}
幼儿园教育目标
MAXA (Number1, Number2, ...)Gets the largest of the numbers, including
non-number values MAXA(0, 2, "76", 100, "Telephone", TRUE) {100}
MIN (Number1, Number2, ...)Gets the smallest of the numbers,
including Text fields containing numbers
幻灯片母版MIN(0, 2, "76", 100) {0}
MINA (Number1, Number2, ...)Gets the smallest of the numbers,
including non-number values MINA(0, 2, "76", 100, "Telephone", TRUE) {0}白耳黄鸡
SUM (Number1, Number2, ...)Returns the total of all Numbers and
number-like values
SUM(0, 2, "76", 100, TRUE) {179} Logical Description Example {Result}
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) {Fal}
Conditional values Description Example {Result}
CHOOSE(Num, Val1, Val2, ...)Returns the value corresponding to the
number. Up to 29 values can be ud.
CHOOSE(3, "A", "B", "C", "D") {"C"}
IF(Condition, Val1, Val2)If Conditon is true, return Value1,
otherwi return Value2IF([Modified] > [Created], "Changed", "Original") {"Changed" or "Original"}
Type/Error checking Description Example {Result}
ISBLANK (Value)Returns True if Value is empty, Fal
otherwi IF(ISBLANK([Assignee]), "Needs assigning", "Assigned")
ISERR (Value)Returns True if Value returns an error
(except #N/A), Fal otherwi
ISERR(#REF!) {True}
ISERROR (Value)Returns True if Value returns an error ISERROR(#REF!) {True}冬虫夏草花
ISLOGICAL (Value)Returns True if Value returns a logical
value (True or Fal), Fal otherwi
ISLOGICAL(FALSE) {True}
ISNA (Value)Returns True if Value returns error #N/A,
Fal otherwi
ISERR(#N/A) {True}
ISNONTEXT (Value)Returns True if Value is not text or is
empty, Fal otherwi ISNONTEXT(2) {True}, ISNONTEXT("Two") {Fal}
ISNUMBER (Value)Returns True if Value is a Number, Fal
otherwi
ISNUMBER(2) {True}, ISNUMBER("Two") {Fal} ISTEXT (Value)Returns True if Value is Text ISTEXT(2) {Fal}, ISTEXT("Two") {True} Columns that can't be ud
[Today] and [Me]Only available in default columns.Will NOT work correctly in calculated columns [ID]Only works on column addition/update. Fails to work from then on.
Lookup columns Currently unsupported爱简单
Calculated column reference (page 2)
