Round Function Philosophy

[herrdue]

I teach Excel classes at a community college, and have a very good
understanding of the Round fuction, except for one statement I heard
another instructor make one time which has left me curious. I heard
him say that you should always use the Round fuction in any formula
where any multiplication or division is taking place. I think that's
quite a broad statement, and if true, the spreadsheet world would be
covered with Round functions. But it does beg the question...is there
something to what he said? Is there a broad rule about when a Round
function needs to be employed, and perhaps when it should NOT be
employed?

Curious as to what you folks have to say. Thanks for your help in
advance.

 

[Vasant Nanavati]

Totally incorrect. Whether to use it or not depends on your requirements;
for example, if you are looking for an integral answer.
________________________________________________________________________

 

[Rick Rothstein \(MVP - VB\)]

I teach Excel classes at a community college, and have a very good

understanding of the Round fuction, except for one statement I heard
another instructor make one time which has left me curious. I heard
him say that you should always use the Round fuction in any formula
where any multiplication or division is taking place. I think that's
quite a broad statement, and if true, the spreadsheet world would be
covered with Round functions. But it does beg the question...is there
something to what he said? Is there a broad rule about when a Round
function needs to be employed, and perhaps when it should NOT be
employed?

My question back to this instructor would have been... Round to what number
of decimal places? For example, how many decimal places should I round PI to
before calculating the area of a circle? The only time I can think off the
top of my head of where rounding might be something to consider is in
laboratory experiments where measurement precision, and its effect on
calculated results (error propagation), could be a consideration.

Rick

 

[Peo Sjoblom]

If you use Excel in accounting for reconciliation of bank statements etc you
need to round or else you will be a penny off eventually.

 

[herrdue]

If you use Excel in accounting for reconciliation of bank statements etc you
need to round or else you will be a penny off eventually.

--
Regards,

Peo Sjoblom

message





- Show quoted text -

Rick, so does that mean every formula in your entire accounting
spreadsheet needs to use the Round Function...that's the take I get
from this other instructor.

 

[Meebers]

Conversly, rounding can make you a penny off....eventually.

 

[Peo Sjoblom]

I am not Rick but it sounds as though you directed the question to me

I don't using rounding everywhere

John McGimpsey and Chip Pearson have info on their sites

http://www.mcgimpsey.com/excel/pennyoff.html

http://www.cpearson.com/excel/rounding.htm

 

[Peo Sjoblom]

I know, that is why I do it very carefully

 

[Guest]

In such acct. cases, rounding is generally an option needed to please the
calculator's eye of who shall check and accept it....

*i'm concerned about the word "to be employed". ***
Philosophically relevant among human and computer sense of performance...to
acclaim a considerable precision results...unless newly accepted
experimentations arises that replaces the universally known tolerances on
every feld of study and implementation - separately derived from human and
computer results.

regards,
driller

 

[Rick Rothstein \(MVP - VB\)]

If you use Excel in accounting for reconciliation of bank statements

etc you need to round or else you will be a penny off eventually.

Accounting... that half (three-quarters? nine-tenths? more?<g>) of the Excel
world is unknown to me... I was a Civil Engineer/Programmer/CADD Developer
for some 31+ years... my only use of Excel was from the "scientific" end.

Rick

 

[Chip Pearson]

you should always use the Round fuction in any formula

where any multiplication or division is taking place.

As a general rule, he is completely wrong.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Guest]

I tend to think in terms of science and engineering applications, where I
cannot think of a good reason to ever round intermediate results after
multiplication or division.

For financial calculations, you might want to round to the smallest unit of
money (to the penny for U.S. currency), but even then, you still might have
to track the remaining portion if you are accumulating compond interest.

For numeric reasons, if you are only adding and subtracting numbers with a
fixed number of decimal places, then you might want to round after each
subtraction to the smallest decimal place ever used. This would insure that
calculations like =4.3-4.2-0.1 reconcile as expected, instead of accurately
reflecting the results of the binary approximations to the original numbers
that are used in computers.

Jerry

 

[Pete_UK]

You will know then, Rick, that if you multiply a number to 2dp with a
similar number, then you could end up with a number to 4dp (base-10
arithmetic). Note also that Excel cannot always represent fractional
values in binary to the same precision, and so errors can easily
propagate, particularly if you then use the result in some comparison
operation.

I don't agree with the suggestion that you should ALWAYS use ROUND,
but there are many situations when it should be used and not just for
monetary calculations.

Pete

 

[joeu2004]

