Excel formula

Excel formula refers to the formulas used for various calculations. Let's first discuss the concept of formula.

What is the formula?

A formula is a set of mathematical elements that specify a rule. It connects one or more elements with an equal sign. The formula can be used to find the element's value if one or more elements are known.

For example,

Sum = a + b

If we know the value of a and sum, we can find the value of b (b = Sum - a). Similarly, if we know the value of a and b, we can find the sum (sum = a + b).

Or

Mean = Sum of all the observations/ Number of observations

The above formulas consist of three elements. If we know the value of any two elements, we can easily find the value of third element.

What is a formula in excel?

The formula in excel is calculated based on the cells enclosed within the brackets of the function. It means that these formulas have two parameters, function name and the cells declared in a function. For example, B + C + D find the sum of the range of values from B to D. The format of the functions defined in excel as a formula is given by:

Function (cell range1: cell range2)

Where,

Function: It defines the predefined formula in excel, such as SUM and AVERAGE. The names given to the function are familiar.

For example,

SUM (B1: B4)

Here, B1 and B4 are the cell range. The SUM() function will sum all the values from B1 to B4. Similarly, other formulas in excel are defined based on the same concept.

Automatic search for formulas

Excel also provides us an option to find the available functions formulas in the form of a list. So, if anyone is not aware of the available formulas, it is best to find the available formulas in excel quickly. It is given by:

'Insert function'

We can find this function under the formula option present on the tool bar. To operate, consider the below steps.

1. On the excel home page, click on the Formulas option present above the toolbar -> click on the Insert function option, as shown below:
   
2. A dialogue box will appear.
3. We can select the functions from the list, as shown above.
   We can also search for a function by specifying the statement for the corresponding function. For example,
   We need to find the minimum value, but there is no function present in the function list. So, we can specify the statement 'to find the minimum value' and press on the 'Go' option. The functions according to the specified statement will appear in the function table, as shown below:
   
   We can use the appropriate function from the table to find the minimum value.
   Similarly, other functions can be easily found by specifying the related statement.

Note: Excel provides a detailed explanation of the given formula. It can be seen on the bottom of the function dialogue box. The statement related to the formula will also appear when we type a function on the formula bar with an equal (=) sign mentioned at the beginning.

Formulas in Excel

Let's discuss the most common formulas in excel. We will also discuss examples based on each formula.

1. SUM ()

The sum function is used as the addition function. It is used to add two or more than two numbers. The addition using excel is quick as compared to the calculator. We can add hundreds or thousands or more numbers easily using the sum function.

Shortcut Method

The AutoSum symbol available on the toolbar can be directly used to add the selected numbers in a single click. It is present on the toolbar under the formula tab.

Example 1: To find the sum of Price of various products.

Consider the below steps to find the sum.

1. Click on any cell outside the given table.
2. Drag the mouse on the formula bar and type '=SUM(C5:C11).' Here, C5 and C11 are the name of the first and last element of the price column. Excel will add the numbers from C5 to C11.
3. Press Enter. The sum will appear on the specified cell, as shown below:
   

OR

1. On the excel home page, select the numbers to be added with the help of a cursor.
2. Click on the AutoSum option under the formula tab, as shown below:
   
3. The sum will appear beneath the cell of the last element.

Example 2: To find the sum of specific elements of the column.

Consider the below steps:

1. Click on any cell outside the given table.
2. Drag the mouse on the formula bar and type '=SUM(C5:C7,C9,C11).' Here, the sum will be calculated of cells C5, C6, C7, C9, and C11. The selected cells for addition are shown below:
   
3. Press Enter. The sum will appear on the specified cell, as shown below:
   

Note: We can type the formula either on the formula bar or on the specified cell. The formula will appear at both the places (formula bar and the specified cell).

2. Subtraction

It is similar to the addition process. We only need to insert a negative sign behind the number we want to subtract.

The formula is given by:

SUM(cell1, -cell2)

For example,

SUM(A1, -A3)

The above formula will be used to subtract value of cell A3 from A1. It will be considered as A1 - A3.

Let's consider an example.

Example: To find the difference between the change in prices of commodities.

Consider the below steps:

1. Click on the first cell of the difference column, as shown below:
   
2. Drag the mouse on the formula bar and type '=SUM(D3, -C3).' The value of cell C3 will be subtracted from the value given in cell D3.
3. Press Enter. The difference between two specified cells will appear. It is shown below:
   
   Similarly, we can find perform the subtraction of multiple cells.

3. Multiplication and Division

A particular name does not specify these formulas. We can directly use the multiplication and division symbol between the two numbers to compute multiplication and division.

It is given by:

A1 * A2

A1 / A2

Let, A1 = 4, and A2 = 2.

Multiplication = A1*A2 = 4*2 = 8

