Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Aug 2002
    Location
    Portland, OR, USA
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Erratic Rounding (Access XP)

    I am trying to use a simple formula to round up the results from a query to the nearest whole number. The formula works perfectly 90% of the time.
    I have 3 variables: qty, NumberPerPallet, SpacesPerPallet. The formula needs to take the quantity/NumberPerPallet*SpacesPerPallet and then round up. If I take Int(2/3*2) =2, everything is fine. But Int(1/3*2)=1 when it should be 2. I've tried using Int, Fix, IIF to no avail.

    Tried:
    IIF(int(x)=x,x,x+1)
    -(int(-number)
    IIF(number>int(number),int(number)+1,number
    Am I just brain dead? Any ideas would be greatly appreciated.

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Erratic Rounding (Access XP)

    >>But Int(1/3*2)=1 when it should be 2<<

    Really? That's not how I learned it in school.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Erratic Rounding (Access XP)

    The Int function returns the integer portion of a number.
    (1/3)*2 = 1.5. the integer portion of that is 1

    You say that youwant to "round up the results from a query to the nearest whole number".
    Do you want to go to the nearest whole number or round up?

    The CInt function will round your values to the nearest integer, but it will round 1.5 down to 1.
    Regards
    John



  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Erratic Rounding (Access XP)

    >>(1/3)*2 = 1.5. <<

    What type of calculator are you using to get that result?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    352
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Erratic Rounding (Access XP)

    Try

    INT((x/y*z)+0.99)

    You can extend the number of nines if you are likey to need to round up numbers below 0.01. (i.e if y*z could be greater than 100)
    David Grugeon
    Brisbane Australia

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Erratic Rounding (Access XP)

    Who knows what I was thinking when I wrote >>(1/3)*2 = 1.5. <<,

    but it remains true that the Int function always rounds down. (for positive numbers at least, I forget what happens for negatives).
    Regards
    John



  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Erratic Rounding (Access XP)

    You wrote

    >> the Int function always rounds down

    This is what Int is intended to do. It doesn't round, but remove the fractional part of a number. From VB6 help:
    <hr>Both Int and Fix remove the fractional part of number and return the resulting integer value.

    The difference between Int and Fix is that if number is negative, Int returns the first negative integer less than or equal to number, whereas Fix returns the first negative integer greater than or equal to number. For example, Int converts -8.4 to -9, and Fix converts -8.4 to -8.<hr>
    To round numbers to the nearest integer, use (surprise, surprise) the Round function, or use Int(x+0.5).

  8. #8
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Erratic Rounding (Access XP)

    Int( ) rounds down, so Int(-1.5) becomes -2. Fix( ) just takes the integer part of the argument, so Fix(-1.5) = -1.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  9. #9
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Erratic Rounding (Access XP)

    You wrote:
    >>...or use Int(x+0.5). <<

    This won't consistently work. Try various values for x, such as 1.0, 1.5, 2.0, 2.5.

    You should use Cint(x) if you want to convert to an integer. This actually follows rounding rules, Int merely takes the integer part of the arguement.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Erratic Rounding (Access XP)

    Hi Mark,

    It depends on how you define "consistently". When rounding a number ending in .5, what is the nearest integer? After all, 1.5 is equidistant from 1 and 2.

    CInt uses what is called "banker's rounding" - numbers ending in .5 are rounded to the nearest even integer. So 1.5 and 2.5 are both rounded to 2, 3.5 and 4.5 are both rounded to 4, etc. If that's what you want - fine.

    Int(x+0.5) always rounds numbers ending in .5 up to the next highest integer. This is what mathematicians mostly use. The ROUND function in Excel acts like this.

    So, you can't say that one method is correct and the other one is wrong. You must decide what you expect rounding to do and then pick a method that does that.

    Regards,
    Hans

  11. #11
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Erratic Rounding (Access XP)

    Using Int to "round" as you suggested only works for rounding to whole numbers. All other rounding in Access uses the "round to the even digit" approach when the rounded digit is 5. This applies to using the Round function (Access2000 and above) and Cint, and indeed any math that you do that eventually converts to an integer or currency, or that uses the format function. So I guess its a matter of consistency.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •