Is Round-to-even possible in Excel? 1

[Einstein47]

I am dealing with an analytical lab that is requiring all rounding to be done with "round-to-even" methodology. Can Excel even do "round-to-even"?

If you aren't familiar with "round-to-even" please look up rounding in wikipedia for a very good explanation. (Wikipedia Rounding) Basically it is just like normal rounding, except when the number to be rounded on is a 5 with trailing zeros. In this case you round up if the digit before the 5 is odd, and don't do anything if the digit is even.
[ul][li]123.45 round-to-even on the tenths would be 123.4[/li][li]123.35 round-to-even on the tenths would also be 123.4[/li]
[/ul]
I have found information for ROUND, ROUNDUP, ROUNDDOWN but none of those seems to be able to do ROUND-TO-EVEN. I'm just stumped on this one.

I have been working on this off and on for the last 4 weeks and I just can't get it. Any suggestions?

Thanks in advance.


Einstein47
“Evil abounds when good men do nothing.“ - Nelson R.
[[]Starbase47.com]

 

[SkipVought]

You can try something like this...
[tt]
=(INT(A1*10)+MOD(INT(A1*10),2))/10
[/tt]

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[strongm]

There are a couple of ways of doing this. Either ustilise the fact that VBA does Banker's rounding, and write a user function such as:

Public Function FINANCIALROUND(ByVal number As Double, ByVal num_digits As Long) As Double
FINANCIALROUND = Round(number, num_digits)
End Function

Or add the Analysis Toolpak addin, and use the MROUND function:

=MROUND(123.45, 0.2)
=MROUND(123.35, 0.2)

 

[SkipVought]

Never knew that was there in Analysis Toolpak. ==> *

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[stefen054]

select the cells and run the macro written down here..

Sub RoundToEven()
Dim rng As Range
Dim intNumberOfDecimal As Integer
intNumberOfDecimal = Application.InputBox("NUMBER OF DECIMALS", "DECIMALTOROUND", , , , , , 1)
Set rng = Selection
If Not rng Is Nothing Then
For Each rng In Selection
rng.Value = Round(rng.Value, intNumberOfDecimal)
Next
End If
End Sub

Stefen.

 

[Einstein47]

Ok - the MROUND() function works in the worksheet, but I need to use it in a VBA macro - another wrinkle, the number of significant figures varies as does the size of the input number.

I have run across a function that will give me the correct number of significant figures and uses the ROUND() function, but when I try to use the MROUND() function it breaks saying that function is undefined. Here is my code:

[b][red]Function[/red] [blue]RoundToSigFig[/blue][/b](Number [blue]As Double[/blue], Significance [blue]As Integer[/blue]) [blue]As Double[/blue]
    Dim DecimalPlaces [blue]As Integer[/blue]
    DecimalPlaces = Significance - Int((Log(Abs(Number)) / Log(10#)) + 1)
    [green]' This rounds as expected[/green]
    RoundToSigFig = Application.WorksheetFunction.Round(Number, DecimalPlaces)
    [green]' This function can't be found[/green]
    test = Application.WorksheetFunction.MRound(Number, (2 * 10 ^ (-1 * DecimalPlaces)) )
[b][red]End Function[/red][/b]

How do I add the MROUND() function so that my VBA macro will recognize it?



Einstein47 (Starbase47.com)
“Never put both feet in your mouth at the same time.
Because then you won't have a leg to stand on.“
- Unknown

 

[Einstein47]

Ok - I think I have painted myself in corner. I was so worried about the MROUND() function that I missed the real power of the "round-to-even" logic. You [red]ONLY[/red] round down when the digit being rounded on is [red]EXACTLY[/red] 5 and the digit just to the left of that is [red]EVEN[/red].

Grrr - I even created code to do the MROUND function without the Analysis ToolPak installed. Here is my function so far - highlighted is the place where I need to figure out how to determine the even digit:

Function RoundToSigFig(Number As Double, Significance As Integer) As Double
' round the number to the given number of significant figures
    Dim DecimalPlaces As Integer
    Dim mroundPlaces As Double
    DecimalPlaces = Significance - Int((Log(Abs(Number)) / Log(10#)) + 1)
    RoundToSigFig = Round(Number, DecimalPlaces)
    [highlight]' If the number we are rounding on is exactly 5 then we need to round down[/highlight]
    mroundPlaces = 2 * (10 ^ (-1 * DecimalPlaces))
    RoundToSigFig = Round(Number / mroundPlaces, 0) * mroundPlaces
End Function

Einstein47 (Starbase47.com)
“Never put both feet in your mouth at the same time.
Because then you won't have a leg to stand on.“
- Unknown

 

[strongm]

>but I need to use it in a VBA macro
Er ... did you actually look at my FinancialRound VBA function

 

[combo]

Concerning Analysis Toolpak functions - they are available in vba after installing 'Analysis Toolpak - VBA' and referencing 'atpvbaen.xla' in vba project references.

combo

 

[strongm]

I think you are still confused, Einstein47. I'll repeat - the VBA Round function does Banker's (round-to-even) rounding. You don't need to do anything more.

 

[Einstein47]

You are correct strongm - VBA Round() does do Banker's Rounding. I was a total idiot there. However, I did find something odd. In my function if the "DecimalPlaces" comes back as negative (meaning we are rounding to the left of the decimal) the VBA round does not work. I have to use the Application.Worksheetfunction.Round function. And the problem is that that function does NOT use Banker's Rounding, but the more common round up always on number ending in 5.

So what I'm going to have to do is if the "DecimalPlaces" value is less than zero, I'll have to divide by a power of ten, do the rounding, and then multiply by that same power of ten to make the VBA Rounding work.

Function RoundToSigFig(Number As Double, Significance As Integer) As Double
[green]' round the number to the given number of significant figures[/green]
    Dim DecimalPlaces As Integer
    Dim mroundPlaces As Double
    Dim Multiplier As Double
    Multiplier = 1#
    If Not Number = 0 Then
        DecimalPlaces = Significance - Int((Log(Abs(Number)) / Log(10#)) + 1)
        If (DecimalPlaces < 0) Then
          Multiplier = 10 ^ Abs(DecimalPlaces)
          DecimalPlaces = 0
        End If
        RoundToSigFig = Round(Number / Multiplier, DecimalPlaces) * Multiplier
    End If
End Function

I think this will do everything I need - am I forgetting something now?

Einstein47 (Starbase47.com)
“Never put both feet in your mouth at the same time.
Because then you won't have a leg to stand on.“
- Unknown

 

[strongm]

>I did find something odd ... rounding to the left of the decimal ... the VBA round does not work

That's not odd. It's what I'd expect. It's what is documented.

And your solution is certainly one way of dealing with it

 

[shdixon]

Try this as an excel formula. This will do bankers rounding to the even penny.

ROUND((C4-0.001)/2,2)*2

 

[strongm]

I'm afraid that's just ... wrong. It does not do banker's rounding (except under a very limited condition) and doesn't solve the OP at all.