MESHGRID Function for Excel

Work in Progress :: Please contact us to report errors, typos, etc.

=L_MESHGRID(xvec, yvec, [return_option])

Argument	Description	Example
xvec	The vector of x-coordinate values	{0;2;3;4}
yvec	The vector of y-coordinate values	{0;1;2}
return_option	"X" returns the X matrix, "Y" returns the Y matrix	(blank)

Description

MESHGRID creates a set of 2D grid coordinates that ends up looking like a mesh. The inputs are the vectors of x- and y-coordinates which can be generated using a function like LINSPACE or LOGSPACE.

MESHGRID returns the coordinates as two columns of (x,y) pairs. If n_x is the number of values in the x vector and n_y is the number of values in the y vector, then meshgrid will end up having n_x*n_y pairs of coordinates.

Internally, the function first generates an X matrix where the x vector is a row repeated n_y times vertically. The Y matrix is generated with the y vector column repeated n_x times horizontally.

The optional [return_option] parameter allows you to specify "X" or "Y" to return the corresponding matrix.

Note

MESHGRID is typically used prior to evaluating the z-coordinate values for 3D surfaces and plots. Excel does not have a built-in 3D scatter plot. However, the new Python functionality will allow the use of MatLibPlot which will bring more plotting capability to Excel.

Lambda Function Code

This code for using L_MESHGRID in Excel is provided under the License as part of the LAMBDA Library, but to use just this function, you may copy the following code directly into your spreadsheet.

Code to Create Function via the Name Manager

Name: L_MESHGRID
Comment: Create a 2D grid of X-Y Coordinates
Refers To:

/**
* Creates a set of 2D grid coordinates with x- and y-coordinates defined by 
* vectors x and y. Returns the coordinates as two columns of (x,y) pairs.
*/
L_MESHGRID = LAMBDA(xvec,yvec,[return_option],
LET(doc,"https://www.vertex42.com/lambda/meshgrid.html",
    xvec,IF(AND(ROWS(xvec)=1,COLUMNS(xvec)>1),TRANSPOSE(xvec),xvec),
    yvec,IF(AND(ROWS(yvec)=1,COLUMNS(yvec)>1),TRANSPOSE(yvec),yvec),
    xn, ROWS(xvec),
    yn, ROWS(yvec),
    xM, MAKEARRAY(yn,xn,LAMBDA(i,j,INDEX(TRANSPOSE(xvec),1,j))),
    yM, MAKEARRAY(yn,xn,LAMBDA(i,j,INDEX(yvec,i,1))),
    IF(return_option="X",xM,
        IF(return_option="Y",yM,
            HSTACK(TOCOL(xM),TOCOL(yM))
        )
    )
))

Code for AFE Workbook Module (Excel Labs Add-in)

/**
* Creates a set of 2D grid coordinates with x- and y-coordinates defined by 
* vectors x and y. Returns the coordinates as two columns of (x,y) pairs.
*/
L_MESHGRID = LAMBDA(xvec,yvec,[return_option],
LET(doc,"https://www.vertex42.com/lambda/meshgrid.html",
    xvec,IF(AND(ROWS(xvec)=1,COLUMNS(xvec)>1),TRANSPOSE(xvec),xvec),
    yvec,IF(AND(ROWS(yvec)=1,COLUMNS(yvec)>1),TRANSPOSE(yvec),yvec),
    xn, ROWS(xvec),
    yn, ROWS(yvec),
    xM, MAKEARRAY(yn,xn,LAMBDA(i,j,INDEX(TRANSPOSE(xvec),1,j))),
    yM, MAKEARRAY(yn,xn,LAMBDA(i,j,INDEX(yvec,i,1))),
    IF(return_option="X",xM,
        IF(return_option="Y",yM,
            HSTACK(TOCOL(xM),TOCOL(yM))
        )
    )
));

Named Function for Google Sheets

Name: L_MESHGRID
Description: Create a 2D grid of X-Y Coordinates
Arguments: xvec, yvec, return_option
Function:

=LET(doc,"https://www.vertex42.com/lambda/meshgrid.html",
    xvec,IF(AND(ROWS(xvec)=1,COLUMNS(xvec)>1),TRANSPOSE(xvec),xvec),
    yvec,IF(AND(ROWS(yvec)=1,COLUMNS(yvec)>1),TRANSPOSE(yvec),yvec),
    xn, ROWS(xvec),
    yn, ROWS(yvec),
    xM, MAKEARRAY(yn,xn,LAMBDA(i,j,INDEX(TRANSPOSE(xvec),1,j))),
    yM, MAKEARRAY(yn,xn,LAMBDA(i,j,INDEX(yvec,i,1))),
    IF(return_option="X",xM,
        IF(return_option="Y",yM,
            HSTACK(TOCOL(xM),TOCOL(yM))
        )
    )
)

L_MESHGRID Examples

Example

Generate the coordinates shown in the above example.

Test: Copy and Paste this LET function into a cell
=LET(
    xvec, L_LINSPACE(0,3,4),
    yvec, L_LINSPACE(0,2,3),
    L_MESHGRID(xvec,yvec)
)

Result: {0,0; 1,0; 2,0; 3,0; 0,1; 1,1; 2,1; 3,1; 0,2; 1,2; 2,2; 3,2}

L_MESHGRID