< Previous lesson | Overview | Next lesson >


How to round numbers

Often when we work with numbers, we might want whole numbers, completely without decimals. Or we might want decimals, but no more than two. Etc., etc.

But what do we do when we have a result calculation that looks like this?
which gives 4,3333333333333... as a result?

Yup, you guessed it, we use the formula =ROUND()

This is one of the easier formulas, so you can lower you shoulders now :-)
The syntax is as follows:

=ROUND([any number];[number of digits])

[any number]
This is the number you want to round

This can be either a:

  • number (e.g. [2,3333])
  • calculation (e.g. [13/4])
  • cell reference (e.g. [A1])
  • another formula (e.g. [=SUM(A1:C8)]

[number of digits]
This part describes how much you want to round it, where a positive number tells how many digits you want, while a negative number tells how many places to the left of the decimals you want to round.

Here are some examples:

  • =ROUND(23,4445;2) = 23,44
  • =ROUND(23,4445;0) = 23,00
  • =ROUND(23,4445;-1) = 20,00
  • =ROUND(23,4445;3) = 23,445
  • =ROUND(1223,4445;-3) = 1000,00
  • =ROUND(1223,4445;-4) = 0,00

OK, now we'll try another usefull one... Remember the use of =RAND() when creating random ages?

Now we'll turn it up a notch...

Do you also remember how this formula created a decimal number?
  • =RAND()*(100-18)+18

Normally that's not what we use when referring to the age of a person, so now we'll simply round it:
  • =ROUND(RAND()*(100-18)+18;0) will create a random age between 18 and 100, rounded to whole years!

Please notice that when we insert a formula inside a formula, there's no need for [=] in front of the formula residing inside, in this case [RAND].

If you follow the color coding above, you'll se that the entire random formula has become the first argument of the =ROUND() formula.