How to Calculate in Excel (Table of Contents)
Introduction to Calculations in Excel
MS Excel is the most preferred option for calculation, it is used by most of the investment bankers and financial analysts to do data crunching, preparing presentation or modeling of data.
There are two ways to perform the calculation in Excel, one is Formula and second is Function. Where formula is the normal arithmetic operation like summation, multiplication, subtraction, etc. Function is the inbuilt formula like SUM (), COUNT (), COUNTA (), COUNTIF (), SQRT () etc.
Operator Precedence: It will use default order to calculate, if there is some operation in parentheses then it will calculate that part first then multiplication or division after that addition or subtraction. it is same as BODMAS rule.
Examples of Calculations in Excel
Here are some examples of How to Use Excel to Calculate with Basic calculations
Example #1 – Basic Calculations like Multiplication, Summation, Subtraction and Square Root
Here we are going to learn how to do a basic calculation like multiplication, summation, subtraction and square root in Excel.
Let’s assume there is a user who wants to perform calculation like multiplication, summation, subtraction by 4 and find out the square root of all number in Excel.
Let’s see how we can do this with the help of calculations.
Step 1: Open an Excel sheet. Go to sheet1 and insert the data as shown below.
Step 2: Now create headers for Multiplication, Summation, Subtraction and Square Root in row one.
Step 3: Now calculate the multiplication by 4. Use the equal sign to calculate. Write in cell C2 and use asterisk symbol (*) to multiply “=A2*4“
Step 4: Now press on the Enter key, multiplication will be calculated.
Step 5: Drag the same formula till C9 cell to apply to the remaining cells.
Step 6: Now calculate subtraction by 4. Use an equal sign to calculate. Write in cell D2 “=A2-4“
Step 7: Now click on the Enter key, subtraction will be calculated.
Step 8: Drag the same formula till cell D9 to apply to the remaining cells.
Step 9: Now calculate the addition by 4, use an equal sign to calculate. Write in E2 Cell “=A2+4“
Step 10: Now press on the Enter key, addition will be calculated.
Step 11: Drag the same formula till E9 cell to apply to the remaining cells.
Step 12: Now calculate the square root>> use equal sign to calculate >> Write in F2 Cell >> “=SQRT (A2“
Step 13: Now press on the Enter key >> square root will be calculated.
Step 14: Drag the same formula till F9 cell to apply the remaining cell.
Summary of Example 1: As the user wants to perform calculations like multiplication, summation, subtraction by 4 and find out the square root of all number in MS Excel.
Example #2 – Basic Calculations like Summation, Average, and Counting
Here we are going to learn how to use Excel to calculate basic calculation like summation, average, and counting.
Let’s assume there is a user who wants to find out total sales, average sales and the total number of products available in his stock for sale.
Let’s see how we can do this with the help of calculations.
Step 1: Open an Excel sheet. Go to Sheet1 and insert the data as shown below.
Step 2: Now create headers for Result table, Grand Total, Number of Product and an Average Sale of his product in column D.
Step 3: Now calculate grand total sales. Use the SUM function to calculate the grand total. Write in cell E3. “=SUM (“
Step 4: Now it will ask for the numbers, so give the data range which is available in column B. Write in cell E3. “=SUM (B2:B11) “
Step 5: Now press on the Enter key. Grand total sales will be calculated.
Step 6: Now calculate the total number of products in the stock, use the COUNT function to calculate the grand total. Write in cell E4 “=COUNT (“
Step 7: Now it will ask for the values so give the data range which is available in column B. Write in cell E4. “=COUNT (B2:B11) “
Step 8: Now press on the Enter key. The total number of products will be calculated.
Step 9: Now calculate the average sale of products in the stock, use the AVERAGE function to calculate the average sale. Write in cell E5. “=AVERAGE (“
Step 10: Now it will ask for the numbers so give the data range which is available in column B. Write in cell E5. “=AVERAGE (B2:B11) “
Step 11: Now click on the Enter key. Average sale of products will be calculated.
Summary of Example 2: As the user wants to find out total sales, average sales and the total number of products available in his stock for sale.
Things to Remember About Calculations in Excel
- During calculations, if there are some operation in parentheses then it will calculate that part first then multiplication or division after that addition or subtraction.
- It is the same as BODMAS rule. Which follow as Parentheses, Exponents, Multiplication and Division, Addition and Subtraction.
- When a user uses an equal sign (=) in any cell it means to excel that the user going to put some formula not a value.
- There is a small difference from the normal mathematics symbol like for multiplication use asterisk symbol (*) and for division use forward slash (/).
- There is no need to write the same formula for each cell, once it is written then just copy past to other cells it will calculate automatically.
- A user can use SQRT function to calculate the square root of any value, it has only one parameter. But a user cannot calculate square root for a negative number it will throw an error #NUM!
- If a negative value occurs as output, then use ABS formula to find out absolute value which is an in-built function in MS Excel.
- A user can use COUNTA in-built function if there is confusion in the data type because COUNT support only numeric value.
Recommended Articles
This is a guide to Calculations in Excel. Here we discuss How to Use Excel to Calculate along with examples and downloadable excel template. You may also look at the following articles to learn more –
