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:

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:

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):

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:

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:

Company	Amount	Discount
ABC INC	100	0.01
XYZ CORP	2000	0.02
ABC INC	400	0.05
RST LLC	50	0.05
XYZ CORP	1000	0.02
XYZ CORP	5000	0.02

I may want to create a calculated field that calculates the total discount they will receive:

Company	Amount	Discount	Calc_field
ABC INC	100	0.01	1.00
XYZ CORP	2000	0.02	40.00
ABC INC	400	0.05	20.00
RST LLC	50	0.05	2.50
XYZ CORP	1000	0.02	20.00
XYZ CORP	5000	0.02	100.00

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):

Company	Amount	Discount	Calc_field
ABC INC	100	0.01	0.00
XYZ CORP	2000	0.02	40.00
ABC INC	400	0.05	0.00
RST LLC	50	0.05	0.00
XYZ CORP	1000	0.02	20.00
XYZ CORP	5000	0.02	100.00

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:

Company	Amount	Discount	Calc_field	Calc_field2
ABC INC	100	0.01	0.00	100.00
XYZ CORP	2000	0.02	40.00	1960.00
ABC INC	400	0.05	0.00	400.00
RST LLC	50	0.05	0.00	50.00
XYZ CORP	1000	0.02	20.00	980.00
XYZ CORP	5000	0.02	100.00	4900.00

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: