How to round numbers up and down using Excel functions. Easy rules for rounding numbers after the decimal point

Methods

Different fields may use different methods of rounding. In all these methods, the "extra" signs are set to zero (discarded), and the sign preceding them is corrected according to some rule.

Rounding to nearest integer(English) rounding) - the most commonly used rounding, in which the number is rounded up to an integer, the modulus of the difference with which this number has a minimum. In general, when a number in the decimal system is rounded up to the Nth decimal place, the rule can be formulated as follows:
- if N+1 character< 5 , then the Nth sign is retained, and N+1 and all subsequent ones are set to zero;
- if N+1 characters ≥ 5, then the N-th sign is increased by one, and N + 1 and all subsequent ones are set to zero;
For example: 11.9 → 12; -0.9 → -1; −1,1 → −1; 2.5 → 3.
Rounding down modulo(rounding towards zero, integer Eng. fix, truncate, integer) is the most “simple” rounding, since after zeroing the “extra” signs, the previous sign is retained. For example, 11.9 → 11; −0.9 → 0; −1,1 → −1).
Rounding Up(round to +∞, round up, eng. ceiling) - if the nullable signs are not equal to zero, the preceding sign is increased by one if the number is positive, or kept if the number is negative. In economic jargon - rounding in favor of the seller, creditor(of the person receiving the money). In particular, 2.6 → 3, −2.6 → −2.
Rounding Down(round to −∞, round down, engl. floor) - if the nullable signs are not equal to zero, the preceding sign is retained if the number is positive, or incremented by one if the number is negative. In economic jargon - rounding in favor of the buyer, debtor(the person giving the money). Here 2.6 → 2, −2.6 → −3.
Rounding up modulo(round towards infinity, round away from zero) is a relatively rarely used form of rounding. If the nullable characters are not equal to zero, the preceding character is incremented by one.

Rounding options 0.5 to nearest integer

A separate description is required by the rounding rules for the special case when (N+1)th digit = 5 and subsequent digits are zero. If in all other cases, rounding to the nearest integer provides a smaller rounding error, then this particular case is characterized by the fact that for a single rounding it is formally indifferent whether to make it “up” or “down” - in both cases, an error of exactly 1/2 of the least significant digit is introduced . There are the following variants of the rounding rule to the nearest integer for this case:

Mathematical rounding- rounding is always up (the previous digit is always increased by one).
Bank rounding(English) banker's rounding) - rounding for this case occurs to the nearest even number, i.e. 2.5 → 2, 3.5 → 4.
Random rounding- rounding up or down randomly, but with equal probability (can be used in statistics).
Alternate rounding- Rounding occurs up or down alternately.

In all cases, when the (N + 1)th sign is not equal to 5 or subsequent signs are not equal to zero, rounding occurs according to the usual rules: 2.49 → 2; 2.51 → 3.

Mathematical rounding simply formally corresponds to the general rounding rule (see above). Its disadvantage is that when rounding a large number of values, accumulation can occur. rounding errors. A typical example: rounding up to whole rubles of monetary amounts. So, if in the register of 10,000 lines there are 100 lines with amounts containing the value of 50 in terms of kopecks (and this is a very realistic estimate), then when all such lines are rounded “up”, the sum of the “total” according to the rounded register will be 50 rubles more than the exact .

The other three options are just invented in order to reduce the total error of the sum when rounding a large number of values. Rounding "to the nearest even" is based on the assumption that with a large number of rounded values that have 0.5 in the rounded remainder, on average, half will be to the left and half to the right of the nearest even, thus rounding errors will cancel each other out. Strictly speaking, this assumption is true only when the set of numbers being rounded has the properties of a random series, which is usually true in accounting applications where we are talking about prices, amounts in accounts, and so on. If the assumption is violated, then rounding “to even” can lead to systematic errors. For such cases, the following two methods work best.

The last two rounding options ensure that approximately half of the special values are rounded one way and half the other. But the implementation of such methods in practice requires additional efforts to organize the computational process.

Applications

Rounding is used to work with numbers within the number of digits that corresponds to the actual accuracy of the calculation parameters (if these values are real values measured in one way or another), the realistically achievable calculation accuracy, or the desired accuracy of the result. In the past, the rounding of intermediate values and the result was of practical importance (because when calculating on paper or using primitive devices such as the abacus, taking into account extra decimal places can seriously increase the amount of work). Now it remains an element of scientific and engineering culture. In accounting applications, in addition, the use of rounding, including intermediate ones, may be required to protect against computational errors associated with the finite bit capacity of computing devices.

