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.

 

Outlook on Mac is slow again :(

A new rev of Outlook is out. I used the auto-update option and always just update the environment when something new is available.

This time it is Outlook Version 15.18. It has couple of new features and the performance of Outlook on my OS X El Capitan Macbook Air is horrendous once again. This is a recurring issue.

They will probably find a fix within few weeks and push another update. It happened before. But for now, Outlook on OS X is almost unusable. Every preview of email takes for ages and you see the OSX Spinning Wait Cursor. You want to open an appointment on the calendar. Same thing happens. It takes time.

Continue to be a disappointing experience to the point i am considering to stop updating Outlook automatically and switch to a once in a year update

 

 

Hosting NodeJS + MongoDB for Free

For a while I have been using Google Cloud to host a not-for-profit, not-mission-critical, small-scale MEAN application (MEAN as in Mongodb, Expressjs, Angularjs and Nodejs). Google Cloud Compute infrastructure was my first choice as I had some budget there for free and Google Cloud in general is nice. There are plenty of tutorials and lot’s of discussions with really good Q&As. It is also not too complex to setup the environment, get a small Linux instance and deploy there everything I needed on a single instance.

However, for this particular project it was not perfect and it did include few challenges:

The first thing was that I had to install and maintain the environment myself. Not huge effort but I was thinking about the on-going maintenance, security patches, software updates and all the rest.

The second one is that while it is a not-mission-critical project I still wanted to make sure it is up and running all time without my constant monitoring. There are probably some additional tools I could have used in the Google Compute ecosystem but I was not familiar with those tools. Overall, there were not a lot of interruptions but there was one time where the system went down and I discovered that only after few days.

The third challenge is that this is not-for-profit project so I wanted to reduce costs to zero or at least close to that. At the beginning I had some free budget from Google but after a while I started to pay for the small instance and while not expensive at all, it was still around $15 per month.

I was looking for some Platform-as-a-Service options that will have the NodeJS and MongoDB combination more out-of-the-box, that will take care of all the operations, and will be very cheap.

My first choice there was again to use Google Cloud, but instead of using the Compute side, use their platform support for NodeJS (Get up and running with Node.js on Google Cloud Platform). The challenge there is that as far as I can tell they do not support MongoDB as part of the platform and for that component I will have to use the Compute option again.

Surprisingly, when search for more options I could not find too many free or close to free options. Heroku from Salesforce was one option but  I ended up choosing OpenShift from redhat, mainly because I was just a little bit familiar with that platform.

It took few tutorials to get some of their concepts of cartridges, use of git and the client tool. But once I tried it with a test application everything just made sense. Next I started to migrate my application and the changes to my existing NodeJS were trivial. The main (if not only) changes was to get connection string and ip/port information from the environment variables. Really very smooth effort.

Getting the MongoDB moved was just a little bit more complex and required additional cartridge called RockMongo to upload a dump of the DB.

I also have my own domain name and the setup of the domain routing was not not tough as well.

Overall, as of now the OpenShift platform looks just like what I was looking for, it is easy to setup, easy to maintain, it is free (for up to 3 apps) and so far the performance/stability looks good.

2016-01 OpenShift NodeJS MongoDB

The tutorials/resources used in this effort:

Do-It-Yourself Hosting Static Website at Google Cloud

For a while I hosted my small static website manha10.com using Dropbox (see here). But recently i started to see performance issues and finally also connection reset errors (see here for more details).

I decided to take action and experiment with the Google Cloud platform. Once i passed the mental issue of creating an account there and providing a method of payment, i found the following tutorial:

How to Host a Static Website

The instructions were fairly easy to follow and in a matter of minutes the new website was up and running.

So far from performance and stability it is nothing like what i had before. In other words, it is dam fast and i have seen no error so far.

For now I’m on their trial, but assuming i understand everything correctly and since the size of the website is small and the amount of traffic is also small, this should be a freebie for ever. Time will tell and i will share.

Errors with hosting web-site on Dropbox

As I wrote in a previous entry, I’ve been “hosting” my small static website on top of the Dropbox infrastructure. It worked well for few months but than I started to notice a “The connection was reset” message from time to time.

Recently it becomes a real problem where 1 in 3 calls (my estimate) to the site is terminated with this message.

I’m not sure if this is some kind of control added by Dropbox or something else in the chain is causing this (i also use godaddy to redirect traffic into dropbox and mask the actual URLs).

At this stage I’m searching for alternatives…

Document Instpector on Mac?

I read today a blog entry regarding the hidden information in Office document and what can be done about it (Do You Know What’s Hiding in Your Documents?).

From a personal experience, especially in business negotiation and contracts, it is kind of surprising to see what can be extracted out of a seemingly clean Word document that the author did not really planned to exposed.

Microsoft has a built-in tool to clean all of that called “Document Inspector”. The tool look easy to use and similar functionality is available for Word, Excel, and PowerPoint.

But I was not able to find the tool or the menu option on my Mac… A google search reveal that this capability is simply not there. I’ve seen multiple discussions about this, like this one – Using document inspector to find personally identifiable information.

As a user of a Mac I find these missing capabilities as a real annoyance. I just hope that Microsoft will take that to heart and start to complete these items…

Basic Pivot Table Example

Microsoft describes PivotTables as “A PivotTable report is useful to summarize, analyze, explore, and present summary data. A PivotChart report can help you visualize PivotTable report summary data so that you can easily see comparisons, patterns, and trends. Both reports enable you to make informed decisions about critical data in your enterprise. The following sections provide an overview of PivotTable and PivotChart reports.” (see here).

And indeed PivotTable is a query tool that quickly generates reports on top of a base table of data. Once you get the basics, it is easy, interactive and very useful tool to know.

As always, let’s see that ability in a simple example:

Let’s assume we have a detailed report of product sales transactions. The report has a lot of information about each transaction, including the product code, the geo, the channel, and the amount.

sales data

And we want to run reports and analyze this date. We do that by creating Pivot Table like this:

Select the entire data area and go to the “Data” menu and select “PivotTable…”. A menu like below will open:

PivotTable dialog

Adjust the range if needed and for this simple example just leave all other parameters without a change.

The result is a new sheet that looks like this:

PivotTable Empty

Now we can dynamically select items to display and to summarize.

The simplest example: Let’s create a report of the total sales by GEO.

1. In the “PivotTable Builder” dialog, Drag & Drop the “Geo” field to the “Row Labels”.

2. Drag & Drop the “Price” field to the “Values”. By default the Count function will be applied.

3. Click on the little “i” near the Price field and a new dialog will be shown like below. Select there the function “Sum”.

PivotTable Function selection

4.Format the PivotTable column to show nice $ formatted data.

You should get a report like this:

Basic PivotTable report

Now let’s extend this example and create a report showing Total and Average price by Geo/Channel.

We will continue from the previous report.

1. Open again the “PivotTable Builder” dialog by either clicking on the previous report or by navigating to the PivotTable tab option and clicking on the Builder icon.

2. Drag & Drop the “Channel” field to the “Row Labels”. Make sure it is the second row on that list.

3. Drag & Drop the “Price” field to the “Values”. Should now have two lines under “Values”.

4. Adjust the second “Price” line to use Average function. At this stage the Builder dialog should look like this:

PivotTable Builder 2

5. Format the result report as needed.

You should have a report like this:

Basic PivotTable report 2

We are now masters of the simple PivotTable example.