Rounding in Excel - which of the 12 formulas is better to use?

  • home
  • Directory
  • Algebra
  • Numbers
  • Rounding numbers
  • Approximate values
  • Rounding numbers
  • How to round a number to a whole number
  • How to round to tenths
  • How to round a number to hundredths
  • How to round a number to thousandths
  • How to round a number to tens
  • How to round a number to hundreds
  • How to round a number to thousands

In practical human activity there are two types of numbers: exact and approximate. Often, knowing only an approximate number is enough to understand the essence of the matter. Sometimes approximate numbers are used, since the exact number is not required, and sometimes the exact number is impossible to find in principle.

Approximate values

Sometimes it is not necessary to use exact numerical values ​​in calculations. To speed up or simplify calculations, obtaining an approximate result is often sufficient. To do this, round the numbers that are involved in the calculations, as well as the final result of the calculations. Approximate values ​​are used when the exact value of something is impossible to find, or this value is not important for the subject under study.

For example, you can say that the road to home takes half an hour. This is an approximate value, since it is either too difficult or in most cases not so important to say exactly how long it will take to get home. The main thing is to indicate the order of the numbers and this is quite enough.

In mathematics, approximate values ​​are indicated using a special sign.

\[ \LARGE \approx \]

To indicate the approximate value of something, use rounding of numbers.

Rounding to 6 personal income tax.

Rounding by 6 personal income tax is the process of bringing tax amounts to the required form, namely, without kopecks, to whole numbers in rubles. As in other forms of personal income tax, in the 6-NDFL declaration, you also need to round off the numbers that reflect the amount of the total tax and advance payments (approved by Order of the Federal Tax Service of October 14, 2015 No. ММВ-7-11 / [email protected] ).

Let's look at an example of rounding in 6-personal income tax.

Task: Employees' income for 6 months amounted to 1,550,640.82 rubles. The tax amount (without deductions) amounted to 156,230.29 rubles. Question - is there a need to round off income and tax values ​​to fill out the 6-NDFL calculation?

The 6-NDFL calculation form and the procedure for filling it out were approved by order of the Federal Tax Service of Russia dated October 14, 2015 No. ММВ-7-11/ [email protected]

There are lines in the calculation that consist of 2 fields. They must contain information in rubles and kopecks in the form of a decimal fraction. These are the lines:

  • 020 – amount of transferred income;
  • 025 – amount of transferred income in the form of dividends;
  • 030 – tax deductions;
  • 130 – the amount of income actually received.

From here we understand that income, both accrued and paid to individuals, must be entered as is - with pennies. The amount in rubles is indicated in the first field, and after the separating sign “dot” we write kopecks.

The amount of calculated and withheld personal income tax is entered in the calculation on lines 040 and 140, respectively, these lines have one field, which means that the data entered here must be rounded to the nearest whole number.

In addition, in paragraph 6 of Art. 52 of the Tax Code of the Russian Federation states that tax amounts must be calculated in full rubles. A value less than 50 kopecks is discarded, and a value of 50 kopecks or more is rounded to the nearest ruble.

Rounding numbers

The essence of rounding is to find the closest value from the original. At the same time, the number can be rounded to a certain digit - to the tens digit, the hundreds digit, the thousand digit.

First rounding rule:

If, when rounding numbers, the first of the separated digits is less than 5 (0, 1, 2, 3, 4), then the last of the left digits remains unchanged (no amplification or increase is made).

The number 47.271 is rounded down as – 47.3. In this case, the digit 2 will be strengthened to 3, since the first digit cut off is 7, greater than 5.

Second rounding rule:

If, when rounding numbers, the first of the separated digits is greater than 5 (5, 6, 7, 8, 9), then the last of the remaining digits is increased by one (amplification is performed).

The number 64.28 is rounded down as – 64. The number 64 is closer to the number being rounded than 65.

Third rounding rule:

If the digit 5 ​​is cut off and there are no significant digits behind it, then rounding is done to the nearest even number, in other words, the last digit retained remains unchanged if it is even, and is strengthened if it is odd.

