SharePoint/Excel Formulas (Quick Notes for Calculated Fields)

This may be overkill, but I wanted to create a quick set of notes of available “Microsofty” formulas.

 

From an Excel Quick Reference

 

Add, divide, multiply, and subtract

Type an equal sign (=), use math operators, and then press ENTER.

=10+5 to add

=10-5 to subtract

=10*5 to multiply

=10/5 to divide

 

Use more than one math  operator in a formula

If a formula has more than one operator, Excel follows the rules of operator precedence

instead of just calculating from left to right. Multiplication is done before addition:

=11.97+3.99*2 is 19.95. Excel multiplies 3.99 by 2, and then adds the result to 11.97.

Operations inside parentheses take place first: =(11.97+3.99)*2 is 31.92. Excel adds

first and then multiplies the result by 2.

 

Type formulas correctly

Excel needs very precise instructions, which means that formulas must be typed exactly

as shown. Missing a comma or parenthesis, inserting an extra space, or misspelling a

function name, will produce errors.

Start each formula by typing an equal sign (=).

Place parentheses around function arguments.

Separate multiple arguments within parentheses by using a comma or commas.

 

Change capital letters

In cell B1, type =PROPER(A1) to change to initial capitals: Nancy Davolio

In cell B1, type =UPPER(A1) to change case to all capitals: NANCY DAVOLIO

In cell B1, type =LOWER(A1) to change case to no capitals: nancy davolio

 

Delete extra spaces

In cell B1, type =TRIM(A1) to remove all spaces except the one between words.

 

Count characters  in a cell

In cell B1, type =LEN(A1)

 

Combine first and last names in one cell

In cell C1, type =A1&" "&B1. The result is "Nancy Davolio" in cell C1. The space

between the quotation marks in the formula inserts a space between the two names.

To reverse the order of the names, type =B1&", "&A1. The result is "Davolio, Nancy"

in cell C1. The comma and the space between the quotation marks in the formula insert

a comma and a space between the names.

Note You can also combine text by using the CONCATENATE function:

=CONCATENATE(A1," ",B1), but it's faster to just type the ampersands.

 

Compare cells

In cell C1, type =EXACT(A1,B1). Results are TRUE (the cells are identical), or FALSE.

In the example, results are FALSE in cells C1 and C3 because of spelling differences in

the last names.

 

Separate characters on the left

In cell B1, type =LEFT(A1,5). The result, "10249", is the first 5 characters from cell A1.

 

Separate characters on the right

In cell B1, type =RIGHT(A1,6). The result, "$18.60", is the last 6 characters from cell A1.

 

Separate the first word out of two words in one cell

In cell B1, type =LEFT(A1,FIND(" ",A1)-1). The result, "Nancy", is all characters to

the left of the space.

 

Separate the last word out of two words in a cell

In cell B1, type =RIGHT(A1,LEN(A1)-FIND(" ",A1)). The result is "Davolio".

 

From Microsoft’s site

 

Conditional formulas

You can use the following formulas to test the condition of a statement and return a Yes or No value, to test an alternate value such as OK or Not OK, or to return a blank or dash to represent a null value.

 

Check if a number is greater than or less than another number

 

Use the IF function to perform this comparison.

 

Column1 Column2 Formula Description (possible result)

15000 9000 =[Column1]>[Column2] Is Column1 greater than Column2? (Yes)

15000 9000 =IF([Column1]<=[Column2], "OK", "Not OK") Is Column1 less than or equal to Column2? (Not OK)

 

Return a logical value after comparing column contents

 

For a result that is a logical value (Yes or No), use the AND, OR, and NOT functions.

 

Column1 Column2 Column3 Formula Description (possible result)

15 9 8 =AND([Column1]>[Column2], [Column1]<[Column3]) Is 15 greater than 9 and less than 8? (No)

15 9 8 =OR([Column1]>[Column2], [Column1]<[Column3]) Is 15 greater than 9 or less than 8? (Yes)

15 9 8 =NOT([Column1]+[Column2]=24) Is 15 plus 9 not equal to 24? (No)

 

