Ok my bad
The first example I used was just a random number I was using to "try" to
explain what I want to do. Yes I did want 190 and thats where I guess my
problem lies, it went down to 189 instead of rounding up to 190. Yeah I
realize this is probably a confusing question but the whole thing was
confusing to me so I guess I appreciate you guys helping me out here. I
guess I was looking more at if its displayed in the g9 as .51 then why isn't
it treating it like .51 instead of .5097. But there again my bad.

I tried your mod(...) and it worked.

Thank you very much everyone for your help and next time I will try to
explain myself a little better.

Merry Christmas to everyone (Happy Holidays for the PC people) and Have a
good New Years.



" wrote:

UBR362 wrote:
I inputed the formula that [Sandy] and Bob gave me and it seemed to work
until I plugged in enough changes to the "base amount" that I ended up with a
case where it was
E9 = 183.99 ("Base Amount")
G9 = 189.51 (Adjusted Amount)
I9 = 189.00 (Rounded Amount)

And you wanted 190? The problem is in the ambiguity of your original
posting. You said you wanted to round up for "10.51 and higher" and
round down for "10.50 and lower". The question is: want about between
10.50 and 10.51?

Bob focused on "10.51 and higher". Sandy interpreted you to mean
"round up for any amount greater than 10.50". Apparently, Sandy's
interpretation is closer to your intent. Use Sandy's formula, namely
MOD(...) 0.50.

However, I suspect you actually mean: round up if the __displayed__
value is greater than 10.50. For example, 10.505 (displayed 10.51)
should round up, but 10.5049 (displayed 10.50) should round down.

In that case, modify Sandy's formula as follows:

=if(mod(round(G9,2),1) 0.50, roundup(G9,0), rounddown(G9,0))

Finally, I wonder if you are really trying to implement banker's
rounding. You example rounded 10.50 down to 10.00. What about 11.50?
Do you want 11.00 or 12.00?

Is it a matter of just carrying out more decimal places? What I am seeing
is that the G9 field is being rounded up to 189.51 (acutal is 189.5097).
Would that affect the results of I9 at all?

Yes, that is the problem.

Also, with respect to Bob's v. Sandy's formula, keep in mind that
(binary) computers can represent 0.50 exactly, but not 0.51. So "
0.50" is better than "= 0.51", even after you make the round(G9,2)
change, which might otherwise seem to make the two formulas identical
in effect.