How to reduce numbers after the decimal point. Rounding numbers in Microsoft Excel

Let's look at examples of how to round numbers to tenths using rounding rules.

Rule for rounding numbers to tenths.

To round a decimal fraction to tenths, you must leave only one digit after the decimal point and discard all other digits that follow it.

If the first of the discarded digits is 0, 1, 2, 3 or 4, then the previous digit is not changed.

If the first of the discarded digits is 5, 6, 7, 8 or 9, then we increase the previous digit by one.

Examples.

Round to the nearest tenth:

To round a number to tenths, leave the first digit after the decimal point and discard the rest. Since the first digit discarded is 5, we increase the previous digit by one. They read: “Twenty-three point seven five hundredths is approximately equal to twenty three point eight tenths.”

To round this number to tenths, we leave only the first digit after the decimal point and discard the rest. The first digit discarded is 1, so we do not change the previous digit. They read: “Three hundred forty-eight point thirty-one hundredths is approximately equal to three hundred forty-one point three tenths.”

When rounding to tenths, we leave one digit after the decimal point and discard the rest. The first of the discarded digits is 6, which means we increase the previous one by one. They read: “Forty-nine point nine, nine hundred sixty-two thousandths is approximately equal to fifty point zero, zero tenths.”

We round to the nearest tenth, so after the decimal point we leave only the first of the digits, and discard the rest. The first of the discarded digits is 4, which means we leave the previous digit unchanged. They read: “Seven point twenty-eight thousandths is approximately equal to seven point zero tenths.”

To round a given number to tenths, leave one digit after the decimal point, and discard all those following it. Since the first digit discarded is 7, therefore, we add one to the previous one. They read: “Fifty-six point eight thousand seven hundred six ten thousandths is approximately equal to fifty six point nine tenths.”

And a couple more examples for rounding to tenths:

You have to round numbers more often in life than many people think. This is especially true for people in professions related to finance. People working in this field are well trained in this procedure. But in everyday life the process converting values to integer form Not unusual. Many people conveniently forgot how to round numbers immediately after school. Let us recall the main points of this action.

In contact with

Round number

Before moving on to the rules for rounding values, it is worth understanding what is a round number. If we are talking about integers, then it must end with zero.

To the question of where in everyday life such a skill can be useful, you can safely answer - during basic shopping trips.

Using the approximate calculation rule, you can estimate how much your purchases will cost and how much you need to take with you.

It is with round numbers that it is easier to perform calculations without using a calculator.

For example, if in a supermarket or market they buy vegetables weighing 2 kg 750 g, then in a simple conversation with the interlocutor they often do not give the exact weight, but say that they purchased 3 kg of vegetables. When determining the distance between populated areas, the word “about” is also used. This means bringing the result to a convenient form.

It should be noted that some calculations in mathematics and problem solving also do not always use exact values. This is especially true in cases where the response receives infinite periodic fraction. Here are some examples where approximate values are used:

some values of constant quantities are presented in rounded form (the number “pi”, etc.);
tabular values of sine, cosine, tangent, cotangent, which are rounded to a certain digit.

Note! As practice shows, approximating values to the whole, of course, gives an error, but only an insignificant one. The higher the rank, the more accurate the result will be.

Getting approximate values

This mathematical operation is carried out according to certain rules.

But for each set of numbers they are different. Note that you can round whole numbers and decimals.

But with ordinary fractions the operation does not work.

First they need convert to decimals, and then proceed with the procedure in the required context.

The rules for approximating values are as follows:

for integers – replacing the digits following the rounded one with zeros;
for decimal fractions - discarding all numbers that are beyond the digit being rounded.

For example, rounding 303,434 to thousands, you need to replace hundreds, tens and ones with zeros, that is, 303,000. In decimals, 3.3333 rounding to the nearest ten x, simply discard all subsequent digits and get the result 3.3.

Exact rules for rounding numbers

When rounding decimals it is not enough to simply discard digits after rounded digit. You can verify this with this example. If 2 kg 150 g of sweets are purchased in a store, then they say that about 2 kg of sweets were purchased. If the weight is 2 kg 850 g, then round up, that is, about 3 kg. That is, it is clear that sometimes the rounded digit is changed. When and how this is done, the exact rules will be able to answer:

