Skip to content

Instantly share code, notes, and snippets.

View ncalm's full-sized avatar
💭
hitting computer with hammer

Owen Price ncalm

💭
hitting computer with hammer
52 followers · 20 following
View GitHub Profile
@ncalm
ncalm / adventureworksdw2019-vSales.sql
Created November 22, 2023 19:05
SQL view and Power Query for replicating GROUPBY testing workbook
/* Install Adventure Works DW 2019 from this link:
https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorksDW2019.bak
View below is used by Power Query in GROUPBY demonstrations
*/
USE [AdventureWorksDW2019]
GO
/****** Object: View [dbo].[vSales] Script Date: 11/22/2023 12:02:03 PM ******/
SET ANSI_NULLS ON
@ncalm
ncalm / excel-lambda-PERCENTOFFN.txt
Created November 22, 2023 18:58
This Excel LAMBDA gist replicates PERCENTOF functionality for other aggregates
/*
Replicating PERCENTOF for non-SUM aggregates
Usage:
For percent of max:
=PERCENTOFFN(MAXL)(data_subset,data_all)
In GROUPBY function argument:
PERCENTOFFN(MAXL)
Note: At this time, this doesn't work:
=PERCENTOFFN(MAX)(data_subset,data_all)
@ncalm
ncalm / excel-lambda-ISLAMBDA.txt
Last active October 4, 2023 17:02
This Excel LAMBDA function tests if its argument is a
ISLAMBDA = LAMBDA(x, AND(VALUETOTEXT(x)="λ", TYPE(x)=128)))
@ncalm
ncalm / excel-lambda-BMAPλ.txt
Created October 3, 2023 12:04
Recursive bisection LAMBDA function for Excel, by Peter Bartholemew
/*
Credit: Peter Bartholemew
https://www.linkedin.com/in/peterbartholomew/
https://techcommunity.microsoft.com/t5/user/viewprofilepage/user-id/214174#profile
---
BMAPλ
Recursively bisects an array and applies a function to the leaf nodes.
@ncalm
ncalm / excel-lambda-strip.txt
Created September 27, 2023 16:00
// trip leading chars
lstrip = LAMBDA(text, char,
BYROW(text,
LAMBDA(t, IF(LEFT(t)=char, lstrip(MID(t,2,len(t)),char), t))
)
);
// trim trailing chars
rstrip = LAMBDA(text, char,
BYROW(text,
@ncalm
ncalm / python-in-excel-gpt4-custom-instructions.txt
Last active September 27, 2023 13:50
ChatGPT Plus Custom Instructions for Python in Excel
There is a feature of Excel called Python in Excel. It allows you to create a "Python cell" in an Excel spreadsheet. In a Python cell, you can execute Python code.
For each session, these imports are run automatically:
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import statsmodels as sm
import seaborn as sns
import excel
@ncalm
ncalm / excel-python-integrate-image-function.py
Last active September 26, 2023 21:16
Use Python in Excel to integrate an expression and render the LaTeX expression and result to a plot
from sympy import integrate
def integration_image(symbol: str, expr: str):
"""Integrates expr with respect to symbol
integration_image(symbol='x', expr='exp(x)*sin(x) + exp(x)*cos(x)')
Dependencies: 'from sympy import integrate' must be run before use
"""
@ncalm
ncalm / excel-lambda-sudoku-solver-no-comments.txt
Last active May 4, 2024 23:51 — forked from lhem/sudoku
solver=LAMBDA(grid,
LET(
numbers, SEQUENCE(9),
numgrid, SEQUENCE(9,9,0),
vgrid, TOCOL(grid*1),
pos, XMATCH(0,vgrid)-1,
IF(
ISNA(pos), grid,
LET(
i,INT(pos/9),
@ncalm
ncalm / excel-lambda-FORMULATEXT.PY.txt
Created August 25, 2023 15:53
This Excel LAMBDA function provides a FORMULATEXT capability for Python in a Python cell
/*
Provides a FORMULATEXT capability for Python cells
pythonCell: A reference to a Python cell
[asArray]: A TRUE/FALSE value indicating whether
or not to spill the results of the formula
default is TRUE
*/
FORMULATEXT.PY = LAMBDA(pythonCell,[asArray],
LET(
@ncalm
ncalm / excel-lambda-chartQuadrants.txt
Created August 2, 2023 16:05
This Excel LAMBDA function calculates the coordinates for two series to add to a scatterplot to produce a vertical and horizontal line
chart.Quadrants = LAMBDA(seriesData,
LET(
// The first column (x values)
x, TAKE(seriesData, , 1),
// The second column (y values)
y, TAKE(seriesData, , -1),
xMax, MAX(x),
yMax, MAX(y),