Support
What's NewTeamGuru.comLog in

Calculated Metrics & Advanced Formulas

Formula examples

  • Ratio (percentage): A/B*100

    Divide metric A by metric B and multiply by 100 to get a percentage.

    Ratio formula A/B*100

  • YTD

    The YTD value is calculated as the sum of all values from the beginning of the year to the current period. If values are missing for any number of periods, use the Default value option to fill the gaps and make sure the formula is calculated.

    YTD formula with a default value

  • Rolling YTD

    Similar to YTD: sums all the values from the prior-year period (+1, so that it starts with the next period of the prior year) to the current period.

    Rolling YTD formula

Mathematical functions

  • ABS

    Returns the absolute value of a number, which is the number without its sign.

    ABS(number)

    The syntax of the ABS function has the following arguments:

    • Number: Required argument. The real number whose absolute value you want to find out.

  • LOG10

    Returns the base-10 logarithm of a number.

    LOG10(number)

    The syntax of the LOG10 function has the following arguments:

    • Number: Required argument. The positive real number whose base-10 logarithm you want to calculate.

  • MOD

    Returns the signed remainder after one number is divided by another.

    MOD(dividend, divisor)

    The syntax of the MOD function has the following arguments:

    • Dividend: Required argument. The number divided by the divisor.
    • Divisor: Required argument. The number by which the dividend is divided.

  • POWER

    Returns a number raised to a power.

    POWER(number, power)

    The syntax of the POWER function has the following arguments:

    • Number: Required argument. The base of the exponentiation. It can be any real number.
    • Power: Required argument. The exponent to which the base is raised.

  • ROUND

    Rounds a number to the specified number of digits.

    ROUND(number, digits)

    The syntax of the ROUND function has the following arguments:

    • Number: Required argument. The number you want to round.
    • Digits: Required argument. The number of digits to which you want to round the number.

  • ROUNDUP

    Rounds a number up, away from zero.

    ROUNDUP(number, digits)

    The syntax of the ROUNDUP function has the following arguments:

    • Number: Required argument. Any real number you want to round up.
    • Digits: Required argument. The number of decimal places to which the number is rounded.

  • ROUNDDOWN

    Rounds a number down, toward zero.

    ROUNDDOWN(number, digits)

    The syntax of the ROUNDDOWN function has the following arguments:

    • Number: Required argument. Any real number you want to round down.
    • Digits: Required argument. The number of decimal places to which the number is rounded.

  • SQRT

    Returns the square root of a number.

    SQRT(number)

    The syntax of the SQRT function has the following arguments:

    • Number: Required argument. The number whose square root you want to know.

  • TRUNC

    Truncates a number to an integer by removing its decimal (fractional) part.

    TRUNC(number, [digits])

    The syntax of the TRUNC function has the following arguments:

    • Number: Required argument. The number you want to truncate.
    • Decimals: Optional argument. The number of decimal places of the truncated number. The default is 0 (zero).

Logic functions

  • AND

    Returns TRUE if all arguments are TRUE.

    AND(A, B)

    The syntax of the AND function has the following arguments:

    • A: Required argument. Condition.
    • B: Required argument. Condition.

  • OR

    Returns TRUE if at least one argument is TRUE.

    OR(A, B)

    The syntax of the OR function has the following arguments:

    • A: Required argument. Condition.
    • B: Required argument. Condition.

  • XOR

    Returns the logical exclusive OR of all arguments.

    XOR(A, B)

    The syntax of the XOR function has the following arguments:

    • A: Required argument. Condition.
    • B: Required argument. Condition.

  • NOT

    Performs a logical negation of its argument.

    NOT(A)

    The syntax of the NOT function has the following arguments:

    • A: Required argument. Condition.

  • TRUE

    Returns the logical value TRUE.

  • FALSE

    Returns the logical value FALSE.

  • IF

    The IF function, one of the most popular functions in TeamGuru, lets you logically compare a value with your expectations.

    An IF statement can have two results. The first result is returned if the condition evaluates as true; otherwise, the second result is returned.

    IF(condition, a, b)

    The syntax of the IF function has the following arguments:

    • condition: Required argument. The condition to evaluate.
    • a: Required argument. The result of the IF function if the condition is TRUE.
    • b: Required argument. The result of the IF function if the condition is FALSE.

  • IFERROR

    Returns the specified value if the formula evaluates to an error; otherwise, returns the result of the formula.

    IFERROR(value, value_on_error)

    The syntax of the IFERROR function has the following arguments:

    • value: Required argument. The argument tested for an error.
    • value_on_error: Required argument. The value returned if the formula results in an error.

  • ISNULL

    Returns TRUE if the result of the formula is an empty value.

    ISNULL(value)

    The syntax of the ISNULL function has the following arguments:

    • value: Required argument. The argument tested for an empty or null value.

Statistical functions (over several periods)

  • AVERAGE

    Calculates the average (arithmetic mean) of the values.

    AVERAGE(values_list)

    The syntax of the AVERAGE function has the following arguments:

    • values_list: Required argument. A list of values, defined in TeamGuru as a range of periods.

  • SUM

    Calculates the sum of all values.

    SUM(values_list)

    The syntax of the SUM function has the following arguments:

    • values_list: Required argument. A list of values, defined in TeamGuru as a range of periods.

  • COUNT

    Returns the number of values in a list.

    COUNT(values_list)

    The syntax of the COUNT function has the following arguments:

    • values_list: Required argument. A list of values, defined in TeamGuru as a range of periods.

  • MAX

    Returns the maximum value from a list of values.

    MAX(values_list)

    The syntax of the MAX function has the following arguments:

    • values_list: Required argument. A list of values, defined in TeamGuru as a range of periods.

  • MIN

    Returns the minimum value from a list of values.

    MIN(values_list)

    The syntax of the MIN function has the following arguments:

    • values_list: Required argument. A list of values, defined in TeamGuru as a range of periods.

  • MEDIAN

    Returns the median of the given numbers. The median is the number in the middle of a set of numbers.

    MEDIAN(values_list)

    The syntax of the MEDIAN function has the following arguments:

    • values_list: Required argument. A list of values, defined in TeamGuru as a range of periods.

  • STDEVP

    Returns the standard deviation of a population (base set) determined from a random selection. The standard deviation expresses how much the values differ from the mean value.

    STDEVP(values_list)

    The syntax of the STDEVP function has the following arguments:

    • values_list: Required argument. A list of values, defined in TeamGuru as a range of periods.

  • STDEVA

    Calculates the standard deviation of a sample (selection). The standard deviation is the degree to which the values vary from the mean value.

    STDEVA(values_list)

    The syntax of the STDEVA function has the following arguments:

    • values_list: Required argument. A list of values, defined in TeamGuru as a range of periods.

  • LAST

    Returns the last value entered in the list of values. Empty and null values are ignored.

    LAST(values_list)

    The syntax of the LAST function has the following arguments:

    • values_list: Required argument. A list of values, defined in TeamGuru as a range of periods.

Still need help?

Write to us. We usually reply within an hour on business days.

Contact support