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.
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.
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
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()
The function works as
=round(value, number of
There are two arguments (the part between brackets) to the function:
The number of decimal places
Here are some examples of the use of round() function
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
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.
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:
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,
To go back to the index of free online excel lessons from 2KO Africa in Cape Town, click here
Copyright 2019, 2KO International
Part of the 2KO Africa Group.