ROUNDING RESULT OF CALCULATION UP OR DOWN

[dartanion]

Is there a formula that will do the following;
The result of a formula can be a whole number plus two decimal places, and I
want a row of data to do the following - If the answer is between .00 and .49
then round down, and if it is between .50 and .99 round up. I can do each
individually, but not both automatically in the same formula.

 

[Niek Otten]

=ROUND(A1,0)

Or am I missing something?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Is there a formula that will do the following;
| The result of a formula can be a whole number plus two decimal places, and I
| want a row of data to do the following - If the answer is between .00 and .49
| then round down, and if it is between .50 and .99 round up. I can do each
| individually, but not both automatically in the same formula.

 

[David Biddulph]

Why not try typing the word ROUND into Excel help, and see what it tells
you?

 

[Mike H]

If your logic is followed precisley then 0.49 becomes zero and I assume you
don't want this so up to 0.99 rounds to 1 and thereafter follows your rules

=IF(A1>0,MAX(mround(A1,1),1),"")

If that assumption is incorrect simply use

=mround(a1,1)

Mike

 

[dartanion]

Hi David,

In my question, I gave two possibilities, above and below .5
ROUND gives me one or the other and I am greedy and want excel to look at
the answer and decide which way to go up or down.
If I have missed something in EXCEL help on the subject of ROUND, please can
you point me in the right direction.

Thanks

 

[David Biddulph]

Perhaps I've misunderstood your question, but please go back and read Excel
help again.

ROUNDDOWN rounds down.
ROUNDUP rounds up.
ROUND rounds to the nearest.

The help topic "Round a number" gives detailed hints for a variety of
different rounding methods (including rounding to the nearest). For each
function in Excel help there is usually a "See also" link for similar and
related topics, and they usually give examples of the function in operation.

 

[dartanion]

Thanks David and others.

I overcame my problem, by adding an extra row, which used the ROUND function
with a reference to the result in the cells where I was having problems
displaying the result that I expected to see.

Regards

Dartanion

 

[David Biddulph]

What is the advantage of MROUND(A1,1) compared with ROUND(A1,0), Mike?

 

[Jerry W. Lewis]

Prior to 2007, MROUND required the Analysis ToolPak (a disadvantage), while
ROUND has always been a native Excel function.

In all versions, ROUND(x,0) and MROUND(x,1) produce similar, but not
identical results. Both approaches were probably intended to deal with the
fact that the intended rounding rule does not translate exactly into binary.
Both approaches can unexpectedly round incorrectly.

ROUND appears to round based on Excel’s 15-digit decimal display of the
value to be rounded; and so is impacted by Excel display bugs such as
http://support.microsoft.com/kb/161234
which affects hundreds of thousands of decimal fractions, not just the one
mentioned in the knowledge base article. For instance, =ROUND(10^14+0.5,0)
incorrectly returns the value of 100000000000000 to match Excel’s incorrect
15 digit display of the original number, but =ROUND(0.499999999999995,0)
correctly returns 0.

MROUND appears round directly from the binary value, but uses too large a
fuzz factor for identifying the branch condition of 5 in the first figure to
be rounded away. Thus =MROUND(10^14+0.5,1) correctly returns 100000000000001
but =MROUND(0.499999999999995,1) incorrectly returns 1 instead of 0.

Jerry

 

[James Silverton]

Jerry wrote on Wed, 23 Jan 2008 20:27:37 -0800:

JWL> In all versions, ROUND(x,0) and MROUND(x,1) produce
JWL> similar, but not identical results. Both approaches were
JWL> probably intended to deal with the fact that the intended
JWL> rounding rule does not translate exactly into binary.
JWL> Both approaches can unexpectedly round incorrectly.

JWL> ROUND appears to round based on Excel’s 15-digit decimal
JWL> display of the value to be rounded; and so is impacted by
JWL> Excel display bugs such as
JWL> http://support.microsoft.com/kb/161234 which affects
hundreds of thousands of decimal
JWL> fractions, not just the one mentioned in the knowledge
JWL> base article. For instance, =ROUND(10^14+0.5,0)
JWL> incorrectly returns the value of 100000000000000 to match
JWL> Excel’s incorrect 15 digit display of the original number,
JWL> but =ROUND(0.499999999999995,0) correctly returns 0.

