📣 Attention Salesforce Certified Trailblazers! Link your Trailhead and Webassessor accounts and maintain your credentials by December 14th. Learn more.
close
trailhead

Use Numbers, Currency, and Percentages in Formulas

Learning Objectives

After completing this unit, you’ll be able to:
  • 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.

Introduction to Numbers, Currency, and Percentages in Formulas

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.

Note

Note

Custom formula fields that use currencies aren’t associated with any particular currency. If your organization has multi-currency enabled, the result of a currency formula displays in the currency of the associated record.

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.

You can mix and match these three numerical data types in formulas, but keep their differences in mind. And remember, no matter which type you’re using, the value your formula returns can’t be more than 18 digits long, including the digits before and after the decimal point.
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.

Common Number, Currency, and Percent Functions and Operators

When you work with numbers, you use certain functions and operators often.

Mathematical Operators

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 <>.

Mathematical Functions

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.

Use Numbers, Currency, and Percentages in Formulas

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

  1. In Setup, use the quick find box to find the Object Manager.
  2. Click Account | Fields & Relationships and click New.
  3. Select Currency and click Next.
  4. In Field Label, enter Principal. Field Name populates automatically.
  5. In Decimal Places, enter 2. Because this is a currency amount, we want two digits after the decimal point.
  6. Change Length to 16. Leave the rest of the options as default, and click Next.
  7. Click Next again and then click Save.
We’ll use Principal to conditionally calculate an interest rate that we can then use to calculate the amount of compounded interest on the account.

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.

  1. In the Object Manager, click Account | Fields & Relationships and click New.
  2. Select Formula and click Next.
  3. In Field Label, enter Interest Rate. Field Name populates automatically.
  4. Select Percent and leave the decimal places at the default 2.
  5. Click Next.
  6. Enter the following formula:
    IF(Principal__c < 10000, 0.02, 
      IF(Principal__c >= 10000 &&
        Principal__c < 100000, 0.03, 0.04))
    Note

    Note

    Our if statement has one gap: Although Currency amounts can be negative, we’re assuming that Principal__c is positive. If it isn’t, this account is in trouble!

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.

Variable Value
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().

This gives us our final function.
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.

Number Formula Examples

  1. Given the margin percent, number of items sold, and the price per item, this formula calculates gross margin profit.
    Margin_percent__c * Items_Sold__c * Price_Item__c
  2. 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 amount.
    MIN(500, Sale__c * Commission__c)
  3. Number formulas are helpful for doing basic unit conversions. This formula converts Celsius temperatures to Fahrenheit.
    1.8 * Degrees_Celsius__c + 32
    And this one converts miles to kilometers.
    Miles__c / 0.6213711921

Common Errors with Number Formulas

  • 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 error.
    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.
retargeting