Introduction to Regularizing 3D Data – Part 1 of 2

5x5 Regularized Surface Animation 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

Bicubic Interpolation – VBA Function Compatible with Both Arrays Ranges

Bicubic Interpolation – One VBA Function Compatible with Both Arrays and Ranges
3D Plot of Tabular Data and Function Syntax

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

Bicubic Interpolation Excel Spreadsheet Function

Bicubic Interpolation on a Vol Surface in ExcelExcel 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.

Continue reading