How to round numbers. Mathematics. Rules rounding numeric values

The Microsoft Excel program works, including with numeric data. When performing division or work with fractional numbers, the program produces rounding. This is due, first of all, with the fact that absolutely accurate fractional numbers are rare when necessary, but it is not very convenient to operate with a bulky expression with several signs after the comma. In addition, there are numbers that in principle are not accurately rounded. But at the same time, an insufficiently accurate rounding can lead to rough errors in situations where preciseness is required. Fortunately, the Microsoft Excel program has the ability to install the users themselves, how the numbers will be rounded.

All the numbers with which Microsoft Excel program works are divided into accurate and approximate. The memory is stored in memory of up to 15 discharge, and displayed before the discharge, which will indicate the user itself. But, at the same time, all calculations are performed according to stored in memory, and not displayed on the data monitor.

Using rounding operation, Microsoft Excel throws some number of semicolons. In Excel, a generally accepted rounding method is used when the number is less than 5 is rounded in a smaller side, and more or equal to 5 - in the most side.

Rounding with buttons on the ribbon

The easiest way to change the rounding number is to select a cell or group of cells, and being in the "Home" tab, click on the tape to the "Enlarge Big" button or "Reduce Bigness". Both buttons are located in the "Number" toolbar. At the same time, only the displayed number will be rounded, but for computations, if necessary, up to 15 digits of numbers will be involved.

When you click on the "Enlarge Bottomy" button, the number of characters made after the comma increases by one.

When you click on the "Reduce Blossomy" button, the number of numbers after the comma decreases to one.

Rounding through cell format

You can also set rounding using the cell format settings. To do this, you need to highlight the range of cells on the sheet, click the right mouse button, and in the menu that appears, select "Format cells".

In the cell format settings window that opens, you need to go to the "Number" tab. If the data format is not specified, then you need to select a numeric format, otherwise you will not be able to adjust the rounding. In the central part of the window near the inscription "The number of decimal signs" simply specify the number of signs that we wish to see when rounding. After that, we perform click on the "OK" button.

Setting accuracy calculations

If in previous cases, the set parameters affected only the external data display, and more accurate indicators were used in the calculations (up to 15 characters), now we will tell you how to change the accuracy of the calculations.

The Excel parameters window opens. In this window, go to subsection "Optional". We are looking for a settings block called "when recalculating this book". Settings in this Boca are applied to any sheet, and the entire book as a whole, that is, to the entire file. We put a tick opposite the parameter "Set accuracy as on the screen". Click on the "OK" button located in the lower left corner of the window.

Now, when calculating the data, the displayed number on the screen will be taken into account, and not the one that is stored in the Excel memory. The setting of the displayed number can be carried out by any of the two methods that we talked above.

Application of functions

If you want to change the rounding value when calculating relatively one or more cells, but do not want to reduce the accuracy of calculations in general for the document, then in this case, it is best to take advantage of the possibilities that the "rounded" function and its various variations, as well as Some other functions.

Among the main functions that regulate rounding should be allocated as follows:

Rounded - rounds the specified number of decimal signs, according to the generally accepted rounding rules;
Districtlvops - rounds up to the nearest number up the module;
Roundlvis - rounds up to the nearest number down the module;
DistrictLT - rounds a number with a given accuracy;
OKRWP - rounds the number with a given accuracy up the module;
Occording - rounds the number down module with a given accuracy;
Scroll - rounds the data to an integer;
Even - rounds the data to the nearest even number;
An odd - rounds the data to the nearest odd number.

For the functions of the rounded, the next input format is the following input format: "The name of the function (number; number of units). That is, if you, for example, you want to round the number of 2.56896 to three digits, then apply the function of the rounded (2,56896; 3). The output is the number of 2.569.

For the functions of the district, OKRWP and the Obrvinis, such a rounding formula is used: "The name of the function (number; accuracy)". For example, to round the number 11 to the nearest number of multiple 2, we introduce the function of the roundt (11; 2). The output is the number 12.

Functions Outbrows, even and default use the following format: "Name of the function (number)". In order to round the number 17 to the nearest even use the function is even (17). We get the number 18.

The function can be entered both in the cell and in the row of functions, after selecting the cell in which it will be. Before each function, you need to sign "\u003d".

There is a somewhat different way to introduce rounding functions. It is especially convenient to use when there is a table with the values \u200b\u200bthat you need to convert into rounded numbers in a separate column.

For this, go to the "Formulas" tab. Clear "mathematical". Further, in the list that opens, select the desired function, for example, rounded.

After that, the function arguments 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 introduction window.

