Excel Calculations (Table of Contents)
Introduction to Excel Calculations
Excel is used for a variety of functions but the basic area why excel was introduced was for handling calculations of day to day work. Like adding, subtracting, multiplication, etc. numbers used for different purposes for a different domain. Though there are other tools for basic calculations but standoff of using excel is its flexibilities and the perception for viewer it gives, for instance, we can add two numbers in calculator as well but the numbers mentioned in the excel sheet can be entered in two cells and used for other functions as well subtraction, division, etc.
Let’s have a quick look at some of the calculations to get a better understanding.
How to Calculate Basic Functions in Excel?
Let us start with the basic calculations and gradually will move further.
List of calculations that one already knows.
After using the above formula output shown below.
Similarly, a formula used in Cell C3, C4, C5.
#1 – MOD
Use to get remainder of two numbers when divided.
Apply the MOD formula in cell C2.
After using the MOD Formula output is shown below.
#2 – Average
Use to take the median or average between the range of numbers.
Apply the Average formula in cell C2.
After using the AVERAGE Formula output shown below.
#3 – Count
Use to count number of cells in data.
Apply the COUNT formula in Cell C2.
After using the COUNT Formula output shown below.
#4 – Abs
Use for removing the sign from the number
Apply the ABS formula in Cell C2.
After using the ABS Formula output shown below.
#5 – Concatenate
Use for merging text from two or more cells.
Apply the Concatenate formula in Cell C2.
After using the CONCATENATE Formula output as shown below.
#6 – Text Join
Use for merging text from two or more cells if one has a range this formula is recommended, though this is a new formula and may not be available in previous versions.
Apply the Concatenate formula in Cell C2.
After using the CONCATENATE Formula output is shown below.
#7 – IFS
Use instead of IF condition when there are two many conditions to be given, i.e. nested if-else.
Apply the IFS formula in Cell C2.
After using the IFS Formula output is shown below.
#8 – VLOOKUP
VLOOKUP function is used in various situations such as comparing data with one column and validating whether the data is present in the current column. Retrieving the data of dependent column by comparing the data of the current column with the column which dependent column depends on, for this below is the example for one’s reference.
Explanation: The “Third column Empid” data is first compared with Empid and respect to the “Empid” the name is retrieved in “Result” column. For Example 10203 from “Third column Empid” is compared with “Empid” column i.e.with 10203 and the corresponding name Sanket is retrieved in the “Result” column.
Apply the VLOOKUP formula in cell C2.
After using the VLOOKUP Formula output is shown below.
After using the VLOOKUP Formula output is shown below.
#9 – MID
Use for splitting the text or in easy terms can be used as a substring.
Apply the MID formula in Cell C2.
After using the MID Formula output shown below.
#10 – LEFT
Use the LEFT function when you want to extract characters starting at the left side of the text.
Apply the LEFT formula in cell C2.
After using the LEFT Formula output is shown below.
#11 – RIGHT
Use the RIGHT function when you want to extract characters starting at the right side of the text.
Apply the RIGHT formula in Cell C2.
After using the RIGHT Formula output is shown below.
Explanation: In the above example, from the text value JamesPune we only need James so we use the MID function to split the JamesPune to James as shown in the formulae column. Same goes with a LEFT function where one can use it to split the string from the left side and RIGHT function to split the string from the right side as shown in the formulae.
#12 – FIND
Use for searching the character’s position in the string.
Apply the FIND formula in cell C2.
After using the FIND Formula output is shown below.
#13 – LEN
Use for checking the length of the string.
Apply the LEN formula in cell C2.
After using the LEN Formula output is shown below.
Explanation: FIND function is used for searching the character’s position in the string like the letter “t” position in the above text is at 6 position. LEN in the above example gives the length of the string
#14 – PMT
It is used for calculation of monthly installments one has to pay.
Apply the PMT formula in cell C2.
After using the PMT Formula output is shown below.
#15 – Nested Formula
Used when our conditions have two to three functions to be used. Suppose we have a text Sau; Mumbai;27 and we want to separate as the Name then we would following formula as =MID(A2,1,FIND(“;”,A2,1)-1).
Apply the MID formula in cell C2.
After using the above Formula output shown below.
After using the above Formula output is shown below.
After using the above Formula output is shown below.
Explanation:
So here for Name we only want “Sau”, so here we use the combination of MID function which will help us to split the string and the FIND function helps to locate the position of ; in the string, i.e.as per MID function it needs:
MID(Text_value,start_position, end_position)
Text_value: Sau;Mumbai;27
Start_position: 1
End_position: Position of the first semicolon by FIND function
FIND (find_text, within_text, [start_num])
Find_text = ;
Within_text= Sau;Mumbai;27
Start_num=1
So here the FIND function gives position as 4 and hence the MID function looks like this MID(A98,1,4-1) ie. MID(A98,1,3) which would result in “Sau” as shown in below fig:
Pros
- The excel calculations provide a robust function.
- Calculations can be dynamic.
- Nested Functions are possible.
- The more functions are getting added up helping users to get as much ease and avoid more usage of Nested Formulae Example: PMT formula.
Cons
- Nested Formulae becomes complex to apply
- Sometimes maintenance and usage of the formulae becomes difficult if the user is not prone to excel
Things to Remember About Excel Calculations
- Save your worksheet after every application of excel calculations
- Functions of excel are getting added up day by day, so it’s good to stay updated and avoiding usage of Nested Formulae
- Whenever one applies the function in the excel, one can click on Tab button to autocomplete the Function name i.e. if one wants to enter Sum function in a cell then choose the cell and write “ = S” and press Tab the function would get autocompleted and one can also see the value which the function ask for
Recommended Articles
This is a guide to Excel Calculations. Here we discuss How to Calculate Basic Functions in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
