Round function that rounds down if 5

[Guest]

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

 

[Harlan Grove]

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)

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

Mikus wrote...
...

Simplest way would be

=ROUND(x-0.0009,2)

Given OP's specifications, how should

2.4559

round?


--ron

 

[Harlan Grove]

Ron Rosenfeld wrote...

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.

 

[Ron Rosenfeld]

Ron Rosenfeld wrote...

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