Formula 1
Functions/Operators Used
Abs(x), Subtract (-), Divide (/), Multiply (*), ToText, Concatenate (+)
Formula Purpose
As a sales manager with a large sales force, you want to identify those sales representatives who are the most consistent performers with regard to quota. You want to find those who come closest to hitting quota, regardless of whether they are slightly over or slightly under. It does not matter whether the variation is over or under quota; all that matters is the percent of variation from the mark.
Formula
ToText(Abs({file.QUOTA}-{file.SALES})/{file.QUOTA} * 100) + "%"
Result
| Sales
| Quota
| Quota - Sales
| % Variation
|
8, 000
| 10, 000
| 2000
| 20%
|
11, 000
| 10, 000
| 1000-
| 10%
|
Explanation
- The formula uses the Subtract operator (-) to subtract {file.SALES} from {file.QUOTA}. This gives the dollar variation from {file.QUOTA} (+ or -).
- The Abs(x) function converts the dollar variation to an absolute number, ignoring any + or - signs.
- It then uses the Divide operator (/) to divide that result by {file.QUOTA}. This gives the variation expressed as a decimal fraction.
- The formula then uses the Multiply operator (*) to multiply the result by 100 in order to calculate the final result in the form of a percentage.
- ToText is used to convert the calculated percent to text that can then be joined with other text.
- · The Concatenate operator (+) is used to join the percentage, once converted to text, to the percent sign (%) character.
Related topics
Formulas In Action