The argument window of the function is folded. Now you need to click on the upper cell of the column, the data of which we are going rounded. After the value is entered in the window, click on the button to the right of this value.

The function arguments window opens again. In the "Number of Discharges" field, write the bit to which we need to cut the fraction. After that, we click on the "OK" button.

As you can see, the number rounded. In order to round up the same way and all other data of the desired column, we bring the cursor to the lower right corner of the cell with a rounded value, click on the left mouse button, and stretch it down to the end of the table.

After that, all values \u200b\u200bin the desired column will be rounded.

As you can see, there are two main ways to round the visible display of the number: using the tape button, and by changing the parameters of the cell format. In addition, you can change the rounding of actually calculated data. It can also be made in two ways: change the settings of the book as a whole, or by applying special functions. The choice of a particular way depends on whether you are going to apply a similar type of rounding for all data in the file, or only for a specific range of cells.

Suppose you want to round the number to the nearest whole, since the decimal values \u200b\u200bare not important to you, or submit a number in the form of degree 10 to simplify approximate calculations. There are several ways rounding numbers.

Changing the number of semicolons without changing the value

On Sheet

In the built-in numerical format

Rounding number up

Rounding number to the nearest value

Rounding the number to the nearest fractional value

Rounding the number to the specified number of significant discharges

Significant discharges are discharges that affect the accuracy of the number.

The examples of this section use functions. District, District and Districtlnoon. They show ways rounding positive, negative, integers and fractional numbers, but the above examples cover only a small part of the possible situations.

The list below contains general rules that need to be considered when rounding the numbers to the specified number of significant discharges. You can experiment with rounding functions and substitute your own numbers and parameters to get a number with the desired number of significant discharges.

The rounded negative numbers are primarily converted into absolute values \u200b\u200b(values \u200b\u200bwithout a "minus" sign). After rounding, the minus sign is reused. Although it may seem illogical, this is how rounding is done. For example, when using the function Districtlnoon For rounding the number -889 to two significant discharges, the result is the number -880. First -889 is converted to absolute value (889). Then this value is rounded up to two significant discharges (880). After that, the "minus" sign re-applies, which results in -880.

When applied to a positive number of functions Districtlnoon It is always rounded down, and when applying the function District - up.

Function District Rounds fractional numbers as follows: If the fractional part is greater than or equal to 0.5, the number is rounded upwards. If the fractional part is less than 0.5, the number is rounded down.

Function District Rounds integers up or down in the same way, while instead of divider 0.5 used 5.

In general, when rounding the number without a fractional part (integer), it is necessary to subtract the length of the number from the desired number of significant discharges. For example, to round up 2345678 down to 3 significant discharges, a function is used. Districtlnoon with parameter -4: \u003d Rounded) (2345678, -4). At the same time, the number is rounded to a value of 2340000, where part "234" represents significant discharges.

Rounding the number to a given multiple

Sometimes it may be necessary to round the value to a multiple of the specified number. For example, let's say that the company supplies goods in boxes of 18 units. Using the roundt function, you can determine how many boxes will be required to supply 204 units of goods. In this case, the answer is 12, since the number 204 during division to 18 gives a value of 11.333, which must be rounded upwards. In the 12th drawer there will be only 6 units of goods.

It may also be necessary to round the negative value to the multiple negative or fractional - to a multiple fractional. To do this, you can also apply the function District.

The numbers are rounded to other discharges - tenths, hundredths, tens, hundreds, etc.

If the number is rounded to some discharge, then all the numbers are replaced by zeros all behind this discharge, and if they are after the comma, they are discarded.

Rule number 1. If the first of the discarded numbers is greater than or equal to 5, then the latter from the stored digits is enhanced, i.e. it increases per unit.

Example 1. The number 45.769 is given to round up to the tenths. The first discarded digit - 6 ˃ 5. Therefore, the latter of the stored digits (7) is amplified, that is, increases by one. And thus, the rounded number will be 45.8.

Example 2. The number 5,165 is given, which must be rounded to the hundredths. The first discarded digit - 5 \u003d 5. Therefore, the latter of the stored digits (6) is amplified, i.e. it increases by one. And thus, the rounded number will be 5.17.

Rule number 2. If the first of the discarded numbers is less than 5, then the amplification is not done.

Example: The number 45.749 is given to round up to the tenths. First discarded digit - 4

Rule number 3. If the digit is dropped 5, and there are no significant numbers behind it, the rounding is made on the nearest even number. That is, the last figure remains unchanged if it is even and enhanced if it is odd.

Example 1: Ringing the number 0.0465 to the third decimal sign, we write - 0.046. Do not do strengthen, since the last saved figure (6) is even.