I heard [another instructor]
him say that you should always use the Round fuction in any formula
where any multiplication or division is taking place. I think that's
quite a broad statement [...]. But it does beg the question...is there
something to what he said?

First, let me say at the outset that I, too, believe that is an overly
broad statement to make, and I agree with all responses that suggest
that the use of rounding or not depends on the context.

(By the way, let's not forget that in addition to ROUND(), there is
ROUNDUP() and ROUNDDOWN(). It makes sense to force rounding in one
direction or the other in some contexts, including some financial
contexts. But I digress ....)

However, I might be able to offer some perspective for the
instructor's comment.

When my son was taking science in high school, I flipped through his
text. One of the first chapters dealt with the accuracy of numbers
arithmetically derived from measurements. I must admit that I am a
bit fuzzy on the details, but as I recall the text said that when
combining numbers, the result should be expressed with the same number
of decimal places as the least accurate operand. For example, if
something is measured to tenths (10.1) and something else is measured
to hundredths (9.01), the sum should be expressed to tenths (19.1, not
19.11).

I remember frowning a great deal when I read that. But I checked
other sources at the time and found similar rules.

"Go figure!" ;-)

 

[Harlan Grove]

combining numbers, the result should be expressed with the same number
of decimal places as the least accurate operand. For example, if
something is measured to tenths (10.1) and something else is measured
to hundredths (9.01), the sum should be expressed to tenths (19.1, not
19.11).

....

Note that that's ADDITION rather than multiplication. Addition needs care to
avoid rounding/truncation or spurious precision errors. Multiplication
usually doesn't, e.g., astronomical calculations involving pi and the
gravitational constant may use as many digits as you'd care to throw at
them, and you don't need to round that intermediate result.

Rounding is generally needed in interest rate calculations in which the end
result should be a currency amount in whole currency units (in US context be
that multiples of whole dollars, cents or even mills).

 

[joeu2004]

Note that that's ADDITION rather than multiplication.

No. When I said "combining", I was referring to all kinds of
arithmetic, including but not limited to addition, subtraction,
multiplication, division and exponential operations.

Addition needs care to avoid rounding/truncation or spurious precision
errors. Multiplication usually doesn't

I disagree. Multiplication of fractional digits is more likely to
give the false impression of greater precision. In the example, 10.1
* 9.01 ostensibly results in a number with 3 decimal places (91.001).
The science text that I referred to would have us round that result at
least to 2 decimal places, but probably to 1 decimal places.

e.g., astronomical calculations involving pi and the
gravitational constant may use as many digits as you'd care to throw at
them, and you don't need to round that intermediate result.

That is different: those constants are derived algorithmically; they
are not measured to those infinitessimal precisions. At least that is
true for pi. Actually, I believe the gravitational constant is
limited to the precision of its measurement; but that is indeed a
large number of decimal places.

In any case, the point is: the results of calculations involving
those highly precise constants can take advantage of their high
precision only to the extent of the precision of other measured values
in the calculations, at least according to the science text. If you
measure the diameter only to an inch, the accuracy of the
circumference can be computed only to the inch, even though you might
know pi to much greater precision. Of course, if the diameter happens
to be an integral number of inches, but you measure it to hundredths
of an inch (e.g 5.00 inches), the circumference can be computed to
hundredths of an inch, as long as you know pi to the degree. (I would
hope so!)

I am just parroting what my son's science text says about
computational accuracy. (And by the way, I remember now that it was a
college text, not a high school text.) Personally, I do not subscribe
to those rules, at least not so dogmatically. "Don't shoot the
messenger".

 

[Guest]

Your argument justifies rounding final reportable values to reflect the
uncertainty of the inputs. It does not justify rounding intermediate
calculations in that process. I would interpret "... always use the Round
fuction in any formula ..." to mean rounding intermediates as well, which is
almost always a bad idea.

Counting sig-figs as a rule of thumb is a crude approximation to an actual
error analysis. Sometimes you should be reporting more or less sig-figs than
that rule of thumb would suggest.

Jerry

 

[Harlan Grove]

I disagree. Multiplication of fractional digits is more likely to
give the false impression of greater precision. In the example, 10.1
* 9.01 ostensibly results in a number with 3 decimal places (91.001).
The science text that I referred to would have us round that result at
least to 2 decimal places, but probably to 1 decimal places.

....

With multiplication you can defer rounding until the final result. And you'd
better defer rounding when dealing with compound interest. Addition, on the
other hand, begins to suffer as soon as you 'combine' numbers at different
orders of magnitude.