Using rounding when working with numbers of limited precision

Real physical quantities are always measured with some finite accuracy, which depends on the instruments and methods of measurement and is estimated by the maximum relative or absolute deviation of the unknown actual value from the measured one, which in decimal representation of the value corresponds either to a certain number of significant digits, or to a certain position in the notation of a number, all the numbers after (to the right) of which are insignificant (they lie within the measurement error). The measured parameters themselves are recorded with such a number of characters that all figures are reliable, perhaps the last one is doubtful. The error in mathematical operations with numbers of limited precision is preserved and changes according to known mathematical laws, so when intermediate values and results with a large number of digits appear in further calculations, only a part of these digits are significant. The remaining figures, being present in the values, do not actually reflect any physical reality and only take time for calculations. As a result, intermediate values and results in calculations with limited accuracy are rounded to the number of decimal places that reflects the actual accuracy of the values obtained. In practice, it is usually recommended to store one more digit in intermediate values for long "chained" manual calculations. When using a computer, intermediate roundings in scientific and technical applications most often lose their meaning, and only the result is rounded.

So, for example, if a force of 5815 gf is given with an accuracy of a gram of force and a shoulder length of 1.4 m with an accuracy of a centimeter, then the moment of force in kgf according to the formula, in the case of a formal calculation with all signs, will be equal to: 5.815 kgf 1.4 m = 8.141 kgf m. However, if we take into account the measurement error, then we get that the limiting relative error of the first value is 1/5815 ≈ 1,7 10 −4 , second - 1/140 ≈ 7,1 10 −3 , the relative error of the result according to the error rule of the multiplication operation (when multiplying approximate values, the relative errors add up) will be 7,3 10 −3 , which corresponds to the maximum absolute error of the result ±0.059 kgf m! That is, in reality, taking into account the error, the result can be from 8.082 to 8.200 kgf m, thus, in the calculated value of 8.141 kgf m, only the first digit is completely reliable, even the second is already doubtful! It will be correct to round the calculation result to the first doubtful digit, that is, to tenths: 8.1 kgf m, or, if necessary, a more accurate indication of the margin of error, present it in a form rounded to one or two decimal places with an indication of the error: 8.14 ± 0.06 kgf m.

Empirical rules of arithmetic with rounding

In cases where there is no need to accurately take into account computational errors, but only need to approximately estimate the number of exact numbers as a result of the calculation by the formula, you can use a set of simple rules for rounded calculations:

All raw values are rounded to the actual measurement accuracy and recorded with the appropriate number of significant digits, so that all digits in the decimal notation are reliable (it is allowed that the last digit is doubtful). If necessary, values are recorded with significant right-hand zeros so that the actual number of reliable characters is indicated in the record (for example, if a length of 1 m is actually measured to the nearest centimeter, “1.00 m” is written so that it can be seen that two characters are reliable in the record after the decimal point), or the accuracy is explicitly indicated (for example, 2500 ± 5 m - here only tens are reliable, and should be rounded up to them).
Intermediate values are rounded off with one "spare" digit.
When adding and subtracting, the result is rounded to the last decimal place of the least accurate of the parameters (for example, when calculating a value of 1.00 m + 1.5 m + 0.075 m, the result is rounded to tenths of a meter, that is, to 2.6 m). At the same time, it is recommended to perform calculations in such an order as to avoid subtracting close numbers and to perform operations on numbers, if possible, in ascending order of their modules.
When multiplying and dividing, the result is rounded to the smallest number of significant digits that the parameters have (for example, when calculating the speed of uniform movement of a body at a distance of 2.5 10 2 m, for 600 s the result should be rounded up to 4.2 m/s, since it is distance has two digits and time has three, assuming all digits in the entry are significant).
When calculating the function value f(x) it is required to estimate the value of the modulus of the derivative of this function in the vicinity of the calculation point. If a (|f"(x)| ≤ 1), then the result of the function is exact to the same decimal place as the argument. Otherwise, the result contains fewer exact decimal places by the amount log 10 (|f"(x)|), rounded to the nearest integer.

Despite the non-strictness, the above rules work quite well in practice, in particular, because of the rather high probability of mutual cancellation of errors, which is usually not taken into account when errors are accurately taken into account.

Mistakes

Quite often there are abuses of non-round numbers. For example:

Write down numbers that have low accuracy, in unrounded form. In statistics: if 4 people out of 17 answered “yes”, then they write “23.5%” (while “24%” is correct).
Pointer users sometimes think like this: “the pointer stopped between 5.5 and 6 closer to 6, let it be 5.8” - this is also prohibited (the graduation of the device usually corresponds to its actual accuracy). In this case, you need to say "5.5" or "6".

