2-way unit conversion

[muyandrea]

Hi,

I'm making a unit conversion table in Excel.. figured out the CONVERT fomula; however, i'd like to have a formula that works 2 ways.

For example: http://www.wbuf.noaa.gov/tempfc.htm

This will reduce the cells needed for this operation from 4 to 2-3.

Please let me know! Thanks

 

[shg]

You can't have a two-cell solution without VBA. Here's another way with four cells, though.

<TABLE style="WIDTH: 431pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=573><COLGROUP><COL style="WIDTH: 37pt" width=49><COL style="WIDTH: 17pt; mso-width-source: userset; mso-width-alt: 1024" width=22><COL style="WIDTH: 37pt" width=49><COL style="WIDTH: 8pt; mso-width-source: userset; mso-width-alt: 512" width=11><COL style="WIDTH: 332pt; mso-width-source: userset; mso-width-alt: 20224" width=442><TBODY><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 37pt; HEIGHT: 12pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 height=16 width=49 align=right>100</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 17pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 width=22>C</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e7e7e7; WIDTH: 37pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl32 width=49 align=right>212</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e7e7e7; WIDTH: 8pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl32 width=11>F</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 332pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 width=442>A1: Input</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 height=16> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31>B1: Input</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 height=16> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31>C1: =CHOOSE(MATCH(B1, {"C","F"}), CONVERT(A1, "C","F"), CONVERT(A1, "F","C"))</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 height=16> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31>D1: =LOOKUP(B1, {"C","F"}, {"F","C"})</TD></TR></TBODY></TABLE>

 

[AlphaFrog]

Can't do it with two cells and formulas. A cell can't be a formula and also accept typed-in input.

It could be done with VBA though.

<br /><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;;">Fahrenheit</td><td style="font-weight: bold;text-align: center;;">Celsius </td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">212.0</td><td style="text-align: center;;">100.0</td></tr></tbody></table>

To install the code below...

• Right-click on the sheet tab
• Select View Code in the pop-up menu
• Paste the code below in the VBA edit window.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Range("A2:B2"), Target) Is Nothing Then
        Application.EnableEvents = False
        If IsNumeric(Target) Then
            Select Case Target.Address(0, 0)
                Case "A2": Range("B2").Value = [Convert(A2, "F", "C")]
                Case "B2": Range("A2").Value = [Convert(B2, "C", "F")]
            End Select
        Else
            Range("A2:B2").ClearContents
        End If
        Application.EnableEvents = True
    End If
    
End Sub

 

[Boller]

Probably need to change this :

ElseIf IsNumeric(Target) Then

To this :

If IsNumeric(Target) And Target <> "" Then

 

[muyandrea]

Thank you everyone! all these posts were extremely helpful, I got what I wanted.

Any ideas on useful resources where I can learn how to use VBA?

Regards, keep up the good work

 

[muyandrea]

You can't have a two-cell solution without VBA. Here's another way with four cells, though.

This didn't work for me.. and i couldn't figure out what the problem is