Another Approach to Round Where the last Digist is 5

Mike Collett · Feb 5, 2004

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

Are you ready for this?

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

just_jon · Feb 5, 2004

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.

Juan Pablo González · Feb 5, 2004

Or you could use VBA's Round function...

Book1

A

B

C

D

6

Excel'sROUND

VBA'sROUND

7

1.535

1.54

8

1.545

1.55

1.54

9

1.555

1.56

10

1.565

1.57

1.56

Sheet1

You could use a UDF like this:Function VBAROUND(NumberAsDouble, DecimalsAsLong)AsDouble
VBAROUND = Round(Number, Decimals)EndFunction

just_jon · Feb 6, 2004

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?

Juan Pablo González · Feb 6, 2004

Just different calculation methods...

http://support.microsoft.com/default.aspx?scid=kb;en-us;194983&Product=xlw

But it is weird that they have different methods for that.

just_jon · Feb 6, 2004

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!]

Rail · Feb 6, 2004

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?

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

Jay Petrulis · Feb 6, 2004

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?

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

A

B

C

D

1

226.5012669

226

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

Mike Collett · Feb 9, 2004

Thanks Everyone for your assistance.

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

rrdonutz · Feb 9, 2004

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

A

B

C

D

1

Simplified

Power

2

Data

Formula

3

1.495

1.50

4

1.505

1.50

5

1.515

1.52

6

1.525

1.52

7

1.535

1.54

8

1.545

1.54

9

1.555

1.56

10

1.565

1.56

11

1.575

1.58

12

1.585

1.58

13

1.595

1.60

14

1.605

1.60

15

1.613

1.61

16

1.616

1.62

17

4.975

4.98

18

4.980

4.98

19

4.985

4.98

20

4.995

5.00

21

5.005

5.00

22

5.015

5.02

23

4.456

4.46

24

4.557

4.56

25

4.498

4.50

26

2.149

2.15

27

4.549

4.55

28

-1.505

-1.50

29

-1.515

-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

Another Approach to Round Where the last Digist is 5

Mike Collett

New Member

just_jon

Legend

Juan Pablo González

MrExcel MVP

just_jon

Legend

Juan Pablo González

MrExcel MVP

just_jon

Legend

Rail

New Member

Jay Petrulis

MrExcel MVP

Mike Collett

New Member

rrdonutz

Well-known Member

Forum statistics

Share this page

Another Approach to Round Where the last Digist is 5

New Member

Legend

MrExcel MVP

Legend

MrExcel MVP

Legend

New Member

MrExcel MVP

New Member

Well-known Member

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock