This is a custom Excel function for regularizing 3D data. It uses the same methodology and arrives at the same results as the spreadsheet that goes with Introduction to Regularizing with 3D Data, but it adds the convenience of a single spreadsheet function that automates the calculation processes without taking up space on your spreadsheet or requiring you to install additional add-ins or DLLs.
The VBA code puts a lot of effort into solving the matrix equations to demonstrate several techniques. For example, it uses a sparse matrix data structure that it reorders by a reverse Cuthill-McKee permutation and solves with a Cholesky decomposition. All calculations are performed in VBA and the example spreadsheet includes full source code. Continue reading
An Entire 3D Surface from a Few Measured Points
- An investment banker needs to create an entire volatility surface from a handful of derivatives trades that took place today.
- A chemist needs to know the temperature near the center of a reaction chamber based on only a few temperature measurements scattered around its perimeter.
- An engineer needs to model something with a smooth function of two variables, but he doesn’t know the function and all he has are a few dozen noisy measurement points.
How do they do it? By regularizing their 3D data of course!
The first part of this article showed how we use bilinear interpolation to relate arbitrary input points to locations on an output grid. Now we will look at the fidelity equations, set up second-derivative equations for the 3D data, and find the least-squares solution. The result is a regularized surface in 3D.
For those who like to see the details, the example spreadsheet is available for download. Continue reading
How to Regularize 3D Data
This article shows how to regularize data (3D Data) in an Excel spreadsheet using basic spreadsheet functions. This is remarkably similar to the 2D case, except for a few extra steps to handle the 3D data.
This introduction shows how to set up the 2D interpolation and explains the difference between triangle interpolation and bilinear interpolation. It also shows how to set up the second derivatives for 3D data. The Excel spreadsheet is available for download. Continue reading
In Part 1 of RegularizeData2D, the Excel Spreadsheet Function for Regularizing 2D Data we saw how the program is organized, reviewed some of the differences between theory and implementation, and listed the important programming shortcuts that become available when we depart from rigorous mathematical theory in favor of practical implementation. In this part we will see the inner workings of the Excel spreadsheet function by looking at an example in detail. In the example we will take five “input points” in x and y, and we will regularize them to ten “output points.” The spreadsheet and source code are available for download. Continue reading
Custom Excel Spreadsheet Function for Regularizing 2D Data
This is a custom Excel spreadsheet function that regularizes 2D data. It is an Excel array function that duplicates the results of the spreadsheet calculations we saw in an earlier article about regularizing. This article shows the development of the VBA function and how, as a computer program, its design is different from the mathematical formulation. We arrive at the same result as the spreadsheet example, but we avoid slow operations like multiplying matrices, storing unnecessary data, and solving a matrix equation inefficiently.
Syntax for the Excel Regularizing Function
In Part 2 of Introduction to Regularizing with 2D Data we set up an overdetermined system of 13 equations with 10 variables and showed how to find the best solution using the normal equations. In this part we will look at the solution to the example and calculate the residual errors for this. This and several other examples of higher resolution (higher numbers of output points) are demonstrated in an Excel spreadsheet. Continue reading
In Part 1 of Introduction to Regularizing with 2D Data we set up a system of fidelity equations to map five input points to ten output points. In this part we will add second derivative equations and impose a scaling factor to ensure consistent results. Continue reading
Introduction to Regularizing with 2D Data
Start with some noisy measured data. You want to subsample the data or change the point intervals. You could fit a polynomial to what you measured, but the noise interferes with the fit.
A better method is to regularize the data. This means to construct a numerical dataset similar to what you measured but with better resolution, custom spacing in x, and smoothing in y.
This series of articles shows how to regularize 2D data with a simple and elegant process. Continue reading