Free Excel Training Online Lesson Three: Getting the Right Result with Rounding

2KO Africa welcomes you back to our free Excel training online. Now that we have had a look at basic number formatting, let's look at rounding numbers.

 

Lesson 3

Getting the Right Result with Rounding
Excel is immensely accurate when it comes to calculation. Numbers will be calculated to the umpteenth decimal place, unless the decimal is recurring. The number in a cell is always calculated based on the actual value in the cell, rather than the appearance of the number. When numbers are formatted, they are presented in a way that makes it easy to understand or read the number. But this can easily lead to an incorrect or unexpected result.

 

The Problem with Fractions

Whenever division is present in calculations, there is a very real possibility of fractions. Fractions are numbers with values in decimal points. In other words, 3 divided by 2 gives a result of 1.5


However, other division formulas can result in many more decimals.


The formula =687 / 29 gives a result of 23.68966


Now assuming that we are dealing with money, there is no such thing as 9/10ths of a cent. Therefore, most people will format the result to two decimal places, and the number will display as 23.69


The problem with this method (formatting) is that Excel does not calculate 23.69. No, it calculates 23.68966


Now where there are enough of these values, the actual result of cash in hand will reflect one thing, but Excel will calculate another.


Enter Rounding!!!!!


Excel provides a way to ensure that numbers which have fractions can be converted to numbers with specific decimal places, and will calculate accordingly.


Let's have a look at what this means:
 

  • If a number in a cell has a value of 17.253, and that number is copied 20 times, and then all cells are totalled, the result will be 345.06 (=17.253 * 20)

  • The numbers would normally be formatted to 2 decimal places so that instead of displaying 17.253, they would show as 17.25

  • However, the formula =17.25*20 leads to a result of 345.00. This is already a difference of 6 cents. Why is this?

 

The answer to why is simple. Excel calculates the .3 of a cent for every value (of the 17.253)


In fact, if customers were paying in physical cash, then the amount collected from 20 customers would be 345.00. No-one actually pays 25.3 cents.


Therefore, Excel ought to calculate the amount of 17.25, and not 17.253. The issue is that although 17.253 has been entered into all twenty cells, the numbers have been formatted (see lesson 2) to two decimal places, to have a sensible, consistent appearance.


How to Force Excel to Calculate to Two Decimals

In order to force Excel to calculate correctly, we need to ensure that the value in the cells does not contain the fraction of a cent, in the above case .3 of a cent.
 

To facilitate this, Excel has a function - the Round function.
 

Functions in excel, are pre-built formulas that perform a specific calculation. The Round function is used to round off numbers to a specific number of decimals. The rounded values become the stored values, and thus the calculated the values.
 

The format of the Round() function
 

The function works as follows:
 

=round(value, number of decimals)
 

There are two arguments (the part between brackets) to the function:

 

  • The value

  • The number of decimal places
     

Here are some examples of the use of round() function

 

Table 6

 

Numbers can be rounded directly, or they can be rounded using the cell reference.
=round(17.253,2) results in a value of 17.25


In Table 6 above, the same formula could be applied as follows:
=round(A1,2) which also results in a value of 17.25


Recap

Rounding should be used whenever:

 

  • There is division in play which can possibly result in fractions / values with more than 2 decimals

  • When percentages are used

  • When you are using exchange rates
     

Rounding with other functions


Rounding can be applied to all numbers, even those numbers which are an aggregate of other functions.
Example:
=sum(A5:G29)
 

The above function totals the values in cells A5 all the way to G29. Assuming however that some of those numbers had decimals, one could round the total as follows:
=round(sum(A5:G29),2)
 

Remember the two parts / arguments to the Round function?


The value is sum(A5:G29)
The decimal places for rounding is 2
The number of places for rounding
 

Rounding works to x number of decimal places, in most circumstances, But it can also round to tens, or units, or hundreds, or thousands.


=round(123.45,1) would result in a value of 123.5
=round(123.45,0) would result in a value of 123
=round(123.45,-1) would result in a value of 120
=round(123.45,-2) would result in a value of 100
 

Practise on your own


Create the following on a blank Excel sheet


 

 

Use =round() formulas in cells C2 up to C7

 

 

Check your answers:

If you would like to check your answers, click here     (Note the file will open in Excel)


To start lesson 4, click here


To go back to the index of free online excel lessons from 2KO Africa in Cape Town,
click here