JWL> MROUND appears round directly from the binary value, but
JWL> uses too large a fuzz factor for identifying the branch
JWL> condition of 5 in the first figure to be rounded away.
JWL> Thus =MROUND(10^14+0.5,1) correctly returns
JWL> 100000000000001 but =MROUND(0.499999999999995,1)
JWL> incorrectly returns 1 instead of 0.

This is all very interesting but ultimately what does it matter
if ROUND sometimes goes in the wromg direction at the limits of
precision? It is hard for me to imagine a real case where so
many figures are necessary but I am ready to be instructed.

James Silverton
Potomac, Maryland

E-mail, with obvious alterations:
not.jim.silverton.at.verizon.not

 

[Bernd P]

Hello,

If you only want to SEE the rounded results you can apply the number
format "#,###" to your cells.

Regards,
Bernd

 

[joeu2004]

 Jerry  wrote  on Wed, 23 Jan 2008 20:27:37 -0800:
JWL> For instance, =ROUND(10^14+0.5,0)
 JWL> incorrectly returns the value of 100000000000000

This is all very interesting but ultimately what does it matter
if  ROUND sometimes goes in the wromg direction at the limits of
precision?  It is hard for me to imagine a real case where so
many figures are necessary but I am ready to be instructed.

It isn't so much that anyone needs this much precision, but that these
flaws cause problems in formulas that involve large numbers. The
explanation is more precise than the solution requires.

I presented this anomaly when I was having a problem with INT().
Actually, the problem is with MOD(x,y), which is well-know to an error
with large numbers. I wanted to use x - y*INT(x/y) work around the
problem. When that was misbehaving, I did a binary search and
discovered the 10^14 boundary and speculated the relationship to the
number of significant digits that Excel will display.

The problem that this caused for me is: MOD(x,y), when implemented
using INT(), was resulting in a negative number. The root cause of
the problem is that INT(10^14+0.5) results in 100000000000001 instead
of 100000000000000, as it should. In other words, for values at and
above 10^14, INT() rounds insteads of truncating. (It is interesting
that ROUND() does the opposite!) There is no reason why INT() should
fail at this boundary, at least none based on IEEE binary computer
arithmetic. In fact, VBA Int() works just fine, even when the
expression is evaluated using 64-bit floating-point for intermediate
results.

 

[James Silverton]

joeu2004 wrote on Thu, 24 Jan 2008 10:11:40 -0800 (PST):

j> On Jan 24, 5:28 am, "James Silverton"
??>> Jerry wrote on Wed, 23 Jan 2008 20:27:37 -0800:
JWL>>> For instance, =ROUND(10^14+0.5,0)
JWL>>> incorrectly returns the value of 100000000000000
??>>
??>> This is all very interesting but ultimately what does it
??>> matter if ROUND sometimes goes in the wromg direction at
??>> the limits of precision? It is hard for me to imagine a
??>> real case where so many figures are necessary but I am
??>> ready to be instructed.

j> It isn't so much that anyone needs this much precision, but
j> that these flaws cause problems in formulas that involve
j> large numbers. The explanation is more precise than the
j> solution requires.

j> I presented this anomaly when I was having a problem with
j> INT(). Actually, the problem is with MOD(x,y), which is
j> well-know to an error with large numbers. I wanted to use x
j> - y*INT(x/y) work around the problem. When that was
j> misbehaving, I did a binary search and discovered the 10^14
j> boundary and speculated the relationship to the number of
j> significant digits that Excel will display.

Again without wishing to start a fight, can you give me an
example of a real calculation where the rounding error makes a
difference to a conclusion? I probably wouldn't use Excel if
there were such a case since it's not that hard to program in
double precision or the like.

James Silverton
Potomac, Maryland

E-mail, with obvious alterations:
not.jim.silverton.at.verizon.not

 

[Niek Otten]

