Excel Prophet

Let's dive into the weeds of the mappings formula discussed in my earlier posts. Our goal is to translate fields without any hardcoded string literals or direct references to the fields. By dereferencing formulas, we make them adaptable to change and easy to copy/paste formulas without changing the references. First let's review the following mappings formula: This formula uses the header to "map" another field in its row to a mapped value. The from=>to mappings are defined in the "Mappings" tab. The first inner "Index" function gets the value in the "neighborhood" column. This is in LOC 4 and 7 (used to return the default value). Here is the syntax highlighting from that LOC: "MappingsHeaders" is a named range set to the first row of the "Mappings" tab. The second inner "Index" returns the mapped-from range. This is what the offset in the original formula returned. Here's the "ne...

Charles Williams of FastExcel put together a survey asking his visitors to tag the best and worst functions in Excel. Indirect and Offset are high on that list which makes me question my first post's mapping formula; Indirect even got its own Excel's Most Evil Function post! This post fixes my dynamic mapping formula by using hero functions and then looks at the readability and performance differences. The original "SuperNeighborhood" formula was: The new "FastSuperNeighborhood" formula is: I prototyped diagnostic VBA code to calculate the columns and return the execution time. The code sets the application to xlCalculationManual and then calculates the selected cells ten times to get a more consistent response. The new, non-indirect code clocked in at 35ms versus 75ms for the old code. I was kind of surprised to see that because the old code only uses the cells that it needs, whereas the new code is a little more lax re its coverage. However indir...

My first post shows you how to build a clean report in Excel using a basic design principle in computer science: separation of concerns. The idea here is to separate distinct parts of your workbook into separate tabs including: inputs, mappings, data, pivots, presentation. Here is the finished product of a line chart comparing median prices of 1-family homes in Harlem and the UWS: Let's get started with the "Inputs" tab. This is where we'll define the inputs that drive the workbook. For this example, let's create Date and Url named ranges. You can define a NamedRange by highlighting the cell and entering the named range in the top-left ComboBox. Next let's grab the data. For this example, we'll pull home sales data from NYC's OpenData which "is free public data published by New York City agencies and other partners". The nice thing about NYC OpenData is that they provide an OData interface which plugs neatly into Excel. Let's get st...