The number 0.0465 is rounded down as – 0.046. In this case, no amplification is done, since the last digit left, 6, is even. The number 0.935 is rounded down as – 0.94. The last digit left, 3, is strengthened since it is odd.

Is it possible to keep accounting records in whole rubles?

BOO

Organizations can keep records of property, liabilities, and business transactions in whole rubles.

Differences that arise in commercial organizations should be attributed to financial results in the accounts of other income and expenses (clause 25 of the Accounting Regulations, approved by Order of the Ministry of Finance dated July 29, 1998 N 34n, clause 11 PBU 10/99, clause 7 PBU 9/ 99):

  • The rounding method has not been established; it must be approved by the Accounting Policy, for example, according to the rules of mathematics.

How to round a number to a whole number

Rule for rounding a number to a whole number

To round a number to an integer (or to round a number to units), you need to discard the comma and all numbers after the decimal point.

If the first digit discarded is 0, 1, 2, 3 or 4, then the number will not change.

If the first digit dropped is 5, 6, 7, 8, or 9, the previous digit must be increased by one.

Examples of rounding a number to a whole number:

\[ 86,\underline 2 4 \approx 86 \] To round a number to an integer, discard the comma and all numbers after it. Since the first digit discarded is 2, we do not change the previous digit. They read: “eighty-six point twenty-four hundredths is approximately equal to eighty-six whole.”

\[ 274,\underline 8 39 \approx 275 \] When rounding a number to an integer, we discard the comma and all numbers following it. Since the first of the discarded digits is equal to 8, we increase the previous one by one. They read: “Two hundred and seventy-four point eight hundred and thirty-nine thousandths is approximately equal to two hundred and seventy-five whole.”

\[ 0,\underline 5 2 \approx 1 \] When rounding a number to a whole number, we discard the comma and all the numbers following it. Since the first of the discarded digits is 5, we increase the previous one by one. They read: “Zero point fifty-two hundredths is approximately equal to one point.”

\[ 0,\underline 3 97 \approx 0 \] We discard the comma and all the numbers after it. The first of the discarded digits is 3, so we do not change the previous digit. They read: “Zero point three ninety-seven thousandths is approximately equal to zero point.”

\[ 39,\underline 7 04 \approx 40 \] The first of the discarded digits is 7, which means that the digit in front of it is increased by one. They read: “Thirty-nine point seven hundred and four thousandths is approximately equal to forty whole.” And a couple more examples for rounding numbers to integers:

Problems with buyers

The buyer may question the accuracy of the rounded amounts. Even minor discrepancies cause constant requests from counterparties to correct documents. In this case, we suggest adding the following provision to the accounting policy: “Employees prepare primary documents and invoices in a special program. The cost of goods is automatically rounded to two decimal places. When calculating, the program takes into account VAT without rounding, so discrepancies between manual calculation and program data are possible - up to 5 kopecks.”

This provision will help solve the problem of constant customer dissatisfaction.

How to round to tenths

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 of rounding to tenths:

\[ 23.7\underline 5 \approx 23.8 \] 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.”

\[ 348.3\underline 1 \approx 348.3 \] To round this number to tenths, leave only the first digit after the decimal point, 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.”

\[ 49.9\underline 6 2 \approx 50.0 \] 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.”

\[ 7.0\underline 2 8 \approx 7.0 \] We round to the nearest tenth, so after the decimal point we leave only the first of the digits, discarding 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.”

\[ 56.8\underline 7 06 \approx 56.9 \] To round this 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.”

How to round a number to hundredths

Rule for rounding numbers to hundredths

To round a number to hundredths, you need to leave two digits after the decimal point and discard the rest.

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.

An example of rounding a number to hundredths:

\[ 32.78\underline 6 \approx 32.79 \] To round a number to hundredths, leave two digits after the decimal point and discard the next digit. Since this digit is 9, we increase the previous digit by one. They read: “Thirty-two point seven hundred and eighty-six thousandths is approximately equal to thirty-two point seventy-nine hundredths.”

\[ 6.96\underline 1 \approx 6.96 \] When rounding this number to hundredths, we leave two digits after the decimal point and discard the third. Since the discarded digit is 1, we leave the previous digit unchanged. They read: “Six point nine hundred sixty-one thousandths is approximately equal to six point nine hundred six.”

