Introduction

Let’s take a deep dive into those formula fields in Salesforce that involves numbers → Number, Currency, and Percentages!

Numbers being the simplest of all three, are a delight to work with! We can create formula fields in Salesforce that returns a numeric value, and they can have anywhere between 0 to 18 digits in the decimal place. These Numbers can be positive or negative, integer or decimal! Numbers are great for measurements such as dimension, volume, temperature, and populations, to name a few.

Percents are quite similar to numbers, except they have their own data type. They’re represented as a number with a percentage sign attached to them. When we say percent, what we’re really saying is per 100. A percent gets stored as a number divided by 100. For example, 100% is represented as 1, and 2% as 0.02. Percents can be used to represent Discounts, Bank Interest Rates, etc.

The currency also has its own data type. Currency values come with a currency sign built-in. For example, if your org has USD as its currency, all formulas returning Currency will have the dollar sign attached. For example, 100 dollars would be represented as $100.

When we create a custom formula field that uses Currency, they aren’t associated with any particular currency. And if your org has multi-currency enabled, the result of the currency formula depends on the associated record.

We can mix and match these three numeric data types in formulas. And a point to keep in mind. → The value returned by these formulas can’t be more than 18 digits length, including the digits before and after the decimal point.

Common Number, Currency, and Percent Functions and Operators

When working with numbers, we’ll often use functions and operators. Let’s get to know a couple of the frequently used functions and operators.

Mathematical Operators

I’m sure we’re all familiar with a couple of these mathematical operators we’ve used since our earliest arithmetic classes. We can add (+), subtract (-), multiply (*), and divide (/) numbers, additionally, we can use exponentiation (^) to multiply a number a certain number of times with itself.

The next set of operators is used to compare two numerical values. We can use greater than (>), less than (<), greater than or equal (>=), or less than or equal (<=). To check if two numbers are equal to each other, we can use =. We can use do not equals, and they can be written as != or <>, to check if two numbers are not equal.

Mathematical Functions

We can access mathematical functions too in the formula editor. A few of the frequently used functions include:

• ROUND(number, num_digits) – Returns a number to a specified number of digits. It uses the round half-up rule, meaning halfway digits are always rounded up.
  Example: ROUND( 1.45,1) = 1.5 , ROUND( 1.42,1) = 1.4

• ABS(number) – Returns the absolute value of a number, a number without it’s sign.
  Example: ABS(-10) = 10.

• MAX(number, number,…) – Returns the greatest of all the arguments.
  Example: MAX(10,20,5) = 10.

Use Numbers, Currency, and Percentages in Formulas

Let’s use the number, currency, and percentage fields to create some magic!

Create a Custom Field for the Principal

Principal represents the sum of money invested or lent on which interest is paid. We’ll be using this field to calculate an interest rate that we can use to compute the amount of compounded interest on the account.

1. From Setup, open Object Manager.
2. Open Account, and select Fields & Relationships from the sidebar.
3. Click New.
4. Select Currency for Data Type, and then click Next.
5. For Field Label, enter Principal.
6. Change Length to 16, and enter 2 for Decimal Places.
7. Click Next, Next, and Save.

Calculate Percent Interest

Let’s calculate the interest rate for the account. If the principal balance is less than $10,000, the interest rate is 2% or 0.02. If the balance is between $10,000 and $50,00, the interest rate is 3%, or 0.03. If the balance is over $50,000, the interest rate is 4% or 0.04.

1. From Setup, open Object Manager, and click Account.
2. Open Fields & Relationships from the sidebar.
3. Click New.
4. Select Formula for Data Type. And then click Next.
5. In the Field Label, enter Interest Rate.
6. For Formula Return Type, select Percent and then click Next.
7. Click Next, Next, and Save.

Enter the following formula:

IF (  Principal__c < 10000, 0.02, 
IF( Principal__c >=10000 &&  Principal__c <50000, 0.03, 0.04))

Calculate Accumulated Interest on an Account

Now that we have a principal amount and an associated interest rate, let’s calculate the accumulated interest on the account. We’ll be using this formula to calculate the accumulated interest on the account.

1. From Setup, open Object Manager, and click Account.
2. Open Fields & Relationships from the sidebar.
3. Click New.
4. Select Formula for Data Type. And then click Next.
5. In the Field Label, enter Accumulated Interest.
6. For Formula Return Type, select Currency, and then click Next.
7. After you enter the formula given below, Click Next, Next and Save.

Principal__c * EXP( Interest_Rate__c * ( YEAR( TODAY()) - VALUE ( YearStarted )))

The functions we’ve used here include the following:

• EXP (number) – Returns e raised to the power of a given number.
• YEAR date) -Returns the year of a date.
• TODAY() – Returns the current date.
• VALUE(text) – Converts a text string that represents a number to a number.

Number Formula Examples

1. Convert Fahrenheit Temperatures to Celsius

((  Fahrenheit__c - 32) * 5 ) / 9

2. Convert Miles to Kilometres

Miles__c / 0.6213711921

3. Sales Commission

We’ll use the function MIN(), which returns the smallest of all the arguments. The commission earned by a salesperson is the lesser amount between $ 1,000 and the percent commission multiplied by the sales amount.

MIN(1000, Sale__c * Commission__c)

4. Volume of a Cube

The function, PI(), returns the value of Pi.

4/3 *  PI() * ( Radius__c ^ 3 )

Common Errors with Number Formulas

It’s natural to encounter errors when working with formulas. Let’s take a look at some common errors.

1. Division by Zero

If your formula divides by zero, you’ll run into an error later on. If you’re dividing two numbers, ensure that the denominator is never zero. If you still need to divide a number by zero, we can work around that issue using an IF() statement.

For Example, if we need to avoid dividing a number by zero, let’s do the following to avoid an error:

IF(Field__c <> 0, 25/Field__c, 0)

2. Checking empty fields

It’s quite tempting to check if a field is blank using the equal (=) or not equal operator (!=). But the best practice in this scenario is to use the built-in function ISBLANK() to check whether the field is empty or not. The function checks whether an expression is blank or not and returns TRUE if it is blank.

When we create a formula, we can choose to treat the empty fields as either blanks or zeroes. By default, Treat blank fields as zero is chosen. To check whether a field is empty using ISBLANK(), select Treat blank fields as blank.

Image Source