Designed primarily as a spreadsheet program, Microsoft Excel is a powerful and versatile. It calculates the numbers, solves the problem of mathematics and engineering.
It helps you for the total or average numbers of columns in a single moment. With this, you can count compound interest and weighted averages, get the best budget for your advertising campaign, reduce shipment costs, or prepare work schedules for your own Employees.
To do all this work, you have to enter the formula in the cell.
Do not waste much time on manually doing all of these things in Microsoft Excel. There are several ways to use Excel formulas so that you can reduce the amount of time spent in Excel and increase the accuracy of your data and reports.
The purpose of this tutorial is to teach you the essentials of Excel functions and to learn to use basic formulas in Excel.
The first excel function you need to get acquainted with is SUM which performs basic arithmetic operations of addition.
Syntax Of SUM Function:
The syntax of the SUM function is as follows:
SUM (number1, [number2], …)
The first argument is required in the SUM function, the other numbers are optional, and you can give 255 numbers in a formula.
Meaning, your SUM formula should contain a reference number of at least 1 number, cell or cell range.
Example of SUM Function in Hindi:
= SUM (A1: A5) – This adds value to cells from cells A1 to A5.
= SUM (A2, A5) – This adds value to cell A2 and A5 cells.
= SUM (A2: A5) / 5 – This adds value to cells from cell A1 to A5 and divides this sum to 5.
In your Excel worksheet, this formula can look like this:
Tip – The fastest way to sum any column or row is to select the next cells of these cells and click the AutoSum button on the Home tab.
Excel’s AVERAGE function searches the arithmetic mean of numbers.
Syntax Of AVERAGE Function:
The syntax of the AVERAGE function is as follows:
AVERAGE (number1, [number2], …)
Here number 1, [number2], etc. are one or more numbers (or references of numbers with numbers), whose average calculation you want to do.
Example of AVERAGE Function in Hindi:
= AVERAGE (A1: A5)
3) MAX & MIN:
The MAX and MIN formulas in Excel are the largest and smallest values in the set of numbers, respectively.
Example of MAX & MIN Function in Hindi:
= MAX (A2: A5)
= MIN (A2: A5)
4) COUNT & COUNTA:
If you want to know how many cells in the range of cells are numeric values, then do not waste their time in manually calculating them. COUNT functions of Excel will count in seconds.
Syntax Of COUNT Function:
The syntax of the COUNT function is as follows:
COUNT (value1, [value2], …)
While the COUNT function only counts the cells that contain the numbers, but the COUNTA function of Excel counts all those cells which are not blank, whether they are numbers, dates, times, text, logical values of TRUE and FALSE logical Values, errors, or empty text strings (“”).
Syntax Of COUNTA Function:
The syntax of the COUNTA function is as follows:
COUNTA (value1, [value2], …)
Example of COUNT & COUNTA Function in Hindi:
For example, to find out how many numbers in column A are included, use this formula:
= COUNT (A: A)
To count all non-empty cells in Column A, use this formula:
= COUNTA (A: A)
In both formulas, you are referring to the entire A column sales.
When you use IF formula in Excel, then you ask Excel to test some conditions and when the condition is complete, give the value or excel value, and if this condition is not complete, then the second Does value or other customization.
Syntax Of IF Function:
The syntax of the IF function is as follows:
IF (logical_test, [value_if_true], [value_if_false])
In easy language –
IF (if something is true, do something else, otherwise do something else)
So there can be two results of an IF statement. If your comparison is correct, then the first result, otherwise the second result.
Example of IF Function in Hindi:
For example, if students are above 35 marks, then they are PASS and if they have fewer than 35 marks they are FAIL.
= IF (A2> = 35, “PASS”, “FAIL”)
Excel’s TRIM function removes extra space from the words and does not contain any space character in the start or end of the text.
There are several ways to remove unwanted space in Excel, in which the TRIM function is the easiest way:
Syntax Of TRIM Function:
The syntax of the TRIM function is as follows:
Example of TRIM Function in Hindi:
For example, to remove all the extra space in the A column column, enter the following formula in cell A1, and then copy it below the column:
= TRIM (A1)
This will remove only all space spaces in the cells and will keep only one space character between the words.
Whenever you want to know how many characters are in a cell, use LEN.
Syntax Of LEN Function:
The syntax of the LEN function is as follows:
= LEN (text)
Example of LEN Function in Hindi:
To know how many characters are in A2 cell –
= LEN (A2)
Please keep in mind that Excel’s LEN function counts all characters with a space.
8) AND & OR:
These are the two most popular logical functions to test many parameters.
This comes in handy when you have to test many conditions and make sure that they are all TRUE.
The Technical and Function tests the conditions you have specified, and if all the conditions are TRUE then it returns TRUE returns or FALSE returns returns.
Syntax Of AND Function:
The syntax of the AND function is as follows:
= AND (logical1, [logical2], …)
Example of AND Function in Hindi:
For example, if students have a score of 35 on both Math and English, then they are PASS, otherwise FAIL is.
= IF (AND (B2> 35, C2> 35), “PASS”, “FAIL”)
An OR is a logical function similar to an Excel or a function, which is used to compare two values or statements.
The only difference is that the OR function, if any of the given conditions are TRUE, TRUE returns and FALSE returns if all the conditions are wrong.
Syntax Of OR Function:
The syntax of the OR function is as follows:
= OR (logical1, [logical2], …)
Example of OR Function in Hindi:
For example, if students have marks above 35 in any of the subjects in Math and English, then they are PASS, otherwise FAIL.
= IF (OR (B2> 35, C2> 35), “PASS”, “FAIL”)
Excel’s CONCATENATE function joins two or more text items. It combines the value of two or more cells into a single cell.
Syntax Of CONCATENATE Function:
The syntax of the CONCATENATE function is as follows:
CONCATENATE (text1, [text2], …)
Example of CONCATENATE Function in Hindi:
For example, to combine the value of A2 and B2 cells, simply enter the following formula in a separate cell:
= CONCATENATE (A2, B2)
If you want to separate these combined values, that is to include a space in it, type the space character (“”) in the arguments list.
= CONCATENATE (A2, “”, B2)