We have expanded our custom Excel interpolation functions to interpolate both 2D and 3D datasets, i.e. to interpolate over a curve or over a surface.
Cubic and Bicubic Interpolation Excel Examples with Source Code
The Excel Solver is a powerful tool for solving complicated problems. But what if you want something smaller and more compact like… an Excel spreadsheet function? Minimize() and MinimizeNonnegative() combine the power of a nonlinear optimizer with the convenience of a spreadsheet function. Unlike the Excel Solver, these functions automatically respond to changes in the spreadsheet and recalculate like any other formula. As an added bonus, Minimize() and MinimizeNonnegative() can optimize some things that the Excel Solver cannot.
The Excel Solver is a powerful tool for solving complicated problems. But what if you want something smaller and more compact like… an Excel spreadsheet function? Minimize() combines the power of a nonlinear optimizer with the convenience of a spreadsheet function. Unlike the Excel Solver or Goal Seek, it responds to changes in the values of its arguments and recalculates automatically like any other formula in the spreadsheet. As an added bonus, Minimize() can optimize some things that the Excel Solver cannot.
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
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 DataThis 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
People write VBA functions to use in their spreadsheets. People write VBA functions to use in their VBA scripts. How about a single VBA function that works in both script and spreadsheet?
Sometimes your tabular dataset is in a worksheet. Other times it is already in an array and you don’t have any need to copy it to a worksheet. Can you just use the bicubic interpolation function on both? This article shows how to bring versatility to an Excel VBA function for both spreadsheets and VBA scripts using bicubic interpolation as an example. 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
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 DataStart 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
Math for Mere Mortals 