×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Metric Conversion
3

Metric Conversion

Metric Conversion

(OP)
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.

RE: Metric Conversion

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.  

RE: Metric Conversion

2
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

RE: Metric Conversion

(OP)
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.  ?????

RE: Metric Conversion

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

RE: Metric Conversion

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources