rounding up on .5

**mseo** · Jun 9 '10, 12:12 AM

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** · Jun 9 '10, 12:16 AM

Originally posted by 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?

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

Code:

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** · Jun 9 '10, 12:00 PM

Originally posted by ADezii

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

Code:

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

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 ;)

Code:

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** · Jun 9 '10, 12:20 PM

Originally posted by fnwtech

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

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!

End Sub

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

Code:

Final = (qtr1 + qtr2 + qtr3 + qtr4) / 4

'Write to a Text Box
Me![txtResults] = fRoundNew(Final)

**OldBirdman** · Jun 9 '10, 12:26 PM

I would use:

Code:

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

**ahmedtharwat19** · Jun 9 '10, 01:05 PM

Try This:

Code:

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:

Code:

?RNearest(125.335)

It`s Return To:

Code:

125.25

This equation is inadequate for dealing with cash transactions

Best Regards,

Ahmed Tharwat(Medo)

**fnwtech** · Jun 9 '10, 01:17 PM

Originally posted by ADezii

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

Code:

Final = (qtr1 + qtr2 + qtr3 + qtr4) / 4

'Write to a Text Box
Me![txtResults] = fRoundNew(Final)

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:

Code:

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** · Jun 9 '10, 02:07 PM

Using the function :

Code:

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.

Code:

?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** · Jun 9 '10, 02:56 PM

Originally posted by fnwtech

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:

Code:

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.

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** · Jun 9 '10, 03:08 PM

Originally posted by OldBirdman

I would use:

Code:

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

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** · Jun 10 '10, 06:35 AM

Originally posted by fnwtech

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!

Your Function Is:

Code:

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** · Jun 10 '10, 12:47 PM

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** · Jun 10 '10, 01:37 PM

Originally posted by 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.

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:

Code:

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:

Code:

?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:

Code:

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:

Code:

?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** · Jun 10 '10, 04:59 PM

Originally posted by ahmedtharwat19

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?

rounding up on .5

rounding up on .5

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment