For calculations in Excel can be often assumed difference between the format of the result and the real value rounded. In this tutorial you will learn how solve this problem and learn practical function to correct round. If this guide has helped you, become a fan on Facebook and recommend this site to your friends, it can be useful for them too.

Format vs. real result

Imagine that in the table comes from the formula result 3.646. If you format the result eg. to display 2 decimal places (menu Format Cells> Number) then appears in cell number 3.65. But the real value does not change with the format, we can look in the formula bar. If we then multiply this cell 1000 *, the result will be 3646. If we use a calculator, multiply 3.65 x 1000, result will be 3650. We see the difference between the values 4, and sometimes, even if seemingly small difference, have a major impact.

Conclusion: Format Cells does not change a real value of the cell, with which other formulas count.

The following functions change the real value for further calculations.

Funkce zaokrouhlení

Round functions (czech)

Function ROUND

=ROUND(number, num_digits)

  • number … numerical value or cell to be rounded
  • num_digits … indicates the number of decimal places

This function rounded as in mathematics, ie. 5 always at a higher number, up to 5 at a lower number.

Function ROUNDUP

=ROUNDUP(number, num_digits)

  • number … numerical value or cell to be rounded
  • num_digits … indicates the number of decimal places

This function is always rounded up to the higher number.

Function ROUNDDOWN

=ROUNDDOWN(number, num_digits)

  • number … numerical value or cell to be rounded
  • num_digits … indicates the number of decimal places

This function is always rounded down to the lower number.

Function TRUNC

=TRUNC(number; num_digits)

  • number … numerical value or cell to be rounded
  • num_digits … indicates the number of decimal places, if omitted it removes all decimal places

This function is always “cut off” all from the numbers after specified decimal places.

Function INT

=INT(number)

  • number … numerical value or cell to be rounded

This function always rounded down to the nearest integer.

Function FLOOR

=FLOOR(number, significance)

  • number … numerical value or cell to be rounded
  • significance … indicates the unit / multiple to which number will be rounded up

This function is always rounded down to a given multiple. If we want to round to an integer set argument significance with a value of 1, to tens set 10, to hundreds set 100, to tenths 0,1 etc.

Similarly works CEILING function which always rounded up to the given multiple.

In version 2013, these functions are replaced by functions CEILING.MATH a FLOOR.MATH, but still remain supported.

Similar posts about this topic

Leave a Reply

Your email address will not be published. Required fields are marked *