\[ 17.48\underline 3 9 \approx 17.48 \] When rounding to hundredths, we leave two digits after the decimal point and discard the rest. The first of the discarded digits is 3, so we do not change the previous digit. They read: “Seventeen point four thousand thirty-nine ten-thousandths is approximately equal to seventeen point forty-eight hundredths.”

\[ 0.12\underline 5 4 \approx 0.13 \] To round this number to hundredths, we will leave only two digits after the decimal point and discard the rest. The first of the discarded digits is 5, so we increase the previous digit by one. They read: “Zero point two hundred and fifty-four thousandths is approximately equal to zero point thirteen hundredths.”

\[ 549.30\underline 7 3 \approx 549.31 \] When rounding a number to hundredths, we leave two digits after the decimal point and discard the rest. Since the first of the discarded digits is 7, we increase the previous digit by one. We read: “Five hundred forty-nine point three, three thousand seventy-three ten-thousandths is approximately equal to five hundred forty-nine point thirty one hundredth.”

How to round a number to thousandths

Rule for rounding numbers to thousandths

To round a decimal fraction to thousandths, you must leave only three digits after the decimal point and discard the remaining 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.

An example of rounding a number to thousandths:

\[ 3.785\underline 4 \approx 3.785 \] To round a number to thousandths, you need to leave only three digits after the decimal point and discard the fourth. Since the discarded digit is 4, we leave the previous digit unchanged. They read: “Three point seven thousand eight hundred and fifty-four ten-thousandths is approximately equal to three point seven hundred and eighty-five thousandths.”

\[ 37,207\underline 6 \approx 37,208 \] To round this number to thousandths, we leave three digits after the decimal point and discard the fourth. The discarded digit is 6, which means we increase the previous digit by one. They read: “Thirty-seven point two thousand seventy six ten thousandths is approximately equal to thirty seven point two hundred eight thousandths.”

\[ 69,999\underline 8 1 \approx 70,000 \] When rounding the number to thousandths, we leave three digits after the decimal point and discard all the rest. Since the first of the discarded digits is 8, we add one to the previous one. They read: “Sixty-nine point nine thousand nine hundred eighty-one hundred thousandths is approximately equal to seventy point zero thousandths.”

\[ 863,124\underline 2 3 \approx 863,124 \] We round the number to thousandths, so after the decimal point we leave the first three digits, and discard the following ones. Since the first of the discarded digits is 2, we do not change the previous digit. They read: “Eight hundred sixty-three point twelve thousand four hundred twenty-three hundred thousandths is approximately equal to eight hundred sixty-three point one hundred twenty-four thousandths.”

\[ 0.003\underline 5 9 \approx 0.004 \] To round this number to thousandths, we leave the first three digits after the decimal point, and discard all the rest. The first digit discarded is 5, which means that the previous digit should be increased by one. They read: “Zero point three hundred fifty-nine hundred thousandths is approximately equal to zero point four thousandths.”

How to round a number to tens

Rule for rounding numbers to tens

To round a number to tens, you need to replace the digit in the units place with a zero, and if the number contains digits after the decimal point, they should be discarded.

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 of rounding numbers to tens:

\[ 58\underline 3 \approx 580 \] To round a number to tens, replace the digit in the units place (that is, the last digit in the natural number) with zero. Since this digit is 3, we do not change the previous digit. They read: “Five hundred eighty-three is approximately equal to five hundred eighty.”

\[ 103\underline 7 \approx 1040 \] We round to the nearest ten, so we replace the digit in the units place with zero. Since this number is 7, we increase the previous one by one. They read: “One thousand thirty-seven is approximately equal to one thousand forty.”

\[ 35\underline 2,78 \approx 350 \] When rounding a decimal fraction to tens, we replace the digit in the units place (that is, the last digit before the decimal point) with zero, and discard the comma and all digits after it. The digit replaced by zero is 2, which means there is no need to change the previous digit. They read: “Three hundred fifty-two point seventy-eight hundredths is approximately equal to three hundred fifty.”

