LINSPACE Function for Excel

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

=L_LINSPACE(start, end, n)

Argument	Description	Example
start	The beginning value in the vector	2
end	The end value in the vector (can be < start)	4
n	Number of points, including start and end	10

Description

LINSPACE means "linear spacing." It creates a uniform sequence of values between the start and end values, with n equally spaced points. The number of segments or intervals is n-1. L_LINSPACE is based on the SEQUENCE function, but L_LINSPACE is useful when you know the start and end values instead of the start value and step size. Both MATLAB and NumPy have a similiar function named linspace. Examples of uses may include:

Charts and Graphs: Creating the x-axis values for plotting a function, or defining points for drawing curves and surfaces.
Sensitivity Analysis: Setting up the paramater values for a Data Table to evaluate your model at n points between some min and max.
Calculus: Defining the number of points over which a function is numerically integrated between x1 and x2
Time Series: Defining points for a time-based model with equal time intervals

The SEQUENCE function in Excel can handle this easily, but in Google Sheets the SEQUENCE function can only handle integer steps, so we have to scale the sequence to [start,end] after creating it.

Formulas for LINSPACE In Excel:

Using SEQUENCE:
LINSPACE =SEQUENCE(n,1,start,(end-start)/(n-1))

Using RESCALE:
LINSPACE =L_RESCALE(SEQUENCE(n),start,end)

Lambda Function Code

This code for using L_LINSPACE 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_LINSPACE
Comment: Create a linear vector with n equally spaced points
Refers To:

=LAMBDA(start,end,n,
LET(doc,"https://www.vertex42.com/lambda/linspace.html",
    SEQUENCE(n,1,start,(end-start)/(n-1))
))

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

/**
* Create a vector on interval [start,end] with n linearly spaced points
* L_LINSPACE(2,3,5) = {2; 2.25; 2.5; 2.75; 3}
*/
L_LINSPACE = LAMBDA(start,end,n,
LET(doc,"https://www.vertex42.com/lambda/linspace.html",
    SEQUENCE(n,1,start,(end-start)/(n-1))
));

Named Function for Google Sheets

Name: L_LINSPACE
Description: Create a linear vector with n equally spaced points
Arguments: start, end, n (see above for descriptions and example values)
Function:

LET(doc,"https://www.vertex42.com/lambda/linspace.html",
    ARRAYFORMULA(start+(end-start)*(SEQUENCE(n)-1)/(n-1))
)

Warning

These L_LINSPACE functions are not compatible between Excel and Google Sheets. The Excel version of SEQUENCE permits a stepsize that is a decimal value, but GS requires the step size to be an integer. The GS version also requires ARRAYFORMULA to return multiple results.

L_LINSPACE Examples

Example 1

To generate 6 points equally spaced between 1 and 3:

Test: Copy and Paste this LET function into a cell
=LET(
    start, 1,
    end, 3,
    n, 6,
    L_LINSPACE(start,end,n)
)

Result: {1; 1.4; 1.8; 2.2; 2.6; 3}

Example 2: Plotting the SIN Function

The X values for plotting a SIN function were generated using =L_LINSPACE(0,720,20). The values in the Y column were calculated using =SIN(RADIANS(x)).

F53: =L_LINSPACE(0,720,20)
     =L_LINSPACE(D52,D53,D54)
    
G53: =SIN(RADIANS(F53#))

LINSPACE Example: Plotting the SIN Function

A better modeling technique for this example would be to ensure that all values are labeled with correct units and better descriptions, such as "degrees" next to the cells for x1 and x2 and "points" next to n. Or, change the x1, x2, and n labels to "Start Angle (degrees)", "End Angle (degrees)", "Number of Points."

Without proper labeling, a user would not easily know whether x1 and x2 should be entered as degrees or radians. The SIN and COS functions in Excel require the input to be radians, so a conversion is necessary only if the input is degrees.

L_LINSPACE