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...

 

[Kelbo]

Good Point Barry,

Each of my round..(J4,3) functions would need to be changed to require decimal places.

Regards
Kelbo

 

[tusharm]

In the link you were given (to a website that seems to simply act as a frontend to the newsgroup world), there was a post by Harlan Grove of a worksheet formula. If Harlan says it works and if someone like Jerry Lewis did not disagree, it works.

Also, the corrected UDF that Harlan posted should also be something worth checking out.

As I continue to search I keep coming across still more variations of the formulas but I still do not see one which is consistent with all rounding.

 

[channel7]

I pasted in the UDF from the link into my worksheet but I did not understand what values the formula wanted me to enter into the cell to make the calculation work. But I did not click any of the links located on the post found in the link, maybe I could have found more information there.

The reason I didn't use the formula provided in the post isn't because it would not work, but because I didn't understand enough to implement it properly.

 

[channel7]

Okay, in between being gone from the office and all my other work I was able to do some extensive testing with the two formulas kelbo and barry posted. Here are my findings:

I ran both formulas on every number with four decimal places from 0.0001 all the way to 0.9999 and physically verified each calculation (it was about as fun as sticking a pencil in your eye).

Barry: Yours rounded the sample data perfectly but miss calculated 58 numbers.
(I can list those if you are interested)

Kelbo: Yours rounded every number perfectly except for one, .5000
Instead of giving a number for a result is gives the error message #VALUE!

But, since it correctly rounded all other numbers we are implementing your formula into our work to speed things up a bit and to help with overall productivity.

Thank you to both of you for taking time out of your day to help us with out rounding problem!!!

Phillip

 

[Kelbo]

Phillip

Thanks for the feedback. Good to know the results.

I have just taken another look at your problem and my solution. It seems odd that only .5 is affected, .6 seems to be OK.

However if the numbers are converted to text, all numbers seem to work correctly!!

More food for thought.

Regards

Kelbo

 

[Scott Huish]

I think this would do it for 3 decimal places:

=ROUND(A1/0.002,0)*0.002

which will give the same result as MROUND without needing the Analysis Toolpak, and MROUND gave the same results which matched your expected results:

Excel Workbook
	A	B	C
1	0.0785	0.078	0.078
2	0.5115	0.512	0.512
3	0.5035	0.504	0.504
4	0.0005	0	0
5	0.0025	0.002	0.002
6	0.0194	0.02	0.02
7	0.0195	0.02	0.02
8	0.0135	0.014	0.014
9	0.0115	0.012	0.012
10	0.8115	0.812	0.812
Sheet1

 

[barry houdini]

Barry: Yours rounded the sample data perfectly but miss calculated 58 numbers.

Thanks for that Phillip, clearly I didn't test sufficiently.....

The problem with the ones that round incorrectly is caused by a rounding error with the MOD function, for instance you want 0.0345 to round to 0.034. My suggested formula incorrectly returned 0.035. This is because MOD(0.0345*10000,20) should equal 5 [because MOD(345,20)=5] but excel incorrectly calculates it as 5.00000000000006 so you get the wrong result.

This can be easily corrected by introducing another ROUND function.....so this formula will give the correct results in all cases [even 0.5 ]

=ROUND(A1,3)-(MOD(ROUND(A1*10000,0),20)=5)/1000

Note: assumes A1 is a positive number......

 

[barry houdini]

It seems odd that only .5 is affected, .6 seems to be OK.

When 0.5000 is numeric it's read as 0.5 so MID(A1,LEN(A1)-1,1) returns the decimal point, and then ISEVEN(.) causes an error. Of course the MID part also returns the decimal point for 0.6 but that part of the IF function isn't being applied [because RIGHT(A1,1)<>"5" is TRUE]

 

[ZVI]

Hi,

In spite of the fact that it is not declared in VBA help, the VBA Round() function provides strong even Bankers Rule rounding. It is a headache for those who needs VBA arithmetic rounding, but it just that is required for you.

Lasw10 has given you the link to the required BANKROUND() function (don't use unclear BRound() in this link).
Try this UDF:

Function BankRound(Number As Double, Digits As Integer) As Double 
  BankRound = Round(Number, Digits) 
End Function

Regards,
Vladimir

 

[ZVI]

I have tested formula =BankRound(TestValue,3) with your test table and have found that
rounding of 0.5115 returns .511 (not required .512)
It is necessary to think about for a while...
Vladimir