Hemal is an enthusiastic and active Excel user who loves to work in Microsoft Excel worksheet and provide information about it.
Why do we need to know about Excel formulas and functions ?
Just imagine, what would happen if there were no formulas and functions in Microsoft Excel? Alas, your Excel spreadsheet used to be just a word processing document storing tabular information. The formulas and functions in Excel make it a tremendously useful spreadsheet application software. There are so many functions and formulas in Excel that almost all types of calculations can be done with it, besides that many types of useful text-based operations can also be performed.
In the present hub, I am going to discuss formulas and functions which are not very difficult to understand but if you have proper knowledge of them then you will be proficient in most of the work of Excel.
Your attention please.
In this hub, I will discuss some very handy and useful functions of Excel, by learning them your life would be better than past.
Addition is a very common thing. Sometimes we are more than that. It can be done on a calculator or even manually. But when you have to add up a lot of statistics, the Excel formula proves to be an agave cure.
There are different ways of addtion of numbers.
- By entering values directly in the cell or formulabar
Press the Enter key to get the result of above equation i.e. 40.
2. Enter the cell address in another cell.
After this, press the Enter key, you will get value =40 in the cell c1.
3. Add values by giving names to the particular cells and use them in the formula.
Please, note that here 'Total_cost' name is used for A2 cell and 'Profit' name is used for B2 cell. By assigning the meaningful names to the cells readibility of the spreadsheet would be better.
4. whenever you want to add multiple vales written in the multiple cells then you should use the Excel's SUM function. It is very easy and straight forward thing as shown in the figure.
Enter the formula of sum in the formula bar or cell B1 and press the Enter key. You will get the total of all the values in the range of cells from A1 to A10 in cell B1.
Suppose you have 10 numbers and you want to average them, then use Average function.
Enter the formula in the formula bar or directly into the cell to calculate average of the range of numbers, you will get the value of average in the chosen cell. Please, refer above figure.
Suppose you are a school teacher and you want to find out which student gets the maximum marks according to the total marks of the total students in your class, then use Max function.
Just enter the formula of Max function into the formula bar or directly into the cell where in you want to show maximum number of the range of numbers. You will get the expected value (i.e.89) in the desired cell.
Another simple function is to do the exact opposite of Max. That is Min, it will extract the minimum value.
Min function does exactly reverse of the Max function. I.e. It finds the minimum value of the numbers in the range. Just Enter the formula of Min function in the formula bar or directly into cell, you will get minimum value of the numbers in the range given. Here, you can see that, number 10 is the minimum value of all the numbers in the range from the cell A2 to cell A11.
Sometimes we have to do calculations based on 'if and then', for which a wonderful function is already available in Excel. And that is if function.
Suppose you are a teacher in a school and you have to prepare the exam results, for this you have to give an A grade to the students who get more than 60 marks and give B grade to the students who get less than 60 marks, then you need Excel's ‘If function’ as mentioned below.
Hey, friends, how precious is time? If we forget that, it is said to be very wrong. Using Excel's Now function, you get the exact current date & time in the spreadsheet itself.
It often happens that the cell in which we have entered has unnecessary spaces, the function to remove those blank spaces is the TRIM function.
In the below figure we can easily see that how the Trim function removes extra blank spaces from the value entered in the cells A1, A 2 and A3. The trimmed content is displayed in the cells B1, B2 and B3
Now, there are situations when it becomes necessary to know whether the content of a cell is a number or a text (for example when we have entered the number in the cell as the text, of course using formatting.) the ‘ISTEXT’ function helps a lot in such situations. ISTEXT() function returns 'TRUE' if value of the cell is a text value and it returns 'FALSE' if value of the cell is not a text value.
Often, especially when we are creating a report, we repeat a certain character in a column, this is what the REPT function is used for. In the figure shown below character * has been repeated 21 times in the cell A1 using REPT function.
Find out simple interest using formula
And let's talk about simple interest calculation, who doesn't like to calculate interest on savings in his bank account? I always love to work out the same. Simple interest can be calculated by multiplying Principal amount, Rate of interest and No. of years as shown in the figure below.
Now let us talk about the very common situation in which the spreadsheet contains 3 columns like ‘first name’, ‘middle name’, and ‘last name’. So what do we do if we want to make a full name by combining these three? As shown in the figure below I have combined 3 columns to create full name in a single column using concatenate function. This can also be achieved by using '&' operator
Excel has full of functions that can be used for diversified and multiple purposes. Using Excel's formula and functions features we can carry out complicated worksheet functions in the simplified manner.