Division = A1/A2 = 4/2 = 2

Let's understand it with an example.

Example: to find the multiplication of two values in columns A and B.

Consider the below steps:

1. Click on the first cell of the multiplication column.
2. Drag the mouse on the formula bar and type '=C3*D3.'
3. Click on the bottom-right corner of that block and drag to the last cell of the multiplication column.
4. Press Enter. The result will appear on all the specified cells of the column. It is shown below:
   

Now, we will consider the same steps for the division. We only need to insert the formula = 'C3/D3.' It is shown below:

4. LEFT, MID, and RIGHT

These three formulas are used to break down the cell into different segments. Let's discuss this in detail.

LEFT

The LEFT formula is used to extract the starting elements from the specified cell. It is given by:

LEFT(text, Number_of_characters)

Where,

Text refers to the specified cell from which we want to extract the elements.

Number_of_characters refers to the characters we want to extract from the starting from the left most character.

MID

The MID formula is used to extract the number of elements in the middle position from the specified cell. It is given by:

MID(text, start position, Number_of_characters)

Where,

Text refers to the specified cell from which we want to extract the elements.

Start position refers to the position from where we want to start extracting.

Number_of_characters refers to the characters we want to extract.

RIGHT

The RIGHT formula is used to extract the number of elements from the last or right-end. It is given by:

RIGHT(text, Number_of_characters)

Where,

Text refers to the specified cell from which we want to extract the elements.

Number_of_characters refers to the characters we want to extract starting from the right-end character.

Let's consider an example for better understanding.

Example:

Consider the below table in excel.

The steps to extract the characters are as follows:

1. Click on the first cell of the First column, as shown below:
   
2. Drag the mouse on the formula bar and type =' =LEFT(B3,4).'
3. Press Enter. The extracted characters from the specified cell will appear.
4. Drag the bottom-right corner of the first cell to the last element. It will now appear as:
   
5. Now, click on the first cell of the Middle column and type formula =' =MID(B3,5,3)' and press Enter.
6. Similarly, click on the first cell of the Last column and type formula = '=RIGHT(B3,1)'and press Enter.
7. Drag the bottom-right corner of the first cell to the last element of both Middle and Last column. The extracted characters will appear as:
   

5. AVERAGE

The average function is same as the mean function of mathematics. It is used to find the average of the selected numbers or cells. We can calculate the average of multiple numbers easily.

It is given by:

AVERAGE(number1, number2,....)

In case of range of cells, we can specify it as:

AVERAGE(cell1: cell2)

AVERAGE() is similar to the SUM()/Number of elements in a given column

For example,

AVERAGE(5, 10, 15, 20) = SUM(5, 10, 15, 20)/ 4

Shortcut Method

To access the average symbol, select the numbers with the help of cursor -> click on the drop-down arrow in front of the AutoSum symbol under the formula tab and click on the Average option. The desired average will appear beneath the selected number cells, as shown below:

We can also access more functions by clicking on the 'more functions' option at the bottom.

Example 1: To find the average of the price of the given data table.

Consider the below steps:

1. Click on any cell outside the given table.
2. Drag the mouse on the formula bar and type '=AVERAGE(C5:C9).' Here, the average will be calculated from the range C5 to C9 or (C5, C6, C7, C8, C9).
3. Press Enter. The average of the specified data will appear on the selected cell. It is shown below:
   

The above example finds he average of the elements presents in a particular column. Let's find the average of the elements of a particular row.

Example 2: To find the average of a particular student of a class.

Here, we will find the average marks of the second student Dimple.

Consider the below steps:

1. Click on the second cell under the Average marks column to find the average of a student Dimple.
2. Drag the mouse on the formula bar and type '=AVERAGE(B6:F6).' Here, the average will be calculated from the range B6 to F6 or (B6, C6, D6, E6, F6). The specified range is shown below:
   
3. Press Enter. The average of the specified data will appear on the selected cell. It is shown below:
   
   We can calculate the percentage of rest of the students by dragging downside the bottom-right corner of the cell.

6. COUNT

Count function is used to count the number of data cells in the specified column or row.

COUNT(number1, number2,....)

In case of range of cells, we can specify it as:

COUNT(cell1: cell2)

Shortcut Method

To access the count symbol, select the numbers to count-> click on the drop-down arrow in front of the AutoSum symbol under the formula tab -> click on the count option. The desired count will appear beneath the selected number cells. It is shown below:

Example 1: To find the count of the number of students in the given data.

Consider the below steps:

1. Select any cell near the given data table.
2. Drag the mouse on the formula bar and type '=COUNT(C3:C14).' Excel will count the cells from C3 to C14.
3. Press Enter. The counts will appear on the selected cell. It is shown below:

Similarly, we can count the number of elements in a row or column.

