The Round Function
In many situations, the result of a formula may contain
many decimal places. Formatting these values to a set number
of decimal places makes the worksheet LOOK
better, but does not change the true value. When that value is used
in another formula, all the decimal places are remembered and used in the
calculation.
In this example, I have a formula in cell E1 which
divides A1 by C1. The result of the formula in cell E1 is actually
3.875, but because the cell was formatted to fixed with 0 decimal places,
it APPEARS to contain the value 4. When the value of E1 is multiplied
by 5, the result in cell E3 APPEARS to be 19. The true value
of cell E3, however, is 19.375. Do you see how this can present problems?
To avoid these problems, we can use the Round function.
=Round(formula,number of
decimal places)
In this example, cell E1 contains the formula =Round(A1/C1,0).
The division is calculated, then the result is rounded to 0 decimal places.
In cell E3, the formula is still =E1*E2
but the values being multiplied now are 4 and 5.
There are four situations when a formula should be
rounded. If the formula contains:
-
Division
-
Multiplication by a decimal or percentages
-
Exponentiation
-
A function such as =AVERAGE which could cause the result
to contain decimal places.
The EXCEPTION to
the rules above is: If the result of the formula will not be used
in any other formula, it is not necessary to round the formula. For
example, if I am averaging grades, I will round the average of the assignments
and round the test average because these averages will be used in computing
the final grade. But the final average does not need to be rounded
because I will not use it in another formula.
Below is an example of using the Round function with
another function:
=Round(AVERAGE(B5:B10),2)
Note: Round is always at the beginning of the formula
and the comma and number of decimal places to round to are always at the
end followed by the closing parenthesis. the formula or function
is between the opening parenthesis and the comma.
Quick Quiz:
Send me an e-mail message with Round Quick Check
as the subject. In the body of the message, write the following formulas:
-
Multiply cells D8 and B20, then add cell D10.
Round the result to 2 decimal places.
-
Average cells D10 through D20 and round the result to
0 decimal places.
|
|
