rounding up on .5

[fnwtech]

Using Access 2003
I am using the following expression in a reportcard db:
Final=(qt1+qtr2+qtr3+qtr4)/4

The field is a Number Field set to integer and decimal places set to 0

Most of the time it rounds correctly. However, when the answer has .5 (such as 98.5) it rounds down to 98 instead of up to 99.

Is there a fairly easy way to fix this so that .5 always rounds up?

[mseo]

hi,
the attached file contains the function and how to use it within query or form, it will let you round the numbers up or down
hope this helps

[ADezii]

@fnwtech
You may need some kind of 'Hybrid' Round Function, try this:

Public Function fNewRound(sngNumber As Single)
Dim sngDecimal As Single
 
'Reduce to 2 Decimal Places
sngDecimal = FormatNumber(sngNumber, 2) - Fix(sngNumber)
 
'Analyze the 2-Digit Decimal Component
If sngDecimal >= 0.5 Then
  fNewRound = Int(sngNumber) + 1
Else
  fNewRound = Int(sngNumber)
End If
End Function

[fnwtech]

@ADezii
ADezii - can you tell me where I place this? Do I place it somewhere within this statement? Tried after the End sub and of course it ignores it ;)

Private Sub Final_GotFocus()
Final = (qtr1 + qtr2 + qtr3 + qtr4) / 4
 
End Sub

mseo - I am looking to round up when it is .5 not to the nearest 5...(e.g 92.5 round to 93) Currently, something to do with bankers math, Access rounds even numbers down and odd numbers up...
Thanks!

[ADezii]

@fnwtech
Pass the Value of Final to the Function, as in:

Final = (qtr1 + qtr2 + qtr3 + qtr4) / 4
 
'Write to a Text Box
Me![txtResults] = fRoundNew(Final)

[OldBirdman]

I would use:

Final = ROUND((qtr1 + qtr2 + qtr3 + qtr4) / 4 + .001)

[ahmedtharwat19]

Try This:

Public Function RNearest(Amount As Double)
Dim diceml As Double
Dim intgr As Double
Dim Rtotal As Double
Dim dtotal As Double
Dim xfinal As Double
Dim final As Double
 
intgr = Int(Amount)
Rtotal = FormatNumber(intgr, 2)
diceml = Amount - intgr
dtotal = diceml
 
  Select Case dtotal
    Case Is <= 0.125
        dtotal = 0
    Case Is <= 0.25
        dtotal = 0.25
    Case Is <= 0.375
        dtotal = 0.25
    Case Is <= 0.5
        dtotal = 0.5
    Case Is <= 0.625
        dtotal = 0.5
    Case Is <= 0.75
        dtotal = 0.75
    Case Is <= 0.785
        dtotal = 0.75
    Case Is <= 0.999
        dtotal = 1
  End Select
 
xfinal = Rtotal + dtotal
final = FormatNumber(xfinal,2)
RNearest = final
End Function

For Test In imeediate window write thes:

?RNearest(125.335)

