Excel cells as input AND output?

October 24, 2006 1:05 PM

Excel wizards: is it possible to create a temperature conversion calculator (Celsius, Fahrenheit, and Kelvin) using only 3 cells for the interface? I have the conversions and know how to do conditional formulas, but what tricks would be involved in having each cell handle both output and potential input?
posted by Tubes to Computers & Internet (15 answers total)

Sounds like this isn't quite what you're looking for, but you could create a form that has three text boxes; you could use either a button to evaluate the contents of the cells & change the values accordingly, or you could use an afterupdate event to do the same.

Not sure if there's an afterupdate (or equivalent) event that could be applied to cells, but if there is, that would be another way to go.

You can't, as far as I know, have a formula in one cell update the contents of a different cell, which is what I think you'd need if you wanted to avoid using VBA.
posted by treepour at 1:21 PM on October 24, 2006

Short answer is no because a single cell can't handle both output (formula) and take input. As soon as you type over the formula as input, it's gone.

You can however, work around it with VBA. But that requires either an event trigger or a manual button macro.
1. For example, using treepour's example, you could set an event that checks the Celsius cell. When there's a change in the cell, it overrides whatever is written in the Fahrenheit and Kelvin cells and forces a calculation into those cells based on the value of the Celsius cell.
2. With buttons, you could have 1 button for each temperature scale. Click the Celsius button and it uses the value in the Celsius cell and converts it into the appropriate values in the Fahrenheit and Kelvin cells, again overriding whatever was already in them. Same for the other 2 buttons.
posted by junesix at 1:43 PM on October 24, 2006

Cell 1 = input
Cell 2 = C or F
Cell 3 = =if(Cell2="C",,)

cheap 'n nasty, but it'll work
posted by defcom1 at 1:43 PM on October 24, 2006

uhh.. sorry didn't read the post right..
posted by defcom1 at 1:45 PM on October 24, 2006

If you're open to using a little bit of VBA, you could use the Worksheet_Change event (in the worksheet, not as a separate module) combined with the Target.Address property to determine which cell was changed, and change the other two accordingly.

