As you can see, you’ll need to learn how to use formulas to get the most from your Panorama investment. Fortunately, formulas are easy to learn and use, especially for the most common mathematical formulas like totals, taxes and percentages.
The Formula Workshop, available in the Help menu, lets you quickly test formulas and perform calculations. It’s great for quick experiments with formulas before you put them into actual use.
Formula results are calculated immediately as you type, there is no need to press Return or Enter.
To learn more about this workshop, see Formula Workshop.
Just as a sentence is constructed from basic words, a formula is created by combining simple elements — values (also called operands), operators and functions. Values (operands) are roughly equivalent to nouns, while operators and functions act as verbs. This illustration show the components that go into a typical formula.
Panorama formulas have grammar rules just as human languages like English and Spanish do. These rules tell how values, operands and functions can be combined to make a valid formula.
The simplest formula is a single data value. Here are four examples of such simple formulas.
A 47 "Oregami" ShippingMethod
Two values can be combined with an operator in between. The first example below adds two numbers together. The second example multiplies two numbers together. The third example appends two text values together (to produce a value like Mr. Jones).
2+2 Total * TaxRate "Mr. " + LastName
The values must be the appropriate type for the operator. For example, you can multiply two numbers together like this
but you cannot multiply two text values together like this (see Grammar Errors below).
"Mr. " * LastName
You can combine three or more data values with an operator between each pair of values.
7+3*4/2 FirstName + " " + MiddleInitial + " " + LastName
When a formula contains more than one operator, the calculations are performed from left to right unless one of the operators has a higher precedence (priority). This is the natural arithmetic order—multiply and division first, then addition and subtraction. This list shows the order of precedence for all operators.
For example, consider the formula below.
Panorama first multiplies 3 * 4 to get 12, then divides this by 2 to get 6. Finally it adds 7 (addition is last because of its low precedence) to get the final result, 13.
You can override the natural calculation order with parentheses. For example, the parentheses in the formula below force the addition to be calculated first, then the multiplication and division.
(7 + 3) * 4 / 2
Now the final result is 20 instead of 13. When in doubt you can always add parentheses to force Panorama to calculate the formula in any order you want.
A function is a formula component that calculates a value. It may calculate the value out of thin air (for example, calculating the value of the current date or time) or it can calculate the value from other values (for example trigonometric functions calculate values from angles). Panorama has several hundred functions available. Each function has a name, and is always followed by parentheses. For example, the tan( function calculates the tangent (a trigonometric function) of an angle.
A function can be used in a formula anywhere a regular value can be used. Just as with ordinary values, you can use operators to combine functions with other values (and functions).
3 + tan(35)
The value operated on by the function is called a parameter.
A function takes the parameter value (in this case 35) and transforms it into another value (in this case 0.4738, the tangent of 35). The parameter can be a formula itself, like this.
tan( A + B )
In this case Panorama first calculates the value A+B, then computes the tangent of that sum. A parameter may be as complex a formula as you need, with additional parentheses and even other functions nested inside the first function.
tan( sqr( A + B ) + 1 )
The parameter to the sqr( function is A+B, while the parameter to the tan( function is sqr(A+B)+1. (The sqr( function, by the way, calculates square roots.) Panorama will always calculate the formula from the inside out until the entire formula has been computed.
Many functions use more than one parameter. When more than one parameter is required each parameter is separated from the next by a comma. All of the parameters are surrounded by parentheses, just as with single parameter functions. For example, the pattern( function (shown below) requires two parameters. The first parameter must be a numeric value (in this case a calculated average) and the second parameter must be a text value containing a pattern for formatting the number (see Numeric Patterns).
Some functions require as many as six parameters.
Most function parameters are required — you must supply them every time you use the function. However, some functions have optional parameters. You can leave these off if you don’t need them. If a function has optional parameters, that will be mentioned in the documentation page for that function.
A small handful of functions don’t require any parameters at all. These functions generate a value all by themselves, either by consulting the computer hardware (current date, current time), querying internal Panorama data (line number, imported data) or by generating a completely random number each time the formula is computed.
today() ☞ current date now() ☞ current time seq() ☞ line number import() ☞ line of text from import file rnd() ☞ random number
As you can see, these functions have no parameter within the parentheses. You cannot omit the parentheses — you are required to include them as shown in the examples above.
Most of the examples you’ve seen so far have extra spaces between the components, like these.
7 + 3 * 4 / 2 FirstName + " " + MiddleInitial + " " + LastName tan( sqr( A + B ) + 1 )
Panorama ignores spaces between components. You can leave out the spaces, like this.
7+3*4/2 FirstName+" "+MiddleInitial+" "+LastName tan(sqr(A+B)+1)
Or you can add extra spaces between components, or even carriage returns, like this. (Note: Some dialogs do not allow you to enter carriage returns, because pressing the Return key closes the dialog.)
7 + 3 * 4 / 2 FirstName + " " + MiddleInitial + " " + LastName tan( sqr( A + B ) + 1 )
Spaces are only ignored between components, not within components. A common mistake is to place a space in between the function name and the left parenthesis. This is not allowed. The formula below will not work (see Grammar Errors below) because of the spaces after
tan ( sqr ( A + B ) + 1 )
Another common problem is spaces or other punctuation in field names. If your database has fields named First Name, Middle Initial and Last Name you might be tempted to try a formula like this.
First Name + " " + Middle Initial + " " + Last Name
Sorry, but it won’t work (see Grammar Errors below). Because of the spaces inside the field names, Panorama will think that First and Name, Middle and Initial, and Last and Name are separate components. The solution is to place chevron (« and ») characters around the field names. Press Option-\ to create the « chevron character and Shift-Option-\ to create the » chevron character. Here’s the revised formula, which will work perfectly.
«First Name» + " " + «Middle Initial» + " " + «Last Name»
You’ll also need to put chevrons around a field or variable name that contains punctuation, for example
Without the chevrons Panorama will think that this is four separate components:
P / E Ratio
Unlike a human listener, Panorama is not able to tolerate incorrect or sloppy grammar. If you ask Panorama to calculate a formula that has incorrect grammar it will refuse to comply until you correct the mistake. For example, consider the formula shown below
When you try to use this formula, Panorama will complain that an operator was encountered when an operand was expected. The solution is either to remove the extra + operator or add another value in between the two + symbols.
When you are editing a formula within a procedure, Panorama will attempt to point out the location of the grammatical error when you press the Check Procedure tool.
This location is usually fairly close to where the actual error is. However, in some cases Panorama is unable to determine exactly where the problem is. Consider the formula shown below, which has a missing left parenthesis. When I press the Check Procedure tool, Panorama indicates that there is a Missing Left Parenthesis and indicates that the error is just before the right parenthesis.
But wait — is this really where the error is? No, the error actually is somewhere earlier in the formula. In this case the missing ( probably goes in front of the B or the C. Panorama has done the best job it could to locate the error for you. One thing you do know for sure, though, is that the error is always before the insertion point and not after.
Panorama allows “comments” to be placed inside a formula. A comment is a note within the formula that is ignored when the formula is evaluated. A comment must start with
/* and end with
«C Ratio» /* C Ratio must be updated every 24 hours */ * Amount
will produce the same value as this one:
«C Ratio» * Amount
In addition to notes to yourself, comments are also useful for temporarily disabling a section of a long formula without actually deleting the text (for example if you are trying to debug the formula).
|10.0||No Change||Carried over from Panorama 6.0|