significant figures problem

[BAlGaInTl]

Hello all...

I'm using a formula that I found elsewhere on the board to rount a number based on significant figures. This is a sample of what I'm using.

ROUND(G32,B11-1-INT(LOG10(ABS(G32))))

Where G32 is the number you want to round, and B11 is the number of sig. figs. desired.

Here is the problem that I have found. It seems that Excel insists on dropping the last number if it is a zero. So, in effect, what you get is not correct if the last significant figure is a zero.

Example. Lets say that I want 2 significant figures.

0.00123 will correctly display as 0.0012

However,

0.00101 will display as 0.001, which is only 1 significant figure, and not 2.

This only happens when the last number is a zero.

Help?

 

[BAlGaInTl]

=SIGN(A1)*SF(ABS(A1),...)

Still the same problem. All of my starting numbers will always be positive, so the above did nothing for me...

I think...

 

[Aaron Blood]

For those unaware...

Apparently, to a physics professor, a zero after a decimal IS significant.

When you find the time, read up on signifcant digits and why the display of information (in a scientific journal for instance) is important.

http://www.purplemath.com/modules/rounding.htm

A quick google search on "Significant Digits" will turn up a multitude of hits spanning several universities around the globe. I'm quite confident this is a very widely accepted standard.

The bottom line is, 1000 and 1000.0 mean something VERY different in a scientific journal. The "implied" difference between these numbers is VERY large.

No solution to offer yet, but I'll look into it some more. Interesting topic, I'll continue to follow.

 

[tusharm]

Don't understand what you did. The code I posted -- and retested just before this message -- gave 0.10 as the answer to =NbrToSigDigits(0.101, 2)

{snip}Tusharm,
I did some thorough testing of your UDF and found it works in almost all cases. The one problem that I ran in to was when trying to round a value less than one, where the value of the number rounding to was a zero.

For example
0.101 rounded to 2 sig figs comes up as 0.1 instead of 0.10
{snip}

 

[bosco_yip]

.........
0.101 rounded to 2 sig figs comes up as 0.1 instead of 0.10

................
Example

1234.5 rounded to 4 sig figs (x = 0) comes out to 1235... great
1234.5 rounded to 2 sig figs (x = -2) gives the value 1200... super
1234.5 rounded to 1 sig fig (x = -3) gives the value 1000... terrific!
1234.5 rounded to 3 sig figs (x = -1) gives #VALUE!...(should be 1230)
wth?

I can't understand why -2 and -3 will work, but -1 will not?

Book1
	A	B	C	D
1	Num	SigFigs	Formula
2	0.00123	2	0.0012
3	0.00101	2	0.0010
4	1.23E-05	2	0.000012
5	12.345	2	12.
6	0.1234	2	0.12
7	0.00123	2	0.0012
8	0.12	3	0.120
9	0.00126	2	0.0013
10	0.12	4	0.1200
11	12.345	2	12.
12	12.345	3	12.3
13	128.567	2	130
14	1234.5	4	1,235.
15	1234.5	2	1200
16	1234.5	1	1000
17	1234.5	3	1230
Sheet1

The formula in C2 is :

=IF(ISERROR(TEXT(A2, "#,##0." & REPT(0,B2-1-INT(LOG10(ABS(A2)))))),ROUND(A2,B2-INT(LOG10(ABS(A2)))-1),TEXT(A2, "#,##0." & REPT(0,B2-1-INT(LOG10(ABS(A2))))))

HTH

Regards
Bosco

 

[BAlGaInTl]

Bosco,

That formula seems to be the best working solution yet. Thanks alot for the input.

I will see if I can turn that one into a UDF as a learning tool on VBA for myself.

Thanks again.

 

[BAlGaInTl]

Ok...

After doing some research and some reading, this is the UDF that I came up with. It is the first one that I have completed really. Thanks to all those that gave input into this thread that made this happen.

This basically takes Bosco's function and turns it into a UDF.

I would be forever greatful if some people could test it, and give me any pointers on how to improve it. It seems to work for all of the numbers that I have tried so far.

Function SF(Val As Variant, Fig As Variant) As Variant
    Dim x As Variant
    x = Fig - 1 - Int(WorksheetFunction.Log10(Abs(Val)))
    If x < 0 Then
        SF = WorksheetFunction.Round(Val, x)
    ElseIf IsError(WorksheetFunction.Text(Val, "#,##0." & WorksheetFunction.Rept(0, x))) Then
        SF = WorksheetFunction.Round(Val, (x - 1))
    Else
        SF = WorksheetFunction.Text(Val, "#,##0." & WorksheetFunction.Rept(0, x))
    End If
End Function

TIA

 

[bosco_yip]

Hi Sdible,

Is it better to use FIXED function in lieu of ROUND function ?

=IF(ISERROR(TEXT(A2, "#,##0." & REPT(0,B2-1-INT(LOG10(ABS(A2)))))),FIXED(A2,B2-INT(LOG10(ABS(A2)))-1),TEXT(A2, "#,##0." & REPT(0,B2-1-INT(LOG10(ABS(A2))))))

Regards

Bosco

 

[BAlGaInTl]

I'm still working on refining this technique... More just out out of spite for excel now.

Here is a formula that gets rid of the problem of decimal places appearing after whole numbers... (ie. 12. )

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

I have also modified my SigFig Function to take care of the same problem.

Function SF(Val As Variant, Fig As Variant) As Variant
    Dim x As Variant
    x = Fig - 1 - Int(WorksheetFunction.Log10(Abs(Val)))
    If x <= 0 Then
        SF = WorksheetFunction.Round(Val, x)
    ElseIf IsError(WorksheetFunction.Text(Val, "#,##0." & WorksheetFunction.Rept(0, x))) Then
        SF = WorksheetFunction.Round(Val, (x - 1))
    Else
        SF = WorksheetFunction.Text(Val, "#,##0." & WorksheetFunction.Rept(0, x))
    End If
End Function

Hopefully, these functions can help someone in the future.

Special thanks to all those who provided input

ray:

 

[Mike Collett]

Hi sdible:

I hope that things are well with you.

The custom Excel function:

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

works very well with our efforts to convert approximately 50,000 metric conversion to 4 signficant figures.

I wish that I had a knowledge of Excel which is at the level of programing demonstrated at this Board.

I noticed, an apparent inconsistency, which is some cases when I try to "SUM" groups of values from this function I do not get a total (i.e. I get 0.000), while in some cases, I do actually get the total of all values produced from the funcion.

Is the result that is produced from this function an acutal number - i.e. which you can use to add or multiply etc?

Or is it text - i.e. the "TEXT" component of the function leads me to suspect that the result migh actually be text?

Thank you

P.S. I have another question; however, I will wait for your response.

 

[Aladin Akyurek]

...The custom Excel function:

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

...

Is the result that is produced from this function an acutal number - i.e. which you can use to add or multiply etc?

Or is it text - i.e. the "TEXT" component of the function leads me to suspect that the result migh actually be text?

...

To audit...

=SUMPRODUCT(--Range)

If the result of this differ from that of

=SUM(Range)

your hypothesis will be confirmed.