Export (0) Print
Expand All
ABS
AND
ASC
COS
DAY
DDB
EXP
FV
IF
INT
LEN
LN
LOG
MAX
Me
MID
MIN
MOD
NOT
IS
NPV
ODD
OR
PI
PMT
PV
SIN
SLN
SUM
SYD
T
TAN
VAR
Expand Minimize
This topic has not yet been rated - Rate this topic

Examples of common formulas

Archived content. No warranty is made as to technical accuracy. Content may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

Note: You 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.

Conditional 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

=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)

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

=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

=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)

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(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(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)

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)

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)

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 (-)

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)

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)

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)

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)

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)

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)

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)

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)

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

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,"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,"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)

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")

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")

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)

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)*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)*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

=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

=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

=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)

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)

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)

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)

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)

12:25

=TEXT(Column1/24, "h:mm")

Hours since 12:00 AM (12:15)

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.

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,"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)

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)

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)

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)

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)

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

=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(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)

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

=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)

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)

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

=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)

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)

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/Column2

Divides 15000 by 12 (1250)

15000

12

=(Column1+10000)/Column2

Adds 15000 and 9000, and then divides the total by 12 (2000)

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(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)

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)

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

=MIN(Column1, Column2, Column3)

Smallest number (7)

10

7

9

=MAX(Column1, Column2, Column3)

Largest number (10)

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, including date and time values. Exludes text and null values. (1)

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)

$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+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)

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)

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

2

=Column1^Column2

Calculates five squared (25)

5

3

=POWER(Column1, Column2)

Calculates five cubed (125)

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)

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)

-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)

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

=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)

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)

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)

-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)

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)

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 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)

-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)

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)

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)

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)

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)

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

=UPPER(Column1)

Changes text to uppercase (NANCY DAVOLIO)

nancy Davolio

=LOWER(Column1)

Changes text to lowercase (nancy davolio)

nancy Davolio

=LOWER(Column1)

Changes text to lowercase (nancy davolio)

nancy Davolio

=PROPER(Column1)

Changes text to title case (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 (NancyFuller)

Nancy

Fuller

=Column1&" "&Column2

Combines the two strings, separated by a space (Nancy Fuller)

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

=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)

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)

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.

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)

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

="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)

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 and OR functions.

Column1

Column2

Formula

Description

BD122

BD123

=EXACT(Column1,Column2)

Compare contents of first two columns (No)

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

=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)

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(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)

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)

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)

Counts the number of nonblank columns (2)

Sales

19

 

=COUNTA(Column1, Column2, Column3)

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 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)

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!)

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(".",3)

Repeats a period 3 times (...)

=REPT("-",10)

Repeats a dash 10 times (----------)

=REPT("-",10)

Repeats a dash 10 times (----------)

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

=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),"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

10

0

=IF(ISERROR(Column1/Column2),"-",Column1/Column2)

Returns a dash when the value is an error

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft. All rights reserved.