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.

=ROUND(A1,0)

Or am I missing something?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"dartanion" wrote in message ...
| 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.

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(A10,MAX(mround(A1,1),1),"")

If that assumption is incorrect simply use

=mround(a1,1)

Mike

"dartanion" wrote:

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.

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

"Mike H" wrote in message
...
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(A10,MAX(mround(A1,1),1),"")

If that assumption is incorrect simply use

=mround(a1,1)

Mike

"dartanion" wrote:

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.

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 Excels 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 Excels 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

"David Biddulph" wrote:

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

"Mike H" wrote in message
...
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(A10,MAX(mround(A1,1),1),"")

If that assumption is incorrect simply use

=mround(a1,1)

Mike

"dartanion" wrote:

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.

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 Excels 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 Excels 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

On Jan 24, 5:28*am, "James Silverton"
wrote:
*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.

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

"James Silverton" wrote:

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.

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

"dartanion" wrote in message
...
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.

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" wrote:

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

"dartanion" wrote in message
...
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.

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.
--
David Biddulph

"dartanion" wrote in message
...
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" wrote:

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

"dartanion" wrote in message
...
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.

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" wrote:

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.
--
David Biddulph

"dartanion" wrote in message
...
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" wrote:

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

"dartanion" wrote in message
...
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.

Hello,

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

Regards,
Bernd