Number functions

The Math Functions category calculates amounts, discounts, scores and durations. These functions work with every number type: Whole Number, Decimal Number, Floating Point Number and Currency.

Two rules apply to the whole category:

• The result uses the widest number type involved, in the order Whole Number → Floating Point Number → Decimal Number / Currency. Dividing two Whole Numbers therefore gives a Whole Number and drops the fraction. See Mixing number types.
• Empty numbers count as 0 in arithmetic, Max Value and Min Value. See Empty values.

Arithmetic

Add

value1 + value2 + …Returns Widest number type

Adds two or more numbers.

Parameter	Type	Required	Description
Values	Any number	Yes, at least two	The numbers to add.

Example: totallineitemamount + freightamount gives an order total including freight.

Subtract

value1 - value2 - …Returns Widest number type

Subtracts the second and later numbers from the first, left to right.

Parameter	Type	Required	Description
Values	Any number	Yes, at least two	The first value, then the values to subtract from it.

Example: totalamount - discountamount gives the net amount of a quote.

Multiply

value1 * value2 * …Returns Widest number type

Multiplies two or more numbers.

Parameter	Type	Required	Description
Values	Any number	Yes, at least two	The numbers to multiply.

Example: quantity * priceperunit gives the amount of an order line.

Divide

value1 / value2 / …Returns Widest number type

Divides the first number by the second, and by any later numbers, left to right.

Parameter	Type	Required	Description
Values	Any number	Yes, at least two	The value to divide, then the divisors.

• Dividing by zero fails the run. Because empty numbers count as 0, an empty divisor also fails the run. Check the divisor first, for example with If(estimatedvalue > 0, actualvalue / estimatedvalue, 0).
• If every value is a Whole Number, the fraction is dropped: 7 / 2 gives 3. Make at least one value a Decimal Number to keep it, for example with To Decimal.

Example: actualvalue / estimatedvalue gives how close a won opportunity came to its estimate.

Modulo

dividend % divisorReturns Widest number type

Returns the remainder after dividing one number by another. Shown as Remainder in the outline.

Parameter	Type	Required	Description
Dividend	Any number	Yes	The number to divide.
Divisor	Any number	Yes	The number to divide by.

• The remainder has the sign of the dividend: -7 % 3 gives -1.
• A Whole Number divisor of 0 fails the run.

Example: SequenceNumber % 2 = 0 sends even-numbered cases to one queue and odd-numbered cases to another.

Raise to Power

base ^ exponentReturns Widest number type

Raises a number to a power.

Parameter	Type	Required	Description
Base	Any number	Yes	The number to raise.
Exponent	Any number	Yes	The power. Fractions are allowed, so ^ 0.5 gives a square root.

• The result is calculated approximately and then returned in the widest type of the two values. With two Whole Numbers, the fraction is dropped.

Example: Principal * GrowthFactor ^ Years projects a contract value with yearly growth, where GrowthFactor is a Decimal Number such as 1.05.

Minimum, maximum and random

Max Value

Max(value1, value2, …)Returns Widest number type

Returns the largest of the values. Shown as Max in the outline.

Parameter	Type	Required	Description
Values	Any number	Yes	The numbers to compare.

• Empty values count as 0.

Example: Max(0, estimatedvalue - Discount) makes sure a discounted value never goes below zero.

Min Value

Min(value1, value2, …)Returns Widest number type

Returns the smallest of the values. Shown as Min in the outline.

Parameter	Type	Required	Description
Values	Any number	Yes	The numbers to compare.

• Empty values count as 0, so an empty value usually becomes the minimum. Check it with Not Null first if that matters.

Example: Min(creditlimit, RequestedAmount) approves no more than the customer’s credit limit.

To find the largest or smallest item of a list, use Collection Maximum and Collection Minimum in Lists.

Random

Random(min, max)Returns Whole Number

Returns a random whole number.

Parameter	Type	Required	Description
Min Argument	Whole Number	Yes	The lowest number that can be returned.
Max Argument	Whole Number	Yes	One more than the highest number that can be returned.

The maximum is never returned

The minimum is included but the maximum is excluded. Random(1, 7) returns 1, 2, 3, 4, 5 or 6. To simulate a die, use Random(1, 7), not Random(1, 6).

Example: Random(1, 4) picks one of three queues (1, 2 or 3) to spread new leads evenly.

Rounding

All rounding functions return the same type as the value you round. Floor, Ceiling and Truncate return a Whole Number unchanged.

Round

Round(value, digits)Returns Same type as the value

Rounds to a number of decimal places.

Parameter	Type	Required	Description
Value	Any number	Yes	The number to round.
Digits (optional)	Whole Number	No	Decimal places to keep. Defaults to 0. A negative number rounds to tens, hundreds, and so on.

• Halves round away from zero, as in Excel and Power Fx: 2.5 gives 3, -2.5 gives -3, and 2.675 gives 2.68 with two digits.
• Round(1234, -2) gives 1200.

Example: Round(estimatedvalue * 0.15, 2) calculates a 15% commission to the cent.

Round Up

RoundUp(value, digits)Returns Same type as the value

Rounds away from zero. Same parameters as Round.

• RoundUp(1.21, 1) gives 1.3; RoundUp(-1.21, 1) gives -1.3.

Example: RoundUp(Hours, 0) bills every started hour as a full hour.

Round Down

RoundDown(value, digits)Returns Same type as the value

Rounds toward zero. Same parameters as Round.

• RoundDown(1.29, 1) gives 1.2; RoundDown(-1.29, 1) gives -1.2.

Example: RoundDown(Score, 0) turns a satisfaction score of 4.8 into 4 for a star rating.

Floor

Floor(value)Returns Same type as the value

Rounds down to the next whole number, toward negative infinity: Floor(2.7) gives 2, Floor(-2.5) gives -3.

Parameter	Type	Required	Description
Value	Any number	Yes	The number to round.

Example: Floor(LoyaltyPoints / 100) gives the number of full 100-point rewards a customer has earned.

Ceiling

Ceiling(value)Returns Same type as the value

Rounds up to the next whole number: Ceiling(2.1) gives 3.

Parameter	Type	Required	Description
Value	Any number	Yes	The number to round.

Example: Ceiling(Quantity / PackSize) gives the number of packs to ship, where Quantity is a Decimal Number.

Truncate

Truncate(value)Returns Same type as the value

Drops the fraction, toward zero: Truncate(2.9) gives 2, Truncate(-2.5) gives -2.

Parameter	Type	Required	Description
Value	Any number	Yes	The number to truncate.

Example: Truncate(TenureInMonths / 12) gives completed years.

Absolute Value

Abs(value)Returns Same type as the value

Removes the sign of a number: Abs(-250) gives 250.

Parameter	Type	Required	Description
Value	Any number	Yes	The number.

Example: Abs(actualvalue - estimatedvalue) gives the forecast error of an opportunity, whether it closed above or below its estimate.

Currency

Convert Currency

ConvertCurrency(amount, from, to)Returns Currency

Converts an amount from one currency to another, using the exchange rates set in Dataverse. It is listed under Math Functions and suggested wherever a Currency value fits.

Parameter	Type	Required	Description
Amount	Any number	Yes	The amount to convert.
From currency (record or ISO code)	Currency lookup, ID, or Text	Yes	The currency the amount is in: a currency record, or an ISO code such as "USD".
To currency (optional, base currency when empty)	Currency lookup, ID, or Text	No	The currency to convert to. When empty, the organization’s base currency is used.

• The result is rounded to the target currency’s precision, with halves rounded away from zero.
• When both currencies are the same, the amount is returned unchanged.
• An empty amount gives an empty result.
• An unknown ISO code, an empty From currency, or a currency without an exchange rate fails the run.

Examples

• ConvertCurrency(totalamount, transactioncurrencyid) gives an order’s total in the base currency, for reporting across regions.
• ConvertCurrency(Budget, "USD", "JOD") converts a budget from US dollars to Jordanian dinars.

Related

• Data types
• Conversion
• Lists: totals and averages of lists