If the rounded digit is followed by a digit 0, 1, 2, 3 or 4, then the rounded digit is left unchanged, and all subsequent digits are discarded.
If the digit being rounded is followed by a digit 5, 6, 7, 8 or 9, then the digit being rounded is increased by one, and all subsequent digits are also discarded.

For example, how to correct a fraction 7.41 bring closer to units. Determine the number that follows the digit. In this case it is 4. Therefore, according to the rule, the number 7 is left unchanged, and the numbers 4 and 1 are discarded. That is, we get 7.

If the fraction 7.62 is rounded, then the units are followed by the number 6. According to the rule, 7 must be increased by 1, and the numbers 6 and 2 discarded. That is, the result will be 8.

The examples provided show how to round decimals to units.

Approximation to integers

It is noted that you can round to units in the same way as to round to integers. The principle is the same. Let us dwell in more detail on rounding decimal fractions to a certain digit in the whole part of the fraction. Let's imagine an example of approximating 756.247 to tens. In the tenths place there is the number 5. After the rounded place comes the number 6. Therefore, according to the rules, it is necessary to perform next steps:

rounding up tens per unit;
in the ones place, the number 6 is replaced;
digits in the fractional part of the number are discarded;
the result is 760.

Let us pay attention to some values in which the process of mathematical rounding to whole numbers according to the rules does not reflect an objective picture. If we take the fraction 8.499, then, transforming it according to the rule, we get 8.

But in essence this is not entirely true. If we round up to whole numbers, we first get 8.5, and then we discard 5 after the decimal point and round up.

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 to consider 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.

Negative numbers that are rounded are first 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 a positive number, the function 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 the required number of significant digits. 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 units. You can use the ROUND function to determine how many boxes will be needed to supply 204 units of an item. 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.

Methods

Different areas may use different rounding methods. In all these methods, “extra” signs are reset (discarded), and the sign preceding them is adjusted according to some rule.

Round to the nearest integer(English) rounding) - the most commonly used rounding, in which a number is rounded 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 to the Nth decimal place, the rule can be formulated as follows:
- If N+1 sign< 5 , then the Nth sign is retained, and N+1 and all subsequent ones are reset to zero;
- If N+1 character ≥ 5, then the Nth sign is increased by one, and N+1 and all subsequent ones are reset to zero;
For example: 11.9 → 12; −0.9 → −1; −1,1 → −1; 2.5 → 3.
Rounding down modulo(round to zero, integer English) fix, truncate, integer) is the “simplest” rounding, since after zeroing out the “extra” signs, the previous sign is retained. For example, 11.9 → 11; −0.9 → 0; −1,1 → −1).
Round up(round to +∞, round up, eng. ceiling) - if the zeroing signs are not equal to zero, the preceding sign is increased by one if the number is positive, or retained if the number is negative. In economic jargon - rounding in favor of the seller, creditor(person receiving money). In particular, 2.6 → 3, −2.6 → −2.
Round down(round to −∞, round down, English. floor) - if the zeroing signs are not equal to zero, the previous sign is retained if the number is positive, or increased 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 toward infinity, round away from zero) is a relatively rarely used form of rounding. If the zeroing signs are not equal to zero, the preceding sign is increased by one.

Options for rounding 0.5 to the nearest integer

Rounding rules require a separate description 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 it is done “up” or “down” - in both cases an error of exactly 1/2 of the least significant digit is introduced . There are the following options for the rounding to the nearest integer rule for this case:

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

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

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

