Excel formulaExcel 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 formulasExcel 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.
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 ExcelLet'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.
OR
Example 2: To find the sum of specific elements of the column. Consider the below steps:
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. SubtractionIt 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:
3. Multiplication and DivisionA 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:
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 RIGHTThese 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 rightend. 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 rightend 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:
5. AVERAGEThe 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 dropdown 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:
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:
6. COUNTCount 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 dropdown 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:
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. COUNTACOUNTA() 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:
7. MAX and MINThe 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 dropdown 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:
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:
Thus, the student named Mohan scored the least marks (55) in the class. 8. TRIMThe 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 bottomright 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:
9. IFThe 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:
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:
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. PROPERThe 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:
11. RANDBETWEENThe 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. CONCATENATEThe 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:
13. EVEN and ODDThe EVEN function rounds off the decimal value to a near even number, and the ODD function rounds off the decimal value to the nearodd 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:
14. TODAYTODAY 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 currencyThe 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:
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 FormulasThe 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:
Note: The calculations will be performed based on the BODMASS rule in mathematics.
Next TopicRatio in Excel
