# FORMULA OPERATORS

There are four different types of calculation operators: arithmetic, comparison, text concatenation and reference.

Arithmetic Operators

To perform basic mathematical operations such as addition, subtraction or multiplication, combine numbers and produce numeric results, arithmetic operators are used.

Comparison Operators

You can compare two values with the following operators. When two values are compared by using these operators, the result is a logical value either TRUE or FALSE.

Text Concatenation Operator

Use the ampersand (&) to concatenate (combine) one or more text strings to produce a single piece of text.

Reference Operators

To combine the range of cells for various calculations reference operator are used.

Order of Evaluation of Formulas

Results provided by the formulas, are dependent on the preferences of operators. Preferences of operators are as follows:

: Range reference

– Negative Number

% Percentage

^ Exponentiation

* Multiplication

/ Division

– Subtraction

& Concatenation

= < > or < = or >= or > or < Comparison

Displaying Formulas

To show the formula which is entered in a cell, the steps are as follows:

Step 1 Select the cell where formula has entered. ‘

Step 2 Click Formulas tab → Formula Auditing group → Show Formulas.

Functions

A function is a predefined formula that performs calculations using specific values in a particular order, e.g. SQRT (), LOWER (), etc. Functions make it easy to perform common calculations on data.

In order to use these functions correctly, you need to understand the different parts of functions.

The Parts of Function

Each function has a specific order called “syntax”, which must be followed for the function to work correctly. The basic syntax to create a formula with function is:

• to insert an equal sign (=).

• a function name (for e.g. SUM).

• an argument that contain the information related to formula to calculate such as range of cell references.

Function Categories

In MS-Excel, functions are categorised into following groups:

1. Text Functions These functions are used to remove extra characters e.g. TRIM, CLEAN, etc.

2. Logical Functions These functions are performed boolean operations on given data e.g. AND, OR, NOT. etc.

3. Date and Time Functions These functions are used to create date and time.

e.g. DATE, TIME, DATEVALUE, etc.

4. Financial Functions These are used to calculate financial calculations such as investment valuations, interest rate.

5. Maths and Trigonometry These functions perform mathematical calculation including arithmetic, sum, products and trigonometry.

e.g. PRODUCT, MOD, TAN, etc.

6. Information Functions These functions provide information about error in formula or data value.

e.g. ISERROR, ISNA, etc.

7. Engineering Functions These functions are used to perform calculations related to complex numbers, bessel functions.

etc. e.g. CONVERT, BESSELI, etc.

8. Lookup and References Functions These functions are used to perform operation on data array.

e.g. VLOOKUP, MATCH, etc.

9. Database Functions These functions are used to calculate the average, maximum or minimum data from the database list.

e.g. DMAX, DCOUNT, etc.

10. Statistical Functions These functions are used to find the largest and the smallest value from a list of data values.

e.g. MAX A, LARGE, etc.

Some useful functions in Excel are listed below:

Charts

Charts are the graphical and pictorial representation of worksheet data. Microsoft Excel supports charts, graphs or histograms generated from specified groups of cells. The generated graphic component can either be embedded within the current sheet, or added as a separate object.

Different types of chart are as follows:

1. Column

2. Line

3. Pie

4. Area

5. Scatter

6. Stock

7. Surface

8. Doughnut

9. Bubble

Components of a Chart

A chart consists of the following components:

X-axis is a horizontal axis known as category axis.

Y-axis is a vertical axis known as values axis.

Data Series is the set of values you want to plot in the chart.

Chart Area is the total area of the chart.

Legends is a unique color or pattern, which helps you to identify an individual data series.

Chart Title is the descriptive text aimed at helping user identify the chart.

Axis Titles are the titles given to three axis i.e. X, Y, Z.

Gridlines are horizontal and vertical lines which are inserted in the chart.

Creating Chart

Steps to create a chart are as follows:

Step 1 Select the data you want to summarise visually and then click on the Insert tab.

Step 2 In the charts group, click the type of chart you want to create, to display the available chart subtypes.

Step 3 Then, select anyone of them by clicking it.

Step 4 After selecting click OK button.