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.

	-2.0000	-1.7000	-1.5000	-1.3000	-1.0000	-0.7000	-0.5000	-0.3000	0.0000	0.3000	0.5000	0.7000	1.0000	1.3000	1.5000	1.7000	2.0000
Expected	-2	-2	-2	-1	-1	-1	-1	-0	0	0	0	1	1	1	1	2	2
Actual	-2	-2	-2	-2	-1	-1	-1	-1	0	0	0	0	1	1	1	1	2
	.	.	.	Error	.	.	.	Error	.	.	.	Error	.	.	.	Error	.

<colgroup><col span="18"></colgroup><tbody>
</tbody>

 

[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