Using Formulas to Create Calculations
Calculated fields require formulas in order to actually create calculations. This section will provide an overview of using formulas within calculated fields.
A calculated field can be used to create calculations independent of any existing data. For example, you may want to simply multiply two numbers together. You could enter in the following formula:
20 * 5
This will produce a result of "100" for each record in your calculated field. Similarly, you could add together two pieces of text such as:
"Ex" + "ample"
This will produce a result of "Example" for each record of your calculated field. Or, you could create a Boolean expression (true or false logical condition):
3 > 1
This will produce a result of "T" for each record of your calculated field, since this formula is True.
Of course, calculated fields are far more powerful when you can add functions to perform more interesting tasks than simple mathematics. For example, you may want to take a word and make it completely uppercase, such as:
This will produce a result of "EXAMPLE" for each record in your calculated field. Or, if you wanted to find out the result of 3 to the 2nd power:
This will produce a result of "9" for each record in your calculated field.
Referencing Existing Data
The examples above showed how you can create calculations based on fixed numbers and characters. However, the true power of calculated fields become apparent when, instead of entering in fixed values, you actually reference values from other data fields. For example, the following table shows a set of customers with the company name, invoice amount and discount terms:
I may want to create a calculated field that calculates the total discount they will receive:
Amount * Discount
Or, I could use a function to provide me with specific information I am looking for. Maybe in this case, discounts are not valid if they are less than $1,000, so in order to only see valid discounts we would use this formula (using the IIF() function):
iif(Amount >= 1000, (Amount * Discount), 0)
Referencing (Nesting) Calculations
Because calculated field act just like standard fields, they can be referenced in other calculated fields as well. This can be quite helpful in building up calculations in a step by step manner by just creating new calculated fields each step of the way. The alternative is to build the entire complicated formula in a single calculated field, which may prove more prone to error. So, in the previous example, we had a calculated field called "Calc_field" that showed valid discounts. If we wanted to determine our net receipts, we could create a second calculated field based upon that first calculated field:
Amount - Calc_field
Of course, there are always multiple ways to get to the correct result. For example, we could have used any of these equivalent formulas in the beginning of this exercise to get the same result:
Amount - (iif(Amount >= 1000, (Amount * Discount), 0))
iif(Amount >= 1000, (Amount - (Amount * Discount)), Amount)
iif(Amount >= 1000, Amount * (1 - Discount), Amount)
For further information about using formulas, click on the following links: