Unit awareness

[kkjensen]

Hi there excelers...

I'm trying to make an engineering spreadsheet "unit aware" so the cells will contain Kg or lbs depending on what unit the client wants. Does anyone have any ideas on how to go about this in the best way.

I'm not expecting any miracles but I'm sure someone somewhere has had to deal with the same thing before.

thanks,

 

[Oaktree]

Not too sure what you're after, but have a look at the CONVERT function. You could use data-->validation to give the user/s a list of to/from values from which to make the conversion.

 

[kkjensen]

what I'm after...hmmm. Most of my clients come in with planes with handbooks written either in Imperial or Metric. I want to set up my spreadsheets so I can output both so there is no confusion.

Example: If i have inputs in inches (I'll select this from a dropdown list) and could use a concatenate to add "in" to the end of the number but then I couldn't really link to this cell very easily.

It be nice to see "1in. (0.0254m)" in the cell A1 (lets just say) and be able to have equation pointing at it in the regular A1+B1 way and not have to be perpetually concatenating and trim the string to get the number back out again or be losing precision to rounded numbers.

 

[Oaktree]

I still think the CONVERT function is what you're looking for...

Book15
	A	B	C	D
1	Input Value	1
2	Input Unit	in
3	Output Unit	m
4	Output Value	0.0254
Sheet1

 

[kkjensen]

If I were to do a custom format on B1 in your example at add so it were 0"in" is there a way to make an IF statement to detect the "in" and automatically select meters? I guess it would be something like:


=CUSTOMCONVERT(B1)
where: (pardon my humble VBA skills)

Function CUSTOMCONVERT(CELLREF As Variant)

' detect units used
If IsNumeric(Find("in", CELLREF)) Then
    Unit = "in"
ElseIf IsNumeric(Find("m", CELLREF)) Then
    Unit = "m"
End If
  
' select output units
If Unit = "m" Then
    outputunit = "in"
End If

If Unit = "in" Then
    outputunit = "m"
End If


CUSTOMCONVERT = CONVERT(CELLREF, Unit, outputunit)

End Function

...edited a few times...sorry if you see one with glaring mistakes...I typed it instead of copying and made some typos

Am I far off? It doesn't seem to be working and I'm pretty sure it's because it's not finding the unit since it's part of the format and not actually written in the cell. How can I look for the unit in the format?

 

[kkjensen]

I'm not seeing the CONVERT function in my excel 2000...

 

[Joe Was]

I use a DropDown in my Column Label and code that detects what that label is to adjust my column values!

Also:

Converts a number from one measurement system to another. For example, CONVERT can translate a table of distances in miles to a table of distances in kilometers.

If this function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in.