Computers, Programming
Formula "Divide" in Excel - basic principles of use and examples
Users who are accustomed to working with formulas in Microsoft Excel table processor sometimes try to find a ready function for dividing one number into another. But such function is not present, and to use a colon character as it will not turn out - it in "Excel" is intended for designation of borders of ranges. The division formula in Excel is just a backslash (/).
How to divide one number into another
On the Excel worksheet, enter any two numbers in different cells. They will help you figure out how to make the division formula in Excel. Then in the free cell type the sign of the beginning of the formula (equals =) and specify the first cell with the divisible one. Next, type the division sign in the Excel formula - the backslash - and specify the cell with the divisor.
H | I | ||
5 | 2 | 2.5 | = H6 / I6 |
Conveniently, if you try to divide into a cell whose contents is 0, Excel will display the corresponding error: # DEL / 0 !.
Important! Excel allows you to divide any number, including dates. But when you try to divide by cells containing text, you get the error "#VALUE!", O there is an incorrect value.
Dividing dates
It's very interesting to share dates. Try to divide "01/01/2017" on "01/01/2016". Do you think that you will not succeed? This is not true. The fact is that Excel automatically converts all dates into a number, counting the number of days that have passed since January 1, 1900. The date "01/01/1900" is the number 1, "02.01.1900" is 2. You can easily verify this by converting the cell formats with the specified dates from the "Date" format to the "Numeric" format. Thus, on January 1, 2017 - this number is 42736, just so many days passed from the same date and month of 1900. Therefore, dividing the dates one by another, we can get a very specific result.
C | D | E | |
9 | 01/01/2017 | 01/01/2016 | 1,008638187 |
10 | 42736.00 | 42370.00 | 1,008638187 |
It is logical that you can divide not only the date on the date, but any number on the date and vice versa. Because the formula "division" in Excel will still present your date in the number. And they can operate in any way.
The division table in Excel
Let's build the table in Excel, place the numbers in cells A2: A6, which we will divide (3, 7, 9, 12, 15), and in cells B1: E1 - the numbers to which we will perform division (1, 2, 3 , 4).
A | B | C | D | E | |
1 | 1 | 2 | 3 | 4 | |
2 | 3 | ||||
3 | 7th | ||||
4 | 9 | ||||
5 | 12 | ||||
6th | 15 |
To make a division table, it's important to remember absolute and relative references in Excel. The division formula in Excel when compiling such a table involves the use of mixed references. So that the addresses of the cells do not shift during copying, you need to fix the column number in the delimited address, and the line number in the divider. So, enter the formula "= $ A2 / B $ 1" in cell B2 and drag it to the end of the line? And then on the whole table. The table is filled with data. Now stand on any cell, say, on D5. Its formula will be as follows: "= $ A5 / D $ 1". All done correctly.
Division with remainder
When doing division, in most cases we get a fractional number. To simplify the perception of such numbers, there are two important points in Excel.
The cell in which the result is written is by default the format "General". If you go to the "Home" tab in the "Number" section, you can select the "Other numeric formats" format change item from the drop-down list and set the format to "Numeric". For this format, you can assign the number of decimal places displayed for the cell. So, when dividing 7/3, we usually see the number 2.333333333, and by using a precision constraint of three decimal places it will look like 2.333. That, you will agree, is quite convenient.
In Excel there are two distinct functions for obtaining the quotient and the remainder of the division. They are called "Private" and "Balance" and both take two mandatory arguments - the numerator and the denominator. We will deal with them in more detail.
Enter in Excel two numbers - 7 and 3 (in cells D9 and D10 in our case), and to the right of them - formulas:
D | E | ||
9 | 7th | 2 | = PRIVATE (D9; D10) |
10 | 3 | 1 | = RESIDUE (D9; D10) |
As you can see, the quotient of the division is equal to two, the remainder is 1. In any free cell, enter the formula:
= E9 & "" & E10 & "/" & D10
Here the ampersand sign helps to attach the contents of another cell to the contents of one cell as text.
The result of this formula will be as follows: 2 1/3 - two whole and one third.
Similar articles
Trending Now