# 2-way unit conversion

#### muyandrea

##### New Member
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.

### 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
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
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
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
Probably need to change this :

Code:
``ElseIf IsNumeric(Target) Then``

To this :

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

#### muyandrea

##### New Member
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

##### New Member
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

Replies
11
Views
115
Replies
0
Views
31
Replies
1
Views
35
Replies
3
Views
38
Replies
1
Views
46