# Rounding with Excel - many formulas

#### channel7

##### New Member
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...

### Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

#### Kelbo

##### Board Regular
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

##### New Member

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

##### New Member
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

##### Board Regular
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

##### MrExcel MVP
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

##### New Member
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

##### MrExcel MVP
....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

##### New Member
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

Replies
3
Views
121
Replies
34
Views
257
Replies
2
Views
113
Replies
9
Views
487
Replies
4
Views
389

1,191,204
Messages
5,985,262
Members
439,953
Latest member
suchitha

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back