For a result that is another calculation, or any other value other than Yes or No, use the IF, AND, and OR functions.

 

Column1 Column2 Column3 Formula Description (possible result)

15 9 8 =IF([Column1]=15, "OK", "Not OK") If the value in Column1 equals 15, then return "OK". (OK)

15 9 8 =IF(AND([Column1]>[Column2], [Column1]<[Column3]), "OK", "Not OK") If 15 is greater than 9 and less than 8, then return "OK". (Not OK)

15 9 8 =IF(OR([Column1]>[Column2], [Column1]<[Column3]), "OK", "Not OK") If 15 is greater than 9 or less than 8, then return "OK". (OK)

 

Display zeroes as blanks or dashes

 

To display a zero, perform a simple calculation. To display a blank or a dash, use the IF function.

 

Column1 Column2 Formula Description (possible result)

10 10 =[Column1]-[Column2] Second number subtracted from the first (0)

15 9 =IF([Column1]-[Column2],"-",[Column1]-[Column2]) Returns a dash when the value is zero (-)

 

Hide error values in columns

 

To display a dash, #N/A, or NA in place of an error value, use the ISERROR function.

 

Column1 Column2  Formula Description (possible result)

10 0 =[Column1]/[Column2] Results in an error (#DIV/0)

10 0 =IF(ISERROR([Column1]/[Column2]),"NA",[Column1]/[Column2]) Returns NA when the value is an error

10 0 =IF(ISERROR([Column1]/[Column2]),"-",[Column1]/[Column2]) Returns a dash when the value is an error

 

Date and time formulas

You can use the following formulas to perform calculations that are based on dates and times, such as adding a number of days, months, or years to a date, calculating the difference between two dates, and converting time to a decimal value.

 

Add dates

 

To add a number of days to a date, use the addition (+) operator.

 

 Note   When you manipulate dates, the return type of the calculated column must be set to Date and Time.

 

Column1 Column2 Formula Description (result)

6/9/2007 3 =[Column1]+[Column2] Adds 3 days to 6/9/2007 (6/12/2007)

12/10/2008 54 =[Column1]+[Column2] Adds 54 days to 12/10/2008 (2/2/2009)

 

To add a number of months to a date, use the DATE, YEAR, MONTH, and DAY functions.

 

Column1 Column2 Formula Description (result)

6/9/2007 3 =DATE(YEAR([Column1]),MONTH([Column1])+[Column2],DAY([Column1])) Adds 3 months to 6/9/2007 (9/9/2007)

12/10/2008 25 =DATE(YEAR([Column1]),MONTH([Column1])+[Column2],DAY([Column1])) Adds 25 months to 12/10/2008 (1/10/2011)

 

To add a number of years to a date, use the DATE, YEAR, MONTH, and DAY functions.

 

Column1 Column2 Formula Description (result)

6/9/2007 3 =DATE(YEAR([Column1])+[Column2],MONTH([Column1]),DAY([Column1])) Adds 3 years to 6/9/2007 (6/9/2010)

12/10/2008 25 =DATE(YEAR([Column1])+[Column2],MONTH([Column1]),DAY([Column1])) Adds 25 years to 12/10/2008 (12/10/2033)

 

To add a combination of days, months, and years to a date, use the DATE, YEAR, MONTH, and DAY functions.

 

Column1 Formula Description (result)

6/9/2007 =DATE(YEAR([Column1])+3,MONTH([Column1])+1,DAY([Column1])+5) Adds 3 years, 1 month, and 5 days to 6/9/2007 (7/14/2010)

12/10/2008 =DATE(YEAR([Column1])+1,MONTH([Column1])+7,DAY([Column1])+5) Adds 1 year, 7 months, and 5 days to 12/10/2008 (7/15/2010)

 

Calculate the difference between two dates

 

Use the DATEDIF function to perform this calculation.

 

Column1 Column2 Formula Description (result)

01-Jan-1995 15-Jun-1999 =DATEDIF([Column1], [Column2],"d") Returns the number of days between the two dates (1626)

01-Jan-1995 15-Jun-1999 =DATEDIF([Column1], [Column2],"ym") Returns the number of months between the dates, ignoring the year part (5)

01-Jan-1995 15-Jun-1999 =DATEDIF([Column1], [Column2],"yd") Returns the number of days between the dates, ignoring the year part (165)

 

Calculate the difference between two times

 

To present the result in the standard time format (hours:minutes:seconds), use the subtraction operator (-) and the TEXT function. For this method to work, hours must not exceed 24, and minutes and seconds must not exceed 60.

 

Column1 Column2 Formula Description (result)

06/09/2007 10:35 AM 06/09/2007 3:30 PM =TEXT([Column2]-[Column1],"h") Hours between two times (4)

06/09/2007 10:35 AM 06/09/2007 3:30 PM =TEXT([Column2]-[Column1],"h:mm") Hours and minutes between two times (4:55) 

06/09/2007 10:35 AM 06/09/2007 3:30 PM =TEXT([Column2]-[Column1],"h:mm:ss") Hours, minutes, and seconds between two times (4:55:00) 

 

To present the result in a total that is based on one time unit, use the INT function, or HOUR, MINUTE, or SECOND function.

 

Column1 Column2 Formula Description (result)

06/09/2007 10:35 AM 06/10/2007 3:30 PM =INT(([Column2]-[Column1])*24) Total hours between two times (28)

06/09/2007 10:35 AM 06/10/2007 3:30 PM =INT(([Column2]-[Column1])*1440) Total minutes between two times (1735)

06/09/2007 10:35 AM 06/10/2007 3:30 PM =INT(([Column2]-[Column1])*86400) Total seconds between two times (104100)

06/09/2007 10:35 AM 06/10/2007 3:30 PM =HOUR([Column2]-[Column1]) Hours between two times, when the difference does not exceed 24 (4)

06/09/2007 10:35 AM 06/10/2007 3:30 PM =MINUTE([Column2]-[Column1]) Minutes between two times, when the difference does not exceed 60 (55)

06/09/2007 10:35 AM 06/10/2007 3:30 PM =SECOND([Column2]-[Column1]) Seconds between two times, when the difference does not exceed 60 (0)

 

Convert times

 

To convert hours from the standard time format to a decimal number, use the INT function.

 

Column1 Formula Description (result)

10:35 AM =([Column1]-INT([Column1]))*24 Number of hours since 12:00 AM (10.583333)

12:15 PM =([Column1]-INT([Column1]))*24 Number of hours since 12:00 AM (12.25)

 

To convert hours from a decimal number to the standard time format (hours:minutes:seconds), use the division operator and the TEXT function.

 

Column1 Formula Description (result)

23:58 =TEXT(Column1/24, "hh:mm:ss") Hours, minutes, and seconds since 12:00 AM (00:59:55)

2:06 =TEXT(Column1/24, "h:mm") Hours and minutes since 12:00 AM (0:05)

 

Insert Julian dates

 

A Julian date refers to a date format that is a combination of the current year and the number of days since the beginning of the year. For example, January 1, 2007, is represented as 2007001 and December 31, 2007, is represented as 2007365. This format is not based on the Julian calendar.

 

To convert a date to a Julian date, use the TEXT and DATEVALUE functions.

 

Column1 Formula Description (result)

6/23/2007 =TEXT([Column1],"yy")&TEXT(([Column1]-DATEVALUE("1/1/"& TEXT([Column1],"yy"))+1),"000") Date in Julian format, with a two-digit year (07174)

6/23/2007 =TEXT([Column1],"yyyy")&TEXT(([Column1]-DATEVALUE("1/1/"&TEXT([Column1],"yy"))+1),"000") Date in Julian format, with a four-digit year (2007174)

 

To convert a date to a Julian date that is used in astronomy, use the constant 2415018.50. This formula only works for dates after 3/1/1901, and if you are using the 1900 date system.

 

Column1 Formula Description (result)

6/23/2007 =[Column1]+2415018.50 Date in Julian format, used in astronomy (2454274.50)

 

Show dates as the day of the week

 

To convert dates to the text for the day of the week, use the TEXT and WEEKDAY functions.

 

Column1 Formula Description (possible result)

19-Feb-2007 =TEXT(WEEKDAY([Column1]), "dddd") Calculates the day of the week for the date and returns the full name of the day (Monday)

3-Jan-2008 =TEXT(WEEKDAY([Column1]), "ddd") Calculates the day of the week for the date and returns the abbreviated name of the day (Thu)

 

 

Mathematical formulas

You can use the following formulas to perform a variety of mathematical calculations, such as adding, subtracting, multiplying, and dividing numbers, calculating the average or median of numbers, rounding a number, and counting values.

 

Add numbers

 

To add numbers in two or more columns in a row, use the addition operator (+) or the SUM function.

 

Column1 Column2 Column3 Formula Description (result)

6 5 4 =[Column1]+[Column2]+[Column3] Adds the values in the first three columns (15)

6 5 4 =SUM([Column1],[Column2],[Column3]) Adds the values in the first three columns (15)

6 5 4 =SUM(IF([Column1]>[Column2], [Column1]-[Column2], 10), [Column3]) If Column1 is greater than Column2, adds the difference and Column3. Else add 10 and Column3 (5)

 

Subtract numbers

 

To subtract numbers in two or more columns in a row, use the subtraction operator (-) or the SUM function with negative numbers.

 

Column1 Column2  Column3 Formula Description (result)

15000 9000 -8000 =[Column1]-[Column2] Subtracts 9000 from 15000 (6000)

15000 9000 -8000 =SUM([Column1], [Column2], [Column3]) Adds numbers in the first three columns, including negative values (16000)

 

Calculate the difference between two numbers as a percentage

 

Use the subtraction (-) and division (/) operators and the ABS function.

 

Column1 Column2 Formula Description (result)

2342 2500 =([Column2]-[Column1])/ABS([Column1]) Percentage change (6.75% or 0.06746)

 

Multiply numbers

 

To multiply numbers in two or more columns in a row, use the multiplication operator (*) or the PRODUCT function.

 

Column1 Column2  Formula Description (result)

5 2 =[Column1]*[Column2] Multiplies the numbers in the first two columns (10)

5 2 =PRODUCT([Column1], [Column2]) Multiplies the numbers in the first two columns (10)

5 2 =PRODUCT([Column1],[Column2],2) Multiplies the numbers in the first two columns and the number 2 (20)

 

Divide numbers

 

To divide numbers in two or more columns in a row, use the division operator (/).

 

Column1 Column2  Formula Description (result)

15000 12 =[Column1]/[Column2] Divides 15000 by 12 (1250)

15000 12 =([Column1]+10000)/[Column2] Adds 15000 and 10000, and then divides the total by 12 (2083)

 

Calculate the average of numbers

 

The average is also called the mean. To calculate the average of numbers in two or more columns in a row, use the AVERAGE function.

 

Column1 Column2 Column3 Formula Description (result)

6 5 4 =AVERAGE([Column1], [Column2],[Column3]) Average of the numbers in the first three columns (5)

6 5 4 =AVERAGE(IF([Column1]>[Column2], [Column1]-[Column2], 10), [Column3]) If Column1 is greater than Column2, calculate the average of the difference and Column3. Else calculate the average of the value 10 and Column3 (2.5)

 

Calculate the median of numbers

 

The median is the value at the center of an ordered range of numbers. Use the MEDIAN function to calculate the median of a group of numbers.

 

A B  C D E F Formula Description (result)

10  7 9 27 0 4 =MEDIAN(A, B, C, D, E, F) Median of numbers in the first 6 columns (8)

 

Calculate the smallest or largest number in a range

 

To calculate the smallest or largest number in two or more columns in a row, use the MIN and MAX functions.

 

Column1 Column2  Column3 Formula Description (result)

10  7 9 =MIN([Column1], [Column2], [Column3]) Smallest number (7)

10 7 9 =MAX([Column1], [Column2], [Column3]) Largest number (10)

 

Count values

 

To count numeric values, use the COUNT function.

 

Column1 Column2  Column3 Formula Description (result)

Apple  12/12/2007 =COUNT([Column1], [Column2], [Column3]) Counts the number of columns that contain numeric values. Excludes date and time, text, and null values (0)

$12 #DIV/0! 1.01 =COUNT([Column1], [Column2], [Column3]) Counts the number of columns that contain numeric values, but excludes error and logical values (2)

 

Increase or decrease a number by a percentage

 

Use the percent (%) operator to perform this calculation.

 

Column1 Column2  Formula Description (result)

23 3% =[Column1]*(1+5%) Increases number in Column1 by 5% (24.15)

23 3% =[Column1]*(1+[Column2]) Increases number in Column1 by the percent value in Column2: 3% (23.69)

23 3% =[Column1]*(1-[Column2]) Decreases number in Column1 by the percent value in Column2: 3% (22.31)

 

Raise a number to a power

 

Use the exponentiation operator (^) or the POWER function to perform this calculation.

 

Column1 Column2  Formula Description (result)

5 2 =[Column1]^[Column2] Calculates five squared (25)

5 3 =POWER([Column1], [Column2]) Calculates five cubed (125) 

 

Round a number

 

To round up a number, use the ROUNDUP, ODD, or EVEN function.

 

Column1 Formula Description (result)

20.3 =ROUNDUP([Column1],0) Rounds 20.3 up to the nearest whole number (21)

-5.9 =ROUNDUP([Column1],0) Rounds -5.9 up to the nearest whole number (-5)

12.5493 =ROUNDUP([Column1],2) Rounds 12.5493 up to the nearest hundredth, two decimal places (12.55)

20.3 =EVEN([Column1]) Rounds 20.3 up to the nearest even number (22)

20.3 =ODD([Column1]) Rounds 20.3 up to the nearest odd number (21)

 

To round down a number, use the ROUNDDOWN function.

 

Column1 Formula Description (result)

20.3 =ROUNDDOWN([Column1],0) Rounds 20.3 down to the nearest whole number (20)

-5.9 =ROUNDDOWN([Column1],0) Rounds -5.9 down to the nearest whole number (-6)

12.5493 =ROUNDDOWN([Column1],2) Rounds 12.5493 down to the nearest hundredth, two decimal places (12.54)

 

To round a number to the nearest number or fraction, use the ROUND function.

 

Column1 Formula Description (result)

20.3 =ROUND([Column1],0) Rounds 20.3 down, because the fraction part is less than .5 (20)

5.9 =ROUND([Column1],0) Rounds 5.9 up, because the fraction part is greater than .5 (6)

-5.9 =ROUND([Column1],0) Rounds -5.9 down, because the fraction part is less than -.5 (-6)

1.25 =ROUND([Column1], 1) Rounds the number to the nearest tenth (one decimal place). Because the portion to be rounded is 0.05 or greater, the number is rounded up (result: 1.3)

30.452 =ROUND([Column1], 2) Rounds the number to the nearest hundredth (two decimal places). Because the portion to be rounded, 0.002, is less than 0.005, the number is rounded down (result: 30.45)

 

To round a number to the significant digit above 0, use the ROUND, ROUNDUP, ROUNDDOWN, INT, and LEN functions.

 

Column1 Formula Description (result)

5492820 =ROUND([Column1],3-LEN(INT([Column1]))) Rounds the number to 3 significant digits (5490000)

22230 =ROUNDDOWN([Column1],3-LEN(INT([Column1]))) Rounds the bottom number down to 3 significant digits (22200)

5492820 =ROUNDUP([Column1], 5-LEN(INT([Column1]))) Rounds the top number up to 5 significant digits (5492900)

 

 

Text formulas

You can use the following formulas to manipulate text, such as combining or concatenating the values from multiple columns, comparing the contents of columns, removing characters or spaces, and repeating characters.

 

Change the case of text

 

To change the case of text, use the UPPER, LOWER, or PROPER function.

 

Column1 Formula Description (result)

nina Vietzen =UPPER([Column1]) Changes text to uppercase (NINA VIETZEN)

nina Vietzen =LOWER([Column1]) Changes text to lowercase (nina vietzen)

nina Vietzen =PROPER([Column1]) Changes text to title case (Nina Vietzen)

 

Combine first and last names

 

To combine first and last names, use the ampersand operator (&) or the CONCATENATE function.

 

Column1 Column2 Formula Description (result)

Carlos Carvallo =[Column1]&[Column2] Combines the two strings (CarlosCarvallo)

Carlos Carvallo =[Column1]&" "&[Column2] Combines the two strings, separated by a space (Carlos Carvallo)

Carlos Carvallo =[Column2]&", "&[Column1] Combines the two strings, separated by a comma and a space (Carvallo, Carlos)

Carlos  Carvallo =CONCATENATE([Column2], ",", [Column1]) Combines the two strings, separated by a comma (Carvallo,Carlos)

 

Combine text and numbers from different columns

 

To combine text and numbers, use the CONCATENATE function, the ampersand operator (&), or the TEXT function and the ampersand operator.

 

Column1 Column2 Formula Description (result)

Yang 28 =[Column1]&" sold "&[Column2]&" units." Combines contents above into a phrase (Yang sold 28 units.)

Dubois 40% =[Column1]&" sold "&TEXT([Column2],"0%")&" of the total sales." Combines contents above into a phrase (Dubois sold 40% of the total sales.)

 Note   The TEXT function appends the formatted value of Column2 instead of the underlying value, which is .4.

 

Yang 28 =CONCATENATE([Column1]," sold ",[Column2]," units.") Combines contents above into a phrase (Yang sold 28 units.)

 

Combine text with a date or time

 

To combine text with a date or time, use the TEXT function and the ampersand operator (&).

 

Column1 Column2 Formula Description (result)

Billing Date 5-Jun-2007 ="Statement date: "&TEXT([Column2], "d-mmm-yyyy") Combines text with a date (Statement date: 5-Jun-2007)

Billing Date 5-Jun-2007 =[Column1]&" "&TEXT([Column2], "mmm-dd-yyyy") Combines text and date from different columns into one column (Billing Date Jun-05-2007)

 

Compare column contents

 

To compare one column to another column or a list of values, use the EXACT and OR functions.

 

Column1 Column2 Formula Description (possible result)

BD122 BD123 =EXACT([Column1],[Column2])  Compares contents of first two columns (No) 

BD122 BD123 =EXACT([Column1], "BD122") Compares contents of Column1 and the string "BD122" (Yes) 

 

Check if a column value or a part of it matches specific text

 

To check if a column value or a part of it matches specific text, use the IF, FIND, SEARCH, and ISNUMBER functions.

 

Column1 Formula Description (possible result)

Vietzen =IF([Column1]="Vietzen", "OK", "Not OK") Checks to see if Column1 is Vietzen (OK)

Vietzen =IF(ISNUMBER(FIND("v",[Column1])), "OK", "Not OK") Checks to see if Column1 contains the letter v (OK)

BD123 =ISNUMBER(FIND("BD",[Column1])) Checks to see if Column1 contains BD (Yes)

 

Count nonblank columns

 

To count nonblank columns, use the COUNTA function.

 

Column1 Column2 Column3 Formula Description (result)

Sales 19  =COUNTA([Column1], [Column2])  Counts the number of nonblank columns (2) 

Sales 19  =COUNTA([Column1], [Column2], [Column3]) Counts the number of nonblank columns (2) 

 

Remove characters from text

 

To remove characters from text, use the LEN, LEFT, and RIGHT functions.

 

Column1 Formula Description (result)

Vitamin A =LEFT([Column1],LEN([Column1])-2) Returns 7 (9-2) characters, starting from left (Vitamin) 

Vitamin B1 =RIGHT([Column1], LEN([Column1])-8) Returns 2 (10-8) characters, starting from right (B1)

 

Remove spaces from the beginning and end of a column

 

To remove spaces from a column, use the TRIM function.

 

Column1 Formula Description (result)

  Hello there! =TRIM([Column1]) Removes the spaces from the beginning and end (Hello there!) 

 

Repeat a character in a column

 

To repeat a character in a column, use the REPT function.

 

Formula Description (result)

=REPT(".",3) Repeats a period 3 times (...)

=REPT("-",10) Repeats a dash 10 times (----------)