Excel Formulas and Functions

Microsoft Excel, a spreadsheet software, provides a rich set of formulas and functions for doing calculations and other work in your worksheets. Functions and Formulas are two important concepts in Excel:

• Excel function represents a built-in program that performs a specific operation on a set of given values. Examples of Excel functions are SUM, SUMPRODUCT, VLOOKUP, AVERAGE etc.
• Excel formulas are the expressions that use Excel functions to do calculations based on the given parameters.

For new students of Excel, it is important to understand these concepts. If you want to master Excel, you’ll have to acquire good command on Excel functions and formulas. In this article, we will learn more about how functions and formulas are used in Microsoft Excel.

Concept of Formulas and Functions

Let’s take an very simple example to understand these concepts. Let’s say you have two cells that contain numerical values. And you want to display sum of these values in a third cell. As you may know, you don’t need to manually add these values, all you require is an Excel formula.

• Cell A1 = 23
• Cell A2 = 57
• We want sum of these two in Cell B4

To perform this task, we can easily use the SUM function in the following formula in Cell B4:

=SUM(A1, A2)

When you will write this formula in Cell B4 and press enter, the sum (23+57 = 80) will appear in B4:

So, what did we learn from this simple formula example?

• You can write an Excel formula in any cell or in the formula bar
• Excel formula starts with an equal to sign ( = )
• After equal to sign comes an Excel function (e.g. SUM)
• After function name, you need to write parameters within brackets
• When you press enter, the formula executes and the result is shown in the cell

Most Excel formulas can accept cell address as parameters. If you provide cell reference as parameter, the function will automatically pick the value given in that cell.

Types of Excel Functions

Microsoft Excel comes loaded with hundreds of functions. These functions can be divided in several categories according to the tasks they perform. Examples of these categories are:

1. Maths and trigonometry: These functions perform mathematical and trigonometrical calculations. For example: SUM, FLOOR, LOG, ROUND, SQRT, ASIN, ATAN etc.
2. Statistical functions: perform tasks related with statistics calculations: For example: AVERAGE, COUNT, TRED, VARA etc.
3. Logical functions: give results based on logical constructs like IF, AND, OR, NOT etc.
4. Text functions: perform tasks related with string manipulation. For example, CONCAT, CHAR, LOWER, UPPER, TRIM etc.
5. Date and time functions: perform calculations of date and time values. For example, DATEDIFF, NOW, TODAY, YEAR etc.
6. Database functions: give results by accessing a database

For the complete list of formula categories you can see Microsoft Office website.

Nested Functions

Excel allows you to use functions in nested fashion. Nested Excel functions allow you to pass one function’s result as parameter to another function. Consequently, Excel simplifies formula usage. Initially you may feel that nesting of functions makes formula complicated —but soon you will realize that nesting actually makes your life easier!

Following is an example of nested functions:

=IF(AVERAGE(F2:F5)>50,SUM(G2:G5),0)

The above formula looks complicated but it is not so. This formula can be easily read from left to right. It says that IF AVERAGE of cell range(F2:F5) turns out to be greater than 50 then calculate the SUM of cell range (G2:G5) otherwise return zero.

Here the output of AVERAGE function is becoming an input for IF function. So, we say that AVERAGE function is nested inside IF function.

Function Parameters

Excel functions work on the input provided by you in the form of parameters. A comma separated list of parameters is given after the function name and inside brackets. There are two types of function parameters:

• Literal parameters: When you provide numbers or text string as parameter. For example the formula =SUM(10,20) will give you 30 as result.
• Cell reference parameters: Instead of literal numbers/text, we provide address of the cell that contains the value on which we want function to operate. For example, =SUM(A1,A2)

This was a basic tutorial about Excel formulas and functions. We hope the learners will find it easy to understand. If you have any questions regarding this, please feel free to ask us in the comments section. Thank you for using TechWelkin!