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]

I'm sorry,

I didn't mean in any way for this to become an argument.

These rules only hold true in your "LAST" calculation. This is about the result that you can report with confidence, not the "actual" calculated value.

1.234 * 1.2 can only be confidently reported as 1.5 since you have some uncertainty in the 1.2.

1.234 * 1.20 can be reported as 1.48 for the same reason.

The rule is that when you multiply numbers, your final answer can only have as many significant figures as the number used that has the least sig figs. Again, these rounding rules are only applied "AFTER" the last calculation is completed.

I think it just comes down to a different way of looking at things mathematically and scientifically. It is all about what you can report with confidence in my case. If I'm confident of 1.2, then I need to be able to report 1.20 so that anyone else looking at my data knows that what I'm confident of. This is a scientific standard, and has been for many years.

In that respect, Goblin is correct in the value that could be reported with confidence considering the distance that light travels. Mathematics tells you one thing, but science only lets you report another.

Again... I didn't want this to be a point of contention. I have gotten a lot of help from this board. I was just looking for a solution to my problem.

I have no desire to get into an argument over this, but this is the first time I've come across this 'significance' -- and most people would consider my academic, consulting, and other work experience mind-numbing.

In all this time I have *never* run across this rule. Which leads me to conclude that what you mention is, at best, a convention, though hardly an universal one.

Even if one were to accept the convention you mention, in the example you quote (1.234 * 1.2 vs. * 1.20), why is the number of digits in the 2nd number more important than the number of digits in the first number? Given that 1.234 * 1.2 is *exactly* 1.4808, I would think the 3 decimal places in the first number would require the result rounded to 1.481.

{sniip}
Yes, it is significant. Many people often abuse significant figures and rounding. Here is the lowdown on zeros...
{snip}Finally, if you follow the rules of significant figures, there is a mathematical difference as well.

1.234 x 1.2 = 1.5
1.234 x 1.20 = 1.48

I used to have the hardest time trying to get my students to understand this when I was a teacher.

 

[BAlGaInTl]

What about tackling this as a UDF?

Say in the format of

SF(A1,2,3)

Where
A1 Would be the number you want to report
2 would be the number of significant figures
3 would be the maximum decimal accuracy (optional?)

So..

12.345 --> 12
0.1234 --> 0.12
0.00123 --> 0.001

Is it possible?

 

[Andrew Poulsom]

A UDF wouldn't help with trailing zeroes. That's a formatting issue and functions can only return a value, not perform actions like cell formatting.

 

[Aladin Akyurek]

What about tackling this as a UDF?

Say in the format of

SF(A1,2,3)

Where
A1 Would be the number you want to report
2 would be the number of significant figures
3 would be the maximum decimal accuracy (optional?)

So..

12.345 --> 12
0.1234 --> 0.12
0.00123 --> 0.001

Is it possible?

I should stay out of this thread, but would this help?

Book3
	A	B	C	D
1	Num	N(Decimals)	Text	Numeric
2	12.345	0	12	12
3	0.1234	2	0.12	0.12
4	0.00123	3	0.001	0.001
Sheet1

Formulas...

C2:

=INT(A2)&IF(B2,"."&LEFT(SUBSTITUTE(MOD(A2,1),"0.",""),B2),"")

D2:

=--(INT(A2)&IF(B2,"."&LEFT(SUBSTITUTE(MOD(A2,1),"0.",""),B2),""))

 

[BAlGaInTl]

Thank for the input.

But that determines the amount of decimal places, not the significant figures.

I came up with a formula for that by using

=IF(B11="","",TEXT(G34,CHOOSE(B11,"#,##0.0","#,##0.00","#,##0.000","#,##0.0000","#,##0.00000","#,##0.000000","#,###.0000000","#,###.00000000")))

Not as pretty as yours, but it worked...

I will keep working on the problem and post here if I find a solution

 

[George J]

Not exactly what you were looking for, but i slightly altered code from Jan Karel Pieterse who posted on the microsoft newsgroup.

http://groups.google.com/groups?hl=...uthor%3Ajkpieterse@netscape.net+roundtodigits

As you had G32 in an example, then changed to G34 i though that this macro could be adapted to suit your needs. Possibly someone can alter it to a "User Defined Function" that you can use in a formula.

