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]

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...

Yeah...

It is because it is looking for the number 1 place past the number of places you have.

ie. it is looking for what comes after 0.1203_ Nothing is there, so It can't make it an integer.

I guess I could stick an IF(ISERROR in there, and repeat the first test, but that will make for a long formla...

 

[just_jon]

Does this fit all possibilities?

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

Returns 0.1203 in the instant case.

Book1
	A	B	C	D
1	0.1203	4	0.1203
2
Sheet1

 

[BAlGaInTl]

I seem to be getting the same error with your formula as before.

Actually... I seem to be getting an error everywhere now with that one.

[edit]
error on 1 sig fig now
[/edit]

 

[just_jon]

That errored out on 1; Maybe this?

=IF(AND(MID(A1,FIND(".",A1)+B1-1-INT(LOG10(ABS(A1))),1)="0",INT(MID(A1,MIN(LEN(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)))))

 

[BAlGaInTl]

That errored out on 1; Maybe this?

=IF(AND(MID(A1,FIND(".",A1)+B1-1-INT(LOG10(ABS(A1))),1)="0",INT(MID(A1,MIN(LEN(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)))))

That seems to do the trick!!

Now the question is... How the heck can you turn that into a UDF? I wish I was better with VBA... Still learning...

SF(A1,3)
Where A1 would be the number to round
3 would be the number of sigfigs

So now it can be done, and the previous limitation of the LOG10 method of significant figures as been addressed.

Thanks to everyone for their input and help.

 

[just_jon]

I don't think a UDF will import the required format of your target cell; let me see if I've got some time to check...

 

[BAlGaInTl]

Oops...

I spoke too soon...

Although it will work in all cases for me I believe.....

Number, SigFigs, Result
0.12, 3, 0.12

It should be 0.120

It is quitting time here now... I will think about this over the weekend. I think I can come up with the solution, but it won't be pretty.

 

[Andrew Poulsom]

Here is a UDF:

Function SF(Val As Variant, Fig As Variant) As Variant
    Dim x As Variant
    Dim i As Boolean
    Dim j As Boolean
    x = Fig - 1 - Int(WorksheetFunction.Log10(Abs(Val)))
    i = Mid(Val, InStr(1, Val, ".") + x, 1) = "0"
    j = Int(Mid(Val, WorksheetFunction.Min(Len(Val), InStr(1, Val, ".") + x + 1), 1)) < 5
    If i And j Then
        SF = Int(Val) & Mid(Val, InStr(1, Val, "."), x + 1)
    Else
        SF = WorksheetFunction.Round(Val, x)
    End If
End Function

which is based on the formula you posted. It doesn't resolve your last problem though.

 

[just_jon]

Maybe --

<font face=Courier New><SPAN style="color:darkblue">Function</SPAN> SigDig(Val <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Double</SPAN>, Dig <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Integer</SPAN>) <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Double</SPAN>

<SPAN style="color:darkblue">If</SPAN> (Mid(Val, Application.WorksheetFunction.Find(".", Val) + Dig - 1 - Int(Log10(Abs(Val))), 1) = 0) And _
    (Int(Mid(Val, Application.WorksheetFunction.Min(Len(Val), Application.WorksheetFunction.Find(".", Val) + Dig - 1 - Int(Log10(Abs(Val))) + 1, 1)) < 5)) <SPAN style="color:darkblue">Then</SPAN>
        SigDig = Int(Val) & Mid(Val, Application.WorksheetFunction.Find(".", Val), Dig - 1 - Int(Log10(Abs(Val))) + 1)
    <SPAN style="color:darkblue">Else</SPAN>
        SigDig = Round(Val, Dig - 1 - Int(Log10(Abs(Val))))
    <SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">If</SPAN>

<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">Function</SPAN>

<SPAN style="color:darkblue">Static</SPAN> <SPAN style="color:darkblue">Function</SPAN> Log10(X)
    Log10 = Log(X) / Log(10#)
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">Function</SPAN>

</FONT>

 

[bosco_yip]

"Number, SigFigs, Result
0.12, 3, 0.12

It should be 0.120"
----------------------------------------------------------------

In Cell C1 enter
=TEXT(A1, "#,##0." & REPT(0,B1-1-INT(LOG10(ABS(A1)))))


Regards
Bosco