Asymmetric Rounding "Rounding-Half-Down"

[Juggler_IN]

While I have been able to code a function for Asymmetric Rounding "Rounding-Half-Up" (code attached). But, I am struggling with Asymmetric Rounding "Rounding-Half-Down" method.

Public Function RoundHalfUpAsym( _
       ByVal myNum As Double, _
       ByVal myFac As Double) As Double
        
        RoundHalfUpAsym = Int(myNum * CDec(10# ^ myFac) + 0.5) / CDec(10# ^ myFac)


End Function

The concept of Asymmetric Rounding "Rounding-Half-Down" can be referenced at:

https://www.eetimes.com/document.asp?doc_id=1274515#A4

 

[ZVI]

Hi,
Try this code:

Function RoundHalfDownAsym(myNum As Double, myFac As Integer) As Double
  If myNum < 0 Then
    RoundHalfDownAsym = WorksheetFunction.RoundUp(myNum, myFac)
  Else
    RoundHalfDownAsym = WorksheetFunction.RoundDown(myNum, myFac)
  End If
End Function

Regards

 

[Juggler_IN]

@ZVI;

No, it doesn't work.

[TABLE="width: 1152"]
<colgroup><col span="18"></colgroup><tbody>[TR]
[TD][/TD]
[TD]-2.0000[/TD]
[TD]-1.7000[/TD]
[TD]-1.5000[/TD]
[TD]-1.3000[/TD]
[TD]-1.0000[/TD]
[TD]-0.7000[/TD]
[TD]-0.5000[/TD]
[TD]-0.3000[/TD]
[TD]0.0000[/TD]
[TD]0.3000[/TD]
[TD]0.5000[/TD]
[TD]0.7000[/TD]
[TD]1.0000[/TD]
[TD]1.3000[/TD]
[TD]1.5000[/TD]
[TD]1.7000[/TD]
[TD]2.0000[/TD]
[/TR]
[TR]
[TD]Expected[/TD]
[TD="align: right"]-2[/TD]
[TD="align: right"]-2[/TD]
[TD="align: right"]-2[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]-1[/TD]
[TD]-0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Actual[/TD]
[TD="align: right"]-2[/TD]
[TD="align: right"]-2[/TD]
[TD="align: right"]-2[/TD]
[TD="align: right"]-2[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD].[/TD]
[TD].[/TD]
[TD].[/TD]
[TD]Error[/TD]
[TD].[/TD]
[TD].[/TD]
[TD].[/TD]
[TD]Error[/TD]
[TD].[/TD]
[TD].[/TD]
[TD].[/TD]
[TD]Error[/TD]
[TD].[/TD]
[TD].[/TD]
[TD].[/TD]
[TD]Error[/TD]
[TD].[/TD]
[/TR]
</tbody>[/TABLE]

 

[ZVI]

No, it doesn't work.

My bad, use the bellow code:

Function RoundHalfDownAsym(V As Double, Optional DecPlaces As Integer = 0) As Double
' ZVI:2019-08-26 https://www.mrexcel.com/forum/excel-questions/1108001-asymmetric-rounding-rounding-half-down.htm
  If DecPlaces < 0 Then
    RoundHalfDownAsym = Round(V / 10 ^ -DecPlaces - Sgn(V) * V * 2E-16, 0) * 10 ^ -DecPlaces
  Else
    RoundHalfDownAsym = Round(V - Sgn(V) * V * 2E-16, DecPlaces)
  End If
  If Abs(RoundHalfDownAsym) = 0 Then RoundHalfDownAsym = 0
End Function

Book1
	A	B	C	D
1	Value	Expected	Actual
2	-2	-2	-2	copy C2 down
3	-1.5	-2	-2
4	-1.3	-1	-1
5	-1	-1	-1
6	-0.7	-1	-1
7	-0.5	-1	-1
8	-0.3	0	0
9	0	0	0
10	0.3	0	0
11	0.5	0	0
12	0.7	1	1
13	1	1	1
14	1.3	1	1
15	1.5	1	1
16	1.7	2	2
17	2	2	2
Sheet1

Cell Formulas
Range	Formula
C2	=RoundHalfDownAsym(A2)

Regards

 

[Juggler_IN]

@ZVI;

Perfect!

 

[ZVI]

Perfect!

Glad it's working well for you