LAMBDA function

PRO

The LAMBDA function in Jspreadsheet Formulas Pro lets you create your very own unique functions using a concept called lambda expressions. Think of lambda expressions as a shorter, more compact way of creating functions that don't have a specific name. This function gives you the flexibility to design and write your own operations, tailored to your specific needs in your Jspreadsheet projects.

Documentation

Allows you to define your own custom functions using a lambda expression, which is a shorthand way of defining anonymous functions.

Syntax

LAMBDA(parameter1, [parameterN], expression)

Parameter	Description
`parameter1`	The name of parameter to be defined in the function.
`[parameterN]`	Optional. The nth name to define a parameter inside the function.
`expression`	The expression that defines the function, can use the specified parameters.

Behavior

The LAMBDA function allows users to define custom functions using the Excel formula language. Here's how it handles different inputs:

Empty Cells: If the lambda function is expected to perform operations on empty cells, it might return an error depending on the operation. It's better to handle such cases within the lambda function.
Text: The LAMBDA function can operate on text based on the operations defined within the function. However, if it's expected to perform numerical operations on text data, it might return an error.
Booleans: LAMBDA can handle boolean values if the operations within the function are defined to work with booleans.
Errors: If there are errors in the operations defined within the LAMBDA function or in its inputs, it will return an error.

Common Errors

Error	Description
`#VALUE!`	This error occurs if one or more of the arguments for the function are not the correct type.
`#CALC!`	This error occurs if the custom function defined by `LAMBDA` is causing a circular reference.
`#REF!`	This error occurs if the LAMBDA function refers to a cell that is not valid.

Best practices

Always define your LAMBDA functions with clear, descriptive names to make your formulas easier to read and understand.

When defining a LAMBDA function, make sure it handles all possible input types that it might receive to prevent errors.

Avoid creating overly complex LAMBDA functions. If a function is becoming too complex, consider splitting it into several smaller functions.

Always test your LAMBDA functions with a variety of inputs to ensure they work as expected before using them in your actual spreadsheet calculations.

Usage

A few examples using the LAMBDA function.

LAMBDA(x, y, SQRT(x^2 + y^2)) custom function that takes two parameters (3, 4) and returns their sum of squares. Example Output: 25 (since 3² + 4² = 9 + 16).
LAMBDA(a, b, (a+b)/2) returns the average of two numbers (10, 20) (in this case, 15).

Interactive Spreadsheet Demo

<html>
<script src="https://jspreadsheet.com/v11/jspreadsheet.js"></script>
<script src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jsuites.net/v5/jsuites.css" type="text/css" />
<link rel="stylesheet" href="https://jspreadsheet.com/v11/jspreadsheet.css" type="text/css" />

<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" />
<script src="https://cdn.jsdelivr.net/npm/@jspreadsheet/formula-pro/dist/index.min.js"></script>

<div id="spreadsheet"></div>

<script>
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('MDNiZDc4NGQyMGQ5OWI5MjIxN2Q3NGY3MjQxMjc2NDQxMTIxNGJmNjE3YmFhZTMxNTBjYjg2ODU1N2M4NzhhNWI3ZjIwMjdlMGM4NDI1NDNkZmI2OWRlMTRhOWNhZGVhNzY0ZWExYzBmYjY4MGVkYWNiODBjZWVhM2NhOWY5MTIsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVMk56a3lPVEF6TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
  worksheets: [{
    data: [
    [
        "X Value",
        "Y Value",
        "Sum of Squares"
    ],
    [
        3,
        4,
        "=LAMBDA(x,y,x^2+y^2)(A2,B2)"
    ],
    [
        5,
        12,
        "=LAMBDA(x,y,x^2+y^2)(A3,B3)"
    ],
    [
        8,
        6,
        "=LAMBDA(x,y,x^2+y^2)(A4,B4)"
    ],
    [
        2,
        7,
        "=LAMBDA(x,y,x^2+y^2)(A5,B5)"
    ]
]
  }]
});
</script>
</html>

import React, { useRef } from "react";
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/react";
import formula from "@jspreadsheet/formula-pro";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";