Note: he SUM(), AVERAGE(), and COUNT() can only be applied to the list of numbers. It cannot be applied to the data in the form of alphabets. If applied, it will not consider that data or will return 0 as a result.

But, what to do if we want to count the cells with alphabets, such as names of students, name of objects, etc. To resolve this, we have a function named COUNTA(). It can count numbers, alphabets, etc. It means that it can count the cells irrespective of the data inside it. It will also ignore the empty spaces in between.

COUNTA

COUNTA() function is similar as COUNT(). Unlike COUNT(), it can count the cells containing data other than the numbers. Let's consider an example.

Example 2: To count the number of students with respect to their names.

In the above example, we have counted in terms of the marks of students. But, here we will count with respect to the names.

Consider the below steps:

1. Select any cell near the given data table.
2. Drag the mouse on the formula bar and type '=COUNTA(B3:B15).' Excel will count the cells from B3 to B15. The B15 cell is empty. Thus, excel will ignore the extra space and will count the rest of the cells.
3. Press Enter. The counts will appear on the selected cell. It is shown below:
   

7. MAX and MIN

The MAX() function find the maximum value, and the MIN() function finds the minimum value from the specified data of an array or table.

MAX(number1, number2,....)

In the case of the range of cells, we can specify it as:

MAX(cell1: cell2)

In the case to find the minimum value, the formula is given by:

MIN(number1, number2,....)

In the case of the range of cells, we can specify it as:

MIN(cell1: cell2)

Shortcut Method

To access the MAX()/MIN() symbol, select the numbers-> click on the drop-down arrow in front of the AutoSum symbol under the formula tab -> click on the Max()/Min() option. The desired maximum or minimum number from the selected numbers will appear. It is shown below:

Example 1: To find the topper of the class.

The topper of the class would score highest marks among all the students. Thus, we need to find the average marks from the list of available students.

Consider the below steps:

1. Select any cell near the given data table.
2. Drag the mouse on the formula bar and type '=MAX(C3:C16).' Excel will find the maximum value from the range of values between C3 to C16.
3. Press Enter. The maximum value will appear on the selected cell. It is shown below:

Thus, the student named Dimple is the topper of the class with the highest marks of 92.

Example 2: Find the student with least marks.

Here, we need to find the minimum average numbers of a student from the list of students. The data is the same as in example 1.

Consider the below steps:

1. Select any cell near the given data table.
2. Drag the mouse on the formula bar and type '=MIN(C3:C16).' Excel will find the minimum value from the range of values between C3 to C16.
3. Press Enter. The minimum value will appear on the selected cell. It is shown below:

Thus, the student named Mohan scored the least marks (55) in the class.

8. TRIM

The TRIM() function removes the empty spaces between the data in the given array. It is quite useful when we want to arrange the large data in a sequential order without any extra spaces. But, the TRIM() formula can only be applied to a single cell at a time. We can further calculate by dragging down the bottom-right corner of that cell.

The formula is given by:

=TRIM(cell)

For example, TRIM (A2)

Let's consider an example.

Example: To remove the extra space from the given data.

Here, we will use the TRIM() formula to remove the extra spaces.

Consider the below steps:

1. Click on the first cell of the second column (C).
2. Drag the mouse on the formula bar and type '=TRIM(B3).' The TRIM() formula here will remove all the extra spaces between the words in the specified cell.
3. Press Enter. The specified statement after the removal of extra spaces will appear on the specified cell, as shown below:
   
4. Drag the bottom-right corner of that cell to the last cell of the column C. The TRIM() formula will be applied automatically to the other cells beneath it, as shown below:
   

9. IF

The IF() function is used to sort the given data s per the logic. It means that excel check the logic specified within the IF() function.

The formula is given by:

IF(logic, [value_if_true], [value_if_false])

The above formula specifies that if the logic specified is correct, excel will return the value specified in [value_if_true]. If the logic specified is incorrect, it will return

[value_if_false].

For example,

Let, A = 5, and B = 7 and the formula = IF(A >B, True, False)

Output = False

Explanation: The logic specified in the function is incorrect because A is less than B. Hence, excel will return the value False.

If the formula is IF(A >B, 1, 0), it will return 0 because the value specified in the case of false logic is 0.

But, there are some errors to be avoided using the IF() formula, which are listed as follows:

• The declared value in place of true and false, should either be a number or alphabet. We can write it as IF(C2>D3, 1, False), but cannot write is as IF(C2>D3, 1, False0).
• There should be no space between two or more numbers specified in place of true and false. We can write it as IF(C2>D3, 12, False), but cannot write is as IF(C2>D3, 1 2, False).
• We cannot use any other word in place of true and false. We can only use numbers without spacing.

Similarly, we can set the desired value to return in the above formula.

Example: To find if price1 is greater or price 2 with the help of IF() formula.