Notes

Literature

Henry S. Warren, Jr. Chapter 3// Algorithmic tricks for programmers = Hacker's Delight. - M .: Williams, 2007. - S. 288. - ISBN 0-201-91465-4

To consider the peculiarity of rounding a particular number, it is necessary to analyze specific examples and some basic information.

How to round numbers to hundredths

To round a number to hundredths, it is necessary to leave two digits after the decimal point, the rest, of course, are discarded. If the first digit to be discarded is 0, 1, 2, 3, or 4, then the previous digit remains unchanged.
If the discarded digit is 5, 6, 7, 8 or 9, then you need to increase the previous digit by one.
For example, if you need to round the number 75.748 , then after rounding we get 75.75 . If we have 19.912 , then as a result of rounding, or rather, in the absence of the need to use it, we get 19.91 . In the case of 19.912, the number after the hundredths is not rounded, so it is simply discarded.
If a we are talking about the number 18.4893 , then rounding to hundredths occurs as follows: the first digit to be discarded is 3, so no change occurs. It turns out 18.48.
In the case of the number 0.2254, we have the first digit, which is discarded when rounding to hundredths. This is a five, which indicates that the previous number needs to be increased by one. That is, we get 0.23 .
There are also cases when rounding changes all the digits in a number. For example, to round the number 64.9972 to hundredths, we see that the number 7 rounds the previous ones. We get 65.00.

How to round numbers to integers

When rounding numbers to integers, the situation is the same. If we have, for example, 25.5 , then after rounding we get 26 . In the case of a sufficient number of digits after the decimal point, rounding occurs in this way: after rounding 4.371251, we get 4 .

Rounding to tenths occurs in the same way as in the case of hundredths. For example, if we need to round the number 45.21618 , then we get 45.2 . If the second digit after the tenth is 5 or more, then the previous digit is increased by one. As an example, you can round 13.6734 to get 13.7.

It is important to pay attention to the number that is located in front of the one that is cut off. For example, if we have the number 1.450, then after rounding we get 1.4. However, in the case of 4.851, it is advisable to round up to 4.9, since after the five there is still one.

We often use rounding in everyday life. If the distance from home to school is 503 meters. We can say, by rounding up the value, that the distance from home to school is 500 meters. That is, we have brought the number 503 closer to the more easily perceived number 500. For example, a loaf of bread weighs 498 grams, then by rounding the result we can say that a loaf of bread weighs 500 grams.

rounding- this is the approximation of a number to a “lighter” number for human perception.

The result of rounding is approximate number. Rounding is indicated by the symbol ≈, such a symbol reads “approximately equal”.

You can write 503≈500 or 498≈500.

Such an entry is read as “five hundred three is approximately equal to five hundred” or “four hundred ninety-eight is approximately equal to five hundred”.

Let's take another example:

44 71≈4000 45 71≈5000

43 71≈4000 46 71≈5000

42 71≈4000 47 71≈5000

41 71≈4000 48 71≈5000

40 71≈4000 49 71≈5000

In this example, numbers have been rounded to the thousands place. If we look at the rounding pattern, we will see that in one case the numbers are rounded down, and in the other - up. After rounding, all other numbers after the thousands place were replaced by zeros.

Number rounding rules:

1) If the figure to be rounded is equal to 0, 1, 2, 3, 4, then the digit of the digit to which the rounding is going does not change, and the rest of the numbers are replaced by zeros.

2) If the figure to be rounded is equal to 5, 6, 7, 8, 9, then the digit of the digit up to which the rounding is going on becomes 1 more, and the remaining numbers are replaced by zeros.

For example:

1) Round to the tens place of 364.

The digit of tens in this example is the number 6. After the six there is the number 4. According to the rounding rule, the number 4 does not change the digit of the tens. We write zero instead of 4. We get:

36 4 ≈360

2) Round to the hundreds place of 4781.

The hundreds digit in this example is the number 7. After the seven is the number 8, which affects whether the hundreds digit changes or not. According to the rounding rule, the number 8 increases the hundreds place by 1, and the rest of the numbers are replaced by zeros. We get:

47 8 1≈48 00

3) Round to the thousands place of 215936.

The thousands place in this example is the number 5. After the five is the number 9, which affects whether the thousands place changes or not. According to the rounding rule, the number 9 increases the thousands place by 1, and the remaining numbers are replaced by zeros. We get:

215 9 36≈216 000

4) Round to the tens of thousands of 1,302,894.

