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

 

[rrdonutz]

Mike, see if the data and formula below fits your needs for significant digits and banker's rounding. (The only baker's rounding that I know of is the baker's dozen, which is a nice sort of rounding. ) The formula is a slight modification of Stephen Bullen's (via Jay Petrulis) "Power Formula" from http://www.mrexcel.com/board2/viewtopic.php?t=76750&start=10, which I referenced on the first page of this topic. The formula in C3 is:

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

MrE2024a.xls
	A	B	C	D	E	F	G
1		Signif.	Power			Signif.	Power
2	Data	Digits	Formula		Data	Digits	Formula
3	0.5450	2	0.540		0.54445	4	0.54440
4	0.5550	2	0.560		0.54455	4	0.54460
5	1.6500	2	1.600		1.7565	4	1.7560
6	1.7500	2	1.800		1.7575	4	1.7580
7	32.500	2	32.000		32.445	4	32.440
8	33.500	2	34.000		32.455	4	32.460
9	12500	2	12,000		13745	4	13,740
10	13500	2	14,000		13755	4	13,760
11	115000	2	120,000		101450	4	101,400
12	125000	2	120,000		101550	4	101,600
13	0.5445	3	0.5440		0.544445	5	0.544440
14	0.5455	3	0.5460		0.544455	5	0.544460
15	1.7550	3	1.7600		1.75445	5	1.75440
16	1.7650	3	1.7600		1.75455	5	1.75460
17	32.450	3	32.400		32.4445	5	32.4440
18	32.550	3	32.600		32.4455	5	32.4460
19	13650	3	13,600		13748.5	5	13,748
20	13750	3	13,800		13749.5	5	13,750
21	101500	3	102,000		101445	5	101,440
22	102500	3	102,000		101465	5	101,460
Sheet7

 

[Mike Collett]

Thank you rrdonutz

I am very grateful for your efforts; I trust that you were not up to the small hours of the morning on this.

I have looked at the results, which you kindly have provide as well, and they look good to me.

I will do a bit more testing.

If you have time, I would be interested in an general explaination of how your modifiations to Jay Petrulis's forumal actually works.

I wish that I have the level of Excel expertise demonstrated by the Board contributors.

I hope that things are well with you in Round Rock, Texas

From a sunny day on the west coast , thank you

 

[Mike Collett]

Hi again rrdonutz


Further to my earlier message, this morning, I have run a few more test numbers through the formual which you kindly provided

That is:

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

Test Results Summary (and apparent shortfalls):

Using the above formual

0.00101 rounded to 2 signficant figures produces 0.001; the result should read 0.0010

2.2730 rounded to 5 signficant figures produces 2.273; the result should read 2.2730

and finally, 2.95 to 2 significant figures produces 3; the result should read 3.0

There was a lot of debate on the need for including end-zeros , as well as, formula revisions to counter Excel's inherent tendency to drop end-zeros.

See:

http://www.mrexcel.com/board2/viewtopic.php?t=66703&highlight=significant+digits


However, in the end, a few subscribers confirmed the need of that end-zero and offered a couple of formulas

For example:


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

and

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

Unfortunately, although these formula solves the end-zero problem, the don't provide the features of your formual (at the top of this message), and they also appear to provide the result in text format.

Once again, very appreciative of the board's assistance.

Thank you

 

[BAlGaInTl]

Mike,

As I'm a stickler for using the correct rules for rounding and SigFigs, your problem interests me. I think that I may be able to come up with something using Bosco_yips updated formula.

I will see what I can do. In the meantime, I have made a couple of updates to the formula to meet the needs of some of my collegues. You can check them out on the original thread http://www.mrexcel.com/board2/viewtopic.php?p=375339#375339

 

[BAlGaInTl]

Ok....

I tried to combine the formulas, but then the rounding goes back to normal.

My question is this... I know how Odds and Evens round, but what about zeros? Right now, the formula is rounding them up.

I am close to a separate solution, but it is a bear of a formula.

SigFigs in Excel has consumed my life...

 

[BAlGaInTl]

Well, I gave up on the formula since I kept getting errors due to breaking the limit of 7 nested functions. However, using the same concept that I was working on, I came up with the following VBA for you.

