In Microsoft Excel, you can do algebraic calculations quite easily. The division is one of them. In the division process, we sometimes have to deal with the decimal places values. Most decimals are infinite and have more than 3 decimal places. In most cases, these long decimals are not so relevant and hard to comprehend. That is why they need modification and truncation. In this article, we are going to see how you can divide numbers with decimals in Excel.
How to Divide with Decimals in Excel: 5 Suitable Examples
In this article, we will use various types of data like numbers with currency signs, scientific decimal values, percentage values, etc to cover a wide range of data and for practical understanding.
Also how different types of formulas and methods hold up for different types of data are gonna be tested.
1. Dividing Total Marks by Number of Subjects
There is no built-in direct function like “DIVIDE” in Excel to divide numbers. We can use simple “/” to divide any type of data starting from decimal, integer, complex, etc. For the decimal type of data, we can use a formatting tool to format the output value and view it in the desired number of decimal places.
Steps
- We are going to determine the average number of students per subject.
- To do this, select the cell F5, and enter the following formula:
=C5/D5
- Then drag the Fill handle icon to cell F8.
- Then you will notice that the average number of each subject is now showing in the range of cells F5:F8.
- But those numbers are in decimal value, and they are computed for 2 decimal places.
- Let’s say we want to compute the average in 3 decimal accuracy, to do this select the range of cells F5:F8 and right-click on the mouse.
- From the context menu, click on Format Cells.
- After that, a new window will open, from that window go to the Number tab, then from there go to Number in Category group.
- Notice there that the decimal place is set to 2.
- Now change the value of the decimal place to 3 from 2.
- A preview below will show how the number will look after the alteration of decimal places.
- Click OK after this.
- After clicking OK, you will notice that the average number per subject of the students is now showing in 3 decimal accuracy.
Read More: How to Create a Division Formula in Excel
2. Using ROUND Function to Divide and Round Decimals
After completion of any type of calculation, you can use the ROUND function to round up or down the output value as you wish.
Steps
- Select the cell E5 and enter the following formula:
=ROUND(B5/C5,2)
- After entering the formula, you will notice that the output shows 2 decimal accuracy.
- Drag the Fill handle icon to cell E8.
- Now you notice that the range of cells E5:E8 is now showing output in 2 decimal place accuracy
3. QUOTIENT Function to Divide and Round Decimals
The QUOTIENT function can work just in the same way as “/”, but with a slight difference. The QUOTIENT function can only return an integer value. This means that the decimal part of the quotient value of any division operation will be ignored and rounded up.
Steps
- Select cell E5 and enter the following formula:
=QUOTIENT(C5,30)
- After entering the formula you will notice that cell E5 now shows the average sales of products per day in integer form. Notice that the output is in integer form because products like TV, AC, and Cable can’t be in fractions or decimals.
- Now drag the Fill handle to cell E8. Then you will notice that the range of cells now shows the output is in integer form.
Read More: Division Formula with Absolute Reference in Excel
4. Paste Special Command to Divide Decimal Values in Excel
Paste special tools have multiple benefits, it can be for doing algebraic operations and can paste data while keeping source formatting intact.
Steps
- We need to calculate the average salary per day of the below dataset in the range of cell E5:E8.
- Now select the range of cells C5:C8, and right-click on the mouse. A context menu will open.
- From the context menu, click Copy which will copy the entire range of data.
- Next, select cell E5, and right-click on the mouse again. A context menu will open.
- From the context menu, click on Paste (Source formatting).
- Then again select the range of cells D5:D8. And right-click on the mouse.
- From the context menu, click on Copy.
- Then select cell E5 and right-click on the mouse, and a context menu will appear.
- From the context menu, click on the Paste Special.
- Another window will open and in that window select Divide.
- Click OK after this.
- After clicking OK, you will notice that the values in the range of cells E5:E8 are now divided by the values in the range of cells D5:D8 after pasting.
Read More: Formula for Addition Subtraction Multiplication and Division in Excel
5. Divide Decimals by Percentage in Excel
Percentage values are decimal values themselves. So we are basically dividing decimal/integer values by decimal values.
Steps
- In this method, we are going to divide numbers by percentage in the range of cells E5:E8.
- To do this we select the cell E5 and the following formula:
=B5/C5
- After entering the formula will notice that cell E5 now contains the output of the division of B5 by cell C5.
- Then drag the Fill handle icon to cell E8.
- Next, you will notice that the division of cell entries of column Divisor by column Dividend is shown in decimal points.
Download Practice Workbook
Download this practice workbook below.
Conclusion
To sum it up, the question “how to divide with decimal in Excel” is answered here in 5 different ways. At first, we tried to solve it by formatting the output, then using functions like QUOTIENT, and ROUND, after that, we used Paste Special command, and finally, we demonstrate how we can divide decimal values by percentage values. Feel free to ask any questions or feedback through the comment section.