The other three options were invented precisely in order to reduce the overall error of the sum when rounding large quantity values. Rounding “to the nearest even” is based on the assumption that if there are a large number of rounded values that have a 0.5 remainder, on average half will end up to the left and half to the right of the nearest even number, thus canceling out rounding errors. 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, account amounts, 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 better.

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 decimal places that corresponds to the actual accuracy of the calculation parameters (if these values represent real quantities measured in one way or another), the actually achievable accuracy of the calculations, or the desired accuracy of the result. In the past, rounding intermediate values and results was of practical importance (since 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 rounding, may be required to protect against computational errors associated with the finite capacity of computing devices.

Using rounding when working with numbers of limited precision

Real physical quantities are always measured with a certain finite accuracy, which depends on the instruments and measurement methods and is estimated by the maximum relative or absolute deviation of the unknown real value from the measured one, which in the decimal representation of the value corresponds to either a certain number of significant digits or a certain position in the notation of a number, all the numbers after (to the right) of which are insignificant (are within the measurement error). The measured parameters themselves are recorded with such a number of characters that all numbers are reliable, perhaps the last one is doubtful. The error in mathematical operations with numbers of limited accuracy 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 numbers, while present in the values, do not actually reflect any physical reality and only take up 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 obtained values. In practice, it is usually recommended for long "chain" manual calculations to store one more digit in intermediate values. When using a computer, intermediate rounding in scientific and technical applications most often loses its meaning, and only the result is rounded.

So, for example, if a force of 5815 gf is given, accurate to the nearest gram of force, and the arm length is 1.4 m accurate to the 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, we find that the maximum 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 figure is completely reliable, even the second is already doubtful! It would be correct to round the calculation result to the first dubious digit, that is, to tenths: 8.1 kgf m, or, if it is necessary to more accurately indicate the scope of the error, present it in the form rounded to one or two decimal places indicating the error: 8.14 ± 0.06 kgf m.

Rules of thumb for 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 calculation using the formula, you can use a set of simple rules for rounded calculations:

All original values are rounded to actual measurement accuracy and written with the appropriate number of significant digits, so that in decimal notation all digits are reliable (the last digit is allowed to be doubtful). If necessary, values are written with significant right-hand zeros so that the record indicates the actual number of reliable characters (for example, if a length of 1 m is actually measured to the nearest centimeter, write “1.00 m” to show 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 to them).
Intermediate values are rounded with one “spare” digit.
When adding and subtracting, the result is rounded to the last decimal place of the least accurate parameter (for example, when calculating the value 1.00 m + 1.5 m + 0.075 m, the result is rounded to the nearest tenth of a meter, that is, to 2.6 m). In this case, it is recommended to perform calculations in such an order as to avoid subtracting numbers that are close in magnitude and to perform operations on numbers, if possible, in increasing order of their modules.
When multiplying and dividing, the result is rounded to the smallest number of significant figures that the parameters have (for example, when calculating the speed of uniform motion of a body at a distance of 2.5 10 2 m, in 600 s the result should be rounded 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 modulus of the derivative of this function in the vicinity of the calculation point. If (|f"(x)| ≤ 1), then the function result is accurate to the same decimal place as the argument. Otherwise, the result contains fewer exact decimal places by the amount log 10 (|f"(x)|), rounded up to the nearest whole number.

Despite their laxity, the above rules work quite well in practice, in particular, due to the fairly high probability of mutual cancellation of errors, which is usually not taken into account when accurately accounting for errors.

Errors

Abuse of non-round numbers is quite common. For example:

Numbers that have low accuracy are written in unrounded form. In statistics: if 4 people out of 17 answered “yes”, then they write “23.5%” (while “24%” is correct).
Users of pointer instruments sometimes think like this: “the needle stopped between 5.5 and 6, closer to 6, let it be 5.8” - this is also prohibited (the calibration of the device usually corresponds to its real accuracy). In this case, you should say “5.5” or “6”.

Notes

Literature

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

To consider the peculiarities 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, you must 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 we 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 digit that comes after the hundredths is not rounded, so it is simply discarded.
If 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 changes occur. It turns out 18.48.
In the case of the number 0.2254, we have the first digit, which is discarded when rounding to the nearest hundredth. 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 the nearest hundredth, we see that the number 7 rounds the previous ones. We get 65.00.

How to round numbers to whole numbers

The situation is the same when rounding numbers to integers. If we have, for example, 25.5, then after rounding we get 26. In the case of a sufficient number of decimal places, rounding occurs as follows: after rounding 4.371251 we get 4.

Rounding to tenths occurs in the same way as with 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 could round 13.6734 to get 13.7.

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