0

I'm using Access to populate some accounting forms and found an issue where it's add .01 to my excel sheets. The database is set to divide a number in half with 3 decimal places. The issue I'm having is, say the number after the division is 27.605 it's rounding both numbers to 27.61, which adds one penny when the two numbers are combined.

If 3rd decimal place is 5 or more I need to round up only on one number and need the other number to round down. This way when the two numbers are combined, it doesn't add one penny. I know it seems weird the way I've described but our company's forms factors surcharges and it was half of the surcharges twice in the spreadsheet. At the bottom of the spreadsheet it adds the two surcharge (half) totals together.

So I'm thinking

surcharge1 = equals half of the surcharge surcharge2 = the other half of the surcharge

if the 3rd decimal place is 5 or more round surcharge1 up to the nearest penny and round surcharge2 down to the nearest penny. Any idea on how to make this function properly?

Improve this question
5
  • Review allenbrowne.com/round.html. It holds the answers you seek Commented Sep 21, 2017 at 16:43
  • Int(100 * [MyField]) / 100) is supposed to round down to the nearest cent according to that link, yet it's rounding down to the nearest dollar. So $45.485 is actually rounding down to $45.00 Commented Sep 21, 2017 at 17:26
  • I think I found the right answer, seems to work but still testing it. Surcharge2 = Round([half_surcharge], 2) which seems to be rounding down to the nearest cent. Commented Sep 21, 2017 at 17:43
  • Note that as said in that article, Round users bankers rounding (e.g. 20.5 rounded to 0 digits rounds to 20, while 21.5 rounds to 22) Commented Sep 21, 2017 at 18:39
  • @user2002716 Your experience with Int(100 * [MyField]) / 100) suggests it is doing integer division, but VBA does not perform integer division. Are you certain that there is no other code between that statement and the output? Commented Sep 22, 2017 at 15:36

2 Answers 2

Reset to default
4

You can use the RoundUp and RoundDown functions found here at Experts Exchange and here at GitHub.

Your usage would be:

Value = 27.605 Value1 = RoundUp(Value, 2) ' 27.61 Value2 = RoundDown(Value, 2) ' 27.6

These are the functions:

' Rounds Value up with count of decimals as specified with parameter NumDigitsAfterDecimals. ' ' Rounds to integer if NumDigitsAfterDecimals is zero. ' ' Optionally, rounds negative values away from zero. ' ' Uses CDec() for correcting bit errors of reals. ' ' Execution time is about 0.5µs for rounding to integer ' else about 1µs. ' Public Function RoundUp( _ ByVal Value As Variant, _ Optional ByVal NumDigitsAfterDecimals As Long, _ Optional ByVal RoundingAwayFromZero As Boolean) _ As Variant Dim Scaling As Variant Dim ScaledValue As Variant Dim ReturnValue As Variant ' Only round if Value is numeric and ReturnValue can be different from zero. If Not IsNumeric(Value) Then ' Nothing to do. ReturnValue = Null ElseIf Value = 0 Then ' Nothing to round. ' Return Value as is. ReturnValue = Value Else If NumDigitsAfterDecimals <> 0 Then Scaling = CDec(Base10 ^ NumDigitsAfterDecimals) Else Scaling = 1 End If If Scaling = 0 Then ' A very large value for Digits has minimized scaling. ' Return Value as is. ReturnValue = Value ElseIf RoundingAwayFromZero = False Or Value > 0 Then ' Round numeric value up. If Scaling = 1 Then ' Integer rounding. ReturnValue = -Int(-Value) Else ' First try with conversion to Decimal to avoid bit errors for some reals like 32.675. On Error Resume Next ScaledValue = -Int(CDec(-Value) * Scaling) ReturnValue = ScaledValue / Scaling If Err.Number <> 0 Then ' Decimal overflow. ' Round Value without conversion to Decimal. ScaledValue = -Int(-Value * Scaling) ReturnValue = ScaledValue / Scaling End If End If Else ' Round absolute value up. If Scaling = 1 Then ' Integer rounding. ReturnValue = Int(Value) Else ' First try with conversion to Decimal to avoid bit errors for some reals like 32.675. On Error Resume Next ScaledValue = Int(CDec(Value) * Scaling) ReturnValue = ScaledValue / Scaling If Err.Number <> 0 Then ' Decimal overflow. ' Round Value without conversion to Decimal. ScaledValue = Int(Value * Scaling) ReturnValue = ScaledValue / Scaling End If End If End If If Err.Number <> 0 Then ' Rounding failed because values are near one of the boundaries of type Double. ' Return value as is. ReturnValue = Value End If End If RoundUp = ReturnValue End Function

