Align Primary and Secondary axes

There are instances when there are data series plotted on both the primary and secondary axes. For example, suppose we want to plot the two series A and B in Figure 1, with the elements in column B as the x-axis values. The A series will be a column cart on the primary axis and …

Continue reading ‘Align Primary and Secondary axes’ »

List all folders in a Microsoft Outlook account

Recently, I wanted to create a list of all the folders in my MS Outlook PST file together with the size of each folder. Outlook provides that information through the user interface. Unfortunately, it shows the result in a modal dialog with no way to save the information elsewhere. So, I decided to check if …

Continue reading ‘List all folders in a Microsoft Outlook account’ »

Geocoding with Excel and VBA

Before one can position an address (a street address or a town itself) on a map, it must be converted to geographic coordinates (latitude and longitude). Then, that latitude and longitude is mapped to a particular location on the map. Figure 1 shows several thousand U.S. cities and towns on a Mercator projection map. Well, …

Continue reading ‘Geocoding with Excel and VBA’ »

Extract the last token in a cell

There are times when one wants to extract the last part of a string, say the file name from a string that contains the filename including the path. This short note describes a few ways to do that. The example we will use is the following. Cell A1 contains the value c:\x\yz.jpg. What we want …

Continue reading ‘Extract the last token in a cell’ »

Cardinal (i.e., real) numbers and a log scale for a Column chart x-axis

The x-axis of a Column chart can contains either category values (the x values are equidistant) or dates. Neither of these allows for a truly numeric x axis. This tip shows how to simulate a column chart that has cardinal numbers on the x axis (cardinal numbers contain a sense of ‘distance’ – e.g., 97 …

Continue reading ‘Cardinal (i.e., real) numbers and a log scale for a Column chart x-axis’ »

Introducing TM Retro Slicer beta 1

Those who have used an Excel 2010 slicer may have wanted to see that capability in earlier versions of Excel. I like what Microsoft did with slicers in 2010 and wanted to extend that capability backwards. For those who haven’t used a slicer, it is a filter on a particular pivot field shown as an …

Continue reading ‘Introducing TM Retro Slicer beta 1’ »

Introducing TM Tornado

Tornado diagrams graphically display the result of single-factor sensitivity analysis. This lets one evaluate the risk associated with the uncertainty in each of the variables that affect the outcome. Single-factor analysis means that we measure the effect on the outcome of each factor, one at a time, while holding the others at their nominal (or …

Continue reading ‘Introducing TM Tornado’ »