// Set license
jspreadsheet.setLicense('MDNiZDc4NGQyMGQ5OWI5MjIxN2Q3NGY3MjQxMjc2NDQxMTIxNGJmNjE3YmFhZTMxNTBjYjg2ODU1N2M4NzhhNWI3ZjIwMjdlMGM4NDI1NDNkZmI2OWRlMTRhOWNhZGVhNzY0ZWExYzBmYjY4MGVkYWNiODBjZWVhM2NhOWY5MTIsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVMk56a3lPVEF6TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

export default function App() {
    // Spreadsheet array of worksheets
    const spreadsheet = useRef();

    // Worksheet data
    const data = [
    [
        "X Value",
        "Y Value",
        "Sum of Squares"
    ],
    [
        3,
        4,
        "=LAMBDA(x,y,x^2+y^2)(A2,B2)"
    ],
    [
        5,
        12,
        "=LAMBDA(x,y,x^2+y^2)(A3,B3)"
    ],
    [
        8,
        6,
        "=LAMBDA(x,y,x^2+y^2)(A4,B4)"
    ],
    [
        2,
        7,
        "=LAMBDA(x,y,x^2+y^2)(A5,B5)"
    ]
];

    // Render component
    return (
        <Spreadsheet ref={spreadsheet}>
            <Worksheet data={data} />
        </Spreadsheet>
    );
}

<template>
    <Spreadsheet ref="spreadsheet">
        <Worksheet :data="data" />
    </Spreadsheet>
</template>

<script>
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/vue";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";
import formula from "@jspreadsheet/formula-pro";

// Set license
jspreadsheet.setLicense('MDNiZDc4NGQyMGQ5OWI5MjIxN2Q3NGY3MjQxMjc2NDQxMTIxNGJmNjE3YmFhZTMxNTBjYjg2ODU1N2M4NzhhNWI3ZjIwMjdlMGM4NDI1NDNkZmI2OWRlMTRhOWNhZGVhNzY0ZWExYzBmYjY4MGVkYWNiODBjZWVhM2NhOWY5MTIsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVMk56a3lPVEF6TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // Worksheet data
        const data = [
    [
        "X Value",
        "Y Value",
        "Sum of Squares"
    ],
    [
        3,
        4,
        "=LAMBDA(x,y,x^2+y^2)(A2,B2)"
    ],
    [
        5,
        12,
        "=LAMBDA(x,y,x^2+y^2)(A3,B3)"
    ],
    [
        8,
        6,
        "=LAMBDA(x,y,x^2+y^2)(A4,B4)"
    ],
    [
        2,
        7,
        "=LAMBDA(x,y,x^2+y^2)(A5,B5)"
    ]
]

        return {
            data
        };
    }
}
</script>

import { Component, ViewChild, ElementRef } from "@angular/core";
import jspreadsheet from "jspreadsheet";
import * as formula from "@jspreadsheet/formula-pro";

// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('MDNiZDc4NGQyMGQ5OWI5MjIxN2Q3NGY3MjQxMjc2NDQxMTIxNGJmNjE3YmFhZTMxNTBjYjg2ODU1N2M4NzhhNWI3ZjIwMjdlMGM4NDI1NDNkZmI2OWRlMTRhOWNhZGVhNzY0ZWExYzBmYjY4MGVkYWNiODBjZWVhM2NhOWY5MTIsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVMk56a3lPVEF6TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

@Component({
    standalone: true,
    selector: "app-root",
    template: `<div #spreadsheet></div>`
})
export class AppComponent {
    @ViewChild("spreadsheet") spreadsheet: ElementRef;
    // Worksheets
    worksheets: jspreadsheet.worksheetInstance[];

    // Create a new data grid
    ngAfterViewInit() {
        // Create spreadsheet
        this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, {
            worksheets: [{
                data: [
    [
        "X Value",
        "Y Value",
        "Sum of Squares"
    ],
    [
        3,
        4,
        "=LAMBDA(x,y,x^2+y^2)(A2,B2)"
    ],
    [
        5,
        12,
        "=LAMBDA(x,y,x^2+y^2)(A3,B3)"
    ],
    [
        8,
        6,
        "=LAMBDA(x,y,x^2+y^2)(A4,B4)"
    ],
    [
        2,
        7,
        "=LAMBDA(x,y,x^2+y^2)(A5,B5)"
    ]
]
            }]
        });
    }
}

Edit this page on GitHub