And:

' Rounds Value down with count of decimals as specified with parameter NumDigitsAfterDecimals. ' ' Rounds to integer if NumDigitsAfterDecimals is zero. ' ' Optionally, rounds negative values towards zero. ' ' Uses CDec() for correcting bit errors of reals. ' ' Execution time is about 0.5µs for rounding to integer ' else about 1µs. ' Public Function RoundDown( _ ByVal Value As Variant, _ Optional ByVal NumDigitsAfterDecimals As Long, _ Optional ByVal RoundingToZero As Boolean) _ As Variant Dim Scaling As Variant Dim ScaledValue As Variant Dim ReturnValue As Variant ' Only round if Value is numeric and ReturnValue can be different from zero. If Not IsNumeric(Value) Then ' Nothing to do. ReturnValue = Null ElseIf Value = 0 Then ' Nothing to round. ' Return Value as is. ReturnValue = Value Else If NumDigitsAfterDecimals <> 0 Then Scaling = CDec(Base10 ^ NumDigitsAfterDecimals) Else Scaling = 1 End If If Scaling = 0 Then ' A very large value for Digits has minimized scaling. ' Return Value as is. ReturnValue = Value ElseIf RoundingToZero = False Then ' Round numeric value down. If Scaling = 1 Then ' Integer rounding. ReturnValue = Int(Value) Else ' First try with conversion to Decimal to avoid bit errors for some reals like 32.675. ' Very large values for NumDigitsAfterDecimals can cause an out-of-range error when dividing. On Error Resume Next ScaledValue = Int(CDec(Value) * Scaling) ReturnValue = ScaledValue / Scaling If Err.Number <> 0 Then ' Decimal overflow. ' Round Value without conversion to Decimal. ScaledValue = Int(Value * Scaling) ReturnValue = ScaledValue / Scaling End If End If Else ' Round absolute value down. If Scaling = 1 Then ' Integer rounding. ReturnValue = Fix(Value) Else ' First try with conversion to Decimal to avoid bit errors for some reals like 32.675. ' Very large values for NumDigitsAfterDecimals can cause an out-of-range error when dividing. On Error Resume Next ScaledValue = Fix(CDec(Value) * Scaling) ReturnValue = ScaledValue / Scaling If Err.Number <> 0 Then ' Decimal overflow. ' Round Value with no conversion. ScaledValue = Fix(Value * Scaling) ReturnValue = ScaledValue / Scaling End If End If End If If Err.Number <> 0 Then ' Rounding failed because values are near one of the boundaries of type Double. ' Return value as is. ReturnValue = Value End If End If RoundDown = ReturnValue End Function

Note please, that the native Round of VBA is quite buggy. See the test module of the download.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

That's some interesting stuff in the link. Guess I need to revisit all my code that uses Round :(
3

You won't get a better answer than Gustav's for proper rounding functions, and in the case of only two constituent values, then the complimentary RoundDown and RoundUp functions are sufficient. (FYI, similar functions are often named Floor and Ceiling in other languages and programming libraries.)

However, when it is critical to have constituent values sum to an original/expected value, it is best to calculate the last of the constituent values by a difference of the expected total and the sum of all other rounded values. This will guarantee the correct sum even if there are unexpected results from rounding. Even if using the buggy VBA Round() function, this technique would have avoided the 1 cent errors to start with.

surcharge1 = SomeRoundFunction(Total_Surcharge / 2.0) surcharge2 = Total_Surcharge - surcharge1

This is especially important with more than two constituent values, since there is no set of rounding functions that will properly round 3 or more values to guarantee they add up to some value. As an example, I recently had to split out total transaction amounts by (re)calculating discounts and taxes. Although I knew the percentages for both, I needed to figure each part rounded to the penny. Penny errors were acceptable in both discount and tax, but they still needed to add up to the total after any rounding. The only guarantee to ensure that the final transaction value remained unchanged was to first calculate and round the tax, next calculate and round the discount, then finally correct for penny errors by subtracting the sum of parts from the expected total.

Improve this answer

1 Comment

I took up the challenge, and it turned out to be a little more than I'd expected: Round elements of a sum to match a total

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.