The problem you may have is an endless loop of cascading changes events (since changing a cell will trigger the Worksheet_Change event, which means the sheet's changed, which will trigger another Worksheet_Change event, etc.)

One way you could probably avoid it would be to only change the "other" cell contents if the new value is different; I think that would get rid of the problem of a cascading event. For example, assuming you have cells named "Fahrenheit," "Centigrade" and "Kelvin", paste the following code into the code behind the worksheet in which each of those cells reside:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim C as Long, K as Long, F as Long

C = CLng(ActiveSheet.Range("Centigrade").Value)
K = CLng(ActiveSheet.Range("Kelvin").Value)
F = CLng(ActiveSheet.Range("Fahrenheit").Value)
If Target.Address() = ActiveSheet.Range("Centigrade").Address Then
If K <> (C + 273) Then ActiveSheet.Range("Kelvin").Value = C + 273
If F <> ((1.8 * C) + 32) Then ActiveSheet.Range("Fahrenheit").Value = (1.8 * C.Value) + 32
ElseIf Target.Address() = ActiveSheet.Range("Kelvin").Address Then
If C <> (K - 273) Then ActiveSheet.Range("Centigrade").Value = K - 273
'Kelvin-to-F conversion here
ElseIf Target.Address() = ActiveSheet.Range("Fahrenheit").Address Then
If C <> ((F - 32) / 1.8) Then ActiveSheet.Range("Centigrade").Value = ((F - 32) / 1.8)
'F-to-Kelvin conversion here
End If

End Sub
posted by Doofus Magoo at 1:55 PM on October 24, 2006

As far as I know, cells can't handle input and output both.

If you're looking for cell economy, there are a few tricks that might be useful:

You could input both the value and the units in the same cell and have excel parse the input using some of the string functions. For example, if the input is in a form that looks like 120C, 450K, 22F etc., you could make use of the following functions:

LEN returns the length of a string

RIGHT without any parameters just gives the rightmost character, i.e. the units. With the parameter n, it returns the n rightmost characters. LEFT works the same way.

So if the input is in the format above, any time I wanted to reference the units, I would use:

RIGHT(ref. cell)

And any time I wanted to reference the value, I would use:

LEFT(ref. cell, LEN(ref. cell)-1)

You can also output the 2 conversions in the same cell, using the CONCATENATE function, which you can use to string together different outputs into a single line of text.

For example, to output Celsius and Kelvin values for F input, you would write:

CONCATENATE(value Celsius," C, ",value Kelvin," K")

which would return " 0 C, 273.15 K" for an input of 32F.

Using these tricks, you could create an interface of 2 cells, one for the input (which would have to follow whatever format you decided on), and an output line that would output the 2 other units.

To do this would involve a lot of nested IF commands to determine which set of outputs and equations to use, as well as fairly complex references to the units and values, but you can definitely compress it into one long equation and write it into the output cell.
posted by SBMike at 2:06 PM on October 24, 2006

I could imagine a setup with a whole lotta IF statements where you enter a number in cell 1 and "C" or "F" or "K" in cell 2, and the conversion in both of the other two formats shows up in cell 3. That's three cells, three values. But it's cludgey.
posted by raf at 2:59 PM on October 24, 2006

...and looking, that's pretty much what SBMike says, too.
posted by raf at 2:59 PM on October 24, 2006

A neat VBA solution. Paste this into a Worksheet object and define CELSIUS, FAHRENHEIT and KELVIN singe-cell cell ranges. Clicky for example Excel file.

Option Explicit
Dim stop_events As Boolean

Sub Set_Temperatures(c, f, k)
ActiveSheet.Range("CELSIUS") = Round(c, 2)
ActiveSheet.Range("FAHRENHEIT") = Round(f, 2)
ActiveSheet.Range("KELVIN") = Round(k, 2)
End Sub
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If stop_events = True Then Exit Sub
stop_events = True

If Not IsNumeric(Target.Value) Then
Set_Temperatures 999, 999, 999
stop_events = False
Exit Sub
End If

Dim x
x = Val(Target.Value)
If Target.Address = ActiveSheet.Range("CELSIUS").Address Then Set_Temperatures x, x * (9 / 5) + 32, x + 273.15
If Target.Address = ActiveSheet.Range("FAHRENHEIT").Address Then Set_Temperatures (x - 32) * (5 / 9), x, (x + 459.67) * (5 / 9)
If Target.Address = ActiveSheet.Range("KELVIN").Address Then Set_Temperatures x - 273.15, x * (9 / 5) - 459.67, x

stop_events = False
End Sub

Excel for Mac is naaaasty. Had to resort to Parallels.
posted by Well that's a lie at 3:09 PM on October 24, 2006 [1 favorite]

Going on from sbmike's suggestion, the formula is (for input into cell a2)


=IF(RIGHT(A2)="k",VALUE(LEFT(A2,(LEN(A2)-1)))-273.15&"°C, "&(VALUE(LEFT(A2,(LEN(A2)-1)))*(5/9))-459.67&"°F",IF(RIGHT(A2)="C",VALUE(LEFT(A2,(LEN(A2)-1)))+273.15&"°K, "&(VALUE(LEFT(A2,(LEN(A2)-1)))*(9/5))+32&"°F",IF(RIGHT(A2)="F",(VALUE(LEFT(A2,(LEN(A2)-1)))+459.67)*(5/9)&"°K, "&(VALUE(LEFT(A2,(LEN(A2)-1)))-32)*(5/9)&"°C","Please input figure in correct format (eg '100F' or '50K' or '38c')")))
posted by pompomtom at 6:04 PM on October 24, 2006

(...but, you know, all in one line, with a space at the end of the first three lines above...)
posted by pompomtom at 6:05 PM on October 24, 2006

Well that's a lie's solution works really well, although there's a built-in function to stop the spiralling loop-of-death with respect to the worksheet updates...

delete all the bits that refer to "stop-events" (i.e. lines 2, 10, 11, 15, 25)
immediately prior to the "If Target.Address..." block, insert the line "Application.EnableEvents = False", and immedately after it, insert "Application.EnableEvents = True"

Bob *is* your uncle, doncha know?

( @Well that's a lie - nice work!)
posted by Chunder at 3:54 AM on October 25, 2006

Response by poster: Good stuff, good stuff! I got started with sbmike's solution last night, and came up with a formula almost the same as pompomtom's:

=IF(RIGHT(A2)="F",CONCATENATE(ROUND((LEFT(A2,LEN(A2)-1)-32)*5/9,2)," Celsius, ", " ",ROUND((LEFT(A2,LEN(A2)-1)+459.67)*5/9,2)," Kelvin"), IF(RIGHT(A2)="C",CONCATENATE(ROUND((LEFT(A2,LEN(A2)-1)*9/5)+32,2)," Fahrenheit, ", " ",ROUND((LEFT(A2,LEN(A2)-1)+273.15),2)," Kelvin"), IF(RIGHT(A2)="K",CONCATENATE(ROUND((LEFT(A2,LEN(A2)-1)*9/5)-459.67,2)," Fahrenheit, ", " ",ROUND((LEFT(A2,LEN(A2)-1)-273.15),2)," Celsius"), "error")))

It works great.

I can't play with Well That's A Lie's worksheet yet because of workplace-imposed security levels blocking macros, but I'll check it out at home later. It looks really sweet too.

Thank you gurus!
posted by Tubes at 11:37 AM on October 25, 2006

I used Application.EnableEvents initially, Chunder, but it's application wide so might be less than ideal.

Actually, you could probably (and should) do something with On Error...
posted by Well that's a lie at 3:41 PM on October 25, 2006

Now with added Application.EnableEvents and On Error. Clicky.

Option Explicit
Dim stop_events As Boolean

Sub Set_Temperatures(c, f, k)
On Error GoTo OhCrap
Application.EnableEvents = False
ActiveSheet.Range("CELSIUS") = Round(c, 2)
ActiveSheet.Range("FAHRENHEIT") = Round(f, 2)
ActiveSheet.Range("KELVIN") = Round(k, 2)
Application.EnableEvents = True
Exit Sub

OhCrap:
Application.EnableEvents = True
MsgBox "Something went wrong"
End Sub
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not IsNumeric(Target.Value) Then
Set_Temperatures 999, 999, 999
Exit Sub
End If

Dim x
x = Val(Target.Value)
If Target.Address = ActiveSheet.Range("CELSIUS").Address Then Set_Temperatures x, x * (9 / 5) + 32, x + 273.15
If Target.Address = ActiveSheet.Range("FAHRENHEIT").Address Then Set_Temperatures (x - 32) * (5 / 9), x, (x + 459.67) * (5 / 9)
If Target.Address = ActiveSheet.Range("KELVIN").Address Then Set_Temperatures x - 273.15, x * (9 / 5) - 459.67, x
End Sub
posted by Well that's a lie at 1:01 AM on October 26, 2006