§ 4. Rounding of results
Processing of measurement results in laboratories is carried out on calculators and PCs, and it is simply amazing how magically a long series of decimal numbers works on many students. “That’s more accurate,” they think. However, it is easy to see, for example, that the entry a = 2.8674523 ± 0.076 is meaningless. With an error of 0.076, the last five digits of the number mean absolutely nothing.
If we make an error in hundredths of parts, then there is no faith in thousandths, much less ten-thousandths. A proper recording of the result would be 2.87 ± 0.08. The necessary rounding must always be done to avoid the false impression that the results are more accurate than they actually are.
Rounding rules
- The measurement error is rounded to the first significant digit, always increasing by one.
Examples:8.27 ≈ 9 0.237 ≈ 0.3 0.0862 ≈ 0.09 0.00035 ≈ 0.0004 857.3 ≈ 900 43.5 ≈ 50 - The measurement results are rounded to within an error, i.e. The last significant digit of the result must be in the same place as the error.
Examples:243.871 ± 0.026 ≈ 243.87 ± 0.03;
243.871 ± 2.6 ≈ 244 ± 3;
1053 ± 47 ≈ 1050 ± 50. - Rounding the measurement result is achieved by simply discarding digits if the first of the discarded digits is less than 5.
Examples:8.337 (round to the nearest tenth) ≈ 8.3;
833.438 (round to whole numbers) ≈ 833;
0.27375 (round to the nearest hundredth) ≈ 0.27. - If the first digit to be discarded is greater than or equal to 5 (and one or more digits following it are non-zero), then the last remaining digit is incremented by one.
Examples:8.3351 (round to hundredths) ≈ 8.34;
0.2510 (round to the nearest tenth) ≈ 0.3;
271.515 (round to whole numbers) ≈ 272. - If the digit to be discarded is 5 and there are no significant digits behind it (or there are only zeros), then the last digit left is increased by one when it is odd and left unchanged when it is even.
Examples:0.875 (round to the nearest hundredth) ≈ 0.88;
0.5450 (round to the nearest hundredth) ≈ 0.54;
275.500 (round to whole numbers) ≈ 276;
276.500 (round to whole numbers) ≈ 276.
Note.
- Significant numbers are the correct digits of a number, except for the zeros in front of the number. For example, 0.00807 this number has three significant figures: 8, zero between 8 and 7 and 7; the first three zeros are insignificant.
8.12 · 10 3 this number has 3 significant figures. - The entries 15.2 and 15.200 are different. The entry 15,200 means that the hundredths and thousandths are correct. In the notation 15.2 , whole and tenth parts are correct.
- The results of physical experiments are recorded only significant figures. A comma is placed immediately after a non-zero digit, and the number is multiplied by ten to the appropriate power. Zeros at the beginning or end of a number are usually not written down. For example, the numbers 0.00435 and 234000 are written as follows: 4.35·10 -3 and 2.34·10 5 . This notation simplifies calculations, especially in the case of formulas convenient for logarithms.
Rounding numbers - the simplest mathematical operation. To be able to round numbers correctly, you need to know three rules.
Rule 1
When we round a number to a certain place, we must get rid of all the digits to the right of that place.
For example, we need to round the number 7531 to hundreds. This number includes five hundred. To the right of this digit are the numbers 3 and 1. We turn them into zeros and get the number 7500. That is, rounding the number 7531 to hundreds, we got 7500.
When rounding fractional numbers, everything happens the same way, only the extra digits can simply be discarded. Let's say we need to round the number 12.325 to the nearest tenth. To do this, after the decimal point we must leave one digit - 3, and discard all the digits to the right. The result of rounding the number 12.325 to tenths is 12.3.
Rule 2
If to the right of the digit we keep, the digit we discard is 0, 1, 2, 3, or 4, then the digit we keep does not change.
This rule worked in the two previous examples.
So, when rounding the number 7531 to hundreds, the closest digit to the one left was three. Therefore, the number we left - 5 - has not changed. The result of rounding was 7500.
Similarly, when rounding 12.325 to the nearest tenth, the digit we dropped after the three was the two. Therefore, the rightmost digit left (three) did not change during rounding. It turned out to be 12.3.
Rule 3
If the leftmost digit to be discarded is 5, 6, 7, 8, or 9, then the digit to which we round is increased by one.
For example, you need to round the number 156 to tens. There are 5 tens in this number. In the units place, which we are going to get rid of, there is a number 6. This means that we should increase the tens place by one. Therefore, when rounding the number 156 to tens, we get 160.
Let's look at an example with a fractional number. For example, we're going to round 0.238 to the nearest hundredth. According to Rule 1, we must discard the eight, which is to the right of the hundredths place. And according to rule 3, we will have to increase the three in the hundredths place by one. As a result, rounding the number 0.238 to hundredths, we get 0.24.
Microsoft program Excel also works with numerical data. When performing division or working with fractional numbers, the program performs rounding. This is due, first of all, to the fact that absolutely exact fractional numbers are rarely needed, but it is not very convenient to operate with a cumbersome expression with several decimal places. In addition, there are numbers that, in principle, cannot be rounded accurately. But, at the same time, insufficiently accurate rounding can lead to gross errors in situations where precision is required. Fortunately, Microsoft Excel allows users to set how numbers will be rounded.
All numbers that Microsoft Excel works with are divided into exact and approximate. Numbers up to the 15th digit are stored in memory, and are displayed up to the digit specified by the user. But, at the same time, all calculations are performed according to the data stored in memory, and not displayed on the monitor.
Using the rounding operation, Microsoft Excel discards a certain number of decimal places. Excel uses a common rounding method where numbers less than 5 are rounded down and numbers greater than or equal to 5 are rounded up.
Rounding using ribbon buttons
The most in a simple way To change the rounding of a number is to select a cell or group of cells, and being in the “Home” tab, click on the “Increase bit depth” or “Decrease bit depth” button on the ribbon. Both buttons are located in the “Number” tool block. In this case, only the displayed number will be rounded, but for calculations, if necessary, up to 15 digits of numbers will be used.
When you click on the “Increase decimal place” button, the number of decimal places entered increases by one.
When you click the “Decrease decimal place” button, the number of digits after the decimal point is reduced by one.
Rounding via cell format
You can also set rounding using the cell format settings. To do this, you need to select a range of cells on the sheet, right-click, and select “Format Cells” in the menu that appears.
In the cell format settings window that opens, go to the “Number” tab. If the data format specified is not numeric, then you must select a numeric format, otherwise you will not be able to adjust rounding. In the central part of the window, near the inscription “Number of decimal places,” we simply indicate with a number the number of digits that we want to see when rounding. After this, click on the “OK” button.
Setting the accuracy of calculations
If in previous cases, the parameters set affected only the external display of data, and more accurate indicators were used in the calculations (up to the 15th digit), now we will tell you how to change the accuracy of the calculations.
The Excel Options window opens. In this window, go to the “Advanced” subsection. We are looking for a settings block called “When recalculating this book”. The settings in this section apply not to a single sheet, but to the entire workbook as a whole, that is, to the entire file. Check the box next to the “Set accuracy as on screen” option. Click on the “OK” button located in the lower left corner of the window.
Now, when calculating data, the displayed value of the number on the screen will be taken into account, and not the one stored in Excel's memory. The displayed number can be configured in any of the two ways that we discussed above.
Applying functions
If you want to change the rounding amount when calculating relative to one or more cells, but do not want to reduce the accuracy of calculations as a whole for the document, then in this case, it is best to take advantage of the opportunities provided by the “ROUND” function and its various variations, as well as some other functions.
Among the main functions that regulate rounding are the following:
- ROUND – rounds to the specified number of decimal places, according to generally accepted rounding rules;
- ROUNDUP – rounds up to the nearest number;
- ROUNDDOWN – rounds down to the nearest number;
- ROUND – rounds a number with a specified precision;
- OKRVERCH – rounds a number with a given accuracy up to the absolute value;
- OKRVNIZ – rounds a number down modulo with a specified accuracy;
- OTBR – rounds data to a whole number;
- EVEN – rounds data to the nearest even number;
- ODD – Rounds data to the nearest odd number.
For the ROUND, ROUNDUP and ROUNDDOWN functions, the following input format is: “Function name (number; number_digits). That is, if you, for example, want to round the number 2.56896 to three digits, then use the ROUND(2.56896;3) function. The output is 2.569.
For the functions ROUNDUP, OKRUP and OKRBOTTEN, the following rounding formula is used: “Name of function (number, precision)”. For example, to round the number 11 to the nearest multiple of 2, enter the function ROUND(11;2). The output is the number 12.
The functions DISRUN, EVEN and ODD use the following format: “Function name (number)”. To round the number 17 to the nearest even number, use the EVEN(17) function. We get the number 18.
A function can be entered both in a cell and in the function line, having previously selected the cell in which it will be located. Each function must be preceded by an “=” sign.
There is a slightly different way to introduce rounding functions. It is especially useful when you have a table with values that need to be converted to rounded numbers in a separate column.
To do this, go to the “Formulas” tab. Click on the “Mathematics” button. Next, in the list that opens, select the desired function, for example ROUND.
After this, the function arguments window opens. In the “Number” field, you can enter a number manually, but if we want to automatically round the data of the entire table, then click on the button to the right of the data entry window.
The function arguments window is minimized. Now you need to click on the topmost cell of the column whose data we are going to round. After the value is entered into the window, click on the button to the right of this value.
The function arguments window opens again. In the “Number of digits” field, write down the digit number to which we need to reduce the fractions. After this, click on the “OK” button.
As you can see, the number has been rounded. In order to round all other data in the desired column in the same way, move the cursor over the lower right corner of the cell with the rounded value, click on the left mouse button, and drag it down to the end of the table.
After this, all values in the desired column will be rounded.
As you can see, there are two main ways to round the visible display of a number: using a button on the ribbon, and by changing the cell format parameters. In addition, you can change the rounding of the actual calculated data. This can also be done in two ways: by changing the settings of the book as a whole, or by using special functions. The specific method you choose depends on whether you intend to apply this type of rounding to all data in the file, or only to a specific range of cells.
Let's say you want to round the number to the nearest integer because you don't care about decimal values, or express the number as a power of 10 to make approximate calculations easier. There are several ways to round numbers.
Changing the number of decimal places without changing the value
On a sheet
In built-in number format
Rounding a number up
Round a number to the nearest value
Round a number to the nearest fraction
Rounding a number to a specified number of significant digits
Significant digits are digits that affect the precision of a number.
The examples in this section use the functions ROUND, ROUNDUP And ROUND BOTTOM. They show ways to round positive, negative, integers, and fractions, but the examples given only cover a small portion of the possible situations.
The list below contains general rules, which must be taken into account when rounding numbers to the specified number of significant digits. You can experiment with the rounding functions and substitute your own numbers and parameters to get a number with the desired number of significant digits.
Rounded negative numbers First of all, they are converted to absolute values (values without a minus sign). After rounding, the minus sign is reapplied. Although it may seem counterintuitive, this is how rounding is done. For example, when using the function ROUND BOTTOM To round -889 to two significant places, the result is -880. First -889 is converted to an absolute value (889). This value is then rounded to two significant digits (880). The minus sign is then reapplied, resulting in -880.
When applied to positive number functions ROUND BOTTOM it is always rounded down, and when using the function ROUNDUP- up.
Function ROUND rounds fractional numbers as follows: if the fractional part is greater than or equal to 0.5, the number is rounded up. If the fractional part is less than 0.5, the number is rounded down.
Function ROUND rounds whole numbers up or down in a similar manner, using 5 instead of 0.5 as a divisor.
In general, when rounding a number without a fractional part (a whole number), you need to subtract the length of the number from required quantity significant categories. For example, to round 2345678 down to 3 significant digits, use the function ROUND BOTTOM with parameter -4: =ROUNDBOTTOM(2345678,-4). This rounds the number to 2340000, where the "234" part represents the significant digits.
Round a number to a specified multiple
Sometimes you may need to round a value to a multiple of a given number. For example, let's say a company ships products in boxes of 18. You can use the ROUND function to determine how many boxes will be needed to supply 204 units of an item. IN in this case the answer is 12 because 204 when divided by 18 gives a value of 11.333, which must be rounded up. The 12th box will only contain 6 items.
You may also need to round a negative value to a multiple of a negative, or a fraction to a multiple of a fraction. You can also use the function for this ROUND.
There are several ways to round numbers in Excel. Using cell format and using functions. These two methods should be distinguished as follows: the first is only for displaying values or printing, and the second method is also for calculations and calculations.
Using the functions, it is possible to accurately round up or down to a user-specified digit. And the values obtained as a result of calculations can be used in other formulas and functions. However, rounding using cell format will not give the desired result, and the results of calculations with such values will be erroneous. After all, the format of the cells, in fact, does not change the value, only its display method changes. To quickly and easily understand this and avoid making mistakes, we will give a few examples.
How to round a number using cell format
Let's enter the value 76.575 in cell A1. Right-click to bring up the “Format Cells” menu. You can do the same using the “Number” tool on the main page of the Book. Or press the hotkey combination CTRL+1.
Select the number format and set the number of decimal places to 0.
Rounding result:
You can assign the number of decimal places in “monetary”, “financial”, “percentage” formats.
As you can see, rounding occurs according to mathematical laws. The last digit to be stored is increased by one if it is followed by a digit greater than or equal to "5".
Feature of this option: than more numbers we leave after the comma, the more accurate the result will be.
How to properly round a number in Excel
Using the ROUND() function (rounds to the amount required by the user decimal places). To call the “Function Wizard” we use the fx button. Required function is in the "Mathematics" category.
Arguments:
- “Number” - a link to a cell with the desired value(A1).
- “Number of digits” - the number of decimal places to which the number will be rounded (0 – to round to a whole number, 1 – one decimal place will be left, 2 – two, etc.).
Now let's round the whole number (not a decimal). Let's use the ROUND function:
- the first argument of the function is a cell reference;
- the second argument is with the “-” sign (up to tens – “-1”, up to hundreds – “-2”, to round the number to thousands – “-3”, etc.).
How to round a number to thousands in Excel?
An example of rounding a number to thousands:
Formula: =ROUND(A3,-3).
You can round not only a number, but also the value of an expression.
Let's say there is data on the price and quantity of a product. It is necessary to find the cost accurate to the nearest ruble (rounded to the nearest whole number).
The first argument of the function is a numeric expression to find the cost.
How to round up and down in Excel
To round up, use the “ROUNDUP” function.
We fill in the first argument according to the already familiar principle - a link to a cell with data.
Second argument: "0" - rounding decimal to the whole part, “1” - the function rounds, leaving one decimal place, etc.
Formula: =ROUNDUP(A1;0).
Result:
To round down in Excel, use the ROUNDDOWN function.
Example formula: =ROUNDBOTTOM(A1,1).
Result:
The “ROUND UP” and “ROUND DOWN” formulas are used to round the values of expressions (product, sum, difference, etc.).
How to round to a whole number in Excel?
To round up to a whole number, use the “ROUND UP” function. To round down to a whole number, use the “ROUND DOWN” function. The “ROUND” function and cell format also allow you to round to a whole number by setting the number of digits to “0” (see above).
Excel also uses the RUN function to round to a whole number. It simply discards the decimal places. Essentially, no rounding occurs. The formula cuts off the numbers to the designated digit.
Compare:
The second argument is “0” - the function cuts to an integer; “1” - up to a tenth; “2” - up to a hundredth, etc.
A special Excel function that will return only an integer is “INTEGER”. It has a single argument – “Number”. You can specify a numeric value or a cell reference.
The disadvantage of using the "INTEGER" function is that it only rounds down.
You can round to the nearest integer in Excel using the “OKRUP” and “OKRVDOWN” functions. Rounding occurs up or down to the nearest whole number.
Example of using functions:
The second argument is an indication of the digit to which rounding should occur (10 to tens, 100 to hundreds, etc.).
Rounding to the nearest even integer is performed by the “EVEN” function, rounding to the nearest odd integer is performed by the “ODD” function.
An example of their use:
Why does Excel round large numbers?
If in cells table processor large numbers are entered (for example, 78568435923100756), Excel by default automatically rounds them like this: 7.85684E+16 - this is a feature of the “General” cell format. To avoid such display of large numbers, you need to change the format of the cell with this large number to “Numeric” (the most quick way press the hotkey combination CTRL+SHIFT+1). Then the cell value will be displayed like this: 78,568,435,923,100,756.00. If desired, the number of digits can be reduced: “Home” - “Number” - “Reduce digits”.