Decimals, how?

[LogiMAX]

I need to round up decimals if they are greater than .9 and down otherwise...

ex..
33.90 should be rounded to 34
33.98 should be rounded to 34
33.20 should be rounded to 33
33.89 should be rounded to 33

How do I make this happen? Thanks in advance...

 

[Jacob Skaria]

Try one of this with your value in cell A1

=CEILING(A1-0.899,1)

OR

=IF(MOD(A1,1)<0.899,ROUNDDOWN(A1,),ROUNDUP(A1,))


If this post helps click Yes

 

[Ron Rosenfeld]

I need to round up decimals if they are greater than .9 and down otherwise...

ex..
33.90 should be rounded to 34
33.98 should be rounded to 34
33.20 should be rounded to 33
33.89 should be rounded to 33

How do I make this happen? Thanks in advance...

From your examples, I am assuming you want to round up if your values are EQUAL
TO or greater than 0.9



=ROUND(A1-0.4,0)
--ron

 

[Ron Rosenfeld]

Try one of this with your value in cell A1

=CEILING(A1-0.899,1)

OR

=IF(MOD(A1,1)<0.899,ROUNDDOWN(A1,),ROUNDUP(A1,))

You should note that your recommendation only works with input up to 2 decimals
of precision. For example 33.899 would round to 34 using your method.
--ron

 

[JoeU2004]

=ROUND(A19-0.4,0)

For a numerical solution, I would be inclined to do:

=ROUND(ROUND(A19,2)-0.4,0)

to minimize surprises in the general case.

Ron and David's formula might suffice if the OP's numbers are all constants.
And it might suffice even if they are the result of formulas, as long as the
order of magnitude is relatively small.

But consider this contrived example:

A19: =3333.9 - 11*2^-41

That is largest difference (about 5E-12) that displays as 3333.90...0 to 11
decimal places (15 significant digits). We have seen such small numerical
aberrations arising from arithmetic formulas.

ROUND(A19 - 0.4, 0) results in 3333, whereas ROUND(ROUND(A19,2) - 0.4, 0)
results in 3334 as desired.

I was unable to find a simple set of arithmetic operations that results in
the contrived value in A19. But that should not be misconstrued to mean
that there is none. It simply means that I got tired of trying .

For example, 113333.9 - 110000 comes very close. It differs from the
contrived value by only 1 bit (the 2nd least significant bit). But
113333.9 - 110000 displays as 3333.89...9 when formatted to 11 decimal
places. So arguably, Ron and David's formula would be sufficient, by
coincidence.

But I think ROUND(ROUND(A19,2) - 0.4, 0) would work better even in this last
example because the OP is likely to display the result with 10 dp or less
(in fact, probably 2 dp), in which case it __appears__ to be 3333.90...0,
and the OP might expect it to round to 3334.


----- original message -----

 

[Ron Rosenfeld]

For a numerical solution, I would be inclined to do:

=ROUND(ROUND(A19,2)-0.4,0)

to minimize surprises in the general case.

Ron and David's formula might suffice if the OP's numbers are all constants.
And it might suffice even if they are the result of formulas, as long as the
order of magnitude is relatively small.

Interesting discussion pointing out, once again, the difficulties of dealing
with Excel's precision and binary data storage.

Given the OP's *posted level* of precision, Jacob's formula would also work.

Mine and David's are OK with a greater range of input values; and Joeu2004's is
even better.
--ron