Show AllYou can use the following examples in calculated columns. Examples that do not include column references can be used to specify the default value of a column.
Important Formulas can only be used in columns with the data type Calculated. You cannot change the data type of a column to Calculated.
Check if a number is greater than or less than another number
Use the IF function to do this task.
| Column1 | Column2 | Formula | Description |
|---|---|---|---|
| 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
| Column1 | Column2 | Column3 | Formula | Description |
|---|---|---|---|---|
| 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 |
|---|---|---|---|---|
| 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
| Column1 | Column2 | Formula | Description |
|---|---|---|---|
| 10 | 10 | =Column1-Column2 | Second number subtracted from the first (0) |
| 10 | 10 | =IF(Column1-Column2,"",Column1-Column2) | Returns null when the value is zero (blank column) |
| 15 | 9 | =IF(Column1-Column2,"-",Column1-Column2) | Returns a dash when the value is zero (-) |
| Column1 | Column2 | Formula | Description |
|---|---|---|---|
| 6/9/2007 | 3 | =Column1+Column2 | Add 3 days to 6/9/2007 (6/12/2007) |
| 12/10/2008 | 54 | =Column1+Column2 | Add 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 |
|---|---|---|---|
| 6/9/2007 | 3 | =DATE(YEAR(Column1),MONTH(Column1)+Column2,DAY(Column1)) | Add 3 months to 6/9/2007 (9/9/2007) |
| 12/10/2008 | 25 | =DATE(YEAR(Column1),MONTH(Column1)+Column2,DAY(Column1)) | Add 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 |
|---|---|---|---|
| 6/9/2007 | 3 | =DATE(YEAR(Column1)+Column2,MONTH(Column1),DAY(Column1)) | Add 3 years to 6/9/2007 (6/9/2010) |
| 12/10/2008 | 25 | =DATE(YEAR(Column1)+Column2,MONTH(Column1),DAY(Column1)) | Add 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 |
|---|---|---|
| 6/9/2007 | =DATE(YEAR(Column1)+3,MONTH(Column1)+1,DAY(Column1)+5) | Add 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) | Add 1 year, 7 months, and 5 days to 6/9/2007 (1/14/2009) |
Calculate the difference between two dates
| Column1 | Column2 | Formula | Description |
|---|---|---|---|
| 01-Jan-1995 | 15-Jun-1999 | =DATEDIF(Column1, Column2,"d") | Return the number of days between the two dates (1626) |
| 01-Jan-1995 | 15-Jun-1999 | =DATEDIF(Column1, Column2,"ym") | Return the number of months between the dates, ignoring the year part (5) |
| 01-Jan-1995 | 15-Jun-1999 | =DATEDIF(Column1, Column2,"yd") | Return the number of days between the dates, ignoring the year part (165) |
Calculate the difference between two times
For this method to work, hours must not exceed 24, and minutes and seconds must not exceed 60.
| Column1 | Column2 | Formula | Description |
|---|---|---|---|
| 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) |
For presenting the result in a total based on one time unit, use the INT function, or HOUR, MINUTE, and SECOND functions.
| Column1 | Column2 | Formula | Description |
|---|---|---|---|
| 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) |
| Column1 | Formula | Description |
|---|---|---|
| 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 decimal number to the standard time format (hours:minutes:seconds), use the divisor operator and the TEXT function.
| Column1 | Formula | Description |
|---|---|---|
| 10:5833 | =TEXT(Column1/24, "h:mm") | Hours since 12:00 AM (10:35) |
| 12:25 | =TEXT(Column1/24, "h:mm") | Hours since 12:00 AM (12:15) |
There is also a Julian date commonly used in astronomy, which is a serial date system starting on January 1, 4713 B.C.E.
Note 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 |
|---|---|---|
| 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 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 |
|---|---|---|
| 6/23/2007 | =Column1+2415018.50 | Date in "Julian" format, used in astronomy (2454274.50) |
Show dates as the day of the week
| Column1 | Formula | Description |
|---|---|---|
| 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) |
To add numbers in two or more columns in a row, use the addition operator or the SUM function.
| Column1 | Column2 | Column3 | Formula | Description |
|---|---|---|---|---|
| 6 | 5 | 4 | =Column1+Column2+Column3 | Add the values in the first three columns (15) |
| 6 | 5 | 4 | =SUM(Column1,Column2,Column3) | Add 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, add the difference and Column3. Else add 10 and Column3. (5) |
| Column1 | Column2 | Column3 | Formula | Description |
|---|---|---|---|---|
| 15000 | 9000 | -8000 | =Column1-Column2 | Subtract 9000 from 15000 (6000) |
| 15000 | 9000 | -8000 | =SUM(Column1, Column2, Column3) | Add numbers in the first three columns, including negative values (16000) |
Calculate the difference between two numbers as a percentage
| Column1 | Column2 | Formula | Description |
|---|---|---|---|
| 2342 | 2500 | =(Column2-Column1)/ABS(Column1) | Percentage change (6.75% or 0.06746) |
| Column1 | Column2 | Formula | Description |
|---|---|---|---|
| 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) |
Use the division operator (/) to do this task.
| Column1 | Column2 | Formula | Description |
|---|---|---|---|
| 15000 | 12 | =Column1/Column2 | Divides 15000 by 12 (1250) |
| 15000 | 12 | =(Column1+10000)/Column2 | Adds 15000 and 9000, and then divides the total by 12 (2000) |
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 |
|---|---|---|---|---|
| 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 Column, 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
| A | B | C | D | E | F | Formula | Description |
|---|---|---|---|---|---|---|---|
| 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
| Column1 | Column2 | Column3 | Formula | Description |
|---|---|---|---|---|
| 10 | 7 | 9 | =MIN(Column1, Column2, Column3) | Smallest number (7) |
| 10 | 7 | 9 | =MAX(Column1, Column2, Column3) | Largest number (10) |
| Column1 | Column2 | Column3 | Formula | Description |
|---|---|---|---|---|
| Apple | 12/12/2007 | =COUNT(Column1, Column2, Column3) | Counts the number of columns that contain numeric values, including date and time values. Exludes text and null values. (1) | |
| $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
| Column1 | Column2 | Formula | Description |
|---|---|---|---|
| 23 | 3% | =Column1*(1+5%) | Increases number in Column1 by 5% (24.15) |
| 23 | 3% | =Column1*(1+Column2) | Increase number in Column1 by the percent value in Column2: 3% (23.69) |
| 23 | 3% | =Column1*(1-Column2) | Decrease number in Column1 by the percent value in Column2: 3% (22.31) |
| Column1 | Column2 | Formula | Description |
|---|---|---|---|
| 5 | 2 | =Column1^Column2 | Calculates five squared (25) |
| 5 | 3 | =POWER(Column1, Column2) | Calculates five cubed (125) |
To round up a number, use the ROUNDUP, ODD, and EVEN functions.
| Column1 | Formula | Description |
|---|---|---|
| 20.3 | =ROUNDUP(Column1,0) | Rounds 20.3 up to the nearest whole number (21) |
| -5.9 | =ROUNDUP(Column1,0) | Rounds -5.9 up (-6) |
| 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 |
|---|---|---|
| 20.3 | =ROUNDDOWN(Column1,0) | Rounds 20.3 down to the nearest whole number (20) |
| -5.9 | =ROUNDDOWN(Column1,0) | Rounds -5.9 down (-5) |
| 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 |
|---|---|---|
| 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 |
|---|---|---|
| 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) |
| Column1 | Formula | Description |
|---|---|---|
| nancy Davolio | =UPPER(Column1) | Changes text to uppercase (NANCY DAVOLIO) |
| nancy Davolio | =LOWER(Column1) | Changes text to lowercase (nancy davolio) |
| nancy Davolio | =PROPER(Column1) | Changes text to title case (Nancy Davolio) |
| Column1 | Column2 | Formula | Description |
|---|---|---|---|
| Nancy | Fuller | =Column1&Column2 | Combines the two strings (NancyFuller) |
| Nancy | Fuller | =Column1&" "&Column2 | Combines the two strings, separated by a space (Nancy Fuller) |
| Nancy | Fuller | =Column2&","&Column1 | Combines the two strings, separated by a comma (Fuller, Nancy) |
| Nancy | Fuller | =CONCATENATE(Column2, ",", Column1) | Combines the two strings, separated by a comma (Fuller,Nancy) |
Combine text and numbers from different columns
| Column1 | Column2 | Formula | Description |
|---|---|---|---|
| Buchanan | 28 | =Column1&" sold "&Column2&" units." | Combines contents above into a phrase (Buchanan sold 28 units) |
| Dodsworth | 40% | =Column1&" sold "&TEXT(Column2,"0%")&" of the total sales." | Combines contents above into a phrase (Dodsworth sold 40% of the total sales). Note The TEXT function appends the formatted value of Column2 instead of the underlying value, which is .4. |
| Buchanan | 28 | =CONCATENATE(Column1," sold ",Column2," units.") | Combines contents above into a phrase (Buchanan sold 28 units) |
Combine text with a date or time
| Column1 | Column2 | Formula | Description |
|---|---|---|---|
| Billing Date | 5-Jun-2007 | ="Statement date: "&TEXT(Column2, "d-mmm-yyyy") | Combine text with a date (Statement date: 5-Jun-2007) |
| Billing Date | 5-Jun-2007 | =Column1&" "&TEXT(Column2, "mmm-dd-yyyy") | Combine text and date from difference columns into one column (Billing Date Jun-05-2007) |
| Column1 | Column2 | Formula | Description |
|---|---|---|---|
| BD122 | BD123 | =EXACT(Column1,Column2) | Compare contents of first two columns (No) |
| BD122 | BD123 | =EXACT(Column1, "BD122") | Compare contents of Column1 and the string "BD122" (Yes) |
| BD122 | BD123 | =OR(EXACT("BD121", Column1, Column2) | Compare the string "BD121" with the contents of the first two columns (No) |
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 |
|---|---|---|
| Davolio | =IF(Column1="Davolio", "OK", "Not OK") | Checks to see if Column1 is Davolio (OK) |
| Davolio | =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) |
| Column1 | Column2 | Column3 | Formula | Description |
|---|---|---|---|---|
| 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) |
| Column1 | Formula | Description |
|---|---|---|
| Vitamin A | =LEFT(Column1,LEN(Column1)-2) | Return 7 (9-2) characters, starting from left (Vitamin) |
| Vitamin B1 | =RIGHT(Column1, LEN(Column1)-8) | Return 2 (10-8) characters, starting from right (B1) |
Remove spaces from the beginning and end of a column
| Column1 | Formula | Description |
|---|---|---|
| Hello there! | =TRIM(Column1) | Remove the spaces from the beginning and end (Hello there!) |
Repeat a characater in a column
| Formula | Description |
|---|---|
| =REPT(".",3) | Repeats a period 3 times (...) |
| =REPT("-",10) | Repeats a dash 10 times (----------) |
| Column1 | Column2 | Formula | Description |
|---|---|---|---|
| 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 |