Formulas and Functions in MS Excel

Formulas and Functions in MS Excel

MS Excel is quite renowned for its excellent support for complex calculations and built-in formulas and functions. It also provides the flexibility to use these formulas and functions in an entirely convenient manner. As long as you have the understanding and knowledge of what you want to implement, MS Excel provides you with the platform to implement it.

What is a formula?

A formula is an arithmetic expression that calculates the values provided in the given range. It performs the intended operation (i.e. +, -, *, /, etc.) on the provided values and generates the desired output.

What is a function?

A function is a procedure defined in MS Excel to execute the formulas with more ease and efficiency. An example of a function is SUM(). Normally, if you sum the values of two cells (A1 and B1), you would write a formula like  A1 + B1. Now, the same can be obtained using the SUM function. The syntax would be SUM(A1:B1).

The advantage of using the function is when you have to calculate more number of values like for instance if you are required to sum the values of Cell A1 to Z1, it will take you some time to generate the formula however, in SUM function, you will just type SUM(A1:Z1). This will select the whole range and will apply the SUM function to produce the desired result.

The above explanation is provided to give you the general understanding that how effectively MS Excel facilitates the users with meeting their requirements.

Procedure for entering formulas

Entering formula is quite simple, as illustrated in fig.bel, Value 1 and Value 2 are assigned to Cell A2 and B2. Now, the result was required in Cell C2.

Therefore, the formula is written in C2 as “= A2 + B2, where A2 and B2 are cells containing values and “+” is the operation to be performed. Now the most important element of any formula or function is the “=” sign. This sign is the instruction point for MS Excel that anything written afterwards, will be a formula or a function and will be executed.

Result View of Cell C2 Formula view of Cell C2

Now, let’s see some general step by step examples of entering formulas.

Example 1:     Applying a formula

In this example, you will observe a “+” formula implementation on Cell A1 to Cell A3. The result will be shown in Cell A4

1. Enter 3 values in Cell A1 to A3
2. Select Cell A4, where the result of formula will be shown

Formula view on Cell A4

3. Type “=” and click on Cell A1. You will observe a scrolling dashes against the cell. This refers that the cell has been marked.
4. Type “+” and click on Cell A2.
5. Again, type “+” and click on Cell A3.
6. Press “Enter” key.
7. Output of the formula will be displayed in Cell A4 

Formula output on Cell A4

Example 2: Editing a formula

In this example, you will observe a change in the formula applied in Example 1, in which, “+” operation was performed. Now, in current example, the “+” operation will be changed with “-“ formula. There are two methods in which you can make the changes in the formula.

Method 1: Editing formula in output cell.

1. Double Click on the Cell A4. This will put the cell in edit mode
2. Change the “+” symbol with “-“ symbol
3. Press Enter key 

Formula view on Cell A4 2

Method 2: Editing formula in Formula Bar

1. Select Cell A4
2. In formula bar, change the “+” symbol with “-“ symbol.
3. Press Enter key

Formula view on Formula bar

Example 3: Copy/Paste a formula

Just like values in cells, MS Excel supports copy and pasting of formula as well. The operation is performed just like any other copy and pasting operation.

In this example, you will observe that, there are 3 values entered in Cell B1 to B3 but instead of typing the formula (as did in Example 1), a Copy function is applied on Cell A4 and Paste function is applied on B4.

1. Enter any numeric values in Cell B1 to B3

Copy Selection on Cell A4

2. Select Cell A4
3. Click on “Copy” from Clipboard tab or Press “Ctrl + C”
4. Select Cell B4
5. Click on “Paste” from Clipboard tab or Press “Ctrl + V”

Pasted results on Cell B4

Applying a function in MS Excel

Applying function in MS Excel is as simple as applying a formula. You don’t need to do any complex tasks.

In the following example, you will observe the step by step implementation of SUM function on Cell A1 to J1 and the output result will be displayed on Cell K1.

Example 1 Application of SUM function.

1. Put numeric values in Cell A1 to J1

Text Range from Cell A1 to J1

2. Select Cell K1

3. Type =SUM(A1:J1)

Formula view on Cell K1

4. Press Enter key and the result will be shown in Cell K1

Result output on Cell K1

Example 2: Application of AVERAGE function

In this example you will be observing the application of AVERAGE function on the data from Example 1. The AVERAGE function will be implemented on Cell L1. This formula will be covering the values from Cell A1 to J1.

Text range from Cell A1 to J1

1. Select Cell L1

2. Type =AVERAGE(A1:J1)

Formula view on Cell L1

3. Press Enter key and the result will be output on Cell L1

Formula Result output on Cell L1

More Related Articles For You

    Microsoft Excel Tutorial