The thousand digit in this example is the number 0. After zero, there is the number 2, which affects whether the tens of thousands digit changes or not. According to the rounding rule, the number 2 does not change the digit of tens of thousands, we replace this digit and all digits of the lower digits with zero. We get:

130 2 894≈130 0000

If the exact value of the number is not important, then the value of the number is rounded off and you can perform computational operations with approximate values. The result of the calculation is called estimation of the result of actions.

For example: 598⋅23≈600⋅20≈12000 is comparable to 598⋅23=13754

An estimate of the result of actions is used in order to quickly calculate the answer.

Examples for assignments on the topic rounding:

Example #1:
Determine to what digit rounding is done:
a) 3457987≈3500000 b) 4573426≈4573000 c) 16784≈17000
Let's remember what are the digits on the number 3457987.

7 - unit digit,

8 - tens place,

9 - hundreds place,

7 - thousands place,

5 - digit of tens of thousands,

4 - hundreds of thousands digit,
3 is the digit of millions.
Answer: a) 3 4 57 987≈3 5 00 000 digit of hundreds of thousands b) 4 573 426 ≈ 4 573 000 digit of thousands c) 16 7 841 ≈17 0 000 digit of tens of thousands.

Example #2:
Round the number to 5,999,994 places: a) tens b) hundreds c) millions.
Answer: a) 5,999,994 ≈5,999,990 b) 5,999,99 4≈6,000,000 6,000,000.

Understand the meaning of numbers in decimals. In any number, different digits represent different digits. For example, in the number 1872, one represents thousands, eight represents hundreds, seven represents tens, and two represents ones. If there is a decimal point in the number, then the numbers to the right of it reflect fractions of a whole number.

Determine the decimal place to which you want to round it. The first step in rounding decimals is determining the place to which you want to round a number. If you are doing homework, then this is usually determined by the assignment condition. Often, the condition may indicate the need to round the answer to tenths, hundredths, or thousandths of a decimal point.

For example, if the task is to round the number 12.9889 to thousandths, you should start by identifying the location of these thousandths. Count the decimal places as tenths, hundredths, thousandths, followed by ten thousandths. The second eight will be just what you need (12.98 8 9).
Sometimes a condition may specify where to round (for example, "round to three decimal places" means the same as "round to thousandths").

Look at the number to the right of where you want to round off. Now you should find out the number that is to the right of the place to which you are rounding. Depending on this figure, you will round up or down (up or down).

In the example of the number (12.9889) taken earlier, it is necessary to round to thousandths (12.98 8 9), so now you should look at the number to the right of the thousandth, namely the last nine (12.988 9 ).

If this figure is greater than or equal to five, then rounding up is performed. For greater clarity, if the number 5, 6, 7, 8 or 9 is to the right of the rounding point, then rounding up is performed. In other words, it is necessary to increase the digit at the rounded place by one, and discard the remaining digits to the right of it.

In the example taken (12.9889), the last nine is greater than five, so we will round the thousandths to the big side. The rounded number will appear as 12,989 . Note that after the rounding point, the figures are discarded.

If this figure is less than five, then rounding down is performed. That is, if the number 4, 3, 2, 1 or 0 is to the right of the rounding point, then rounding down is performed. Which means the need to leave the figure in place of the rounding in the form in which it is, and discard the numbers to the right of it.

You cannot round down 12.9889 because the last nine is not a four or less. However, if the number in question were 12.988 4 , then it could be rounded up to 12,988 .
Does the procedure sound familiar? This is due to the fact that integers are rounded in the same way, and the presence of a comma does not change anything.

Use the same method to round decimals to integers. Often the task establishes the need to round the answer to integers. In this case, you must use the above method.

In other words, find the location of the integer units of the number, look at the number on the right. If it is greater than or equal to five, then round the whole number up. If it is less than or equal to four, then round the whole number down. The presence of a comma between the integer part of the number and its decimal fraction does not change anything.
For example, if you want to round the above number (12.9889) to integers, you would start by locating the integer units of the number: 1 2 .9889. Since the nine to the right of this place is greater than five, we round up to 13 whole. Since the answer is represented by an integer, there is no need to write a comma anymore.

Pay attention to rounding instructions. The above rounding instructions are generally accepted. However, there are situations where special rounding requirements are given, be sure to read them before resorting to the generally accepted rounding rules right away.

For example, if the requirements say to round down to tenths, then in the number 4.59 you will leave a five, despite the fact that a nine to the right of it should usually result in rounding up. This will give you the result 4,5 .
Similarly, if you are told to round the number 180.1 to whole to the big side, then you will succeed 181 .