Cubic and Bicubic Interpolation Excel Functions

Cubic and Bicubic Interpolation Excel Functions v3

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

Minimize and MinimizeNonnegative Spreadsheet Optimization v6

Excel Spreadsheet Function to Optimize Cells (without Using the Excel Solver) Version 6

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.

Constrained Nonlinear Optimization in Excel without the Excel Solver

Constrained Optimization in Excel

Here we see how to do constrained optimization with an Excel spreadsheet function. The spreadsheet function MinimizeNonnegative() imposes a nonnegativity constraint on the variable. This is useful when you’re solving for something that can’t be negative, like energy or an interest rate.

Excel Spreadsheet Function to Optimize Cells (without Using the Excel Solver or Goal Seek)

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.

RegularizeData3D – the Excel Spreadsheet Function to Regularize 3D Data to a Smooth Surface

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.

Introduction to Regularizing 3D Data – Part 2 of 2

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.

Introduction to Regularizing 3D Data – Part 1 of 2

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.

Bicubic Interpolation – VBA Function Compatible with Both Arrays Ranges

Bicubic Interpolation – One VBA Function Compatible with Both Arrays and Ranges

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.

RegularizeData2D, the Excel Spreadsheet Function for Regularizing 2D Data – Part 2 of 2

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.

RegularizeData2D, the Excel Spreadsheet Function for Regularizing 2D Data – Part 1 of 2

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

Introduction to Regularizing with 2D Data – Part 3 of 3

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.

Introduction to Regularizing with 2D Data – Part 2 of 3

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.

Introduction to Regularizing with 2D Data – Part 1 of 3

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.

Bicubic Interpolation Excel Spreadsheet Function

Excel Spreadsheet Function for Bicubic Interpolation

Suppose you have a grid of points, but you need to know the values somewhere between the points.  Bicubic interpolation is a great way to do this.  This spreadsheet with VBA source code gives a ready-to-use worksheet function that interpolates between any data laid out in a grid using bicubic Lagrange interpolation.

This example shows how to use the custom Excel function BicubicInterpolation() to calculate a value on a volatility surface, something commonly used in financial math for pricing derivatives.

A Numerical Second Derivative from Three Points

What is the second derivative of three unevenly spaced points?  Is there a formula for the numerical second derivative?

In practice, these points could represent measured data that we want to analyze like weather patterns or financial data. We will see that the second derivative is a linear combination of the three points.