It`s Return To:

125.25 

This equation is inadequate for dealing with cash transactions

Best Regards,

Ahmed Tharwat(Medo)

[fnwtech]

@ADezii

First, you used fRoundNew here but fNewRound above, Should they be the same?
So,I changed both to fNewRound so this is what I have:

Private Sub Final_GotFocus()
Final = (qtr1 + qtr2 + qtr3 + qtr4) / 4
'Write to a Text Box
Me![txtResults] = fNewRound(Final)
 
End Sub
Public Function fNewRound(sngNumber As Single)
  Dim sngDecimal As Single
 
   'Reduce to 2 Decimal Places
  sngDecimal = FormatNumber(sngNumber, 2) - Fix(sngNumber)
 
   'Analyze the 2-Digit Decimal Component
 If sngDecimal >= 0.5 Then
   fNewRound = Int(sngNumber) + 1
  Else
 fNewRound = Int(sngNumber)
  End If
  End Function
 
 
End Function 

Now I am receiving an error that nothing but comments can follow End Sub... so I guess I am placing this in the wrong place.

[NeoPa]

Using the function :

Public Function RoundIt(dblVal As Double) As Long
    RoundIt = Fix(dblVal + (0.5 * Sgn(dblVal)))
End Function

I ran the following command from the Immediate pane and got the results shown.

?RoundIt(-1);RoundIt(-.9);RoundIt(-.8);RoundIt(-.7);RoundIt(-.6); _
RoundIt(-.5);RoundIt(-.4);RoundIt(-.3);RoundIt(-.2);RoundIt(-.1); _
RoundIt(0);RoundIt(.1);RoundIt(.2);RoundIt(.3);RoundIt(.4);RoundIt(.5); _
RoundIt(.6);RoundIt(.7);RoundIt(.8);RoundIt(.9);RoundIt(1);
-1 -1 -1 -1 -1 -1  0  0  0  0  0  0  0  0  0  1  1  1  1  1  1

[fnwtech]

@fnwtech
Okay, realized I had to End functions...

So now can you tell me what the Me![txtResults] needs to be pointing to? Is this another field I need to add? That is now where it is hanging.

[fnwtech]

@OldBirdman
OldBirdman - this one seems to work for what I need it to do. I have tested results that give 90.5 (gives 91)and 91.5 (gives 92) also 91.25 stays a 91... so this seems to do the trick.

Thanks everyone for all your assistance on this!

[ahmedtharwat19]

@fnwtech
Your Function Is:

Public Function RNerst(Amount As Double)
Dim IntAmount As Double
Dim DicAmount As Double
IntAmount = Int(Amount)
DicAmount = Amount - IntAmount
If DicAmount < 0.5 Then
DicAmount = 0
ElseIf DicAmount >= 0.5 Then
DicAmount = 1
End If
RNerst = IntAmount + DicAmount
End Function

Try It.

(Medo)

[OldBirdman]

Good. If negative numbers are possible, then NeoPa's post #9 should be considered. The Sgn() function MAY BE needed if 'to round up' means that the absolute value is larger. If the actual value is higher, my solution is good for negative numbers.
In other words, when you round -91.5, do you expect -91 or -92.

[ahmedtharwat19]

@OldBirdman

In other words, when you round -91.5, do you expect -91 or -92.

I Expect It Should Be -91.
Because When We Increase The Minus The Value Is decrease.

In My Function in #12
eq. say:

-91.5 = -91 after round up
-91.5 = -91 + 1
-91.5 = -91

To Explain It In My Function do Follwing:

Public Function RNerst(Amount As Double)
Dim IntAmount As Double
Dim DicAmount As Double
IntAmount = Int(Amount)
DicAmount = Amount - IntAmount
If DicAmount < 0.5 Then
DicAmount = 0
ElseIf DicAmount >= 0.5 Then
DicAmount = 1
End If
RNerst = IntAmount + DicAmount
Debug.Print "Int IS: " & IntAmount
Debug.Print "*********************"
Debug.Print "Dic IS: " & DicAmount
Debug.Print "*********************"
Debug.Print "Run IS: " & IntAmount + DicAmount
Debug.Print "*********************"
Debug.Print "RNearst IS: " & RNerst
Debug.Print "*********************"
End Function

It`s Return To In Immediate Pane:

?RNerst(-91.5)
Int IS: -92
*********************
Dic IS: 1
*********************
Run IS: -91
*********************
RNearst IS: -91
*********************
-91 

----------------------------------------------------------

OldBirdman,

But your words in any of the really puzzling, some equations and figures put some other figures put
This is another attempt on that:

Public Function RNerst(Amount As Double)
Dim AAmount As Double
Dim FixAmount As Double
Dim DicAmount As Double
 
AAmount = IIf(Amount < 0, Amount * -1, Amount)
FixAmount = Fix(AAmount)
DicAmount = AAmount - FixAmount
If DicAmount < 0.5 Then
DicAmount = 0
ElseIf DicAmount >= 0.5 Then
DicAmount = 1
End If
 
RRNerst = FixAmount + DicAmount
RNerst = IIf(Amount < 0, RRNerst * -1, RRNerst)
Debug.Print "Fix IS: " & FixAmount
Debug.Print "*********************"
Debug.Print "Dic IS: " & DicAmount
Debug.Print "*********************"
Debug.Print "Run IS: " & FixAmount + DicAmount
Debug.Print "*********************"
Debug.Print "RNearst IS: " & RNerst
Debug.Print "*********************"
End Function

It`s Return To In Immediate Pane:

?RNerst(-91.5)
Fix IS: 91
*********************
Dic IS: 1
*********************
Run IS: 92
*********************
RNearst IS: -92
*********************
-92 

Thank You For Let Me Thinking,i`ll Search In A Good Idea

(Medo)

[NeoPa]

ahmedtharwat19: I Expect It Should Be -91.

You may well expect that Medo :D

I suspect the question was directed at the OP (Original Poster - The titular owner of the thread) however.

It is possible that your interpretation is correct, though I would have to say it is unlikely in my experience. Generally, for consistency, the values above zero should reflect those below. Standard default rounding is to round up when the absolute value of the fractional part of a real number is .5 or greater.

The important question though, is what does fnwtech require?

[fnwtech]

I actually never gave any thought to negative numbers, nor does it matter in my case. Being a middle school report card I hope there are no negative scores ;-)

[NeoPa]

Not even for conduct?!!?!

I had loads of those :D

[fnwtech]

@NeoPa
yah - I am sure some teachers wish negatives were allowed!

[ADezii]

@NeoPa

Not even for conduct?!!?!

I had loads of those :D

Somehow I already figured that one out! (LOL)