Examples of common formulas
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
For a result that is a logical value
(Yes or No), use the AND, OR, and NOT functions.
|
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
Use the IF function to do this task.
|
Column1
|
Column2
|
Formula
|
Description
|
|
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 (-)
|
![Date and time formulas]()
Add
dates
To add a number of days to a date,
use the addition (+) operator. Note that when manipulating dates, the return
type of the calculated column must be set to Date and Time.
|
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 (1/14/2009)
|
|
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 (7/15/2010)
|
Calculate
the difference between two dates
Use the DATEDIF function to do this
task.
|
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 presenting 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
|
|
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)
|
Convert
times
To convert hours from standard time
format to a decimal number, use the INT function.
|
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 a decimal
number to the standard time format (hours:minutes:seconds), use the divisor
operator and the TEXT function.
|
Column1
|
Formula
|
Description
|
|
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
The phrase "Julian date"
is sometimes used to refer 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 2003356. 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
To convert dates to the text for the
day of the week, use the TEXT and WEEKDAY functions.
|
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)
|
Add dates
To add a
number of days to a date, use the addition (+) operator. Note that when
manipulating dates, the return type of the calculated column must be set to
Date and Time.
Math formulas
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
|
|
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)
|
Subtract
numbers
Use the subtraction (-) operator to
do this task.
|
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
Use the subtraction (-) and division
(/) operators, and the ABS function.
|
Column1
|
Column2
|
Formula
|
Description
|
|
2342
|
2500
|
=(Column2-Column1)/ABS(Column1)
|
Percentage change (6.75% or
0.06746)
|
Multiply
numbers
Use the multipliation (*) operator
or the PRODUCT function to do this task.
|
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)
|
Divide
numbers
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 10000, and then
divides the total by 12 (2,083)
|
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
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
|
|
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
|
|
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
|
|
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 percentage (%) operator to
do this task.
|
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)
|
Raise
a number to a power
Use the exponent (^) operator or the
POWER function to do this task.
|
Column1
|
Column2
|
Formula
|
Description
|
|
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, 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)
|
Text formulas
Change
the case of text
Use the UPPER, LOWER, or PROPER
functions to do this task.
|
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)
|
Combine
first and last names
Use the ampersand (&) operator
or the CONCATENATE function to do this task.
|
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
Use the CONCATENATE and TEXT
functions, and the ampersand (&) operator to do this task.
|
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
Use the TEXT function and the
ampersand (&) operator to do this task.
|
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)
|
Compare
column contents
To compare one column to another
column or a list of values, use the EXACT function.
|
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)
|
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)
|
Count
nonblank columns
Use the COUNTA function to do this
task.
|
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)
|
Remove
characters from text
Use the LEN, LEFT, and RIGHT
functions to do this task.
|
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
Use the TRIM function to do this
task.
|
Column1
|
Formula
|
Description
|
|
Hello
there!
|
=TRIM(Column1)
|
Remove the spaces from the
beginning and end (Hello there!)
|
Repeat
a characater in a column
Use the REPT function to do this
task.
|
Formula
|
Description
|
|
=REPT(".",3)
|
Repeats a period 3 times (...)
|
|
=REPT("-",10)
|
Repeats a dash 10 times
(----------)
|
![Hide]()
Other formulas
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
|
|
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
|