Quattro Pro Tutorial

Working with Functions

Quattro Pro includes over 500 functions that perform tasks ranging from simple sums and averages to inverse hyperbolic trigonometry and Bessel functions. Quattro Pro also provides a rich macro language that offers additional functions, such as matrix operations, and if this isn’t enough you can write your own functions using Microsoft VisualBASIC or another high-level language that produces VisualBASIC for Applications-compatible code.

This tutorial provides a brief overview of these functions, the different types, how to use them, and resources available to help you with them. The exercises ask you to explore and experiment with some of the functions you'll be using in the SAMS exercises.

Contents


@Functions

The built-in functions are called @functions because they all start with the @ character. The format used in calling these functions is typically

@function(arguments)

where the arguments can be numbers, references to cells or groups of cells (arrays), named cells or arrays and even other functions. While many function calls are quite simple, for example:

=@sqrt(2)

They can also be quite flexible.  For example, two columns of numbers can be summed in using the following three formulas:

=@sum(A1..A10)+@sum(B1..B10)

=@sum(A1..B10)

=@sum((A1..A10)+(B1..B10))

Or, if you want to sum the absolute values of the numbers in these columns the formula could look like this:

=@sum(@abs(A1..B10))

Back to top

Categories of Functions

The 500+ @functions available in Quattro Pro encompass a wide range of applications. The table below lists them and give a brief description and SAMS will use functions from every category, not just the math and engineering categories.

Types of Functions Description
Database Similar to statistical functions such as minimum, maximum, average, standard deviation, etc., except that these functions work on database structures (tables) and the arguments include search criteria.
Date and Time Over forty time and date functions such as the current time and date, the number of months between two dates, number of days in a month, add a number of months to a specified date, etc. These functions can be very useful in some engineering calculations, for instance, modeling processes that start and end on specific dates and times, and of course for budget and purchase calculations.
Engineering These include Bessel functions (4), bit-wise logic functions (34), complex number functions (18), number conversions (25) and others (8). Examples are the error functions (erf and erfc) used in calculating diffusion profiles, gamma and Bessel functions, conversions between decimal, hexadecimal, octal, binary systems and includes conversion to and from text and 64-bit numbers.
Financial Over seventy functions specializing in annuities, bills, bonds, cash flow, CDs, depreciation and stocks.
Logical These functions allow one to test the status and contents of data in cells.
Mathematical Over 80 functions that round and truncate numbers, perform modular math, standard and hyperbolic trigonometry and inverse trigonometry functions, natural and common logarithms, conversions between degrees and radians, random numbers, π, and many others.
Miscellaneous More than fifty functions that set or test the various attributes of cells in the spreadsheet, for example, to find the location of the cell that has the minimum value, or the type (number or formula) of data in a cell.
Statistical Over 100 descriptive (summarize or describe data such as averaging and ranking) and inferential (interpretive, such as t-test, f-test, chi-test and other distribution functions) statistical functions.
String Over thirty functions that manipulate string data, including converting to and from numbers, stripping non-printable ASCII codes, extracting segments of a string, and concatenating strings.

With some 500+ functions to consider, how do you find the one you need?  Quattro Pro's help utility groups its functions in the above categories, making it easier to find what you need. Just thumb through the contents until you find the "Working with formulas and functions" listing, then select the "Quattro Pro Functions Categories" entry.

Help - @Functions Categories
Quattro Pro's help utility can help you quickly locate the function you need.
Back to top

Examples

Examples of several mathematical, engineering functions and array functions are shown in the figure below. The method of calling the mathematical and engineering functions shown here are typical of most functions. The method of calling the array functions is also typical but its usage can be very interesting. By typing only one formula one can duplicate a whole table of numbers, even perform operations on them. The @sum function is similarly interesting. Finally, the @@ function allows one to reference cells indirectly.

Examples of Functions
Examples of math, engineering, and array functions. The input values, the results, and the formulas are shown here. In the case of the array functions, the "B21=" and "A27" parts of the functions simply indicate where they were entered.

The @@ Function: The @@ function can be used to reference cells indirectly. While not a widely used function, the ability to indirectly address a cell can be very useful.  It can be particularly useful when used with various lookup-type functions such as @MaxLookup. For example

Cell A1 contains the value 10. Calling the @@ function as @@("A1") will return a 10.

Cell A2 contains the text 'A1'. Calling the @@ function as @@(A1) will return a 10.

Trigonometric Functions: When using trigonometric functions you need to keep in mind that the angles are expressed in radians. In some fields of science this is the norm, but in others, well, at best this requires an extra step in writing formulas, and at worse a few hours of frustration until you figure this out on your own. Here are a few examples of the use of trig functions:

=2*dSpacing*@sin(Theta), this is from Bragg's law for diffraction and the angle Theta (a named cell containing the value of this angle) is given in radians. dSpacing is also a named cell.

=2*dSpacing*@sin(@radians(Theta)), this is the same formula as above, except that the angle Theta is given in degrees and must be converted to radians.

=2*dSpacing*@sin(@PI*Theta/180), this is as the example above, except instead of using the @radians function the angle is multiplied by π/180, which gives the same result.

=2*@degrees(@asin(Lambda/2/dSpacing)), this formula calculates the diffraction angle 2θ per Bragg's law. In this example Lambda and dSpacing are named cells and the result is given in degrees.  The @asin() (arcsine, inverse of the sine function) function returns an angle in radians, so the @Degrees() function was used to convert this to degrees.

Array Functions: Quattro Pro's array functions can be very useful. Consider a situation where you want to calculate the cube roots of each of the numbers in column B. The traditional method is:

  1. Enter the formula =B1^3

  2. Copy this formula down the column. Quattro Pro will adjust the cell addresses, i.e., B2^3, B3^3...

But, using the array functions you can simply do this

  1. Enter the formula =@array((B1..B10)^3)

In other words, wrap your formula in the @array() function. For this simple example this might not seem to be much of an advantage, but it can really help when you are working with complicated formulas and long columns of numbers.

Back to top

External Functions

Coming soon.

Back to top

Quattro Pro/Excel Equivalent Functions

Quattro Pro and Excel offer nearly identical sets of built-in functions so Excel and Quattro Pro users generally have little difficulty switching between these two spreadsheet programs. Some functions, however, may be named differently. Search Quattro Pro’s help topics index using "Functions, Excel equivalents" to see the complete list.

Back to top

Hints and Suggestions

  • Be careful using trigonometry functions. All angles are expressed in radians, not degrees. The @radians and @degree functions can be helpful in these cases.

  • Divide-by-zero and overflow errors will be reported by log, ln and similar functions when the value of the argument is illegal or out of range.

  • When inserting columns or rows Quattro Pro will automatically adjust the cell references in all formulas, except in functions that ask you to enter cell locations as strings. The @@ function, for example. If your formula contains @@("A10") and you delete row 5, cell A10 will become cell A9, and the @@ function will have to be changed manually. The same is true for macro commands.

Back to top