Learning Objectives

After completing this unit, you’ll be able to:

Formula Field Cheat Sheets Salesforce CRM, For Administrators There are some great Cheat Sheets on the developer community which might be a valuable tool for Salesforce administrators trying to make the most of custom formula fields. Here is the Cheat Sheet page on the developer site, and here are links to the Black & White / Color version. Abstract A formula is an algorithm that derives its value from other fields, expressions, or values. Use formulas in custom fields, evaluation criteria for data validation and workflow, and data calculations in reports, as well as the expression language in Visualforce. This tip sheet lists the operators and functions that formulas can include. Having resources like the cheat sheets provided here can help simplify learning the features available on the platform. Among my favorites is the Salesforce1 Admin Cheat Sheet. Many Salesforce customers haven’t fully jumped on the Salesforce1 bandwagon and this resource gives a powerful crash course to get started.

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

Salesforce Formula Cheat Sheet Download

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

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.

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.

Salesforce Formula Mod

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: 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 can adapt the formula to fit our fields.

1. In the Object Manager, click Account | Fields & Relationships and click New.
2. Select Formula and click Next.
3. In Field Label, enter Accumulated Interest. Field Name populates automatically.
4. 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.
5. Insert the * Multiply operator.
6. Select the Advanced Formula tab and insert the EXP function. EXP(number) returns e raised to the power of number.
7. In place of number, build the formula for interest rate: first, insert the field we made earlier, Interest_Rate__c, between the parentheses.
8. Select the Simple Formula tab and insert the * Multiply operator after the Interest_Rate__c field.
9. Select the Advanced Formula tab and insert the YEAR function after the * Multiply operator.
10. In place of date in the YEAR function, insert the TODAY function.
11. Select the Simple Formula tab and insert the - Subtract operator after the second close parenthesis at the end of the formula (the close parenthesis for the YEAR function).
12. Select the Advanced Formula tab and insert the VALUE function after the - Subtract operator.
13. In place of number in the VALUE function, insert the YearStarted field.

After you enter the function in the formula editor, you can click Next. On the Field Security page, click Next and then Save to finalize your formula.

Congratulations, you successfully wrote 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.
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.
3. Number formulas are helpful for doing basic unit conversions. This formula converts Celsius temperatures to Fahrenheit.And this one converts miles to kilometers.

Common Errors with Number Formulas

Salesforce Formula Examples

Excel Formula Cheat Sheet

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

Salesforce Formula Functions Cheat Sheet

Resources

Whether you’re new to the Salesforce community, or have been around for years, you understand the vast potential of the platform. With incredibly powerful functionality, and new features rolling out quarterly, keeping up with the system can feel daunting while trying to keep up with your growing business. Not to worry, these cheat sheets, designed by Salesforce, can give you some useful tips and tricks for making an impact while you grow your knowledge!

One of the most difficult things about Salesforce can be wrapping your head around what it can and cannot do. Having resources like the cheat sheets provided here can help simplify learning the features available on the platform. Among my favorites is the Salesforce1 Admin Cheat Sheet. Many Salesforce customers haven’t fully jumped on the Salesforce1 bandwagon and this resource gives a powerful crash course to get started. I also enjoy the Visualforce Cheat Sheet because it provides some useful tips for getting your custom pages to behave the way you expect. Check them out along with all the rest, and start building something cool today!

-Jared and the Salesforce Guys