ncalm

/*
GETBIGRAMS - Get the bigrams of a text string

Inputs: 1. str - a text string
Returns: A vertical array of the bigrams of the text string

Example:
    =GETBIGRAMS("banana")
    ={"ba";"an";"na";"a"}
*/

ncalm

function main(workbook: ExcelScript.Workbook) {

  const activeSheet = workbook.getActiveWorksheet()

  // Returns an object representing the worksheet's protection
  const activeSheetProtection = activeSheet.getProtection()

  // Protect the sheet according to preferred options (password as 2nd arg is optional)
  // This line uses the ternary operator. The protect call is only used if the getProtected() call returns false

ncalm

/*
 * Create and format a chart from some AdventureWorksDW2019 data
 * The table has these columns: Year, Month, SalesAmount
 * The worksheet must include a named cell called 'referenceLineValue'. This value is used to draw the line on the chart.
 */
function main(workbook: ExcelScript.Workbook) {

  let selectedSheet = workbook.getActiveWorksheet();

ncalm

let
    Source = "APS Deposit 04/01/2022 $5,174.27 APS ACH Deposit 04/04/2022 $65,186.66",
    Split = Text.Split(Source, " "),
    Typed = List.Transform(Split, 
        each try Number.From(_) otherwise 
             try Date.From(_, "en-gb") otherwise _ ),
    Accumulate = List.Accumulate({0..List.Count(Typed)-1}, "",
        (a, b) => 
            let
                c = Typed{b},

ncalm

two_arg = LAMBDA(function, 
    LAMBDA(x, y, 
        function(x, y)
    )
);

ncalm

RGB2HEX = LAMBDA(rgb_array, 

    LET(
        _rgb_array,TOROW(0+rgb_array),
        IF(COUNT(_rgb_array)<>3,#VALUE!,
            CONCAT("#",DEC2HEX(_rgb_array,2))
        )
    )

);

ncalm

/*
Standard error of the mean

Usage:
For a measurement from a sample of 100:

Initialize:
MYSAMPLE_SEM = SEM(100);

Use:

ncalm

https://www.linkedin.com/posts/crispo-mwangi-6ab49453_excel-exceltips-crispexcel-activity-7147058784119554048-Am6l?utm_source=share&utm_medium=member_desktop

ncalm

PIVOTEVERYNROWS = LAMBDA(attribute, value, first_attribute, 
    LET(
        unique_attributes, UNIQUE(attribute),
        attribute_ids, SEQUENCE(ROWS(unique_attributes)),
        col_id, XLOOKUP(attribute, unique_attributes, attribute_ids),
        shift, --(attribute = first_attribute),
        row_id, SCAN(, shift, SUM),
        pivoted, PIVOTBY(row_id, HSTACK(col_id, attribute), value, SINGLE,0,0,,0),
        DROP(pivoted, 1, 1)
    )

ncalm

DROP PROCEDURE IF EXISTS change_table_description;
GO

CREATE PROCEDURE change_table_description (
    @table_schema nvarchar(128),
    @table_name nvarchar(128),
    @description nvarchar(255)
)
AS
BEGIN