2-way unit conversion

muyandrea

New Member
Joined
May 17, 2011
Messages
5
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 ;)
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,770
Office Version
  1. 2010
Platform
  1. Windows
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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,276
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.

Code:
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

Banned
Joined
Apr 11, 2006
Messages
2,328
Probably need to change this :

Code:
ElseIf IsNumeric(Target) Then

To this :

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

muyandrea

New Member
Joined
May 17, 2011
Messages
5
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 :rolleyes:
 

Watch MrExcel Video

Forum statistics

Threads
1,108,730
Messages
5,524,491
Members
409,584
Latest member
RedHelp

This Week's Hot Topics

Top