Now, Learn Excel Formulas in Hindi
Sum Formula – Excel Formulas in Hindi
Sabse pehle hum dekhte hain Sum Formula. Ye Generic Formula hai, Jo ki aap sabko aana he chaahiye. Ye kaafi easy hai.
SUM(number1, [number2], …)
Sum Formula-this Function, as the name suggests is used to sum up a group of Numbers in a Particular Column. This is complication free. You can easily find the Sum Function on your screen. The Function Button is there on the Home Tab. All you have to do is, select the part of the column and let the dedicated Button sum up the group of Numbers. For Example:
Tip 1: By going on Home Tab, Click on AutoSum at the Right hand side
Tip 2: By going in the Formula Bar
Tip 3: By applying the SUM Formula Directly
Tip 2: By going in the Formula Bar
Tip 3: By applying the SUM Formula Directly
With these Three tips you can quickly SUM a Column and even Row of Numbers. All you have to do is, select the cell next to the numbers you wish to SUM.
Related Formulas
Average Formula
Then comes the Average Formula. Jaisa ke aap sabko yaad hee hoga, jab kabhi bhee aapke saamne kuch nos. kaa samuh hota thaa aur uska Average nikalna hota tha, toh Average Formula kaa use karke Average nikaal liya jaata thaa. Usi tarah, Excel in Hindi mei ye Average Formula ka use hota hai.
AVERAGE(number1, [number2], …)
This Formula will guide you through in case you were searching for how to Calculate Average in Excel. It is often used as an Worksheet Function in Excel. And, in this the AVERAGE Formula is considered a formula that could be entered in a cell in Excel. All you have to do is, click on a cell towards the right or below the numbers of which you need to find the Average for. Now go to the Home TAB, Edit Option, click on the AutoSum, click on the AVERAGE and then press enter.
Take an example, if the range is, A1:A20 which contains numbers, the formula would be =AVERAGE(A1:A20) that will return the average of those numbers.
Average numbers ignore Zero
Tip 1: By going on Home Tab, Click on AutoSum then click on Average at the Right hand side
Tip 2: By going in the Formulas Bar
Tip 3: By applying the Average Formula Directly
Tip 2: By going in the Formulas Bar
Tip 3: By applying the Average Formula Directly
Note: AVERAGE automatically ignores blanks cell and text, but Zero Values are included.
Related Formulas
Count Formula
Count Formula vo excel Formula hai Hindi ka jo ke aapko help karta hai to Count the Number of Numbers in Supply Items.
COUNT(value1, [value2], …)
The most important and used Function in Excel has been SUM and the COUNT Formula. Excel Count function, helps you to find out and count the number or Cells that contains numbers.
Tip 1: By going on Home Tab, Click on AutoSum then click on COUNT Numbers at the Right hand side
Tip 2: By going in the Formulas Bar
Tip 3: By applying the COUNT Formula Directly
Tip 2: By going in the Formulas Bar
Tip 3: By applying the COUNT Formula Directly
Note: The Count Numbers of Cells in range that do not contain numbers,Use SUMPRODUCT Formula
Related Formula
Counta Formula – Excel Formulas in Hindi
COUNTA Function bhe Count Function ke tarah hai par ussei thoda different hai. Ye Excel Formula aapko non-blank cells ki calculation karne mei help karta hai.
COUNTA(value1, [value2], …)
COUNTA FUNCTION: With this Function, Calculate the value, number, text, error values and Logical Values while taking Excel in Hindi.The COUNTA Function does not count the empty cells. You can calculate the value of the Non-blank cells with the help of COUNTA Function.
Note: COUNTA Function helps in counting non blank cells, the cells might contain numbers, dates, text, logical values of TRUE and FALSE, errors or it could be empty text strings (“”). Any of these could be calculated if you use the COUNTA Formula.
Related Formula
Concatenate Formula – Excel Formulas in Hindi
Concatenate ka arth he jodna hai. Toh Concatenate Excel in Hindi mei ka vo Formula hai jo ki do yaa ussei jyaada cell references ko Jodne mei aapki help karega.
CONCATENATE(text1, [text2], …)
If you plan to join or combine two or more strings, numbers or cell references together you can join it by using CONCATENATE Function. It can join around 30 items together. This Function helps you to combine numbers, text from different cells into one cells.
IF Formula
IF Formula vo Excel Formula hai jo kee aapko Sahi ya galat Value i.e Truth or False Value or Results nikaalne mei Help karta hai.
IF(logical_test, [value_if_true], [value_if_false])
Supposedly, the IF Function is considered to be good to perform a Logical Test. And then this Function of Excel returns one Value for true Results and the other Value for False. To bring out the information of Truth and False depending upon the Parameter put forward in the variable Box. The fact is, IF Function could be combined with other Logical Functions as well, such as AND and OR.
NestedIF – Excel Formulas in Hindi
You might call it Nested IF and there are many who term it as Nested IF Statement. If the comparison is to be done for more than three IF Functions that would be needed to put up together. This is used basically when there are more than one IF Function. It helps in testing more Conditions so that there is a possibility to obtain further output and More Results.
You have to make sure, that Each If the statement has been carefully “nested” inside the other statement. And ensure that the logic applied is absolutely correct and in check.
Notes: When you are Constructing test with IF Formula, You can use any of the following Logical operators
| Comparison Operator | Meaning |
| = | equal to |
| <> | not equal to |
| > | Greater than |
| < | Less than |
| >= | Greater than or equal to |
| <= | Less than or equal to |
Related Formula
CountIF Formula – Excel Formulas in Hindi
Jab COUNT IF ke baat aati hai, toh bol sakte hain, ye Count Function in Excel sei thoda alag hai. COUNT IFAdvanced Excel Hindi Formula hai jo ki aapko Excel in Hindi mei saare cell Count karne mei help karta hai.
CountIF(range, criteria)
COUNTIF Formula is the Advanced Excel Formula. This has great uses of Conditional Functions. The COUNTIF Formula will help you to Count all the cells. To find out the solution of your question. COUNTIF Function does not Add up the Matching Values from the cells. What it does is, it Pulls and shows you the set. You can consider the Example of, counting cells that is equal to or greater than 21 (age) to find out the no. of bottles of champagne you need for a client event. So, you can use COUNTIF as the advanced solution here.
Notes: In COUNTIF Function, the values can be supplied directly in the form of Criteria. The text has to be enclosed in the double quotations i.e. (“”). Whereas, in a situation where you’ve already got a criteria in the another cell. In this case, the Cell address can be supplied as the Criteria without quotations. You can see the same in the above example.
Related Formula
VLookup Formula – Excel Formulas in Hindi
VLOOKUP, Doston ye mera favorite formula hai. Agar aapne VLOOKUP Formula in Hindi seekh liya toh maano aapne almost poora Excel in Hindi Seekh liya hai. VLOOKUP is that Excel Formula jo ke aapko aisi jaankaari dhoondhne mei madad karta hai jo ki Big Chunk of Data ke beech mei dhoondhna impossible hota hai.
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
VLOOKUP Function is used when there is a large set of Data. It becomes difficult to look out for any kind of information that has been missing. In order to pull out the piece of Information that you’ve been looking for, you can use the VLOOKUP Function. VLOOKUP will look out and pick up the exact piece of Information from the large segment of Data. And this Data is automatically picked and Put in the newly built Table.
All you have to do is, go to the Function Option. You can enter the VLOOKUP Formula by finding it in the list or by using the Insert Function. With the insert Function, you can enter the VLOOKUP easily. VLOOKUP FORMULA in Hindi ko Doston jara dhyaan sei seekhein.
Sumif Formula – Excel Formulas in Hindi
SUMIF Formula aapko help karega us data ko add karne mei jo ki aapke Criteria ko match karta hai. Learn Excel in Hindi
SUMIF(range, criteria, [sum_range])
SUMIF Function is the Advanced ExcelFormula. This has great uses of Conditional Functions. SUMIF Function helps you find out a certain set of information that you’ve been searching for or which matches your criteria. The criteria contain the Wizard Box and this Wizard Box Contains the Sum Range Tab, range Tab, and the Criteria Tab. Well, Range Tab is the area you’ve been looking for. Criteria Tab helps you in finding the Cell. And with the SUM Range Tab, you’d be able to make additions. You’d be able to add the Data that matches your Criteria.
Maximum Formula – Excel Formulas in Hindi
Max function is used to find the maximum or the largest value in the cells. This function helps us to locate where the maximum value is located in the spreadsheet. Learn Excel in Hindi
MAX(number1, [number2], …)
In the image below we need to find the Maximum amount to be paid to Client. To find this we will enter the Max formula in the empty cell and enter the Excel Formulas.
The answer is 195,800.
For more detailed information regarding Max Function.
Related Formula
Upper Formula – Excel Formulas in Hindi
Upper formula is used when we want convert the text of different cases into a uniform Upper form. This formula can be used for either entire cell or for a particular text in the Excel Sheet. Learn Excel in Hindi
The Upper Formula is to be entered as
UPPER(text)
Here we convert the text in the cell B3 i.e. Kolkata into KOLKATA. For converting the text of the cell in the Upper Case we will use the Upper Formula as seen in the Formula Bar. Press Enter. The Result is seen. The text in the cell is converted into the Upper Case. “KOLKATA”
Lower Formula – Excel Formulas in Hindi
Lower Formula is one of the formula from the Excel Formulas list. This formula is opposite of the Upper Formula explained earlier. The purpose of this formula is to convert the text from upper or proper case to Lower case. Learn Excel in Hindi
The Lower Formula is to be entered as
LOWER(text)
Taking the same example as that of Upper Formula. Here we have text in uppercase in cell B9 and we want to convert into Lowercase. Enter the formulas as seen in the image and Press Enter. The uppercase text is converted into lowercase text.
Trim Formula – Excel Formulas in Hindi
Trim formula is used to clean your data by removing leading, trailing and extra spaces between the words. The Trim function does not remove the spacing between the words. Trim function is the simplest as well as utmost important function from the Excel Formulas. Learn Excel in Hindi
The Trim Formula is to be entered as
TRIM(text)
In the example below, we will use the Trim function that will help to clean our data. In the first example there is more space between the name and the surname. Using the rim function you can clear the extra spacing between the two.
You can check whether your data is Trim or not by using the Len function. Once you use the Len function before and after using the Trim function you will come to know the difference. Enter the Trim Formula and Press Enter. The result are right in front.
Len Formula – Excel Formulas in Hindi
Len formula is one of those Excel Formula which is used to count the length of the cell. Or to count the number of characters entered in the cell. The main purpose of this formula is to know whether the data inputted is up to the mark. Learn Excel in Hindi
The Len Formula is to be entered as
LEN(text)
Here we will calculate the length of the cell. In other words, we say how many characters does the cell contains. In the cell B4 which shows 4789203 number and we want to know the length of the cell.
Enter the Formula as shown in the image and Press Enter.
I really liked your Information. Keep up the good work. Advanced Excel Training Courses
ReplyDelete