Rounding with Excel - many formulas

[channel7]

I have a unique rounding issue I was hoping you guys could help me solve. I am trying to round similar to Banker's Rounding or Scientific Rounding but I can't find a consistent formula that works perfect with decimals.

Using three decimal places for all the samples, I can get 0.0785 to round to 0.078 but 0.1785 wants to round to 0.179 instead of staying 0.078. Or 0.0005 will round to 0 but 0.5115 wants to round to 0.511 instead of 0.512.

Here is a list of sample numbers along with desired results:
.0785 should be .078
.5115 should be .512
.5035 should be .504
.0005 should be 0
.0025 should be .002
.0194 should be .019
.0195 should be .02
.0135 should be .014
.0115 should be .012
.8115 should be .812

I cannot find a formula which gives me all of these results. Here is a list of the formulas I have tried so far (NOTE: cell A2 is the working cell in my worksheet where I enter the number to be rounded)

1) =MROUND(A2,0.001)

3) =ROUND(A2,3)

4) =IF(ISERROR(IF(MOD(MID(A2,4,1),2)=1,CEILING(A2,0.001),FLOOR(A2,0.001))),0,IF(MOD(MID(A2,4,1),2)=1,CEILING(A2,0.001),FLOOR(A2,0.001)))

5) =EVEN(A2)

6) =ROUNDUP(A2,3)

7) =ROUNDDOWN(A2,3)

8) =ROUND(A2*5,2)/5

9) =ROUND(A2,3)

10) =IF(A2*1000-INT(A2*100)*10=5,IF(ISEVEN(INT(A2*100-INT(A2*10)*10)), ROUND(A2,2),A2-0.005),ROUND(A2,3))

11) =ROUND(A2,3)-(MOD(A2*1000,30)=5)/100

12) =IF(A2*1000-INT(A2*100)*10=5,IF(ISEVEN(INT(A2*100-INT(A2*10)*10)), A2-0.005,A2+0.005), ROUND(A2,3))

13) =BankersRound(A2,3)

14) (not working like it is) =MROUND(A2,IF(VALUE(RIGHT(A2/10^(INT(LOG(ABS(A2)))-Plc+1),2))=0.5,2,1)*SIGN(A2)*10^(INT(LOG(ABS(A2)))-Plc+1))

15) =IF(--RIGHT(100*MOD(A2,3))=5,IF(MOD(LEFT(10*MOD(A2,3)),2)=0,ROUNDDOWN(A2,3),ROUNDUP(A2,3)),ROUND(A2,3))

16) =IF(--RIGHT(100*MOD(A2,3))=5,IF(ISEVEN(MOD(A2,3)*10),ROUNDDOWN(A2,3),ROUNDUP(A2,3)),ROUND(A2,3))

17) =IF(OR((--(MID(MOD(A2,3),3,1)))=0,MOD((--(MID(MOD(A2,3),3,1))),2)>0),ROUND(A2,3),ROUNDDOWN(A2,3))

18) =IF(--RIGHT(100*ROUND(MOD(A2,3),2))=5,IF(ISEVEN(MOD(A2,3)*10),ROUNDDOWN(A2,3),ROUNDUP(A2,3)),ROUND(A2,3))

19) =IF(--RIGHT(100*ROUND(MOD($A2,3),2))=5,IF(MOD(TRUNC(10*$A2),2)=0,ROUNDDOWN($A2,3),ROUNDUP($A2,3)),ROUND($A2,3))

20) =IF(MOD(ROUND(ABS(A2)*100,0),20)=5,ROUNDDOWN(A2,3),ROUND(A2,3))

Any one have another formula trick that might help with these decimals? Or does someone see something that I am doing wrong in the setup somewhere?

Thanks!!!

BTW, many of these formulas can from past posts found on this site while searching...

 

[ZVI]

This works:

' ZVI:2008-07-18 True VBA Banking Rounding
Function BankRound(Number As Double, Optional Digits = 0) As Double
  If Fix(Number * 10 ^ Digits) Mod 2 = 0 Then
    ' Even
    BankRound = Round(Number, Digits)
    ' Exclude -0 return value
    If Abs(BankRound) = 0 Then BankRound = 0
  Else
    ' Odd
    BankRound = CDbl(FormatNumber(Number, Digits))
  End If
