Round function that rounds down if 5

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
Click to expand...
....

Simplest way would be

=ROUND(x-0.0009,2)
 
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
Click to expand...

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 wrote...
Given OP's specifications, how should

2.4559

round?
Click to expand...

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 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.
Click to expand...

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
 
Back
Top