# Another Approach to Round Where the last Digist is 5

#### Mike Collett

##### New Member
High Eveyone ...

Caution: First Time Subscriber!

Emotional State: Very impressed with the E-mail dialogue on the "Signficant Figure Problem".

My Problem:

Excel's "ROUND" function rounds up or down based five

Rouding as recommented by the "1973 National Standard of Canada Metric Practice Guide" differs from the standard result provided by Excel's "ROUND" function.

The Guide Recommends:

Only when the number being rounded ends in 5:

If the number before 5 is odd (i.e. 1, 3, 5, 7 and 9) round up.

If the number before 5 is even (i.e. 2, 4, 6, and 8) round down.

Again the key point is that this rule is only if the last digit is 5, all other rounding rules would apply.

Example:

Test Numbers Excel Desired Result

1.535 1.54
1.545 1.55 1.54
1.555 1.56
1.565 1.57 1.56

I would like to:

(1) use the "sdible" and "bosco_yip" solution for the "signficant figures problem" (Jan, 29,2004)

That is,

=IF(B8-1-INT(LOG10(ABS(A8)))<=0,ROUND(A8,B8-1-INT(LOG10(ABS(A8)))),IF(ISERROR(TEXT(A8,"#,##0."& REPT(0,B8-1-INT(LOG10(ABS(A8)))))),FIXED(A8,B8-INT(LOG10(ABS(A8)))-1),TEXT(A14,"#,##0."&REPT(0,B8-1-INT(LOG10(ABS(B8)))))))

(2) However, with a modified "ROUND" function that works as indicated above. (i.e. Recommened by the 1973 Guide)

Thanks Everyone

Mike ### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
I suppose you could create a function which would --

1] evaluate the last digit of the number, then
2] if it were not a 5, then ROUND as usual, else
3] if it were a 5, and the LEN()-1th character were even then ROUNDDOWN, else
4] ROUNDUP.

Haven't got time now, but will try to check back.

Or you could use VBA's Round function...
Book1
ABCD
6Excel'sROUNDVBA'sROUND
71.5351.541.54
81.5451.551.54
91.5551.561.56
101.5651.571.56
Sheet1

You could use a UDF like this:<font face=Courier New><SPAN style="color:#00007F">Function</SPAN> VBAROUND(Number<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Double</SPAN>, Decimals<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Long</SPAN>)<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Double</SPAN>
VBAROUND = Round(Number, Decimals)<SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Function</SPAN></FONT>

Didn't know the functions bevahed differently, Juan! Just another oops in the MS code, or on purpose so as to give alternatives, do you think?

Yeah; wouldn't you have thought that, having written onev of the functions, that would have been incorporated into the other. I know that Baker rounding is the "equitable" method, however it would cause mas confusion to most in ordinary business, I think. [Hey, ROUND didn't work!]

Mike, Hi....

I'm the guy who started all this chatter about rounding ie OP "Rounding, simple, but not so simple! I'm begining to think the later! I'm intrigued with the formula you posted, however, I'm sort of a novice at Excel!

Could you explain why you have different cell locations in your formula?

=IF(B8-1-INT(LOG10(ABS(A8)))<=0,ROUND(A8,B8-1-INT(LOG10(ABS(A8)))),IF(ISERROR(TEXT(A8,"#,##0."& REPT(0,B8-1-INT(LOG10(ABS(A8)))))),FIXED(A8,B8-INT(LOG10(ABS(A8)))-1),TEXT(A14,"#,##0."&REPT(0,B8-1-INT(LOG10(ABS(B8)))))))

You have B8, A8, & A14 cell locations in your formula where my value is only in one cell that I would like to round. Could you explain how to do this with a single cell number!

For instance, a desired result I would like to achieve on my spread sheet would be:

