Metric Conversion 3

[enirwin]

I want a function to convert numbers from inches to mm using the following standard rules.

1) If the first significant digit of the metric value is greater than or equal to the first significant digit of the inch value, round the metric value to the same number of significant digits as the inch value.

2) If the first significant digit of the metric value is less than the first significant digit of the inch value, round the metric value to one more significant digit than the inch value.

So, I need to be able to identify the first significant digit and the number of significant digits of an input number. Once I know how to do this I think I can figure out the rest.

Thanks.

 

[zappedagain]

Logarithms are your friend! Try this in Excel:

=FLOOR(LOG(A1),1)+1

to find the number of significant digits (to the left of the decimal point)

and this:

=FLOOR(A1/(10^FLOOR(LOG(A1),1)),1)

to find out what the most significant digit is.

 

[cummings54]

Here's a set of User defined functions that will return the number significant digits. HTH
~~~~~~~~~~~~~

Function SigDigit(ByVal dblInValue As Double) As Integer
SigDigit = 0
Do
SigDigit = SigDigit + 1
Loop Until dblInValue = SDRound(dblInValue, SigDigit)
End Function
Function SDRound(ByVal dblInValue As Double, ByVal intSD As Integer)
Dim intSign As Integer
Const INVLOG10 As Double = 0.434294481903252
intSD = intSD - 1
Select Case Sgn(dblInValue)
Case -1
intSD = intSD - Int(Log(-dblInValue) * INVLOG10)
SDRound = Application.Round(dblInValue, intSD)
Case 0
SDRound = 0
Case 1
intSD = intSD - Int(Log(dblInValue) * INVLOG10)
SDRound = Application.Round(dblInValue, intSD)
End Select
End Function

 

[enirwin]

Thanks to Zappedagain and Cummings54.

This is very close. In fact cumming54's function works well for all numbers except it doesn't identify trailing zeros as significant.

For example, if I have a dimension 1.000 it indicates accuracy out to the third decimal place, so the zeros are significant. cummings54's function only recognizes trailing non-zero numbers. Is there a way to count trailing zeros?

Maybe an alternate approach would work. Is it possible to detect the format setting of the input cell? In my spreadsheets I set the number format to display the appropriate number of decimal places. If the function can detect this setting, then I can use this to set the output formatting based on the rules above. ?????

 

[gearguru]

zappedagain's formulae work for numbers >1 only;
You can try these two formulae to find the location of the number and the number itself:

=INT(A1/10^INT(LOG(A1)))

=IF(A1<1,INT(LOG(A1)), INT(LOG(A1)+1))

 

[zappedagain]

"zappedagain's formulae work for numbers >1 only;" - that's true, I don't usually work in inches; I use mils or microns (or smaller).

If you want to process trailing zeroes you'll need to process the data as text, not a number (1 = 1.000). Visual Basic probably has a function to get the format but that's out of my realm. Good luck.