Consider the below steps:

1. Click on the cell at the end of the first row, as shown below:
   
2. Type the formula '=IF(D3>C3, 1, 0)' on the specified cell.
3. Press Enter. If the statement is true, 1 will appear otherwise 0.
4. Drag the bottom-right corner of the cell to the last element of the table, as shown below:
   
   The corresponding result will appear on other cells as well.

It depicts that the statement with 0 depicts a false statement and 1 depicts a true statement.

Similarly, we can implement multiple formulas using the 'automatic search for formulas' method discussed above.

Note: Remember to insert an equal sign (=) before every formula.

10. PROPER

The PROPER formula is used to organize the names or the words in a standard manner. It means that the first word in a cell must begin with a capital letter. In case the first letter is small; the PROPER formula can be used to organize such words when there is a large volume of text present in excel.

Let's understand with the help of an example.

Consider the below steps:

1. Click on the first cell of the Proper column, as shown below:
   
2. Type the formula on the specified cell as' =PROPER(B3).'
3. Press Enter. The proper form of the given name will appear. It will be the same word, but with the first letter as Uppercase. If the first letter is already in uppercase, no changes will occur. Drag the cursor to the last element of the column, as shown below:
   

11. RANDBETWEEN

The RANDBETWEEN formula generates a random number between the specified numbers. It is given by:

RANDBETWEEN(bottom, top)

For example,

RANDBETWEEN(1,10)

It will display a random number between 1 and 10. It can be any number. It is generally used to find a lucky draw number in excel among various students or names in excel. We only need to type the formula in the form of '=RANDBETWEEN(bottom, top).' We can set any range as per the requirements.

12. CONCATENATE

The CONCATENATE formula can join two or more cells and combine it into one cell. The data to be combined can be in the form of alphabets or numbers. It is given by:

CONCATENATE(cell1, cell2)

Let's consider an example.

The steps are as follows:

1. Click on the first cell of the column concatenate, as shown below:
   
2. Type the formula '=CONCATENATE(B3, C3)' on the cell and press Enter. Here, B3 and C3 are the specified cell numbers.
3. The specified cells in the formula will be combined together, as shown below:
   
   Similarly, we can join multiple cells.

13. EVEN and ODD

The EVEN function rounds off the decimal value to a near even number, and the ODD function rounds off the decimal value to the near-odd number. The same condition applies when we are working with negative numbers. In case of an integer, it will also be converted to the nearest even or odd number.

It is given by:

EVEN (cell_number) and ODD (cell_number)

Here, cell number refers to the value that we want to convert into even or odd.

Let's consider an example.

Consider the below steps:

1. Click on the first cell of the even column.
2. Type the formula '=EVEN(B3)' and press Enter. The rounded off value will appear, as shown below:
   
3. Drag down the cursor to the last element of the table. All the specified values in the table will be rounded off to the nearest even number, as shown below:
   
4. Similarly, type the formula '=ODD(B3)' on the first cell of the odd column, and press Enter. Drag down the cursor to the last element. The rounded value will now appear as:
   

14. TODAY

TODAY formula replaces the need to manually type the current date whenever we open the excel sheet. The formula automatically updates the current date on the specified cell.

It is given by:

TODAY()

We need to specify the above formula on the specified cell as '=TODAY()' and simply press Enter.

We are not required to specify anything inside the parenthesis. Every time we open the excel sheet, the date will be automatically updated.

15. Converting Numbers into the currency

The method to specify the same amount in terms of dollars can be accomplished with just a simple click. The shortcut method is given by:

Ctrl +Shift + $

The converted value will be complete in the form of commas, dollar sign, and the decimal point.

Let's understand with the help of an example.

Consider the below steps:

1. Select the given data under the Price1$ column, as shown below:
   
2. Press Ctrl +Shift + $.
3. The select values in the form of numbers will be converted into dollar form, as shown below:
   

Similarly, we can use Ctrl +Shift + % to convert the corresponding values in percentage. In such case, the values will be automatically multiplied by 100 with a percentage symbol.

Complex Formulas

The complex formula refers to the mixed use of addition, subtraction, multiplication, and division formulas. We will simply compute the formula on the formula bar and excel will produce the desired result in few seconds. We can create multiple complex formulas based on the above formulas.

Let's consider an example.

Example: To compute the equation and find out the value.

Consider the below steps:

1. Click on the first cell of the result column.
2. Drag the mouse on the formula bar and type '=(C3*E3) + D3 - C3/D3 * E3.'
3. Press Enter. The result will appear on the specified cell, as shown below:
   
4. Drag the cursor of the first cell to the last cell of the result column. The resulted value will appear on all the specified cells, as shown below:
   

Note: The calculations will be performed based on the BODMASS rule in mathematics.