#### Mike Collett

##### New Member

- Joined
- Feb 5, 2004

- Messages
- 12

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