End Function

Regards,
Vladimir

 

[ZVI]

The appropriative Excel formula can be as follow:

=IF(ODD(TRUNC(A1*10^3)),ROUND(A1,3),ROUNDDOWN(A1,3))

Where 3 is the same as Digits parameter in VBA BankRound() function

It is interesting that using of EVEN() cause to wrong result.

Vladimir

 

[ZVI]

Sorry for misprint in my previous post because of mistaken translation from the local language.

Correctly it should be:

=IF(ISODD(TRUNC(A1*10^3)),ROUND(A1,3),ROUNDDOWN(A1,3))

or with the same result:

=IF(ISEVEN(TRUNC(A1*10^3)),ROUNDDOWN(A1,3),ROUND(A1,3))

Vladimir

 

[Sviat]

Hi,
Thanks for a great number of useful formulas for rounding. Unfortunately, still struggling to find the one which would match the needs.
The task is to round the market share with one place after decimal which totals exactly 100.0%
The testing sample is provided below. Could anyone help me with this issue?
Sample 1

		Excel Rounding	Desired result
player 1	0.47646	47.6%	47.7%
player 2	0.38538	38.5%	38.5%
player 3	0.13816	13.8%	13.8%
Total		99.9%	100.0%

<tbody>
</tbody>

Sample 2

		Excel Rounding	Desired result
player 1	0.46132	46.1%	46.1%
player 2	0.39636	39.6%	38.7%
player 3	0.14232	14.2%	14.2%
Total		99.9%	100.0%

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

And it goes on and on... Would really appreciate your suggestions!

 

[ZVI]

Hi,
Try this:

Excel Workbook
	A	B	C	D	E
1	Players	Value	Excel Rounding	0.10%	Fixed Rounding
2	player 1	0.46132	46.1%	0.00032	46.1%
3	player 2	0.39636	39.6%	0.00036	39.7%
4	player 3	0.14232	14.2%	0.00032	14.2%
5		1	99.9%	2	100.0%
Sheet1

 

[Sviat]

Hi Vladimir,

Thanks a lot! That works perfectly in every single case! Can you briefly describe the algorithm?
BTW, are there any ways to make this calculation automated (e.g. VBA)?
I mean that I perform the calculation of share routinely:
1) calculate share based on absolute figures
2) check manually whether it totals 100.0%

Players	Result	Share
P1	5.4	50.5%
P2	3.2	29.9%
P3	2.1	19.6%
Total	10.7	100.0%

<tbody>
</tbody>

Would really appreciate the potential solution!
Thanks in advance!

 

[ZVI]

Algorithm is in adding/subtracting of the fixing difference to the 1st position with max/min residual between result and rounded result.
D1 is the difference between 100% and the sum of the rounded percents in C2:C4.
D2:D4 are residuals equal to ratio minus rounded ratio.
D5 is position of the cell in range D2:D4 where fixing value has to be applied.
If D1 is positive then fixing value (D1) is added to the cell’s value in position with max residual.
If D1 is negative then fixing value (D1) is subtracted from the cell’s value in position with min residual.
For more details analyze the formulas of post #25.

The code of UDF for your post #26 is as follows:

Function Share(Results As Range)
 
  Dim a(), max(1 To 2) As Double, min(1 To 2) As Double
  Dim i As Long, total As Double, rtotal As Double, v As Double
 
  ' Check input range
  If Results.Cells.Count = 1 Then
    Share = 1
    Exit Function
  End If
 
  ' Put input values to a() to speed code up
  a() = Results.Value
 
  ' Calc total
  For i = 1 To UBound(a)
    total = total + a(i, 1)
  Next
 
  ' Calc ratio, rounded ratio, min & max differenses
  For i = 1 To UBound(a)
    v = a(i, 1) / total               ' Ratio
    a(i, 1) = Round(v + v * 2E-16, 3) ' Rounded ratio
    rtotal = rtotal + a(i, 1)         ' Total of rounded ratios
    v = v - a(i, 1)                   ' Differense of the ratio and rounded ratio
    If v > max(1) Then
      max(1) = v                      ' Max differense
      max(2) = i                      ' and its index in a()
    ElseIf v < min(1) Then
      min(1) = v                      ' Min differense
      min(2) = i                      ' and its index in a()
    End If
  Next
 
  ' Calc value to be added/subtracted to the results
  v = 1 - rtotal
  v = Round(v + v * 2E-16, 3)
 
  ' Fix the ratios
  Select Case Sgn(v)
    Case 1
      ' Increase a() with max difference
      a(max(2), 1) = a(max(2), 1) + v
    Case -1
      ' Decrease a() with min difference
      a(min(2), 1) = a(min(2), 1) - v
  End Select
 
  ' Return the array of fixed ratios
  Share = a()
 
