Use Numbers, Currency, and Percentages in Formulas
- Describe what the number, currency, and percent formula return types are.
- Explain the difference between the number, currency, and percent types.
- Describe use cases for formulas with the number, currency, and percent types.
- Create a formula using the number, currency, and percent types.
When most people hear the word formula, they imagine something that involves numbers. While that’s not always true with formulas in Salesforce, a few data types are based on numbers. The most simple of these data types is the Number type. When you create a formula field that returns a Number, you can specify how many decimal places your number has, from 0 up to 18.
Numbers can be positive or negative, integer or decimal. Numbers work well for measurements, such as temperatures, lengths, and populations. Currency, however, has its own data type. A formula that returns currency data is similar to a number formula, except that Currency values come with a currency sign built in.
Percents are also their own data type—a number with a percent sign attached. A Percent is stored as a number divided by 100, meaning that 100% is represented as 1, 90% as 0.9, and so on.
|Formula Return Type||Characteristics|
|Number||A positive or negative number, either integer or decimal.|
|Currency||A number that comes with a currency sign.|
|Percent||A number that comes with a percent sign, stored as a number divided by 100.|
When you work with numbers, you use certain functions and operators often.
The most used operators in formulas that return a number, currency, or percent are the math signs that you’ve been seeing since your earliest arithmetic classes.
You can add (+), subtract (-), multiply (*), or divide (/) numerical values. You can also use exponentiation (^) in formulas. These operators work just like they do on a calculator. Formulas follow the order of operations, and you can group operations in parentheses.
You can also use greater than (>), less than (<), less than or equal (<=), or greater than or equal (>=) to compare two numerical values. To check if two numbers are equal, use =. To check if two numbers are not equal, use do not equals, which can be written either as != or <>.
The formula editor also provides some other mathematical functions. The function ROUND() rounds a numerical value that you give it. It uses the round half-up rule, meaning that halfway values are always rounded up. For example, 1.45 rounds to 1.5, and 1.43 rounds to 1.4.
This Currency formula uses ROUND() to calculate a discount and round it to two decimal points so that it’s a proper Currency amount.
ROUND(Price__c - Price__c * Discount__c, 2)
You can also use built-in operators to perform several other mathematical functions, like absolute value, ceiling, or floor. To learn more about mathematical functions, see Formula Operators and Functions in Salesforce online help.
You can use the number, currency, and percent types in formulas in many different situations, from calculating the percent commission on a sale to totaling annual revenue or profit.
Create a Custom Field for Principal
- In Setup, use the quick find box to find the Object Manager.
- Click and click New.
- Select Currency and click Next.
- In Field Label, enter Principal. Field Name populates automatically.
- In Decimal Places, enter 2. Because this is a currency amount, we want two digits after the decimal point.
- Change Length to 16. Leave the rest of the options as default, and click Next.
- Click Next again and then click Save.
Calculate Percent Interest
For this account, let’s say that if the principal balance is below $10,000, the interest rate is 2% yearly, or 0.02. If the balance is between $10,000 and $100,000, the interest rate is 3%, or 0.03. If the balance is over $100,000, the interest rate is 4%, or 0.04.
Given Principal, we use IF() to decide on the appropriate interest rate.
- In the Object Manager, click and click New.
- Select Formula and click Next.
- In Field Label, enter Interest Rate. Field Name populates automatically.
- Select Percent and leave the decimal places at the default 2.
- Click Next.
- Enter the following formula:
IF(Principal__c < 10000, 0.02, IF(Principal__c >= 10000 && Principal__c < 100000, 0.03, 0.04))
Now we have a principal amount and an interest rate—everything necessary to calculate the accumulated interest on an account.
Calculate Accumulated Interest on an Account
We’re going to create a formula that calculates the interest on an account using Principal and the number of years that the account has been open. Luckily, there’s already a mathematical formula for calculating an account’s interest over time: A = Pe^(rt), which uses the following variables.
|P||The principal amount of money in the account|
|e||The mathematical constant 2.71828182845904|
|r||The annual interest rate of the account|
|t||The number of years the account’s been open|
Now we just need to adapt the formula to fit our fields.
To start, insert the field Principal in the formula editor. We want to multiply Principal by e raised to the power of the interest rate, multiplied by the number of years that the account has been open. We can do that with the EXP() function. EXP(number) returns e raised to the power of number.
For the interest rate, insert the field we made earlier, Interest_Rate__c. To find the number of years that the account has been open, subtract YearStarted, the year that the account was opened, from TODAY(), a built-in function that returns the current day. Because YearStarted is a Text value, you first have to convert it to a Number using VALUE(), which takes a Text string and returns a Number. We also use the function YEAR(), which pulls just the year from a Date, in this case the current date returned by TODAY().
Principal__c * EXP(Interest_Rate__c * (YEAR(TODAY()) - VALUE(YearStarted)))
After you’ve entered the function in the formula editor, you can go ahead and click Next. On the Field Security page, click Next and then Save to finalize your formula.
Congratulations, you’ve successfully written a formula to calculate accumulated, continuously compounded interest on an account! You can see it in action on any account page.
- Given the margin percent, number of items sold, and the price per item, this formula
calculates gross margin
Margin_percent__c * Items_Sold__c * Price_Item__c
- This currency formula calculates a commission, assuming a maximum commission of $500.
Given the existence of a custom percent field Commission and custom
currency field Sale, this formula returns the lesser amount between
$500 and the percent commission multiplied by the sale
MIN(500, Sale__c * Commission__c)
- Number formulas are helpful for doing basic unit conversions. This formula converts
Celsius temperatures to
Fahrenheit.And this one converts miles to kilometers.
1.8 * Degrees_Celsius__c + 32
Miles__c / 0.6213711921
Dividing by zero. The same rules of mathematics you’re used to apply when writing
number-based formulas. If your formula divides by 0, you get an error later on. When you’re
dividing two numbers, ensure that the denominator is never zero. It’s especially important
to think about dividing by zero when you’re dividing by another field whose value can
change. If you still want to divide by a number that might be zero, use an IF() statement to guarantee that you don’t encounter an
error. For example, this formula calculates the revenue per employee on an account by
dividing Annual Revenue by Number of Employees. If
there are zero employees, the formula returns 0, instead of running into an
IF(NumberOfEmployees <> 0, AnnualRevenue / NumberOfEmployees, 0)
- Checking null fields. It might be tempting to check if a field is empty by using the = (equal) or != (not equal) operators to see if it equals null. But the best practice is to use the built-in function ISBLANK(). ISBLANK() takes any field as an argument and returns true if the field is blank, and false otherwise. When you create a formula, you choose to treat blank fields either as zeroes or as blanks. By default, Treat blank fields as zeros is selected. You can only use ISBLANK() to check whether a field is blank if you selected Treat blank fields as blanks.