Excel Bi-Directional Temperature Converter

A revised version of this content is now available at blog.officewizkid.com


I recently received a question on how to develop a bi-directional temperature converter in Excel. Sounds simple enough but the trick here is the bi-directional statement.

The requirement is as follows:

Let’s assume that Cell B4 represents temperatures in Fahrenheit and Cell D4 represents temperatures in Celsius. Changing one of them will need to automatically change the other one.

The end result should look like the following:

Excel Temperature Converter

Developing an expression that can do a one-directional conversion form Fahrenheit to Celsius is easy. Just type in D4 the expression:


=(B4-32)*5/9

Developing an expression that can do the reverse (still a one-directional conversion) form Celsius to Fahrenheit is easy. Just type in B4 the expression:


=D4*9/5+32

But doing both is much tougher. It creates bi-directional dependency between two cells that as far as i can tell can not be done with simple expressions.

The workaround I found is by going down-level to the VBA level and writing a macro to handle a “worksheet change” event. The event will be triggered every time a cell is changed, making this solution a little bit heavy when dealing with large Excel files. It also requires the Excel to be saved as .xlsm or Excel Macro-Enabled Workbook.

Here is a capture of the code:

Excel Worksheet Change

Let’s review these statements one by one.

First please open the VBA editor in Excel (see old post about that) and use the drop-down lists to first select “Worksheet” and then “Change”. This will generate for you an empty Private Sub called Worksheet_Change.

The logic here is not too complex and has two parts:

Check that the B4 (or D4) cell was part of the Target change range, for that we check if the intersection between Target and “B4” is not empty.


If Not Intersect(Target, Range("B4")) Is Nothing Then

Next we use the “ActiveSheet.Range” function to select cell “D4” and set its value based on conversion calculation as a function of “B4” value.


ActiveSheet.Range("D4").Value = (ActiveSheet.Range("B4").Value - 32) * 5 / 9

Once completed, we can exit from that Sub.

 

Advertisements

Excel Icon Sets – Simple Example

They say that picture is worth a thousand words and that is even more accurate when dealing with numbers.

Sometimes in Excel you want to show Icons that provide quick meaningful indication for the value of the rows. A simple way to do that is by using Excel’s icon sets conditional formatting.

For a simple example, let’s assume that we have a table with product pipeline week-over-week and we want to provide visual indication if the pipeline is growing or declining using one of the following icons:

arrows

Let’s assume our pipeline table looks something like this:

pipeline

And we want to add an icon beside the “Delta Prev Week” column with the icon.

To do that (on a Excel:Mac 2011) we will use conditional formatting:

1. In the menu select “Format” and then “Conditional Formatting…”. You should see the following popup:

conditional format

2. Click on the Plus sign and select Style as “Icon Sets”.

3. Microsoft provides multiple predefined sets of icons so select the Icon Set that works best for your scenario. In this case we want use the arrows.

4. Setup the value ranges. In this case, everything above zero is green, zero is yellow and everything below zero is red. The result should be something like this:

format rule

5. Finally select the range for activating the conditional formatting by typing directly in the “Applies to” column. In this case I selected the entire column D:

format rule scope

6. The result is an icon assigned to column D with the needed icon:

pipeline with arrows

There are also options to show only the icons and change the order of the icons.

See more information at Microsoft Office Talk here (It was written for Office 2007 for Windows but is still relevant).

Note that unfortunately at least in this release Microsoft does not provide an elegant way to load your own preferred icons. This is unfortunate and requires a weird workaround.

Excel VLookup – Simple Example

VLookup is an Excel search function. See the official definition at the Microsoft Office site. It is part of a family of search functions that includes Lookup, VLookup, HLookup and Match. Each one with a slightly different behavior.

The VLookup syntax seems abstract and complex so instead of explaining the syntax let’s focus on a use case:

Translation from key to its value. Such as in the case of translation from id to name.

For example, let’s assume in one place in the Excel worksheet we have a table of Customers that looks something like this:

Customer TableThe Customer table has pairs of Customer IDs and the associated Customer Names.

Now in a different place in the worksheet we have a Pipeline Table with Customer ID and Opportunity details. Something like this:

Pipeline table with no customer nameNote that in the Pipeline Table we have at column B the Customer ID. The big ask is to translate the Customer ID to Customer Name so that we can see who the customer in a more human readable fashion. See the empty column C above.

