22. Excel data rounding
When data is compiled for a report or PowerPoint presentation, rounding summations in Excel is a frequent problem. It is often desirable, but difficult to achieve, that rounded totals exactly match the total of the rounded addends. For example, consider the following table:
When the values are rounded to integers using Excel’s FormatCell function, the following table results. Totals which appear to be “miscalculated” are in bold:
Similarly, when Excel’s standard rounding functions are used, totals of the
rounded values are calculated correctly but rounding errors accumulate and results
often deviate substantially from the actual totals of the original values. The following
table shows the result of =ROUND(x,0)
for the example above. Totals that deviate
from the original value by 1 or more are in bold:
Using thinkcell round, you can achieve consistently rounded totals with minimal “cheating”: While most values are rounded to the nearest integer, a few values are rounded in the opposite direction, thus maintaining correct calculations without accumulating rounding error. Since there are many possibilities to achieve correctly rounded totals by changing values, the software picks a solution that requires the minimum number of values changed and the minimum deviation from the precise values. For example, rounding down 10.5 to 10 is preferable over rounding down 3.7 to 3. The following table shows an optimal solution for the above example, with “cheated” values in bold:
To achieve this output in your own calculation, simply select the concerned range of Excel cells. Then, click the button on the Formulas tab and, if necessary, adjust the rounding precision using the toolbar’s dropdown box.
 22.1
 Using thinkcell round
 22.2
 Limitations of thinkcell round
 22.3
 Troubleshooting TCROUND formulas
22.1 Using thinkcell round
thinkcell round seamlessly integrates into Microsoft Excel, providing a set of functions that are similar to Excel’s standard rounding functions. You can easily apply these functions to your own data using the thinkcell round ribbon group in the Formulas tab.
22.1.1 Rounding parameters
Like the Excel functions, the thinkcell rounding functions take two parameters:
 x
 The value that is to be rounded. This can be a constant, a formula or a reference to another cell.
 n
 The rounding precision. The meaning of this parameter depends on the function you use. The parameters for the thinkcell functions are the same as for the equivalent Excel functions. Refer to the table below for examples.
thinkcell round can not only round to integer values, but to any multiple. For example, if you want to represent your data in 51015... steps, simply round to multiples of five. Using the dropdown box in the thinkcell round toolbar, simply type in or select the desired rounding precision. thinkcell round chooses the appropriate function and parameters for you. The following table provides some examples of rounding certain xvalues using the toolbar together with their specific nparameter.
x = n = 
100  50  2  1  0.01 

1.018  0  0  2  1  1.02 
17  0  0  18  17  17.00 
54.6  100  50  54  55  54.60 
1234.1234  1200  1250  1234  1234  1234.12 
8776.54321  8800  8800  8776  8777  8776.54 
If the values are not displayed the way you expect them to, verify that the Excel Cell Formatting is set to General and the columns are wide enough to display all decimal places.
Button  Formula  Description 

TCROUND(x, n)

Let thinkcell round decide to which of the two nearest multiples to round to minimize rounding error.  
TCROUNDUP(x, n)

Force rounding of x away from zero.  
TCROUNDDOWN(x, n)

Force rounding of x towards zero.  
TCROUNDNEAR(x, n)

Force rounding of x to the nearest multiple of the desired precision.  
Remove all thinkcell round functions from the selected cells.  
Select or type the desired rounding multiple.  
Highlight all cells which thinkcell decided to round to the farther of the two closest multiples instead of to the nearest.  
The turning wheel indicates that thinkcell round is busy. 
For optimal results with as little deviation from the underlying values as possible,
you should use TCROUND
wherever possible. Only use the more restrictive
functions TCROUNDDOWN
, TCROUNDUP
or TCROUNDNEAR
if you
must.
Attention: You should never use nondeterministic functions like RAND()
within
any of the TCROUND
formulas. If functions return a different value every
time they are evaluated, thinkcell round will make mistakes calculating
values.
22.1.2 Layout of the calculation
The rectangular layout of the example above is only for sake of demonstration.
You can use the TCROUND
functions to determine the display of arbitrary
summations spread across your Excel sheet. Excel’s 3D references to other sheets
and links to other files do also work.
22.1.3 Placement of TCROUND functions
Since TCROUND
functions are meant to control the output of a cell, they must
be the outermost function:
Bad: 
=TCROUND(A1, 1)+TCROUND(SUM(B1:E1), 1)

Good:  =TCROUND(A1+SUM(B1:E1), 1) 
Bad:  =3*TCROUNDDOWN(A1, 1) 
Good: 
=TCROUNDDOWN(3*A1, 1)

If you happen to enter something along the lines of the bad examples, thinkcell
round will notify you with the Excel error value #VALUE!
.
22.2 Limitations of thinkcell round
thinkcell round always finds a solution for arbitrary summations with
subtotals and totals. thinkcell round also provides sensible solutions for some
other calculations involving multiplication and numerical functions. However,
for mathematical reasons, the existence of a consistently rounded solution
cannot be guaranteed as soon as operators other than +,  and SUM
are
used.
22.2.1 Multiplication with a constant
In many cases, thinkcell round produces good results when constant
multiplication is involved, i.e., at most one of the coefficients is derived from the
result of another TCROUND
function. Consider the following example:
The precise calculation for cell C1 is 3×1.3+1.4=5.3. This result can be met by rounding up the value 1.4 to 2:
However, thinkcell round can only “cheat” by rounding up or rounding down.
Further deviation from the original values is not supported. Thus, for certain
combinations of input values, no consistently rounded solution can be found. In this
case, the function TCROUND
evaluates to the Excel error value #NUM!
. The
following example illustrates an unsolvable problem:
The precise calculation for cell C1 is 6×1.3+1.4=9.2. Rounding cells A1 and B1 would result in 6×1+2=8 or 6×2+1=13. The actual result 9.2 cannot be rounded to 8 or 13, and the output from thinkcell round looks like this:
Note: The Excel function AVERAGE
is interpreted by thinkcell round as a
combination of summation and constant multiplication. Also, a summation where the
same addend appears more than once is mathematically equivalent to a constant
multiplication, and the existence of a solution is not guaranteed.
22.2.2 General multiplication and other functions
As long as the TCROUND
functions are used for all relevant cells and
intermediate results are connected merely by +, , SUM
and AVERAGE
, the addends
as well as (intermediate) totals are integrated into a single rounding problem. In
these cases, thinkcell round will find a solution that provides consistency throughout
all cells involved, if such a solution exists.
Since TCROUND
is a normal Excel function, it can be combined with arbitrary
functions and operators. But when you use functions other than the ones mentioned
above to connect results from TCROUND
statements, thinkcell round cannot
integrate the components into one interconnected problem. Instead, the
components of the formula will be taken as distinct problems which will
be solved independently. The results will then be used as input to other
formulas.
In many cases, the output from thinkcell round will still be reasonable. There are
cases, however, where the use of operators other than +, , SUM
and AVERAGE
leads to rounded results which are far off from the result of the nonrounded
calculation. Consider the following example:
In this case, the precise calculation for cell C1 would be 8.7×1.7=14.79. Since cell A1 and cell B1 are connected by a multiplication, thinkcell round cannot integrate the formulas from these cells into a common problem. Instead, after detecting cell A1 as valid input, cell B1 is evaluated independently and the output is taken as a constant within the remaining problem. Since there are no further constraints, value 1.7 from cell B1 is rounded to the nearest integer, which is 2.
At this point, the “precise” calculation for cell C1 is 8.7×2=17.4. This is the problem that thinkcell round now tries to solve. There is a consistent solution which requires rounding up 17.4 to 18. The result looks like this:
Note that the rounded value in cell C1, which is 18, greatly differs from the original value 14.79.
22.3 Troubleshooting TCROUND formulas
There are two possible error results you may come across when using thinkcell
round: #VALUE!
and #NUM!
.
22.3.1 #VALUE!
The #VALUE!
error hints to syntactical problems, such as mistyped formulas or
bad parameters. Also, pay attention to use correct delimiters: For example, while in
international Excel the formula looks like this: =TCROUND(1.7, 0)
, in a
localized German version of Excel it must be written as =TCROUND(1,7; 0)
.
Another mistake specific to thinkcell round is the placement of the TCROUND
function call: You cannot use a TCROUND
function within another formula. Please
make sure that TCROUND
is the outermost function of the cell’s formula. (see
Placement of TCROUND functions)
22.3.2 #NUM!
The #NUM!
error results from numerical problems. When the output of a
TCROUND
function is #NUM!
, this means that the problem as stated by the given
set of formulas is mathematically unsolvable. (see Limitations of thinkcell round)
As long as the formulas enclosed by TCROUND
functions contain merely +, 
and SUM
, and all TCROUND
statements share the same precision (second
parameter), a solution is guaranteed to exist and will be found by thinkcell round.
However, in the following cases there is no guarantee that a consistently rounded
solution exists:
 Formulas involve other operations like multiplication or numerical functions. Also, summations where the same addend appears more than once are mathematically equivalent to a multiplication.

You use different precisions in the second parameter of the
TCROUND
function. 
You make frequent use of the specific functions
TCROUNDDOWN
,TCROUNDUP
andTCROUNDNEAR
.
You can try to restate the problem to get a consistent solution. Try the following:

Use a finer precision for some or all
TCROUND
statements. 
Do not use
TCROUND
with multiplication or numerical functions other than +,  andSUM
. 
Use the same precision (second parameter) for all
TCROUND
statements. 
Use
TCROUND
instead of the more specific functionsTCROUNDDOWN
,TCROUNDUP
andTCROUNDNEAR
wherever possible.