SpreadJS 14
Formula Reference / Formula Functions / Math and Trigonometric Functions / LET
In This Topic
    LET
    In This Topic

    This function is used to assign names to calculation results. The variable names can then be used to store intermediate calculations, values, or defining names within braces of the LET() function. To use this function, you have to define pairs of names and values associated with it, and a calculation that uses them all.

    By using this function, user don't have to remember what a specific range or cell reference referred to, or what your calculation is supposed to do, or even copy pasting the same expression all over again.

    Syntax

    LET(name1, name_value1, calculation_or_name2, [name_value2, calculation_or_name3...])

    Arguments

    This function has these arguments:

    Argument Description
    name First name to assign. Must always begin with a letter and should not contain a '.'
    name_value1 The value that is assigned to name1.
    calculation_or_name2 You can define one of the following.
    • A calculation that uses all names within the LET function. This must be the last argument in the LET function.
    • A second name to be assigned to a second name_value. If a name is specified, name_value2 and calculation_or_name3 become required.
    name_value2 [Optional] The value or calculation that is assigned to calculation_or_name2.
    calculation_or_name3 [Optional] You can define one of the following.
    • A calculation that uses all names within the LET function. This must be the last argument of this function.
    • A third name to assign to a third name_value. If a name is specified, name_value3 and calculation_or_name4 become required.

    Data Types

    Returns variant data type.

    Remarks

    The last argument must always be a calculation that returns a result.

    Examples

    The following sample code show the basic usage of the LET function.

    JavaScript
    Copy Code
    var formula = '=LET(x,2,x+3)';
    sheet1.setValue(2, 0, 'Pair varible');
    sheet1.setValue(2, 1, formula);
    sheet1.setValue(3, 0, 'Result');
    sheet1.setFormula(3, 1, formula);
    

    The following sample code show the usage of LET local variable and custom name with LET function.

    JavaScript
    Copy Code
    sheet1.addCustomName('user', '="Michael"');
                    
    var formula = '=LET(user,"Ivy","The actual user is: "&user)';
    sheet1.setValue(2, 0, 'Always use LET local variable first');
    sheet1.setValue(2, 1, formula);
    sheet1.setValue(3, 0, 'Result');
    sheet1.setFormula(3, 1, formula);
    
    // Use custom name if local variable is not available
    var formula = '=LET(user,user,"The actual user is: "&user)';
    sheet1.setValue(5, 0, 'Use custom name if local variable is not available');
    sheet1.setValue(5, 1, formula);
    sheet1.setValue(6, 0, 'Result');
    sheet1.setFormula(6, 1, formula);
    
    Note: The name argument should not contain a '.' and has been updated to match Excel's updated behavior. Refer Release Notes for Version 14.1.0 to learn more.