The VLookup for cell C2 will be as follows:
=VLOOKUP(B2,'Customer list'!A:B,2,TRUE)

Or in English:

  • First parameter (B2): Take the value of cell B2
  • Second parameter (‘Customer list’!A;B): Search for the first row containing this value in the “Customer list” table that has two columns. Column A and column B.
  • Third parameter (2): When such row is found, use the second column (column B) of that row to place the value at C2.
  • Fourth parameter (TRUE): The last parameter with value TRUE means that the search uses Exact match.

The final result is a new table with the customer name populated:

Pipeline table with customer name

And that is the simple example of when and how to use VLookup.

How to clean Excel Cells from extra blanks

Simple problem: Importing external data to Excel Cell with values that include extra spaces either at the front or at the back of the values. I.e. Instead of having values like “My Value” the actual value is ”   My Value   “.

So how do we normalize the values?

Excel provides a function called Trim for this purpose exactly, the function receives a single value and returns the value with spaces trimmed on the left and right side of the value, while spaces in the middle of the string remain untouched.

Example Result
Trim ("   My Value") “My Value”
Trim ("My Value   ") “My Value”
Trim ("   My Value   ") “My Value”

See the Microsoft documentation about the Trim function here.

In addition there are function to remove spaces only at the front or only at the back of the value, see LTrim, RTrim, and Trim Functions.

Excel Global User Defined Functions

Let’s assume you developed a great user defined function (See here and here). The user defined function (UDF) is available for this specific Excel file and works as you would expect.

But what happens when you need to create a new Excel file that also needs to use the same UDF?

For now, you simply have to re-create the UDF manually in every Excel file you create. And that is not really great from couple of reasons: First it is annoying. Additional manual steps to do every time you create a new Excel file. The second reasons is that if you decide to improve the UDF, you now have to go back to each Excel file and manually implement the change in the UDF again. And that is really not fun and a potential bigger issue with multiple revisions of the same functions running around your file system.

Luckily Microsoft provides a way to create Global UDFs. It is global in the sense that it is automatically enabled to every Excel file running in your machine. The trick is to turn the UDF inside an Excel Macro-Enabled-Workbook file (.xlsm) to an Excel Add-In file (.xlam) and save this new file in the right location so it is always enabled.

As a reminder this is our beloved Half function:

Function Half(value)
Half = value / 2
End Function

Instructions below explain how to convert the workbook to Add-In and how to enable it:
1. Edit the Excel Macro-Enabled-Workbook file (.xlsm).
2. In Excel, go to file properties and change the title to a meaningful name. Let’s say “Global-Functions”.
3. And save-as Excel Add-In (.xlam)
Save-as Excel Add-in

4. Go to the Finder and copy the new file Global-Functions.xlam to the Excel’s Add-In folder.
If is a rather “secret” folder and it changes from Windows to Mac and across every Excel version.

In my case for Excel 2011 on Mac it is in this location:
Applications->Microsoft Office 2011->Office->Add-Ins

5. The last thing we have to do is to enable the Add-In. Open Excel. Click on the Tools menu. Select the Add-Ins… option. And a pop-up windows will be opened.

6. Click on the checkbox to enable the Add-In.

Excel Enable Add-In

And that’s it. Now every time you create a new Excel file the UDF will be automatically available.

As always, more information is available in the Microsoft site here.

Excel User Defined Function With Optional Parameters

In a previous post (long time ago) we talked about creating a simple user defined function (UDF) called Half:


Function Half(value)
Half = value / 2
End Function


In this post I would like to talk about UDF that accepts optional arguments.

Here is a simple example for that case. Requirement to build a function named “Div”:

  • The function accepts as one argument named “Value”.
  • The function also accepts a second argument named “divideBy”.
  • As you can guess, “Div” calculates a division of “Value” by “divideBy”.
  • But “divideBy” is optional. If “divideBy” is not present then the function assumes a constant value of 1. I.e. Divide “Value” by 1.

To meet these requirements we need two things:

The first thing we will need is the ability to define an argument as optional: VBA provides a simple way to declare arguments as optional by simply prefixing the argument with the text “Optional”.

The second thing we will need is the ability to know if a given argument is present or not: For that VBA provides another very simple function named “IsMissing”. The function accepts an argument and returns a boolean. Value of “True” means that the argument is not present.

With that we have everything we need to write the function Div:

2015 VBA user defined function with optional parameters

Very simple indeed! Give it a try!

Also read some of the additional information at:

IsMissing Function