Users who are used to working with formulas in the Excel Excel table processor from Microsoft sometimes try to find a ready-made function for dividing one number by another. But there is no such function, and it’s also impossible to use the colon sign - it is designed in Excel to indicate the boundaries of ranges. The division formula in Excel is just a backslash character (/).
How to divide one number into another
On the worksheet of the Excel book, enter any two numbers in different cells. They will help you figure out how to make the division formula in Excel. Then in a free cell, type the start sign of the formula (equal to =) and specify the first cell with the dividend. Next, type the division sign in the Excel formula - the backslash - and specify the cell with the divider.
Conveniently, when you try to divide by a cell whose contents are 0, Excel will display the corresponding error: # DEL / 0 !.
Important! Excel allows you to divide among themselves any numbers, including dates. But when you try to perform division on cells containing text, you get the error "#VALUE!", Oh there is an invalid value.
Date division
It is very interesting to share dates. Try to divide "01/01/2017" into "01/01/2016." Do you think you will not succeed? This is not true. The fact is that "Excel" automatically converts all dates to numbers, counting the number of days that have passed since January 1, 1900. Date 01/01/1900 is number 1, 01/02/1900 is 2. You can easily check this by converting the cell formats with the specified dates from the Date format to the Numeric format. Thus, January 1, 2017 is the number 42736, so many days have passed since the same date and month of 1900. Therefore, when dividing dates one by another, we can get a very specific result.
| C | D | E |
9 | 01/01/2017 | 01/01/2016 | 1,008638187 |
10 | 42,736.00 | 42,370.00 | 1,008638187 |
It is logical that you can divide not only date by date, but also any number by date and vice versa. Since the formula "division" in Excel will still represent your date in a number. And they can be operated on in any way.
Division table in "Excel"
Let's build the table in Excel, in the cells A2: A6 we put the numbers that we will divide (3, 7, 9, 12, 15), and in the cells B1: E1 we put the numbers into which we will divide (1, 2, 3 , 4).
| A | B | C | D | E |
1 | | 1 | 2 | 3 | 4 |
2 | 3 | | | | |
3 | 7 | | | | |
4 | 9 | | | | |
5 | 12 | | | | |
6 | fifteen | | | | |
To create a division table, it is important to remember the absolute and relative links in Excel. The division formula in Excel when compiling such a table involves the use of mixed links. So that the addresses of the cells are not shifted during copying, you need to fix the column number in the shared address, and the row number in the divider. Thus, enter in the cell B2 the formula "= $ A2 / B $ 1" and extend it to the end of the line? and then to the whole table. The table is filled with data. Now stand on any cell, for example, on D5. Its formula will be as follows: "= $ A5 / D $ 1". They did everything right.
Division with remainder
When doing the 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 into which the result is written defaults to the "General" format. If you go to the "Number" section on the "Home" tab, then from the drop-down list you can select the "Other numeric formats" format change point and set the "Numeric" format. In this format, you can assign the number of displayed decimal places for a cell. So, when dividing 7/3, we usually see the number 2,333333333, and with the help of the accuracy limit of three decimal places it will look like 2,333. Which, you see, is quite convenient.
In Excel there are two excellent functions for obtaining the quotient and the remainder of the division. They are called "Private" and "Residue" 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 are the formulas:
| D | E | |
9 | 7 | 2 | = PRIVATE (D9; D10) |
10 | 3 | 1 | = OSTAT (D9; D10) |
As you can see, the quotient of the division is 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 as text to the contents of one cell.
The result of this formula will be as follows: 2 1/3 - two integers and one third.
As you can see, the division formula in Excel is not a big deal. The main thing to remember is that when calculating formulas, Excel uses the standard rules of mathematics.