Formula Syntax Guide

Resources and properties can be calculated in the browser and upon data export by mapping run attributes in Riffyn to variables. These variable are used to build a formulas which produce single-valued results only. Formulas use the mean value of multi-valued properties unless a summarization method is used.

Formulas can be created for actual or planned values as detailed in the articles on calculated properties and resource names.

Note that some variable names are restricted. A full list is at the end of this document.

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.

CONCATENATE

or

CONCAT

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.

Attachments

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.