Skip to content

Instantly share code, notes, and snippets.

@jimpea
jimpea / regression.txt
Last active July 19, 2024 15:12
Linear Regression
// 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
jimpea / calibration.txt
Last active July 1, 2024 10:02
Excel Calibration Curve
// 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
jimpea / pair_potentials.txt
Created November 8, 2023 15:35
excel lambda functions
// 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
jimpea / excel_text_lambdas.txt
Created July 24, 2023 16:05
Some text manipulation functions
/* 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
jimpea / ragerank.txt
Created June 15, 2023 10:39
Excel lambda functions for PageRank
// --- 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
jimpea / xl_regression.txt
Created March 29, 2023 11:39
Excel Regression Functions
/*
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
jimpea / lambdas.txt
Last active October 21, 2024 17:57
Some usefull Excel Lambda functions
/*
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
jimpea / appendrangeh.txt
Created February 15, 2022 16:53
SExcel Lambda function to stack two arrays horizontally
//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
jimpea / appendrangev.txt
Created February 15, 2022 16:52
Excel Lambda function to stack two arrays vertically
//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
jimpea / flatten.txt
Last active May 4, 2024 08:15
Excel Lambda function to convert an array to a list
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