End Function

The implementation of UDF:

Excel Workbook
	A	B	C	D
1	Players	Result	Share	Excel's one
2	P1	5.4	50.9%	50.9%
3	P2	3.1	29.3%	29.2%
4	P3	2.1	19.8%	19.8%
5	Total	10.6	100.0%	99.9%
Sheet1

 

[Sviat]

That's truly amazing!
I'll try to figure out how to implement this into my workbook. Do you happen to have the workbook where you tested it?
Thanks a lot!

 

[ForceOneX]

The following will do round half to even (banker's rounding; scientific rounding; statistical rounding) using native Excel formulas (replace "Value" with the number you wish to round and "DecimalPlaces" with the number of decimal places). This function also works with negative decimal places (for rounding significant digits).


=IF(Value < 0,
-IF(FLOOR.MATH(ABS(Value) * POWER(10, DecimalPlaces + 1)) - FLOOR.MATH(ABS(Value) * POWER(10, DecimalPlaces)) * 10 = 5,
IF(MOD(FLOOR.MATH(ABS(Value) * POWER(10, DecimalPlaces)) - FLOOR.MATH(ABS(Value) * POWER(10, DecimalPlaces - 1)) * 10, 2) = 0,
FLOOR.MATH(ABS(Value) * POWER(10, DecimalPlaces)) / POWER(10, DecimalPlaces),
CEILING.MATH(ABS(Value) * POWER(10, DecimalPlaces)) / POWER(10, DecimalPlaces)
),
IF(FLOOR.MATH(ABS(Value) * POWER(10, DecimalPlaces + 1)) - FLOOR.MATH(ABS(Value) * POWER(10, DecimalPlaces)) * 10 < 5,
FLOOR.MATH(ABS(Value) * POWER(10, DecimalPlaces)) / POWER(10, DecimalPlaces),
CEILING.MATH(ABS(Value) * POWER(10, DecimalPlaces)) / POWER(10, DecimalPlaces))
),
IF(FLOOR.MATH(ABS(Value) * POWER(10, DecimalPlaces + 1)) - FLOOR.MATH(ABS(Value) * POWER(10, DecimalPlaces)) * 10 = 5,
IF(MOD(FLOOR.MATH(ABS(Value) * POWER(10, DecimalPlaces)) - FLOOR.MATH(ABS(Value) * POWER(10, DecimalPlaces - 1)) * 10, 2) = 0,
FLOOR.MATH(ABS(Value) * POWER(10, DecimalPlaces)) / POWER(10, DecimalPlaces),
CEILING.MATH(ABS(Value) * POWER(10, DecimalPlaces)) / POWER(10, DecimalPlaces)
),
IF(FLOOR.MATH(ABS(Value) * POWER(10, DecimalPlaces + 1)) - FLOOR.MATH(ABS(Value) * POWER(10, DecimalPlaces)) * 10 < 5,
FLOOR.MATH(ABS(Value) * POWER(10, DecimalPlaces)) / POWER(10, DecimalPlaces),
CEILING.MATH(ABS(Value) * POWER(10, DecimalPlaces)) / POWER(10, DecimalPlaces))
)
)



If you can do macros, the you can do this instead (this method will NOT handle negative decimal places):


1. Save your work book as a macro-enabled work book (use "File | Save As" and change the type to "Excel Macro-Enabled Workbook (*.xlsm)").
2. Open Visual Basic (Alt + F11).
3. Select "Insert | Module" from the main menu.
4. Insert the following code:


Function BROUND(value As Double, decimalPlaces As Long) As Double
BROUND = Round(value, decimalPlaces)
End Function


5. Select "View | Microsoft Excel" from the main menu (Alt + F11)
9. Use the function!