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

 

[DonkeyOte]

see here for VBA function

http://www.tech-archive.net/Archive/Excel/microsoft.public.excel.misc/2004-04/5994.html

Aladin and co are around so you might get a nice formula also.

 

[Kelbo]

Hi,

MRound(0.0785,0.002) should give BankersRounding and it does for all except 0.0194 which on my spreadsheet rounded up to 0.02, not to 0.019 which I would have expected!

Hope this gives a little help.

Regards,

kelbo

 

[channel7]

see here for VBA function

http://www.tech-archive.net/Archive/Excel/microsoft.public.excel.misc/2004-04/5994.html

Thanks, lasw10. I copied in the function but I don't know what it is asking for when it wants me to tell it what 'factor' I want to use. Do you know? I thought it would be decimal places but I keep getting zero as a answer.

MRound(0.0785,0.002) should give BankersRounding and it does for all except 0.0194 which on my spreadsheet rounded up to 0.02, not to 0.019 which I would have expected!

Kelbo, I get the same results. I didn't know about changing the 0.001 to 0.002. Why does that give a different result? Maybe I should change a value in one of the other formulas?

So far, your change to 0.002 has produced the most reliable results round a five. But the failure to round down with the four in 0.0194 is confusing to me as well.

Thanks to you both for taking the time to helo me here!

 

[channel7]

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.

 

[Kelbo]

Another try!

I have played around with this problem and come up with this idea which may meet your needs, or point you in another direction.

The formula I have come up with is:

=IF(RIGHT(J4,1)<>"5",ROUND(J4,3),IF(ISEVEN(MID(J4,LEN(J4)-1,1)),ROUNDDOWN(J4,3),ROUNDUP(J4,3)))

I hope you find this interesting and helpful. It first identifies numbers not endig in 5 and rounds normally, then those ending in 5, round down where previous number is even ...

Regards,

Kelbo

 

[barry houdini]

This is a formula I've suggested before - works for "bankers rounding" of 3 decimal places [it's similar to no 11 on your list]. I believe it gives the correct result for all your examples listed

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

 

[channel7]

Another try!

=IF(RIGHT(J4,1)<>"5",ROUND(J4,3),IF(ISEVEN(MID(J4,LEN(J4)-1,1)),ROUNDDOWN(J4,3),ROUNDUP(J4,3)))

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

You guys are awesome with these Excel formulas!!!! And a little FYI, I believe Microsoft is still under the impression that Excel is incapable of handling this particular rounding using only a formula. Microsoft recommends using VBA code to accomplish it for consistent results.

We will definitely do more testing with more sample data and also test it with other decimal places, not just the three decimal places tested here.

In the event the subsequent testing provides correct results, you two just did the impossible. You should tell Microsoft what you have found and see if they will give you a free tshirt or something.

I will let you know here the results we find.

And thank you again!!!

Phillip

 

[barry houdini]

....and also test it with other decimal places, not just the three decimal places tested here.

The formula I posted is only guaranteed to work with 3 decimal places, I imagine that applies to Kelbo's suggestion, too

You can amend mine if you know the number of decimal places you want to round to, i.e. for 4 change to:

=ROUND(A2,4)-(MOD(A2*100000,20)=5)/10000

 

[channel7]

Thanks for the heads up, Barry. I knew the ROUND(A2,3) would have to changed but I did not know I also needed change the other two values. And I wouldn't have known why the formula didn't work with the other decimal places.

Thanks, again!!!!

Phillip