SharePoint Cheat Sheet.pdf | Mathematics | Science

Please download to get full document.

View again

of 4
All materials on our website are shared by users. If you have any questions about copyright issues, please report us to resolve them. We are always happy to assist you.
Information Report
Category:

Documents

Published:

Views: 7 | Pages: 4

Extension: PDF | Download: 0

Share
Related documents
Description
SharePoint 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 (based on a commission %) $20.
Transcript
  SharePoint  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 (based on a commission %)  $20.00  [Sale]*[Commission] Formatting Formmatted with $ curency, comma thousand seperator 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 (useful 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])-1)/3)+1  Q1 Q & INT((MONTH([Date])-1)/3)+1 & - & YEAR([Date])  Q1-2010  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  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 Chooses a team at random, based on the time (in seconds)  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  Web Parts for Microsoft SharePoint   www.pentalogic.net  SharePoint  OperatorsDescriptionExample {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) TextDescriptionExample {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 Number1 MID( I quite like camping , 9, 11) { like campin }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( uRGEnT ) { urgent )UPPER (Text) Text in upper case 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 ) {False}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 (see  'TEXT Formats' overleaf) TEXT([Created], yyyy|mm ) { 2011|03 } Date/TimeDescriptionExample {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 (uses 24h clock) HOUR([Created]) {13}MINUTE (DateTime) Returns the minute from a datetime MINUTE([Created]) {51}SECOND (DateTime) Returns the seconds 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} MathDescriptionExample {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} Web Parts for Microsoft SharePoint   www.pentalogic.net  Calculated column reference (page 2)  SharePoint  Math (cont.)DescriptionExample {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 0 SIGN(-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, ...)  - SUM(0, 2, 76 , 100, TRUE) {179} LogicalDescriptionExample {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) {False} Conditional valuesDescriptionExample {Result} CHOOSE(Num, Val1, Val2, ...) Returns the value corresponding to the number. Up to 29 values can be used. CHOOSE(3, A , B , C , D ) { C }IF(Condition, Val1, Val2) If Conditon is true, return Value1, otherwise return Value2 IF([Modified] > [Created], Changed , Original ) { Changed or Original } Type/Error checkingDescriptionExample {Result} ISBLANK (Value) Returns True if Value is empty, False otherwise IF(ISBLANK([Assignee]), Needs assigning , Assigned )ISERR (Value) Returns True if Value returns an error (except #N/A), False otherwise 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 False), False otherwise ISLOGICAL(FALSE) {True}ISNA (Value) Returns True if Value returns error #N/A, False otherwise ISERR(#N/A) {True}ISNONTEXT (Value) Returns True if Value is not text or is empty, False otherwise ISNONTEXT(2) {True}, ISNONTEXT( Two ) {False}ISNUMBER (Value) Returns True if Value is a Number, False otherwise ISNUMBER(2) {True}, ISNUMBER( Two ) {False}ISTEXT (Value) Returns True if Value is Text ISTEXT(2) {False}, ISTEXT( Two ) {True} Columns that can't be used [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 Web Parts for Microsoft SharePoint   www.pentalogic.net  Calculated column reference  (page 3)  www.pentalogic.net
We Need Your Support
Thank you for visiting our website and your interest in our free products and services. We are nonprofit website to share and download documents. To the running of this website, we need your help to support us.

Thanks to everyone for your continued support.

No, Thanks