Using formulas and functions

Using formulas and functions

Data in Calc

We usually enter one or two types of data in a cell. These two basic types are text and numbers. However, there is one more type of data that can be entered into the cell. The third type is the formulas. Formulas are simple equations that use numbers and some variables to provide an output. Variables in a spreadsheet refer to the cell locations in which we enter the data.

A function is a calculation that we enter in a cell. They help us to manipulate the data. We have to just add the arguments and the calculation is done automatically. Functions allow us to create formulas that further give us the output.

Using Calc to set up a spreadsheet

Calc helps us to perform various functions in the Nitro Office. One more function of calc is setting up a spreadsheet. You need to take care of some common mistakes that occur while using calc for formulas and functions.

  • Avoid entering fixed values into formulas.
  • Always add notes and comments in your document. It must be clear what type of input the computer needs.
  • You must have a system that checks what a formula does.

Formulas to check errors

There are many errors that can occur while using a spreadsheet. There are formulas that help us to check these errors.

Creating formulas

There are two ways to enter the data in a spreadsheet. You can directly enter the text in the cell or use a function wizard. A formula always begins with a ‘=’ sign. Calc automatically adds the ‘=’ sign in the formula.

Operator types

There are four types of operators in calc. These four types are:

  • Arithmetic
  • Text
  • Reference
  • Comparative

Arithmetic operators:

The operators such as addition, subtraction, multiplication, and division give us numbers as output.

 

Operator                       Name                    Output

+                                          Add                       =2+2

–                                          Minus                    =2-2

*                                         Multiply                =2*2

/                                         Divide                    =2/2

Comparative operators:

These operators are present in a formula. They signify an IF condition. They return the true value if the condition is true and return a false value if the condition is false. We get a direct answer in the form of TRUE or FALSE when we enter any formula while using these operators.

Operator             Name                                  Example

=                             Equal to                                   A=B

>                             Greater than                           A>B

<                             Less than                                 A<B

>=                   Greater than or equal to              A>=B

<=                      Less than or equal to                A<=B

<>                          Inequality                              A<>B

Text operators:

We can add text in a spreadsheet using this operator. We can also join the text from different places in a spreadsheet. There is a function of CONCATENATE in Calc. It performs the same function of joining the text.

Reference operators:

Any cell gets identification by the identifier of the column and a row. On spreadsheets when you read from left to right, the reference we use for the upper-left cell is A1. To show such type of references we use reference operators. They are Range, Concatenation, and Intersection.

Order of calculation in a formula

It is a sequence of evaluation in which the operations on the numbers. Division and multiplication are always done before addition and subtraction. It is a tendency that calculations are done from left to right direction. Calc carefully evaluates the formula before calculating it. It breaks the formula into small parts in the order of the precedence.

Functions

Calc includes a great variety of functions. These functions are mostly in use for numbers. A function can be simple as well as complex. We always enter functions in upper case letters. However, if they are in small letters calc automatically reads it correctly. Each function requires an argument. Our task is to enter these arguments for the function to work properly. In Calc, the functions and arguments have almost similar names for the ease.

Some common functions are:

+: adds the arguments and returns the sum.

*:  Multiplies the arguments and returns the output.

Thus, these functions and formulas make our calculations more organized and easier.

These are some basic functions and options of using formulas and functions in a spreadsheet. You can easily use them and get the desired output.