29/7.104*55.485=226 (the cell with the answer in it...226 I would like rounded to the ten's digit as seen in the equation listed above). Most or all of the cell formula's that I have tried from this forum round this number to 227. The actual number if done on a calculator is 226.501266.

Since I want this particular number to round to the ten's digit and the tenth's digit is a five, Excel should look to see if the ten's digit is even or odd. Since in this case it's even...round down. So therefore my desired result should be 226 not 227 as in most formulas.

Any suggestions!

Thanks!

Rail

Rail said:
Mike, Hi....

I'm the guy who started all this chatter about rounding ie OP "Rounding, simple, but not so simple! I'm begining to think the later! I'm intrigued with the formula you posted, however, I'm sort of a novice at Excel!

Could you explain why you have different cell locations in your formula?

=IF(B8-1-INT(LOG10(ABS(A8)))<=0,ROUND(A8,B8-1-INT(LOG10(ABS(A8)))),IF(ISERROR(TEXT(A8,"#,##0."& REPT(0,B8-1-INT(LOG10(ABS(A8)))))),FIXED(A8,B8-INT(LOG10(ABS(A8)))-1),TEXT(A14,"#,##0."&REPT(0,B8-1-INT(LOG10(ABS(B8)))))))

You have B8, A8, & A14 cell locations in your formula where my value is only in one cell that I would like to round. Could you explain how to do this with a single cell number!

For instance, a desired result I would like to achieve on my spread sheet would be:

29/7.104*55.485=226 (the cell with the answer in it...226 I would like rounded to the ten's digit as seen in the equation listed above). Most or all of the cell formula's that I have tried from this forum round this number to 227. The actual number if done on a calculator is 226.501266.

Since I want this particular number to round to the ten's digit and the tenth's digit is a five, Excel should look to see if the ten's digit is even or odd. Since in this case it's even...round down. So therefore my desired result should be 226 not 227 as in most formulas.

Any suggestions!

Thanks!

Rail

If you want 226 as the result, you will have to round the calculation and then re-round to even.

With the following UDF

Code:
``````Function RoundToEven(num, Optional digits)
If IsMissing(digits) Then digits = 0
RoundToEven = Round(CDbl(CStr(num)), digits)
End Function``````

I get the following...
Book5
ABCD
1226.5012669226
2
3
4
5
6
7
8
9
10
Sheet1

With either rounding convention, your unadjusted initial formula will round to 227 because the calculation results in a decimla part that is greater than 0.5, regardless of how many decimals you show (assuming precision as displayed is not used).

I am new to this form of problem solving; and I am very impressed with the assistance which is quickly provided.

I made a couple of dumb mistakes in cell references, while copying the formula, which is used to round a cell's value (e.g. A8) to a specified number of singnificant fures (e.g. B8)

With correction:

the "sdible" and "bosco_yip" solution for the "signficant figures problem" (Jan, 29,2004)

That is,

=IF(B8-1-INT(LOG10(ABS(A8)))<=0,ROUND(A8,B8-1-INT(LOG10(ABS(A8)))),IF(ISERROR(TEXT(A8,"#,##0."& REPT(0,B8-1-INT(LOG10(ABS(A8)))))),FIXED(A8,B8-INT(LOG10(ABS(A8)))-1),TEXT(A8,"#,##0."&REPT(0,B8-1-INT(LOG10(ABS A8)))))))

My novice level of Excel, not withstanding, I don't believe that my problem has been solved.

The additional rounding rule - i.e. to be used with the above is

Only when the number being rounded ends in 5:

If the number before 5 is odd (i.e. 1, 3, 5, 7 and 9) round up.

If the number before 5 is even (i.e. 2, 4, 6, and 8) round down.

Example:

Start with 1.535, Excels rounds up to 1.54 - this is "OK" ("3" is odd)

Start with 1.545, Excel rounds up to 1.55 - this is not "OK" - instead I desire a result of 1.54 (in keeping with the above, new rule "4" is even)

Start with 1.555, Excel rounds up to 1.56 - this is "OK" ("5" is off), and finally

Start with 1.565, Excel rounds up to 1.57 - this is not "OK" - instead I desire a result of 1.56 (again, in keeping with the above, new rule - "6" is even)

My apology, if I have indeed missed the solution, which was provided in last week's communication.

Regards everyone

Hi Mike,

I think you've gotten more than 1 solution. Below is a sample data set with my proposed formula ("Simplified Formula" column), as well as a variation of Stephen Bullen's (via Jay Petrulis) "Power Formula" from http://www.mrexcel.com/board2/viewtopic.php?t=76750&start=10. I would post the VBA suggestions, too, except my version of Visual Basic for Excel 97 chokes on the ROUND function. (And personally, I prefer the VBA formulas for their simplicity.)
MrE2024.xls
ABCD
1SimplifiedPower
2DataFormulaFormula
31.4951.501.50
41.5051.501.50
51.5151.521.52
61.5251.521.52
71.5351.541.54
81.5451.541.54
91.5551.561.56
101.5651.561.56
111.5751.581.58
121.5851.581.58
131.5951.601.60
141.6051.601.60
151.6131.611.61
161.6161.621.62
174.9754.984.98
184.9804.984.98
194.9854.984.98
204.9955.005.00
215.0055.005.00
225.0155.025.02
234.4564.464.46
244.5574.564.56
254.4984.504.50
262.1492.152.15
274.5494.554.55
28-1.505-1.50-1.50
29-1.515-1.52-1.52
Sheet7

The formula in B3 is:

=IF(MOD(ROUND(ABS(A3)*1000,0),20)=5,ROUNDDOWN(A3,2),ROUND(A3,2))

and the formula in C3 is:

=MROUND(A3,IF(VALUE(RIGHT(A3/10^(INT(LOG(ABS(A3)))-3+1),2))=0.5,2,1)*SIGN(A3)*10^(INT(LOG(ABS(A3)))-3+1))

--Tom

### Forum statistics

1,203,064
Messages
6,053,320
Members
444,653
Latest member
Curdood ### 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