Quattro Pro Tutorial

Working with Formulas

This tutorial focuses on formulas, those mathematical and logical routines that you enter in the cells of a spreadsheet and which do the calculations. It covers the basics of writing formulas, the types of operators available, reviews the concept and rules of precedence, gives some examples, and introduces a couple of features in Quattro Pro that can make writing and using formulas easier.

The exercises in this tutorial cover writing formulas that use the standard arithmetic and logical operators. After completing these exercises you should be able to write any formula and, using the data entry and formatting lessons from previous tutorials, to create a useful and attractive spreadsheets. Using functions and graphing the results are addressed in later tutorials.

Contents


About Formulas

Formulas are the heart of your spreadsheet application. They are used to perform mathematical calculations, string operations, and logical operations on your data, can be a simple as ordinary arithmetic to complicated formulas involving many arguments, and even user-defined or third-party functions written in Microsoft VisualBASIC. Writing formulas is therefore a fundamental part of working with spreadsheets and is almost as easy as writing equations except that one must always remember three things:

  • Formulas are not equations. Formulas are simple (usually) procedures which return the value or text that are displayed in the cell.

  • Context of operators – operators such as = can have different meanings depending on where they appear in the formula. For example, the formula =(2+3) returns the sum 5 while the formula =(2=3) returns a value of 0 because the statement is false.

  • Precedence - the order in which the calculations are done. Calculations do not simply proceed from left to right.

Formulas are not equations as only one side of the equals sign is used. In this respect they are similar to the formulas used in programming languages where the equals sign really means "assign the value of the expression on the right to the variable on the left". The familiar x=x+1 is a classic example for a formula that could never be a valid equation. As a formula, however, what it does is increment the value of x.

Back to top

Operators

The operators used in formulas tell Quattro Pro what to do with the data and functions. The four categories of operators are listed in the table below.

Category Description Result
Arithmetic Familiar arithmetic operations such as addition, subtraction and division. Numeric
Equality Equal to, less than, greater than, etc. 0 if false, 1 if true
Logical Logical operations such as AND, OR and NOT. 0 if false, 1 if true
String Operators that process string (text) data. string (text)
Back to top

Precedence

The operations in a formula are not always carried out as if reading from left to right. Certain operations take precedence over others. For example, to calculate the volume of a sphere whose diameter is equal to 1 one would type the formula

=4 / 3 * @pi * (1 / 2)^3.

In this case the order of operations will be:

    1. =4 / 3 * @pi * 0.5^3
    2. =4 / 3 * @pi * 0.125
    3. =0.2945

If the rules of precedence did not apply and the calculations were performed from left to right, as written, the result would have been 0.5326.

The table below lists the precedence of operations used in formulas. Precedence is listed as number where the higher number means that operation will be performed before all lower ones.

Operation Math Symbol Quattro Pro Operator Precedence

Add, subtract

+, -

+, -

4

Multiple, divide

×, ÷

*, /

5

Exponent (power to...)

Superscript

^

7

Parentheses

( )

( )

7

Positive, negative

+, -

+, -

6

Equal, not equal

=, ≠

=, <>

3

Less than, greater than

<, >

<, >

3

Less than or equal to

<=

3

Greater than or equal to

>=

3

Logical NOT

¬

#NOT#

2

Logical AND

#AND#

1

Logical OR

#OR#

1

Concatenate Strings

n/a

&

1

Back to top

Writing Formulas

Writing formulas is easy and many of the operators are similar to those typed at the keyboard and used in high-level programming languages. Your formula may include numbers, strings (text), addresses to other cells or blocks of cells, and functions. To write a formula simply select the cell where the formula should go and type it. The procedure is:

    1. Select the cell to receive the formula
    2. Start by typing one of the characters shown in the table below.
    3. Type the formula
    4. Press ENTER
    5. Examine the result
Character Description

Significance