What this currently does is when run will take the value of the active cell and ask how many significant places you want this to. I wasn't sure if it would always be the value in B11.

The macro will then put the answer in the cell. Seems to work, but you can try it in a test workbook.


Option Explicit

Sub RoundToDigits()
Dim rCell As Range
Dim dDigits As Double
Dim iRoundDigits As Integer
Dim sFormatstring As String
Dim iCount As Integer
Dim vAnswer As Variant
Dim rRangeToRound As Range
On Error Resume Next
Set rRangeToRound = Selection
If rRangeToRound Is Nothing Then Exit Sub
vAnswer = InputBox("How many digits?", "Rounding function")
If TypeName(vAnswer) = "Boolean" Then Exit Sub
Set vAnswer = Range("B11")
iRoundDigits = Application.Max(1, vAnswer)
On Error GoTo 0
For Each rCell In rRangeToRound.Cells
If IsNumeric(rCell.Value) And rCell.Value <> "" Then
sFormatstring = "0"
If rCell.Value = 0 Then
dDigits = 3
Else
dDigits = Log(Abs(rCell.Value)) / Log(10)
dDigits = -Int(dDigits) + iRoundDigits - 1
dDigits = Application.Min(Len(Abs(rCell.Value)), dDigits)
End If
If dDigits >= 1 Then
sFormatstring = sFormatstring & "." & String(dDigits, "0")
ElseIf dDigits < 0 Then
rRangeToRound.Formula = "=--Text(" & rRangeToRound & ",Rept(""0"",B11)&""E+00"")"
'sFormatstring = sFormatstring & "." & String(iRoundDigits - 1, "0") & "E+00"
End If
rCell.NumberFormat = sFormatstring
End If
Next

End Sub


Hope this is of some use.

 

[BAlGaInTl]

That code does indeed properly round significant figures. At least to the extent that I tested it so far. Thanks for the help.

However, it does so by changing the format of the cell, which as Aladin pointed out can not be acomplished by a UDF.

I don't think it can be converted. However, I would be extremely greatful if someone could prove me wrong. If needed, I could modify the code to work with a user input button that the user could click folowing data input.

Thanks.

 

[Aladin Akyurek]

That code does indeed properly round significant figures. At least to the extent that I tested it so far. Thanks for the help.

However, it does so by changing the format of the cell, which as Aladin pointed out can not be acomplished by a UDF.

I don't think it can be converted. However, I would be extremely greatful if someone could prove me wrong. If needed, I could modify the code to work with a user input button that the user could click folowing data input.

Thanks.

That was Andrew, not me.

Just to complete the formula I posted with rounding:

=ROUND(--(INT(A2)&IF(B2,"."&LEFT(SUBSTITUTE(MOD(A2,1),"0.",""),B2+1),"")),B2)

 

[BAlGaInTl]

Ok... It is almost solved, maybe somebody can help me further.

This actually works...

A1 is the number to round
B1 is the number of significant figures you desire

=IF(AND(MID(A1,FIND(".",A1)+B1-1-INT(LOG10(ABS(A1))),1)="0",INT(MID(A1,FIND(".",A1)+B1-1-INT(LOG10(ABS(A1)))+1,1))<5),INT(A1)&MID(A1,FIND(".",A1),B1-1-INT(LOG10(ABS(A1)))+1),ROUND(A1,B1-1-INT(LOG10(ABS(A1)))))

It is a bit tough to follow... I kept getting lost myself when I was writing it.

Only one problem remains. You get an error in one case, and I can't figure out why. It seems to be when the number is <1, and the number of decimal places is equal to the significant figures you desire.

Number, SigFigs, Answer
0.1203, 4, #VALUE!

I'm sure that this can be cleaned up and fixed, and then turned into a UDF. I would hate to have to program this on multiple spreadsheets.

Thanks for the help so far...

 

[just_jon]

Your problem is here --

=IF(AND(MID(A1,FIND(".",A1)+B1-1-INT(LOG10(ABS(A1))),1)="0",INT(MID(A1,FIND(".",A1)+B1-1-INT(LOG10(ABS(A1)))+1,1))<5),INT(A1)&MID(A1,FIND(".",A1),B1-1-INT(LOG10(ABS(A1)))+1),ROUND(A1,B1-1-INT(LOG10(ABS(A1)))))

Haven't yet figured out the why...