Function SF(Val As Variant, Fig As Variant) As Variant
Dim x As Variant
Dim NoDec As Variant
    
    x = Fig - Int(WorksheetFunction.Log10(Abs(Val)))
    NoDec = Replace(WorksheetFunction.Fixed(Val, x + 1), ".", "")
       
    If Right(NoDec, 2) > 49 And Right(NoDec, 2) < 60 And Right(NoDec, 3) Mod 2 = 0 Then
        SF = WorksheetFunction.Fixed(WorksheetFunction.RoundDown(Val, x - 1), x - 1)
    Else
        SF = WorksheetFunction.Fixed(WorksheetFunction.Round(Val, x - 1), x - 1)
    End If

End Function

To use the function, just paste it into a module in your sheet. Then you can use the format =SF(Num,SigFigs) .

This works in all cases that I have tested. The simple Round function in VBA also does not work completly correctly. It only seems to work if your LAST value is the 5. So, when you use significant figures, you have to ignore everything after that 5.

Let me know if this is of use to you.

 

[bosco_yip]

Try the following banker's rounding formula in according to a user-specified number of signficant figures.

The formula is modified of rrdonutz's modified "Power Formula".

=IF(LEN(A22)-1<=B22,TEXT(MROUND($A22,IF(--(RIGHT($A22/10^(INT(LOG(ABS($A22)))-$B22+1),2))=0.5,2,1)*SIGN($A22)*10^(INT(LOG(ABS($A22)))-$B22+1)),"#,##0."&REPT(0,$B22-1-INT(LOG10(ABS($A22))))),MROUND($A22,IF(--(RIGHT($A22/10^(INT(LOG(ABS($A22)))-$B22+1),2))=0.5,2,1)*SIGN($A22)*10^(INT(LOG(ABS($A22)))-$B22+1)))

Hope can helps

Regards

 

[Mike Collett]

Sdible

Thanks so much for taking yet another look at trying to customize a rounding process - in Excel

My apology for not getting back to you sooner: maintaining dialogue is important, particulary when one considers the effort required to fine tune some of these formulas and routines.

Unfortunately, I am a novice Excel user. However, I will try the "VBA" which you kindly provided me - there is no doubt that you spent a great deal of time defining Excel's rounding process.

Perhaps for your interest, and for other board subscribers I 'd would like to briefly convey why I have an interest in setting up a particular rounding process - in Excel.

In short, we need a specialized and defendable rournding process - to convert over 50,000 imperial-based water volume measures to their metric equivalent.

Imperial to metric conversions are, unfortunately, notorious for producing numbers with many decimals

Rounding by the use of signficant figures (which also includes producing numbers with so-called "end-zeros") is required in this circumstance.

Thanks again for your help

 

[Mike Collett]

bosco_yip

Thank you for this suggested approach to rounding - I will try it.

I am looking for the result to be in a number format.

In contrast to the formula, which I am using now provided by Rrdonutz, (see below) the original formula provided the result in text format

However, the formula was still of value too me - that is, it got me started

To sum up what I am looking, ideally in one formula, or a routine:

- rounding a very wide range of numbers (many with decimals) to a specified number of signficant figures

- keeping the end-zeros, which I believe Excel has an inherent tendency to drop

- producing the result in a number format, that is instead of text. and

- invoking a special rounding process only where the last digit to be rounded ends in a 5 - that is:

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

I don't want board members to go back and try an re-invent the wheel - as the formual (below) which was kindly provided by Rrdonutz - amazingly does all, but one, of the above, and accurately too. (impressive)

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


However, the forumal does drops end-zeros

What I have been doing is to mannually add end-zeros in Excel - that is, where I 've noticed that they have been missed.

To end this message, thanks to you and Sdible - I have a couple of new approaches to test

I will let you both know how your efforts turn out

Thank you, again, for a tremdous effort

 

[BAlGaInTl]

After further testing, there are some flaws with my function, but I will fix them for you.

As for keeping the number in text format with the trailing zeros, exel just won't allow it. I have strugled with it for some time. However, there are ways around that if you want to use the rounded number for further calculations. However, I believe that sigfigs should never be used until the final calculation is rounded. Of course, you may be doing something that requires it.

I will see if I can finish that function in the near future... or perhaps take the long formula you are using now and turn it into a function.