jimpea

// slope of model y~x
// inputs:
//    xs: the input data
//    ys: the response data
// return:
//    the slope
slope = lambda(xs, ys,
    let(
        x_bar, average(xs),
        y_bar, average(ys),

jimpea

// See https://sites.chem.utoronto.ca/chemistry/coursenotes/analsci/stats/ErrRegr.html
// These functions assume a simple linear model y = b_o + b_1*x
// xs and ys are lists of inputs and outputs respectively
// count(xs) == count(ys)

// predicted y_hat values from the model
y_hats = lambda(xs, ys,
    let(
        lin, linest(ys, xs, TRUE, TRUE),
        slope, index(lin, 1, 1),

jimpea

// Dispersion force Models
// See <https://arxiv.org/pdf/1910.05746.pdf>
// In these expressions, the unit of length is /sigma, the interparticle
// distance where the potential changes sign and the unit of energy is
// /epsilon, the well depth.
// 

/* Lennard-Jones Potential
Inputs:
    rs: separation

jimpea

/* split_text
Apply the Excel TEXTSPLIT function down more than one row. For instance, copy paste 
the output of a series of arrays  from Jupyter to Excel. Each array
starts with a '[' character, the numbers are split by spaces and
end with a ']' character. We want to convert:

[0.1050748  0.04837582 0.02369428 0.00918702 0.0065269 ] 0
[0.08102541 0.05405571 0.02647627 0.01026568 0.00729323] 0

to the following across individual cells

jimpea

// --- Page Rank ---
// A set of formulas used to calculate pagerank
// from an adjacency matrix. For instance, with an adjacency
// matrix defined in cells C4:H9, a damping factor of 0.85 and
// 25 iterations, get the pagerank from:
//
//    =pagerank_from_adjacency_matrix(C4:H9, 0.85, 25)

// From a transition matrix  M, where
// 1 indicates a link otherwise leaving other 

jimpea

/*
Append a vector of ones to x inputs.

For use in linear regression, The design matrix is an n by p matrix of 
explanatory variables, with the columns representing the constant term and the
covariates. Thus a typical case for a linear regression model fits
response to multiple inputs (explanatory variables). Users enter the input and
output data into an excel worksheet. The design matrix must also contain the
constants for the y-intercept. This function adds a vector of ones to the input
variables.

jimpea

/*
Append two ranges horizontally. 

Inputs:
    - range1: the first range
    - range2: the second range
    - default: the value entered into missing rows.

Return: The merged ranges, with empty rows filled with the default value. Missing
value within either of the two ranges filled with zeros (0). The number of rows

jimpea

//from <https://exceljet.net/formula/lambda-append-range>
//stack arrays horizontally
APPENDRANGEH = LAMBDA(range1, range2, default,
    LET(
        rows1, ROWS(range1),
        rows2, ROWS(range2),
        cols1, COLUMNS(range1),
        cols2, COLUMNS(range2),
        rowindex, SEQUENCE(MAX(rows1, rows2)),
        colindex, SEQUENCE(1, cols1 + cols2),

jimpea

//from <https://exceljet.net/formula/lambda-append-range>
//stack arrays vertically
APPENDRANGEV = LAMBDA(range1, range2, default,
    LET(
        rows1, ROWS(range1),
        rows2, ROWS(range2),
        cols1, COLUMNS(range1),
        cols2, COLUMNS(range2),
        rowindex, SEQUENCE(rows1 + rows2),
        colindex, SEQUENCE(1, MAX(cols1, cols2)),

jimpea

FLATTEN = LAMBDA(array,
    LET(
        //name definitions
        rows_, ROWS(array),
        cols_, COLUMNS(array),

        // index the zero-based array
        seq_, SEQUENCE(rows_*cols_, 1, 0, 1),

        // row and column indices