Is it possible to construct function that rounds down if 5 is met

For example:
1) 2.455 = 2.45
2) 2.454 = 2.45
3) 2.456 = 2.46

ROUND function (num_digits = 2) does following
1) 2.455 = 2.46
2) 2.454 = 2.45

ROUNDUP function (num_digits = 2) does following
1) 2.455 = 2.46
2) 2.454 = 2.46

Mikus wrote...
Is it possible to construct function that rounds down if 5 is met

For example:
1) 2.455 = 2.45
2) 2.454 = 2.45
3) 2.456 = 2.46
....

Simplest way would be

=ROUND(x-0.0009,2)

On Mon, 29 Aug 2005 08:50:10 -0700, "Mikus"
wrote:

Is it possible to construct function that rounds down if 5 is met

For example:
1) 2.455 = 2.45
2) 2.454 = 2.45
3) 2.456 = 2.46

ROUND function (num_digits = 2) does following
1) 2.455 = 2.46
2) 2.454 = 2.45

ROUNDUP function (num_digits = 2) does following
1) 2.455 = 2.46
2) 2.454 = 2.46

I believe this formula will do what you describe:

=IF(MOD(INT(A1*10^(NumDigits+1)),10^(NumDigits-1))<=5,
ROUNDDOWN(A1,NumDigits),ROUND(A1,NumDigits))


--ron

On 29 Aug 2005 09:33:26 -0700, "Harlan Grove" wrote:

Mikus wrote...
Is it possible to construct function that rounds down if 5 is met

For example:
1) 2.455 = 2.45
2) 2.454 = 2.45
3) 2.456 = 2.46
...

Simplest way would be

=ROUND(x-0.0009,2)

Given OP's specifications, how should

2.4559

round?


--ron

Ron Rosenfeld wrote...
On 29 Aug 2005 09:33:26 -0700, "Harlan Grove" wrote:
Mikus wrote...
Is it possible to construct function that rounds down if 5 is met

For example:
1) 2.455 = 2.45
2) 2.454 = 2.45
3) 2.456 = 2.46
...

Simplest way would be

=ROUND(x-0.0009,2)

Given OP's specifications, how should

2.4559

round?

Either we read nothing more into the OP's specs, in which case they're
silent about this, so it's up to the OP to decide, or we make an
intelligent extension to the OP's specs. The problem is rounding
numbers to 2 decimal places when the 3rd decimal place is 5 and,
presumably, all subsequent decimal places are zero (that's the
intelligent extension). If there were additional decimal places, then
the 4th decimal place may explicitly be something other than zero. If
so, use

=ROUND(ROUND(x,3)-0.0009,2)

The inner ROUND call would return 2.456 given 2.4559, and the outer
ROUND call would return 2.46, which I'd guess is what the OP would
want.

You'd have done better to have used 2.4558.

On 29 Aug 2005 11:07:53 -0700, "Harlan Grove" wrote:

Ron Rosenfeld wrote...
On 29 Aug 2005 09:33:26 -0700, "Harlan Grove" wrote:
Mikus wrote...
Is it possible to construct function that rounds down if 5 is met

For example:
1) 2.455 = 2.45
2) 2.454 = 2.45
3) 2.456 = 2.46
...

Simplest way would be

=ROUND(x-0.0009,2)

Given OP's specifications, how should

2.4559

round?

Either we read nothing more into the OP's specs, in which case they're
silent about this, so it's up to the OP to decide, or we make an
intelligent extension to the OP's specs. The problem is rounding
numbers to 2 decimal places when the 3rd decimal place is 5 and,
presumably, all subsequent decimal places are zero (that's the
intelligent extension). If there were additional decimal places, then
the 4th decimal place may explicitly be something other than zero. If
so, use

=ROUND(ROUND(x,3)-0.0009,2)

The inner ROUND call would return 2.456 given 2.4559, and the outer
ROUND call would return 2.46, which I'd guess is what the OP would
want.

You'd have done better to have used 2.4558.

Well, I didn't know. We made different assumptions, and came up with different
results.

I would think it equally "intelligent" (or not) to make the assumption that
subsequent decimal places are undefined, and hence could be anything. But
YMMV.

It might be important if the numbers are a result of a formula, rather than
direct entry, though.


--ron