0 - 9 Numbers Tells Quattro Pro the formula starts with a number
. Decimal point Tells Quattro Pro the formula starts with a fraction
+ or - Positive or negative Similar to the equals sign below except that a - sign will change the sense of the value that follows it.
( Parentheses Tells Quattro Pro the formula starts with a parenthetical term, i.e, (A1+B1)
@ At sign Precedes all Quattro Pro functions
#   Used to specify logical functions such as #AND#
$ Dollar sign (may be different in other countries) Tells Quattro Pro to format the number as currency. This only applies to numbers.  Using it with a cell address, for example $A1, has a different meaning.  See the tutorial on working with cells.
= Equal sign Use this character to tell Quattro Pro that you are entering a formula. For example, if you start by entering "=A1", Quattro Pro will interpret this as referencing cell A1, but if you start by entering "A1", then Quattro Pro interprets this as string data (text).

When your formula references data in other cells there are two ways to write the equation. Either simply type the cell's address or use the arrow keys to locate the cell. For example, to enter a formula into the cell at address A2 that simply displays the negative value of the contents of cell A1:

Method 1 – Type the cell location of the source data

  1. Source Cell: A1, value is 10
  2. Select Cell: A2
  3. Type: "-A1" (without the quotes)
  4. Result: -10

Method 2 – Navigate to the source cell

  1. Source Cell: A1, value is 10
  2. Select Cell: A2
  3. Type: "-" (without the quotes), then use the arrow keys to move to the source cell, then press Enter
  4. Result: -10
Back to top

Examples

The table below contains a number of examples of formulas using simple arithmetic, grouping of operations and exponents. In the Quattro Pro Formula column more than one formula may be given. The first one always uses the actual values while the others reference cells that contain these values (A1=1, A2=2, etc.) and/or show different ways the formula can be written.

Category Mathematical-style Formula Quattro Pro Formula Result

Simple Arithmetic

1+2+3+4

=1+2+3+4

=A1+A2+A3+A4

10

1 over 3

=A1/3

1/A3

0.3333333

1+2-3 DIV 4 times 3

=1+2-3/4*3

= A1+A2-A3/A4*A3

0.75

Commutative and Associative

(1+2) `TIMES` (3+4)

=(1+2)*(3+4)

=(A1+A2)*(A3+A4)

21

1 over {1+2+3+4}

=1/(1+2+3+4)

=1/(A1+A2+A3+A4)

0.1

{(1+2) `TIMES` (3+4)} over {2+3}

=((1+2)*(3+4))/(2+3)

=((A1+A2)*(A3+A4))/(A2+A3)

4.2

Exponents

10^2

=10^2

=10^A2

100

10 ^ {1 over 3}

=10^(1/3)

=10^(1/A3)

2.1544346

10 ^{ 0.333}

=10^(0.333)

2.1527817

1 over {10 ^ {1 over 3}}

=1/10^(1/3)

=1/(10^(1/A3))

0.4641588

{(1+2+3+4)}^{-1}

=(1+2+3+4)^(-1)

=(A1+A2+A3+A4)^(-1)

0.1

Logical Operations in Formulas: You can do more with your formulas than straight-forward calculations. You can include some limited decision making in them. For instance, suppose you want your calculations to not exceed a particular value. You could use a formula like this:

=A14*(A14<A1)+A1*(A14>=A1)

What it does is return the value of A14 only if A14 is less than the limiting value given in cell A1. Otherwise, it returns the value of A1. To see how it works, let's set A14 equal to 10 and A1 equal to 8. The calculation proceeds like this:

=10*(10<8)+8*(10>=8)

=10*0+8*1

=0 + 8

=8

Manipulating String Data: Your formulas can operate on string data (text), as well as numbers. You can assemble long strings using a number of shorter strings, for instance.  For example, the formula: 

='The'&' '&'quick'&' '&'brown'&' '%'fox...'

will return the string

'The quick brown fox...'

Back to top

Preventing Overflow Errors and Minimizing Numerical Errors

I real math we don't have to worry about round-off errors, numbers that are too big or two small, or even dividing by zero.  In real math, numbers are concepts. They are not real. But they become real when we actually write the numbers down or store them in the computer's memory, and at that point we have to start making some real-world compromises. Do this to see for yourself, write down the decimal equivalent of 1/3, and don't stop until you've finished writing all of it down.  When you decide to stop anyway, count the number of digits you wrote, and that will determine the precision of your math.  In a similar manner, the compiler that generated the final executable version of you spreadsheet program allocated only a couple of bytes of memory to store each number, thus the limited precision of computer calculations.  To demonstrate, calculate 10x where x=0.333 in one case and x=1/3 in the second.  The difference between 0.333 and 1/3 might not be that great, and might even be within acceptable limits, but start using the result in later calculations and things might start going wrong.

A related result of this is that there is a limit to the largest and smallest number a computer program can handle.  In the case of Quattro Pro X3 those numbers are somewhere near ±10308 and ±10-307.  Those might seem like suitably large numbers, surely we'll never get even close to that, but in the physical sciences we often deal with very large and very small numbers, and then we multiply them together, then raise them to some power, and then we divide that by a really small or large number, and the result can be well over, or under, these numbers.  This actually happens, although it is not common.

Now that you are aware of this potential problem you can write formulas that will behave properly.  Here are some examples of what you can do:

  1. Change the order of your operations. Don't multiply a bunch of large numbers, and then divide by a bunch of small numbers. Mix them up.

  2. Switch to using units which do not force you to use such large and small numbers.

  3. Find a work around.  For instance, when calculating the geometric mean of a set of numbers you normally multiply all those numbers together, then take the nth root, n being the number of numbers that you multiplied.  With all that multiplying things could blow up before you get around to taking the root. Instead, add up the logs of these numbers, then divide by n, then take that number x and calculate the final result, which is 10x .

Back to top

Formula Composer

A very handy tool for writing and debugging formulas is Quattro Pro’s formula composer. The formula composer will let you view a diagram and the intermediate results of a formula as you write it. Or, select a cell that has a formula in it and the formula composer will show you its diagram and all intermediate results.

To use the formula select a cell that already has a formula in it or a blank cell where you want to enter a new formula and then press the Function button button on the property bar. The following dialog box will appear:

The Formula Composer can be used to both construct and debug formulas.

The formula composer can be very helpful in building formulas, selecting functions, and checking the intermediate and final results.

Back to top

Recalculation

After you finish entering your formula Quattro Pro will calculate its result and the results of all other formulas in the spreadsheet. If this slows down your work, something that can indeed happen when your spreadsheet is rather large, you can tell Quattro Pro to recalculate the spreadsheet only when you tell it to. Recalculation options are set in the notebook properties dialog (Format|Notebook Properties, or SHIFT+F12). The options are listed in the table below.

Recalculation Option Description
Automatic Recalculates all formulas automatically, pausing all other Quattro Pro operations until the calculations are complete.
Manual Recalculates all formulas when your press F9.
Background Recalculates all formulas during the time between keystrokes. Background calculations are performed automatically but do not slow you down.

To find out if Quattro Pro needs to recalculate your spreadsheet, and to see if the calculations are done, look at the bottom of the desktop for the symbols shown in the figure below.

The three symbols noted in this figure indicate: 1. recalculation in progress, 2. Quattro Pro needs to recalculate the spreadsheet. Press F9 to do this.

Last note, you can also tell Quattro Pro to calculate the spreadsheet in a row-wise or column-wise manner, instead of the default, natural order.

Back to top

Hints and Suggestions

  • It is easy to make mistakes entering even simple formulas. Misplaced parentheses are a common source of error.

  • Test your formulas, always.

  • Break long or complicated formulas into smaller parts, check the results of each part individually, and after you are sure each is working properly you can copy and paste each part into one cell, if desired. This can save a lot of time debugging formulas.

  • Use parentheses whenever you are unsure of how precedence will effect the result, or to simply make the formula easier to read.

  • Use named cells for data that is referenced frequently. This can make your formulas much easier to read, write and debug. For example, name the cell containing the value of Boltzmann’s constant Boltz.

  • One of the more common errors that occur when writing formulas has nothing to do with the formula but rather with the units of the values used. It is always best to convert all of the data to a consistent set of units before using them in formulas.

  • Quattro Pro’s formula editor can be helpful in writing and debugging your formulas. To invoke the formula editor press the button in the notebook toolbar.
Back to top