Rounding!!

[Matt]

I have numbers that vary in length. Such as 12.54336 and
12.121

I want to round these numbers based on two decimal places.
For example. I want take 12.56336 and create a rounding
forumula that states. IF .XX is > than .79 then round UP
to the whole number. If XX is < .80 then round up to the
next whole number.

Anyone that can solve this?

 

[Mike]

=ROUND(A1-0.3,0)

 

[Harlan Grove]

I want to round these numbers based on two decimal places.
For example. I want take 12.56336 and create a rounding
forumula that states. IF .XX is > than .79 then round UP
to the whole number. If XX is < .80 then round up to the
next whole number.

As you state the problem, both situations would lead to the number being
rounded up, but I'll assume you mean that you'd like x.80, x.81, etc rounded
up to the nearest whole number, and x.79, x.78, etc rounded down to the
previous whole number.

=SIGN(X)*INT(ABS(X)+(MOD(5*ABS(X),5)>=4))

if you have to handle positive and negative numbers,

=INT(A1+(MOD(5*A1,5)>=4))

if only positive number.

 

[matt]

Also,

Thanks to Harlan and Mike for your help as well. I don't
want to forget the thank you guys for replying and your
help!

Thanks everyone.

Matt

 

[Dave]

Hi, yes I tried it yesterday but didnt have too long since I was at
work. I am playing with it today though, thanks for taking the time to
explain it.

Dave

 

[Dave]

Harlan;

Not to keep beating this into the ground, but why did you put 5's in
there when you could get away with

INT(A1+(MOD(A1,1)>=.8))

 

[Harlan Grove]

Not to keep beating this into the ground, but why did you put 5's in
there when you could get away with

INT(A1+(MOD(A1,1)>=.8))

...

To avoid floating point rounding error. If A1 appeared to be #.8@, multiplying
it by 5 more reliably makes the result 5*# + 4 + 5*.0@, so MOD(.,5)>=4 than
using fractions as in the formula above.

 

[Dave]

I got your response about the floating point errors.

I tried a few numbers and I deduce that one should use the smallest
multiple of the cutoff value that results in a fractionless number? In
the case the OP asked for, the cutoff was .8, so the smallest multiple
of .8 that results in a whole number is 5. 10, 15 and 20 also result
in whole numbers, but they do not always return the correct result
with the formula (I am cloudy on why is this happening if you could
enlighten). Could it *ever* happen when using 5? I set a worksheet to
test it on 10000 random numbers ending in .8, and through refreshing
many many times, never saw any errors.

I tried to do this with .77 as the cutoff, so had to use 100 as the
smallest multiple to return a whole number -- and it doesn't -always-
work.

Is there a way to get it to work all the time with .77 or .719292 or
???

This maybe getting too far into minutia since the OP got his answer
long ago, but I appreciate further light you can shine on this.

If I were solving the original question before I had seen this INT and
MOD stuff, I would use a formula that looked at the decimal portion of
the formula, as many spaces as I wanted to look at, then either add 1
or add nothing to the whole number part depending on what the portion
was. BUT this is much more interesting.

Thanks
Dave

 

[Harlan Grove]

...
...

I tried a few numbers and I deduce that one should use the smallest
multiple of the cutoff value that results in a fractionless number? . . .
Correct.

I tried to do this with .77 as the cutoff, so had to use 100 as the
smallest multiple to return a whole number -- and it doesn't -always-
work.

OK, but the question is which of MOD(X.77,1)>=0.77 or MOD(100*X.77,100)>=77 fail
more often. In my experience using Excel, I'd be willing to bet the latter was
more reliable, but I've never conducted exhaustive tests.

Is there a way to get it to work all the time with .77 or .719292 or
???

Probably not reliably. The lowest order binary digits are just too flaky. When
dealing with fractions in binary floating point, you're stuck with using
neighborhoods around the points of interest rather than precise points. Using
your last example, MOD(X,1)>=0.719292-1E10 or something similar.