I had an issue once in a Life insurance calculation. In one of the calculation rules we had to round to a whole year. Each year
(of duration) had its own cost factor.
It turned out there were problems because one algorithm established the unrounded duration as 5.49999999999999999 years and the
other one as 5.5.
A whole year of difference after rounding, with a large impact on customer's revenues.

Just an example.

I keep repeating we need (optional) decimal arithmetic in Excel, like scaled integers in VB. There is no way the misinterpretation
I explained above could have come up in decimal arithmetic. Because we're all used to it and are more familiar with the pitfalls,
like repeating fractions.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel



| joeu2004 wrote on Thu, 24 Jan 2008 10:11:40 -0800 (PST):
|
| j> On Jan 24, 5:28 am, "James Silverton"
| ??>> Jerry wrote on Wed, 23 Jan 2008 20:27:37 -0800:
| JWL>>> For instance, =ROUND(10^14+0.5,0)
| JWL>>> incorrectly returns the value of 100000000000000
| ??>>
| ??>> This is all very interesting but ultimately what does it
| ??>> matter if ROUND sometimes goes in the wromg direction at
| ??>> the limits of precision? It is hard for me to imagine a
| ??>> real case where so many figures are necessary but I am
| ??>> ready to be instructed.
|
| j> It isn't so much that anyone needs this much precision, but
| j> that these flaws cause problems in formulas that involve
| j> large numbers. The explanation is more precise than the
| j> solution requires.
|
| j> I presented this anomaly when I was having a problem with
| j> INT(). Actually, the problem is with MOD(x,y), which is
| j> well-know to an error with large numbers. I wanted to use x
| j> - y*INT(x/y) work around the problem. When that was
| j> misbehaving, I did a binary search and discovered the 10^14
| j> boundary and speculated the relationship to the number of
| j> significant digits that Excel will display.
|
| Again without wishing to start a fight, can you give me an
| example of a real calculation where the rounding error makes a
| difference to a conclusion? I probably wouldn't use Excel if
| there were such a case since it's not that hard to program in
| double precision or the like.
|
| James Silverton
| Potomac, Maryland
|
| E-mail, with obvious alterations:
| not.jim.silverton.at.verizon.not
|

 

[James Silverton]

Niek wrote on Thu, 24 Jan 2008 20:03:50 +0100:


NO>
had an issue once in a Life insurance calculation. In one of
the calculation rules we had to round to a whole year. Each year
(of duration) had its own cost factor.
It turned out there were problems because one algorithm
established the unrounded duration as 5.49999999999999999 years
and the
other one as 5.5.
A whole year of difference after rounding, with a large impact
on customer's revenues.
NO>
NO> Just an example.

NO> I keep repeating we need (optional) decimal arithmetic in
NO> Excel, like scaled integers in VB. There is no way the
NO> misinterpretation I explained above could have come up in
NO> decimal arithmetic. Because we're all used to it and are
NO> more familiar with the pitfalls, like repeating fractions.

Now that example is interesting! Can such troubles be avoided by
adding a trivial constant like 0.5 x10^-13 where a round is
expected? I know there are problems in scientific work where two
large numbers need to be subtracted and matrices become singular
but I would not have expected it in Excel used for its normal
purposes.

James Silverton
Potomac, Maryland

E-mail, with obvious alterations:
not.jim.silverton.at.verizon.not

 

[Jerry W. Lewis]

Last September, you participated in a thread where in the original release of
Excel 2007, 2^16-1-epsilon displayed as 100000 instead of 65535 and
2^16-epsilon displayed as 100001 instead of 65536, even though the underlying
values were correct. If I am correct about ROUND rounding based on the Excel
display, then ROUND would drastically alter the underlying value in this case
(does anyone still have an unpatched install of 2007 to test this?). While
the Oct 2006 patch fixed the display issues like
http://support.microsoft.com/kb/161234
that I tested at the time, the incorrect display of =2^14+0.5 shows that
some instances of the display bug remain, and it remains to be seen whether
all of the remaining display issues can be safely ignored or not.

Jerry