\[ 247\underline 6 .05 \approx 2480 \] To round a given decimal fraction to tens, we replace the digit in the units place with zero, and discard the digits after the decimal point. Since the digit replaced by zero is 6, we add one to the previous digit. They read: “Two thousand four hundred and seventy-six point five is approximately equal to two thousand four hundred and eighty.”

\[ 79\underline 9 ,1 \approx 800 \] When rounding a decimal fraction to tens, in the units place we replace the digit with a zero, and discard the comma and everything after the decimal point. Since 9 was replaced by zero, we increase the previous digit by one. They read: “Seven hundred and ninety-nine point one, one tenth is approximately equal to eight hundred.”

Round number


Before moving on to the rules for rounding values, it is worth understanding what a round number is. 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 answer is an infinite periodic fraction. Here are some examples where approximate values ​​are used:

  • some values ​​of constant quantities are presented in rounded form (pi number, 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.

How to round a number to hundreds

Rule for rounding numbers to hundreds

To round a number to hundreds, you need to replace the numbers in the ones and tens places with zeros. When rounding to hundreds of decimals, the comma and all digits following it are discarded.

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 of rounding numbers to hundreds:

\[ 23\underline 1 7 \approx 2300 \] To round this number to hundreds, replace the digits in the units and tens place (that is, the last two digits in the entry) with zeros. Since the first of the digits replaced by zero is equal to 1, we do not change the previous digit. They read: “Two thousand three hundred and seventeen is approximately equal to two thousand three hundred.”

\[ 45\underline 8 1 \approx 4600 \] Rounding this number to hundreds, we replace the last two digits in its notation with zeros. Since the first digit replaced by zero is 8, we increase the previous digit by one. They read: “Four thousand five hundred eighty-one is approximately equal to four thousand six hundred.”

\[ 785\underline 0 9 \approx 78500 \] We round the number to hundreds, which means we replace the last two digits in the number record - tens and ones - with zeros. The first of the digits replaced by zero is equal to zero, so we rewrite the previous one without changes. They read: “Seventy-eight thousand five hundred nine is approximately equal to seventy-eight thousand five hundred.”

\[ 939\underline 5 2 \approx 94000 \] To round this number to hundreds, replace the digits in the tens and units places with zeros. Since the first of the digits replaced by zero is 9, we increase the previous one by one. They read: “Ninety-three thousand nine hundred and fifty-two is approximately equal to ninety-four thousand.”

\[ 14\underline 7 3.12 \approx 1500 \] To round a decimal fraction to hundreds, the comma and all digits after the decimal point must be discarded, and the last two digits of the integer part (units and tens) must be replaced with zeros. The first digit replaced by zero is 7, so we add one to the previous digit. They read: “One thousand four hundred and seventy-three point twelve hundredths is approximately equal to one thousand five hundred.”

Problems with the tax authorities

In invoices, all costs must be indicated in rubles and kopecks. But some suppliers, due to errors or inexperience, round up the total, thereby inflating the VAT amount.

As a result, the buyer's tax deduction is overestimated. The difference will be in kopecks, but because of this, inspectors may refuse not only the difference, but even a VAT deduction in general. Therefore, resolve these issues with your counterparties in advance, and if documents are completed incorrectly, demand corrections.

Example . The company shipped 60 boxes of pineapples. Price for 1 box—RUB 1,527.36. Correct calculation . Indicate on the invoice:

  • cost of goods without VAT - 91,641.6 rubles. = 1527.36 × 60;
  • VAT - 18,328.32 = 91,641.6 × 20%;
  • cost including VAT - 109,969.92 = 91,641.6 + 18,328.32.

If the supplier rounds the amount including VAT to 109,970 rubles, then the VAT will be overestimated, and the buyer may lose the deduction.

There are many problems and questions with VAT calculation, but the Kontur.Accounting web service will help you cope with most of them. The system automatically rounds up VAT in the manner prescribed by law. And tax returns can be submitted online. All new users receive 14 days of using the service as a gift.

Rating
( 2 ratings, average 4 out of 5 )
Did you like the article? Share with friends:
For any suggestions regarding the site: [email protected]
Для любых предложений по сайту: [email protected]