Published: May 2010
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.
Use the IF function to perform this comparison.
For a result that is a logical value (Yes or No), use the AND, OR, and NOT functions.
For a result that is another calculation, or any other value other than Yes or No, use the IF, AND, and OR functions.
To display a zero, perform a simple calculation. To display a blank or a dash, use the IF function.
To display a dash, #N/A, or NA in place of an error value, use the ISERROR function.
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.
To add a number of days to a date, use the addition (+) operator.
To add a number of months to a date, use the DATE, YEAR, MONTH, and DAY functions.
To add a number of years to a date, use the DATE, YEAR, MONTH, and DAY functions.
To add a combination of days, months, and years to a date, use the DATE, YEAR, MONTH, and DAY functions.
Use the DATEDIF function to perform this calculation.
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.
To present the result in a total that is based on one time unit, use the INT function, or HOUR, MINUTE, or SECOND function.
To convert hours from the standard time format to a decimal number, use the INT function.
To convert hours from a decimal number to the standard time format (hours:minutes:seconds), use the division operator and the TEXT function.
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.
To convert a date to a Julian date that is used in astronomy, use the constant 2415018.50. This formula works only for dates after 3/1/1901, and if you are using the 1900 date system.
To convert dates to the text for the day of the week, use the TEXT and WEEKDAY functions.
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.
To add numbers in two or more columns in a row, use the addition operator (+) or the SUM function.
To subtract numbers in two or more columns in a row, use the subtraction operator (-) or the SUM function with negative numbers.
Use the subtraction (-) and division (/) operators and the ABS function.
To multiply numbers in two or more columns in a row, use the multiplication operator (*) or the PRODUCT function.
To divide numbers in two or more columns in a row, use the division operator (/).
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.
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.
To calculate the smallest or largest number in two or more columns in a row, use the MIN and MAX functions.
To count numeric values, use the COUNT function.
Use the percent (%) operator to perform this calculation.
Use the exponentiation operator (^) or the POWER function to perform this calculation.
To round up a number, use the ROUNDUP, ODD, or EVEN function.
To round down a number, use the ROUNDDOWN function.
To round a number to the nearest number or fraction, use the ROUND function.
To round a number to the significant digit above 0, use the ROUND, ROUNDUP, ROUNDDOWN, INT, and LEN functions.
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.
To change the case of text, use the UPPER, LOWER, or PROPER function.
To combine first and last names, use the ampersand operator (&) or the CONCATENATE function.
To combine text and numbers, use the CONCATENATE function, the ampersand operator (&), or the TEXT function and the ampersand operator.
To combine text with a date or time, use the TEXT function and the ampersand operator (&).
To compare one column to another column or a list of values, use the EXACT and OR functions.
To determine whether a column value or a part of it matches specific text, use the IF, FIND, SEARCH, and ISNUMBER functions.
To count nonblank columns, use the COUNTA function.
To remove characters from text, use the LEN, LEFT, and RIGHT functions.
To remove spaces from a column, use the TRIM function.
To repeat a character in a column, use the REPT function.