{"id":96648,"date":"2021-06-29T09:00:33","date_gmt":"2021-06-29T03:30:33","guid":{"rendered":"https:\/\/data-flair.training\/blogs\/?p=96648"},"modified":"2021-06-08T14:42:59","modified_gmt":"2021-06-08T09:12:59","slug":"excel-formulas-and-functions","status":"publish","type":"post","link":"https:\/\/data-flair.training\/blogs\/excel-formulas-and-functions\/","title":{"rendered":"Excel formulas and functions"},"content":{"rendered":"

Microsoft Excel helps in storing any type of data and the main advantage of using Excel is that there are various inbuilt functions available, which makes performing the calculations easy.<\/span><\/p>\n

Microsoft Excel is one of the software used for data analysis. The expression which the user provides to calculate in an excel sheet is termed as formulas and the functions are already predefined in ms excel.\u00a0<\/span><\/p>\n

What is Excel Formula?<\/h3>\n

Excel formula helps the user to analyze the data easily. These help the user to calculate the values in the cells of the spreadsheet. They also save a lot of time and reduce the risk of making mistakes.\u00a0<\/span><\/p>\n

The instruction provided by the user to perform the calculations within the spreadsheet is called formulas.<\/span><\/p>\n

The formula starts with an equal sign and then followed by the remaining part of the calculation formula. There is also another option available and in this method, the user can start a formula with either a plus (+) sign or minus (-) sign. <\/span>Excel assumes that it is a formula, and once the user presses enter, the desired Excel formula’s result appears. If the user doesn’t type an equals sign first, then Excel assumes that the value is either a number or a text.<\/span><\/p>\n

Some steps to follow for entering a formula into Excel.<\/strong><\/span><\/p>\n

1. Enter the formula in the desired cell.<\/span><\/p>\n

2. To make excel understand that it is a formula, start it with an equal or plus or minus sign.<\/span><\/p>\n

3. Complete the formula in the cell.<\/span><\/p>\n

4. Press Enter to view the formula result.<\/span><\/p>\n

Elements of Formulas in Excel<\/h3>\n

There can be any of the elements in the formula and they are:<\/span><\/p>\n

a. Arithmetic operators in Excel<\/strong><\/p>\n

The operators such as + (for sum), – (for subtraction), * (for product) and \/ (for division)<\/span>
\n<\/span>=A1+B1. This adds the values in cells A1 and B1.<\/span><\/p>\n

b. Values or text<\/b>
\n<\/b>The string which can be numeric or text values.\u00a0<\/span><\/p>\n

=100*0.1. This multiplies 100 and 0.1 and this results in 10.\u00a0\u00a0<\/span><\/p>\n

c. Cell references\u00a0<\/b><\/p>\n

This includes both named cells and ranges<\/span><\/p>\n

=A1=B1. This compares the cell A1 with the cell B1. If the cell values are identical, then the formula returns true or else it returns false.<\/span><\/b><\/p>\n

d. Worksheet functions\u00a0<\/b><\/p>\n

This includes the function from the worksheet such as countif, sum, average, etc.<\/span><\/p>\n

=SUM(A1:A10). This adds the values in the range A1:A10.<\/span><\/p>\n

Using Functions in Excel<\/h3>\n

When the user types the equal to (=) sign and an alphabet, the list of functions available with that alphabet will be listed.\u00a0\u00a0<\/span><\/p>\n

\"Functions<\/a><\/p>\n

Here, the list of functions starting with S appears.<\/span><\/p>\n

Function Arguments in Excel<\/h3>\n

The use of arguments varies in the functions. The arguments depend on what the function has to do. Each and every function uses the parentheses and the parentheses holds the list of arguments.\u00a0<\/span><\/p>\n

1. No arguments<\/b><\/p>\n

These functions do not hold any argument between the parentheses. Example: TODAY()<\/span><\/p>\n

2. One argument<\/b><\/p>\n

These functions hold only one argument between the parentheses. Example: LOWER()<\/span><\/p>\n

3. A fixed number of arguments<\/b><\/p>\n

For a few functions, there are a fixed number of arguments to be provided and such functions are RANDBETWEEN(), IF() etc.<\/span><\/p>\n

4. Infinite number of arguments<\/b><\/p>\n

For few functions, there are no fixed number of arguments, we can keep on increasing the arguments and such functions are SUMIFS() etc.<\/span><\/p>\n

5. Optional arguments<\/b><\/p>\n

In few functions, there are optional arguments which means you can either provide them or you can leave it blank and the function considers the default value.\u00a0 EXAMPLE: VLOOKUP()<\/span><\/p>\n

Formula Box in Excel<\/h3>\n

When the user wants to view the formula of a particular cell, then click on the cell and look at the formula box. Formula bar shows the contents of the current and allows the user to create and view formulas.<\/span><\/p>\n

\"Formula<\/a><\/p>\n

Errors in excel formulas<\/h3>\n

Sometimes, instead of getting desired Excel formulas results, the user may encounter an error in Excel. The two main types of Excel formulas errors are:<\/span><\/p>\n

a. #value error<\/span><\/p>\n

b. #name error<\/span><\/p>\n

These errors occur mainly if the user types the invalid formula.<\/span><\/p>\n

1. #VALUE Error in Excel<\/h4>\n

This error means that the formula entered is accepted but excel could not calculate a valid result from the formula.\u00a0<\/span><\/p>\n

For example<\/b> Excel shows you a value error when the user tries to perform the arithmetic operation with numerical and non – numerical values.\u00a0<\/span><\/p>\n

\"#Value<\/a><\/p>\n

Reasons for #VALUE Error<\/strong><\/p>\n

a. Performing arithmetic operations in the cells that contain a non-numerical value.<\/span><\/p>\n

b. Error with the formula<\/span><\/p>\n

c. Non-numerical values<\/span><\/p>\n

This Excel formulas error is frequently encountered when the formula isn\u2019t formatted correctly or contains other errors.<\/span><\/p>\n

How to Correct #VALUE Error<\/b><\/p>\n

Instead of using arithmetic operators, make use of the functions such as SUM, PRODUCT, or QUOTIENT, to perform an arithmetic operation rather than using arithmetic operators.\u00a0<\/span><\/p>\n

Make sure that the adding cell does not contain a non-numerical value.\u00a0<\/span><\/p>\n

To solve the error, click on the warning symbol provided at the side of the cell.<\/span><\/p>\n

\"Correct<\/a><\/p>\n

\u00a02. #NAME Error in Excel<\/h4>\n

This error occurs when excel doesn\u2019t recognize text in the formula.\u00a0 When the user enters a wrong formula, excel gives you a name error.<\/span><\/p>\n

\"#NAME<\/a><\/p>\n

How to Correct #NAME Error<\/b><\/p>\n