Example 2. Rounding the number 0.0415 to the third decimal sign, we write - 0.042. The gain is done, since the last saved figure (1) is odd.

Fractional numbers in Excel spreadsheets can be displayed with varying degrees. accuracy:

most plain Method - on the tab " the main»Press the buttons" Increase bit" or " Reduce bit»;
click right mouse button By cell, in the open menu, choose " Format cells ...", Then tab" Number", Choose the format" Numerical"I define how many decimal places after the comma (by default 2 characters are offered);
click the cell on the tab " the main»Choose" Numerical", Either go to" Other numeric formats ..."And there are set up there.

This is how the fraction of 0.129 looks like, if you change the amount of decimal places after the semicolon in the cell format:

Note, in A1, A2, A3 is recorded the same value, only the form of representation is changing. In further calculations, it will be used not the magnitude apparent on the screen, but source. The novice user of the spreadsheet can be slightly confused. To really change the value, you need to use special functions, there are several in Excel.

Formula rounding

One of the frequently used rounding functions - District. It works according to standard mathematical rules. We choose the cell, click the icon " Insert a function", Category" Mathematical", Find District

Determine the arguments, their two - herself fraction and quantity discharges. Click " OK"And we look at what happened.

For example, expression \u003d Rounded (0.129; 1) Give the result 0.1. The zero number of discharges allows you to get rid of the fractional part. The choice of a negative number of discharges allows you to round the whole part up to dozen, hundreds and so on. For example, expression \u003d Rounded (5,129; -1) Dast 10.

Round in big or smaller

Excel presents other means to work with decimal fractions. One of them - District, gives the closest number more By module. For example, expression \u003d rounded (-10.2; 0) will give -11. The number of discharges here 0, which means that we get an integer value. NearestMore than module, - just -11. Example of use:

Districtlnoon Similar to the previous function, but gives the nearest value less than the module. The difference in the work of the following means is seen from examples:

\u003d Rounded (7,384; 0)	7
\u003d Rounded (7.384; 0)	8
\u003d Rounded) (7,384; 0)	7
\u003d Rounded (7,384; 1)	7,4
\u003d Rounded (7.384; 1)	7,4
\u003d Rounded (7,384; 1)	7,3

When rounding, only the right signs are left, the rest are discarded.

Rule 1. The rounding is achieved by simple discarding numbers if the first of the discarded numbers is less than 5.

Rule 2. If the first of the discarded numbers is greater than 5, the latter digit increases per unit. The latter digit also increases in the case when the first of the discarded numbers 5, and there is one or more digits other than zero. For example, various roundings of the number 35,856 will be 35.86; 35.9; 36

Rule 3. If the discarded digit is 5, and there are no significant digits behind it, the rounding is made on the nearest even number, i.e. The last saved figure remains unchanged if it is even and increases per unit if it is odd. For example, 0.435 round up to 0.44; 0.465 Round up to 0.46.

8. An example of processing measurement results

Determination of the density of solids. Suppose a solid has a cylinder form. Then the density ρ can be determined by the formula:

where D is the diameter of the cylinder, H is its height, M - mass.

Let the measurements of M, D, and h be obtained by the following data:

No. p / p	M, G.	Δm, g	D, mm.	ΔD, mm.	h, mm.	ΔH, mm.	, g / cm 3	Δ, g / cm 3
	51,2	0,1	12,68	0,07	80,3	0,15	5,11	0,07	0,013
	12,63	80,2
	12,52	80,3
	12,59	80,2
	12,61	80,1
average			12,61		80,2		5,11

Determine the average value of D:

Find the errors of individual measurements and their squares

We define the average quadratic error of the measurement series:

We specify the value of the reliability α \u003d 0.95 and on the table we find the coefficient of Student T α. n \u003d 2.8 (for n \u003d 5). Determine the boundaries of the confidence interval:

Since the calculated value Δd \u003d 0.07 mm significantly exceeds the absolute error of the micrometer, equal to 0.01 mm (the measurement is performed by the micrometer), then the resulting value can be an estimate of the boundaries of the confidence interval:

D. = D.̃ ± Δ D.; D. \u003d (12.61 ± 0.07) mm.

We define the value of H:

Hence:

For α \u003d 0.95 and n \u003d 5, the coefficient of Student T α, n \u003d 2.8.

Determine the boundaries of the confidence interval

Since the obtained value of ΔH \u003d 0.11 mm of the same order as the error of the caliper, equal to 0.1 mm (the measurement H is performed by the caliper), then the boundaries of the confidence interval should be determined by the formula: