Another Approach to Round Where the last Digist is 5

[Mike Collett]

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

 

[Mike Collett]

Hi rrdonutz

I am very grateful for your quick and detailed response.

While, hopefully, the is iron is still hot, I would like to ask two related questions - i.e. speaking as an Excel novice.

(1) How would I add your proposed solution(s) to the earlier formula, which rounds the value in a cell to a user-specified number of signficant figures?

Again, that signficant figure rounding formula 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 )))))))

This is used to round a cell's value (e.g. in A8) to a user-specified number of singnificant fures (e.g. in B8)

(2) the second question may be a bit more daunting, i.e. as I don't have a good understanding of how the significan digit rounding formula actually works.

Is the number produced by the significant digit rounding formual a number, which can be added (using Excel's SUM function) or just text, which cannot be added?

I am using the significan digit formual to convert a large data set of imperial volume measures to metric. The formual works very well for this

It appears to me that I am able so use Excel's SUM function to add some columns, of rounded significant figures - i.e. using the signficant digit formula.

However, I find that for other columns comprised of values which are again produced by the significant digit formula I get "0" or "0.00" etc. - when I use the same SUM function at the bottom of a column.

Thank you very much for considering my questions

 

[Yogi Anand]

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

Hi Mike:

Here is another that you may want to try ...

y040209h1a.xls
	A	B	C	D
1	1.535	1.54
2	1.545	1.54
3	1.555	1.56
4	1.565	1.56
Sheet3

Formula in cell B1 is ...

=IF(MID(A1,5,1)="5",IF(MOD((--MID(A1,4,1)),2)=0,ROUNDDOWN(A1,2),ROUNDUP(A1,2)),ROUND(A1,2))

 

[just_jon]

Very nice!

 

[NateO]

The following allows for an input in C1.

Book1
	A	B	C	D
1	1.495	1.5	2
2	1.505	1.5
3	1.515	1.52
4	1.525	1.52
5	1.535	1.54
6	1.545	1.54
7	1.555	1.56
8	1.565	1.56
9	1.575	1.58
10	1.585	1.58
11	1.595	1.6
12	1.605	1.6
13	1.613	1.61
14	1.616	1.62
15	4.975	4.98
16	4.98	4.98
17	4.985	4.98
18	4.995	5
19	5.005	5
20	5.015	5.02
21	4.456	4.46
22	4.557	4.56
23	4.498	4.5
24	2.149	2.15
25	4.549	4.55
26	-1.505	-1.5
27	-1.515	-1.52
Sheet9

=ROUND(ROUND(A1,$C$1)-(--RIGHT(A1,1)=5)*(MOD(MID(ABS(A1),4,1),2)=0)*(0.01-0.02*(A1<0)),$C$1)

The sum function will work on all returns.

 

[Mike Collett]

Thanks NateO

I appreciate your reponse; however, if you look at the data in your table many of the results are not rounded to 2 significant figures.

My apology if I have not properly copied over the forumla that you provided over to my Excel test data.

The goal, is to round a value to a user-specified number of signficant figures.

The sdible formula below is a break through of sorts because it actually accomplishes this goal:

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

The problem, however, it that some of the results are in text format, which cannot be summed - i.e. using Excel's SUM function (all the results will add individually by not summed)

More recently, bosco_yip has kindly simplifed the solution to the significant figure challenge with this formula:

=FIXED($A3,$B3-INT(LOG10(ABS($A3)))-1)

Athough this formual is obviously more succient, all of the results that are produced appear to me to be text - i.e. Excel's SUM function will not work by totaling any of the results.

There is a work-around to the apparent formatting problem, though - i.e. by a copy (1) paste special - multiply sequence.

It would, however, be nice to have a the signficant digit formula to produce numbers

Thanks everyone

 

[just_jon]

You can coerce a total --

Book2
	A	B	C	D
1	45.9989	4	46.00
2	0.987543	3	0.988
3	11.2	2	11
4			57.988
Sheet10

 

[rrdonutz]

Hi Mike,

I see Jon has beaten me to the punch (the !) with respect to coercion. I've kinda lost the thread on what you want, though--is it significant digits, banker's rounding, or both, or none of the above? Anyway, bosco_yip's formula, though elegant, doesn't achieve banker's rounding that I can tell. But maybe I'm interpreting or applying things incorrectly, or maybe it's just not important anymore.

Regards,

Tom

 

[just_jon]

Supposed to be significant digits, Tom -- but, tell you the truth, I still can't understand the rules for 'em!

 

[Mike Collett]

Thank you just_jon

Yes, =SUMPRODUCT(--(range)) solves the SUM problem.

I am very impressed with thethe spirit of this forum.

rrdonutz, kindly remined me that I have one other problem to resolve - i.e. the so-called "baker" rounding technique.

On second look, I think the response from NateO was aimed at solving that earlier request. Unfortunately, the results look inconsistent to me. (Thanks for this Nate).

What I am after is rounding using a specified number of signficant digits; bosco_yip has kindly provided:

That is, =FIXED($A3,$B3-INT(LOG10(ABS($A3)))-1)

combined with the the so-called "baker" rounding method.

That is, perpaps by using single formual or, by running a routine (e.g. comprised of the signficant digit formula and the "baker" rounding solution)


Summarizing "baker" rounding:

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)


Thanks everyone for a watchful eye on the message board, and my apology if I am beating this one to death.

 

[just_jon]

I think the Baker rounding should be a piece of cake, and if I get a minute I think I can post a formula based on LEN that'll work...