Math Functions for Formula Fields in Salesforce
Introduction
Math Functions for Formula Fields in Salesforce: Good to see you all again. Hopefully, you are all well. Previously, we discussed "Date/Time and Advanced Formula Fields in Salesforce". The topic of today's post is Text Functions for Formula Fields in Salesforce.
Just to make you recall formula functions can only be used in Advanced Formulas and are categorized into five types, i.e.
Date & Time
Text
Math
Logical
Advanced
So without further ado, let’s start.
In order to explain the purpose, we have created a different custom object for each category where we will create another formula field with each formula function.
For Math functions, we have created a “Math Operation” object for the Math category with one Number type field, “Number Input by User,” to provide its input.
Math Functions
Ceiling, MCeiling, Floor, MFloor & Round Function
The CEILING function rounds a number up to the nearest integer, away from zero if negative. The syntax of this function is -
From the syntax, it’s clear that the function accepts a number and returns a number.
For example, we have created a ‘Ceiled Number’ formula field to show the Ceiling number of the ‘Number Input by User’.
The following formula is used in this field.
CEILING(Number_Input_by_User__c)
The MCEILING function rounds a number up to the nearest integer, towards zero if negative. The syntax of this function is -
From the syntax, it’s clear that the function accepts a number and returns a number.
For example, we have created an ‘MCeiled Number’ formula field to show the MCeiling number of the ‘Number Input by User’.
The following formula is used in this field.
MCEILING(Number_Input_by_User__c)
The FLOOR function returns a number rounded down to the nearest integer, towards zero if negative. The syntax of this function is -
From the syntax, it’s clear that the function accepts a number and returns a number.
For example, we have created a ‘Floored Number’ formula field to show the Floor number of the ‘Number Input by User’.
The following formula is used in this field.
FLOOR(Number_Input_by_User__c)
The MFLOOR function rounds a number down to the nearest integer, away from zero if negative. The syntax of this function is -
From the syntax, it’s clear that the function accepts a number and returns a number.
For example, we have created an ‘MFloored Number’ formula field to show the Floor number of the ‘Number Input by User’.
The following formula is used in this field.
MFLOOR(Number_Input_by_User__c)
The ROUND function returns the nearest number to a number you specify, constraining the new number by a specified number of digits. The syntax of this function is -
From the syntax it’s clear that the function replaces the number with the field or expression you want rounded; replace num_digits with the number of decimal places you want to consider when rounding.
For example, we have created a 'Rounded Number’ formula field to show the Floor number of the ‘Number Input by User’.
The following formula is used in this field.
ROUND(Number_Input_by_User__c, 2)
Please find the following screenshot to understand the output of these formula fields.
Points to be noted with the ROUND function.
Enter zero for num_digits to round a number to the nearest integer.
Salesforce automatically rounds numbers based on the decimal places you specify. For example, a custom number field with two decimal places stores 1.50 when you enter 1.49999.
Salesforce uses the round half-up rounding algorithm. Half-way values are always rounded up. For example, 1.45 is rounded to 1.5. –1.45 is rounded to –1.5.
The decimal numbers displayed depend on the decimal places you selected when defining the field in the custom field wizard. The num_digits represents the number of digits considered when rounding.
Mod, Sqrt, Max & Min Function
The MOD function returns a remainder after a number is divided by a specified divisor. The syntax of this function is-
From the syntax it’s clear that the function replaces the number with the field or expression you want divided; replace the divisor with the number to use as the divisor.
For example, we have created two fields ‘My Number’ and ‘My Divisor’, and a formula field ‘Remainder’ to show the remainder.
The following formula is used in this field.
MOD(My_Number__c , My_Divisor__c)
The SQRT function returns the positive square root of a given number. The syntax of this function is -
From the syntax, it’s clear that the function replaces the number with the field or expression you want to be computed into a square root.
For example, we have created a formula field named ‘Square Root’ that will show the square root of the ‘My Number’ field.
The following formula is used in this field.
SQRT(My_Number__c)
The MAX and MIN functions return the highest number and the lowest number from a list of numbers respectively. The syntax of these functions is -
From the syntax, it’s clear that the functions replace numbers with the fields or expressions from which you want to retrieve the highest number or lowest number respectively.
For example, we have created two formula fields ‘Maximum Number’ and ‘Minimum Number’ that will show the maximum number and minimum number out of the ‘Remainder’ and ‘Square Root’ fields respectively.
The following formulas are used in these fields.
MAX(Remainder__c, Square_Root__c)
MIN(Remainder__c, Square_Root__c)
Please find the following screenshot to understand the output of these formula fields.
Points to be noted with the SQRT function.
Calculating the square root of a negative number results in an error on the detail page.
Avoid division by zero errors by including an IF function such as: IF(Amplitude__c >= 0, SQRT(Amplitude__c), null).
Abs & Distance Function
The ABS function calculates the absolute value of a number. The absolute value of a number is the number without its positive or negative sign. The syntax of this function is -
From the syntax, it’s clear that the function replaces a number with a merge field, expression, or other numeric value that has the sign you want to be removed.
For example, we have created an ‘Absolute Number’ formula field that will show the absolute value of the ‘My Number’ field.
The following formula is used in this field.
ABS(My_Number__c)
The DISTANCE function calculates the distance between two locations in miles or kilometers. The syntax of this function is -
From the syntax, it’s clear that the function replaces mylocation1 and mylocation2 with two location fields, or a location field and a value returned by the GEOLOCATION function. Replace the unit with mi (miles) or km (kilometers). Although DISTANCE can be calculated in miles or kilometers, the unit isn't returned in the calculation. If possible, include the unit of measure in the name of your distance formula field, so users know whether the distance is in miles or kilometers.
For example, we have created two Geolocation fields ‘Location 1’ and ‘Location 2’, and a formula field ‘Distance between Loc 1 and Loc 2’ to show the distance between these locations.
The following formula is used in this field.
DISTANCE(Location_1__c,Location_2__c, 'km')
Please find the following screenshot to understand the output of these formula fields.
Points to be noted with the DISTANCE function.
The DISTANCE function returns a number data type. Distance is always calculated in decimals, even if you’re displaying the geolocation notation in degrees, minutes, and seconds in the user interface. Specify the number of decimal places to show when you create a custom field.
The DISTANCE function isn’t available in reports, but it can be used in list views. To use DISTANCE in your reports, set up a formula field, and then reference the field in your reports.
DISTANCE is the only formula function that can use GEOLOCATION parameters.
There are limitations on DISTANCE accuracy and equality calculations.
DISTANCE supports only the logical operators > and <, returning values within (<) or beyond (>) a specified radius.
Distance is calculated as a straight line, regardless of geography and topography between the two points.
Exp, Ln & Log Function
The EXP function returns a value for e raised to the power of a number you specify. The syntax of this function is -
From the syntax, it’s clear that the function replaces a number with a number field or value such as 5.
For example, we have created three fields, ‘Principal Amount’, ‘Rate of Interest’, and ‘Years’, and a formula field ‘Compound Interest’ to calculate the compound interest.
The following formula is used in this field.
Principal_Amount__c * EXP(Rate_of_Interest__c * Years__c)
The LN function returns the natural logarithm of a specified number. Natural logarithms are based on the constant e value of 2.71828182845904. The syntax of this function is -
From the syntax, it’s clear that the function replaces the number with the field or expression for which you want the natural logarithm. Note: the LN function is the inverse of the EXP function. LN(10) returns the natural logarithm of 10, which is 2.30.
For example, we have created three formula fields ‘Rate x Years’ to show the product of Rate and Years and ‘Exp of Rate x Years’ to show the exponent of Rate x Years, and ‘Natural Log’ field to show the log of ‘Exp of Rate x Years’ field.
The following formulas are used in these fields.
Rate_of_Interest__c * Years__c
EXP(Rate_x_Years__c)
LN(Exp_of_Rate_x_Years__c)
The LOG function returns the base 10 logarithms of a number. The syntax of this function is -
From the syntax, it’s clear that the function replaces the number with the field or expression from which you want the base 10 logarithm calculated.
For example, we have created a ‘Log’ formula field that will show the base 10 logarithm of the ‘compound interest’ field.
The following formula is used in this field.
LOG(Compound_Interest__c)
Please find the following screenshot to understand the output of these formula fields.
We hope you enjoyed this blog! In the next one, we’ll be talking about the "Logical functions". until then you can take a dive into our other Salesforce blogs from this series here, See ya soon!! 🙂
Resources