Formula results can be calculated in the browser and upon data export. Formulas can apply to resources and properties. Results from formulas produce single-valued results only, and they use the mean value of multi-valued properties. Formulas can be generated for actual or planned values as detailed in the articles on calculated properties and resource names.
ABS | Returns the absolute value of a number. The absolute value of a number is the number without its sign. |
ADD | Provides the ability to perform basic addition function on input values by the user. Note: this will NOT add two cells together, but it will add two numbers together (e.g., ADD(3+5)). |
AND | Use the AND function, one of the logical functions, to determine if all conditions in a test are TRUE. |
BIN2DEC | Converts a binary number to decimal. |
BIN2HEX | Converts a binary number to hexadecimal. |
BIN2OCT | Converts a binary number to octal. |
BITAND | Returns a bitwise 'AND' of two numbers. |
BITLSHIFT | Returns a number shifted left by the specified number of bits. |
BITOR | Returns a bitwise 'OR' of two numbers. |
BITRSHIFT | Returns a number shifted right by the specified number of bits. |
BITXOR | Returns a bitwise 'XOR' of two numbers. |
CEILING | Returns number rounded up, away from zero, to the nearest multiple of significance. For example, if you want to avoid using pennies in your prices and your product is priced at $4.42, use the formula =CEILING(4.42,0.05) to round prices up to the nearest nickel. |
CHAR | Returns the character specified by a number. Use CHAR to translate code page numbers you might get from files on other types of computers into characters. |
CLEAN | Removes all nonprintable characters from text. Use CLEAN on text imported from other applications that contains characters that may not print with your operating system. For example, you can use CLEAN to remove some low-level computer code that is frequently at the beginning and end of data files and cannot be printed. |
CODE | Returns a numeric code for the first character in a text string. The returned code corresponds to the character set used by your computer. |
or or |
Use CONCATENATE, one of the text functions, to join two or more text strings into one string. |
CONVERT | Converts a number from one measurement system to another. For example, CONVERT can translate a table of distances in miles to a table of distances in kilometers. |
DATE | Use Excel's DATE function when you need to take three separate values and combine them to form a date. |
DATEVALUE | Occasionally, dates may become formatted and stored in cells as text. For example, you may have entered a date in a cell that was formatted as text, or the data might have been imported or pasted from an external data source as text. |
DAY | Returns the day of a date, represented by a serial number. The day is given as an integer ranging from 1 to 31. |
DAYS | Returns the number of days between two dates. |
DAYS360 | The DAYS360 function returns the number of days between two dates based on a 360-day year (twelve 30-day months), which is used in some accounting calculations. Use this function to help compute payments if your accounting system is based on twelve 30-day months. |
DEC2BIN | Converts a decimal number to binary. |
DEC2HEX | Converts a decimal number to hexadecimal. |
DEC2OCT | Converts a decimal number to octal. |
DECIMAL | Converts a text representation of a number in a given base into a decimal number. |
DELTA | Tests whether two values are equal. Returns 1 if number1 = number2; returns 0 otherwise. Use this function to filter a set of values. For example, by summing several DELTA functions you calculate the count of equal pairs. This function is also known as the Kronecker Delta function. |
DIVIDE | Let's say you want to find out how many person hours it took to finish a project (total project hours ÷ total people on project) or the actual miles per gallon rate for your recent cross-country trip (total miles ÷ total gallons). Excel gives you several ways to divide numbers. |
EDATE | Returns the serial number that represents the date that is the indicated number of months before or after a specified date (the start_date). Use EDATE to calculate maturity dates or due dates that fall on the same day of the month as the date of issue. |
EOMONTH | Use Excel's DATE function when you need to take three separate values and combine them to form a date. |
EVEN | Returns number rounded up to the nearest even integer. You can use this function for processing items that come in twos. For example, a packing crate accepts rows of one or two items. The crate is full when the number of items, rounded up to the nearest two, matches the crate's capacity. |
EXACT | Compares two text strings and returns TRUE if they are exactly the same, FALSE otherwise. EXACT is case-sensitive but ignores formatting differences. Use EXACT to test text being entered into a document. |
EXP | Returns e raised to the power of number. The constant e equals 2.71828182845904, the base of the natural logarithm. |
FACT | Returns the factorial of a number. The factorial of a number is equal to 1*2*3*...* number. |
FACTDOUBLE | Returns the double factorial of a number. |
FALSE | Returns the logical value FALSE. |
FIND | Finds one text string (find_text) within another text string (within_text), and returns the number of the starting position of find_text, from the first character of within_text. You can also use SEARCH to find one text string within another, but unlike SEARCH, FIND is case sensitive and doesn't allow wildcard characters. |
FINV | Returns the inverse of the (right-tailed) F probability distribution. If p = FDIST(x,...), then FINV(p,...) = x. |
FIXED | Rounds a number to the specified number of decimals, formats the number in decimal format using a period and commas, and returns the result as text. |
FLOOR | Rounds number down, toward zero, to the nearest multiple of significance. |
GAUSS | Calculates the probability that a member of a standard normal population will fall between the mean and z standard deviations from the mean. |
GTE | Returns a formula as a string. |
HEX2BIN | Converts a hexadecimal number to binary. |
HEX2DEC | Converts a hexadecimal number to decimal. |
HEX2OCT | Converts a hexadecimal number to octal. |
HOUR | Returns the hour of a time value. The hour is given as an integer, ranging from 0 (12:00 A.M.) to 23 (11:00 P.M.). |
IF | The IF function allows you to make a logical comparison between a value and what you expect by testing for a condition and returning a result if True or False. |
INT | Rounds a number down to the nearest integer. |
ISBINARY | Returns a bitwise 'AND' of two numbers. |
ISBLANK | Each of these functions, referred to collectively as the IS functions, checks the specified value and returns TRUE or FALSE depending on the outcome. For example, the ISBLANK function returns the logical value TRUE if the value argument is a reference to an empty cell; otherwise it returns FALSE. |
ISEVEN | Returns TRUE if number is even, or FALSE if number is odd. |
ISLOGICAL | To get detailed information about a function, click its name in the first column. |
ISNONTEXT | |
ISNUMBER | Each of these functions, referred to collectively as the IS functions, checks the specified value and returns TRUE or FALSE depending on the outcome. For example, the ISBLANK function returns the logical value TRUE if the value argument is a reference to an empty cell; otherwise it returns FALSE. |
ISODD | Returns TRUE if number is odd, or FALSE if number is even. |
ISOWEEKNUM | Returns number of the ISO week number of the year for a given date. |
ISTEXT | Let's say you want to ensure that a column contains text and not numbers or you want to find all orders by a particular salesperson. There are several ways to check if a cell contains text but the case of the text doesn't matter to you. |
LEFT | LEFT returns the first character or characters in a text string, based on the number of characters you specify. |
LEN | When you need to count the characters in cells, use the LEN function. The function counts letters, numbers, characters, and all spaces. For example, the length of "It's 98 degrees today, so I'll go swimming" (excluding the quotes) is 42 characters—31 letters, 2 numbers, 8 spaces, a comma, and 2 apostrophes. |
LOWER | Converts all uppercase letters in a text string to lowercase. |
MID | MID returns a specific number of characters from a text string, starting at the position you specify, based on the number of characters you specify. |
MINUS | Important: The calculated results of formulas and some Excel worksheet functions may differ slightly between a Windows PC using x86 or x86-64 architecture and a Windows RT PC using ARM architecture. Learn more about the differences. |
MINUTE | Returns the minutes of a time value. The minute is given as an integer, ranging from 0 to 59. |
MOD | Returns the remainder after number is divided by divisor. The result has the same sign as divisor. |
MONTH | Suppose you want to adjust a project's schedule date by adding two weeks to see what the new completion date will be, or you want to determine how long a single activity will take to complete in a list of project tasks. You can add or subtract a number of days to or from a date by using a simple formula, or you can use worksheet functions that are designed to work specifically with dates in Excel. |
MROUND | Returns a number rounded to the desired multiple. |
MULTIPLY | Multiplying and dividing in Excel is easy, but you need to create a simple formula to do it. Just remember that all formulas in Excel begin with an equal sign (=), and you can use the formula bar to create them. |
NOT | Use the NOT function, one of the logical functions, when you want to make sure one value is not equal to another. |
NOW | Let's say that you want to easily enter the current date and time while making a time log of activities. Or perhaps you want to display the current date and time automatically in a cell every time formulas are recalculated. There are several ways to insert the current date and time in a cell. |
NUMERAL | Converts an arabic numeral to roman, as text. |
OCT2BIN | Converts an octal number to binary. |
OCT2DEC | Converts an octal number to decimal. |
OCT2HEX | Converts an octal number to hexadecimal. |
ODD | Returns number rounded up to the nearest odd integer. |
OR | Use the OR function, one of the logical functions, to determine if any conditions in a test are TRUE. |
PI | Returns the number 3.14159265358979, the mathematical constant pi, accurate to 15 digits. |
POW | Returns the result of a number raised to a power. |
POWER | Returns the result of a number raised to a power. |
PRODUCT | The PRODUCT function multiplies all the numbers given as arguments and returns the product. For example, if cells A1 and A2 contain numbers, you can use the formula =PRODUCT(A1, A2) to multiply those two numbers together. You can also perform the same operation by using the multiply (*) mathematical operator; for example, =A1 * A2. |
PROPER | Unlike Microsoft Word, Microsoft Excel doesn't have a Change Case button for changing capitalization. However, you can use the UPPER, LOWER, or PROPER functions to automatically change the case of existing text to uppercase, lowercase, or proper case. Functions are just built-in formulas that are designed to accomplish specific tasks—in this case, converting text case. |
QUOTIENT | Returns the integer portion of a division. Use this function when you want to discard the remainder of a division. |
RADIANS | Converts degrees to radians. |
RAND | Returns an evenly distributed random real number greater than or equal to 0 and less than 1. A new random real number is returned every time the worksheet is calculated. |
RANDBETWEEN | Returns a random integer number between the numbers you specify. A new random integer number is returned every time the worksheet is calculated. |
REFERENCE | Quickly find the difference between two times, control the format of the result, and extend the calculation to a list of times. For example, you can quickly subtract one time from another time, or calculate the duration of agenda items for a meeting. |
REGEXMATCH | Data Analysis Expressions (DAX) sounds a little intimidating at first, but don’t let the name fool you. DAX basics are really quite easy to understand. First things first - DAX is NOT a programming language. DAX is a formula language. You can use DAX to define custom calculations for Calculated Columns and for Measures (also known as calculated fields). DAX includes some of the functions used in Excel formulas, and additional functions designed to work with relational data and perform dynamic aggregation. |
REGEXREPLACE | Word offers several options for locating specific content in your document. You can search for and replace items such as text, images, captions, bookmarks, or certain types of formatting such as paragraphs or page breaks. You can use the Go To command to go to a particular instance of content in your document, and you can also extend your search by using wildcards, codes, or regular expressions to find words or phrases that contain specific characters or combinations of characters. |
REPLACE | REPLACE replaces part of a text string, based on the number of characters you specify, with a different text string. |
REPT | Repeats text a given number of times. Use REPT to fill a cell with a number of instances of a text string. |
RIGHT | RIGHT returns the last character or characters in a text string, based on the number of characters you specify. |
ROMAN | Converts an arabic numeral to roman, as text. |
ROUND | The ROUND function rounds a number to a specified number of digits. For example, if cell A1 contains 23.7825, and you want to round that value to two decimal places, you can use the following formula: |
ROUNDDOWN | Rounds a number down, toward zero. |
ROUNDUP | Rounds a number up, away from 0 (zero). |
SEARCH | The SEARCH and SEARCHB functions locate one text string within a second text string, and return the number of the starting position of the first text string from the first character of the second text string. For example, to find the position of the letter "n" in the word "printer", you can use the following function: |
SECOND | Returns the seconds of a time value. The second is given as an integer in the range 0 (zero) to 59. |
SIGN | Determines the sign of a number. Returns 1 if the number is positive, zero (0) if the number is 0, and -1 if the number is negative. |
SQRT | Returns a positive square root. |
SQRTPI | Returns the square root of (number * pi). |
SUBSTITUTE | Substitutes new_text for old_text in a text string. Use SUBSTITUTE when you want to replace specific text in a text string; use REPLACE when you want to replace any text that occurs in a specific location in a text string. |
T | Returns the text referred to by value. |
TEXT |
The TEXT function lets you change the way a number appears by applying formatting to it with format codes. It's useful in situations where you want to display numbers in a more readable format, or you want to combine numbers with text or symbols. Additional details on how dates and other strings can be parsed using format tokens is available here. These format tokens can be used in addition to the format tokens listed in the excel help here. |
TIME | Returns the decimal number for a particular time. If the cell format was General before the function was entered, the result is formatted as a date. |
TIMEVALUE | Returns the decimal number of the time represented by a text string. The decimal number is a value ranging from 0 (zero) to 0.99988426, representing the times from 0:00:00 (12:00:00 AM) to 23:59:59 (11:59:59 P.M.). |
TODAY | Returns the serial number of the current date. The serial number is the date-time code used by Excel for date and time calculations. If the cell format was General before the function was entered, Excel changes the cell format to Date. If you want to view the serial number, you must change the cell format to General or Number. |
TRIM | Removes all spaces from text except for single spaces between words. Use TRIM on text that you have received from another application that may have irregular spacing. |
TRUE | Returns the logical value TRUE. You can use this function when you want to return the value TRUE based on a condition. For example: |
TRUNC | Truncates a number to an integer by removing the fractional part of the number. |
UNICHAR | Returns the Unicode character that is referenced by the given numeric value. |
UNICODE | Returns the number (code point) corresponding to the first character of the text. |
UNIQUE | Let's say you want to find out how many unique values exist in a range that contains duplicate values. For example, if a column contains: |
UPPER | Converts text to uppercase. |
VALUE | Converts a text string that represents a number to a number. |
WEEKDAY | Returns a Variant (Integer) containing a whole number representing the day of the week. |
WEEKNUM | Returns the week number of a specific date. For example, the week containing January 1 is the first week of the year, and is numbered week 1. |
WORKDAY | Returns a number that represents a date that is the indicated number of working days before or after a date (the starting date). Working days exclude weekends and any dates identified as holidays. Use WORKDAY to exclude weekends or holidays when you calculate invoice due dates, expected delivery times, or the number of days of work performed. |
XOR | Returns a logical Exclusive Or of all arguments. |
YEAR | Returns the year corresponding to a date. The year is returned as an integer in the range 1900-9999. |
YEARFRAC | Calculates the fraction of the year represented by the number of whole days between two dates (the start_date and the end_date). Use the YEARFRAC worksheet function to identify the proportion of a whole year's benefits or obligations to assign to a specific term. |
Trigonometric and Specialized Mathematical Functions
ACOS | Returns the arccosine, or inverse cosine, of a number. The arccosine is the angle whose cosine is number. The returned angle is given in radians in the range 0 (zero) to pi. |
ACOSH | Returns the inverse hyperbolic cosine of a number. The number must be greater than or equal to 1. The inverse hyperbolic cosine is the value whose hyperbolic cosine is number, so ACOSH(COSH(number)) equals number. |
ACOT | Returns the principal value of the arccotangent, or inverse cotangent, of a number. |
ACOTH | Returns the inverse hyperbolic cotangent of a number. |
ASIN | Returns the arcsine, or inverse sine, of a number. The arcsine is the angle whose sine is number. The returned angle is given in radians in the range -pi/2 to pi/2. |
ASINH | Returns the inverse hyperbolic sine of a number. The inverse hyperbolic sine is the value whose hyperbolic sine is number, so ASINH(SINH(number)) equals number. |
ATAN | Returns the arctangent, or inverse tangent, of a number. The arctangent is the angle whose tangent is number. The returned angle is given in radians in the range -pi/2 to pi/2. |
ATAN2 | Returns the arctangent, or inverse tangent, of the specified x- and y-coordinates. The arctangent is the angle from the x-axis to a line containing the origin (0, 0) and a point with coordinates (x_num, y_num). The angle is given in radians between -pi and pi, excluding -pi. |
ATANH | Returns the inverse hyperbolic tangent of a number. Number must be between -1 and 1 (excluding -1 and 1). The inverse hyperbolic tangent is the value whose hyperbolic tangent is number, so ATANH(TANH(number)) equals number. |
DEGREES |
Returns the arccosine, or inverse cosine, of a number. The arccosine is the angle whose cosine is number. The returned angle is given in radians in the range 0 (zero) to pi. |
LN | Returns the natural logarithm of a number. Natural logarithms are based on the constant e (2.71828182845904). |
LOG | Returns the logarithm of a number to the base you specify. |
LOG10 | |
LOGEST | In Excel Online, you can project values in a series by using worksheet functions, or you can click and drag the fill handle to create a linear trend of numbers. But you can’t create a growth trend by using the fill handle. |
SEC | Returns the secant of an angle. |
SECH | Returns the hyperbolic secant of an angle. |
SIN | Returns the sine of the given angle. |
SINH | Returns the hyperbolic sine of a number. |
TAN | Returns the tangent of the given angle. |
TANH | Returns the hyperbolic tangent of a number. |
0 Comments