Panorama formulas are very adept at performing arithmetic—from simple addition to complex financial calculations. Arithmetic formulas usually work just like the ones you learned about in high school. Panorama has seven arithmetic operators:
- + addition
- - subtraction
- * multiplication
- / (or ÷) division
- ^ raise to power
- \ integer division
- mod modulo (remainder)
The ^
operator (press Shift-6) raises the operand on the left to the power specified on the right. For example the formula:
2^3
means raise 2 to the third power, equivalent to the mathematical formula:
The `` operator truncates both operands to integers and then divides them. The result is also an integer. For example,
19/5 ☞ 3.8
9.89/1.89 ☞ 5.23
but
19\5 ☞ 3
9.89\1.89 ☞ 9
Notice that because this is an integer operation, the result is not rounded.
The mod operator computes the remainder after an integer division. For example:
19 mod 5 ☞ 4
20 mod 5 ☞ 0
The result of the mod operator will always be an integer between zero and one less than the value of the operand on the right (in this case 0, 1, 2, 3, or 4).
If the first operand is negative, the result will be negative. If the second operand is negative, the result will be the same as if it had been positive. If either operand has a fractional component, that component will be removed before making the calculation, so:
21.35 mod 4.89 ☞ 1
Dividing by Zero
If you divide any number by 0, the result is infinity:
3/0 ☞ inf
You’ll often want to test to see if the result of a division is valid, which you can do with the infinity( function.
Q = X/Y
if Q = infinity()
message "Oops!"
return /* stop program */
endif
... continue with program ...
Another option is to use the validnumber( function.
Q = X/Y
if validnumber(Q) = false()
message "Oops!"
return /* stop program */
endif
... continue with program ...
It’s numerically incorrect, but if you would prefer that the result of dividing by zero be zero then use the divzero( function:
divzero(3,0) ☞ 0.0000
You can also use the divzeroerror( function if you would prefer that an error be generated.
divzeroerror(3,0) ☞ ERROR
Basic Numeric Functions
These functions perform various mathematical operations. Each of these functions takes one or more numeric parameters and returns a numeric result.
- - -- The - operator subtracts the numeric value on the right from the numeric value on the left.
- * -- The * operator multiplies the numeric value on the left by the numeric value on the right.
- / -- The / operator divides one numeric value by another.
- \ -- The \ operator performs integer division.
- ^ -- The ^ operator raises a number to a power.
- + -- The + operator works differently depending on the type of operands used with it. If both operands are numeric, it does numerical addition. If either operand is text the + operator concatenates (joins together) the two operands.
- abs( -- The abs( function calculates the absolute value of a number.
- ceil( -- The ceil( function converts a number to an integer, truncating toward positive infinity.
- divzero( -- The divzero( function divides two numbers, returns zero if denominator is zero.
- divzeroerror( -- The divzeroerror( function divides two numbers, returns an error if the denominator is zero.
- fix( -- The fix( function converts a number to an integer, truncating toward zero.
- fixed -- The fixed function converts a floating point number to fixed point.
- float( -- The float( function converts a fixed point number to a floating point number.
- infinity( -- The infinity( function returns infinity.
- int( -- The int( function converts a number to an integer, truncating toward negative infinity.
- mod -- The mod operator computes the remainder (modulo) after integer division.
- nan( -- The nan( function checks to see if a numeric value is invalid.
- nanerror( -- The nanerror( function converts invalid numeric values into an error.
- nanzero( -- The nanzero( function converts invalid numeric values into zero.
- randominteger( -- The randominteger( function generates a random integer between the starting and ending values.
- rnd( -- The rnd( function generates a random number between 0 and 1.
- round( -- The round( function rounds a number to a specified increment.
- validnumber( -- The validnumber( function checks to see if a numeric value is valid.
Scientific Functions
These functions perform various log, trig, and exponential calculations. Each of these functions takes one or more numeric parameters and returns a numeric result.
- arccos( -- The arccos( function calculates the inverse cosine of a numeric value.
- arccosh( -- The arccosh( function calculates the inverse hyperbolic cosine of a numeric value.
- arcsin( -- The arcsin( function calculates the inverse sine of a numeric value.
- arcsinh( -- The arcsinh( function calculates the inverse hyperbolic sine of a numeric value.
- arctan( -- The arctan( function calculates the inverse tangent of a numeric value.
- arctanh( -- The arctanh( function calculates the inverse hyperbolic tangent of a numeric value.
- cos( -- The cos( function calculates the cosine of an angle.
- cosh( -- The cosh( function calculates the hyperbolic cosine of a numeric value.
- eulersconstant( -- The eulersconstant( function returns the value of Euler's constant.
- exp( -- The exp( function raises e to a power.
- fact( -- The fact( function computes the factorial of a value.
- log( -- The log( function computes the natural logarithm (base e ) of a value.
- log10( -- The log10( function computes the common logarithm (base 10) of a value.
- pi( -- The pi( function returns the value of pi .
- sin( -- The sin( function calculates the sine of an angle.
- sinh( -- The sinh( function calculates the hyperbolic sine of a numeric value.
- sqr( -- The sqr( function calculates the square root of a value.
- tan( -- The tan( function calculates the tangent of an angle.
- tanh( -- The tanh( function calculates the hyperbolic tangent of a numeric value.
All of the trig functions listed in this table either have an angle as their parameter or produce an angle as their result and they expect input angles to be expressed in radians (1 radian = 180/π degrees) and their angle output will be expressed in radians. If you want to express the angle in degrees instead, you can use the degreestoradians( function to convert the angle from degrees to radians. For example, this formula calculates the sine of 45 degrees.
sin(degreestoradians(45)) ☞ 0.7071067811865475
In a procedure the degree statement may be used to temporarily switch Panorama’s trig functions to use degrees instead of radians. The radian statement switches the mode back to radians (Panorama also switches back automatically when the procedure is finished). For example, the procedure below calculates the tangent of 30 degrees, not 30 radians.
degree
height=tan(30)
Calculations performed outside of a procedure always use radians (for example in a Text Display Object).
Financial Functions
These functions calculate financial data, including loan payments, future value, and present value.
- fv( -- The fv( function (short for future value) calculates the future value of an investment.
- pmt( -- The pmt( function (short for payment) calculates the periodic payment required to pay off a loan.
- pv( -- The pv( function (short for present value) calculates the present value of an income or debit stream of payments.
They are designed to be compatible with the same functions in Microsoft Excel. The financial functions are based on the following formula.
pv(1+rate)periods+payment(1+rate ×begin)×((1+rate)periods-1)/rate+fv=0
See Also
- Characters and Unicode Values -- working with individual characters of text.
- Constants -- values embedded into a formula.
- convertvariablestoconstants -- converts all of the variables in a formula into constant values.
- Date Arithmetic Formulas -- performing calculations on dates, and converting between dates and text.
- Formula Workshop -- formula workshop wizard for testing and experimenting with formulas.
- formulacalc -- allows you to evaluate a formula that you were not able to
code into the procedure when it was being written.
- formulafields( -- returns a list of fields used in a formula.
- formulaidentfiers( -- returns a list of identifiers (fields and variables) used in a formula.
- Formulas -- basics of formulas: components and grammar.
- formulavalue -- calculates the result of a formula. Usually this is done with an assignment statement (for example `x=2 * y` ), but the *formulavalue* statement gives you more flexibility. You can specify what database is to be used for the calculation (an assignment statement always uses the current database) and you can specify the formula using a variable, making it easy to change on the fly. This statement also gives you more control over how errors are handled.
- formulavariables( -- returns a list of variables used in a formula.
- Functions -- index of all functions available for use in Panorama formulas.
- Linking with Another Database -- techniques for relating multiple database files so that they work together.
- makemergeformula( -- builds a formula from an “auto-wrap" style merge template.
- Non Decimal Numbers -- working with numbers in alternative (non-decimal) bases, including binary, octal and hexadecimal.
- Numbers -- Working with numeric values in a formula, and converting between numbers and text.
- Operators -- index of all operators available for use in Panorama formulas.
- Quotes -- text constants embedded into a formula
- Statements -- index of all statements available for use in Panorama procedures.
- SuperDates -- date and time combined into a single value.
- Tag Parsing -- Panorama functions for working with text that contains data delimited by tags, including HTML.
- Text Formulas -- manipulating text with a formula (concatenation, extraction, rearranging, etc.)
- Time Arithmetic Formulas -- performing calculations on times, and converting between times and text.
- True/False Formulas -- logical boolean calculations.
- Using Fields in a Formula -- accessing database fields within a formula.
- Values -- the raw material that formulas work with -- numbers and text.
- Variables -- storing and retrieving individual items of data, not part of a database.
History
10.0 | No Change | Carried over from Panorama 6.0. |