ncalm

/*
CORRELMATRIX

Returns a correlation matrix

Inputs:
  x - an array of 2:n numeric columns of equal size for which to calculate the correlation of each pair of 2 columns
  has_header - TRUE if the first row of x contains column headers. If omitted or FALSE, x is assumed to not include a header row
  ranked - if TRUE, calculate the Spearman Ranked Correlation Coefficient. If FALSE or omitted, calculate the Pearson Correlation Coefficient
*/

ncalm

/*
PMT.DATES

Creates a series of dates for a payment schedule.

Inputs:
  - start_date - the starting date of the payment term (typically the date the first payment is due)
  - term_years - the number of years over which the payment must be made
  - period_months - the number of months between each payment
  - endpoint_offset - OPTIONAL - the number of periods to include before the first payment date and after the last payment date

ncalm

/*
INTRATE.EFFECTIVE

Calculates the effective interest rate using a simplified assumption. 

The intent here is to show an example of recursion in a financial function.

Inputs:
- opening_balance – the opening balance of some period of interest
- base_rate – the base rate of the instrument

ncalm

/*
GETHOLIDAYS

Inputs:
  - year - the four-digit year for which we want to calculate holidays according to the provided lists
  - [relative_holidays] - OPTIONAL if fixed_holidays is provided - a four-column array of data where the columns are:
    1. The Nth week of the month. Positive non-zero integers from 1 to 5 represent the nth week in the month specified in the third column. If 0, this represents the last week of the month prior to the month in the month column. So, {0,2,6,"Last Monday in May"} is the last Monday in the month prior to June. Similarly, {-1,2,6,"Second-to-last Monday in May"} will be the Monday prior to the last Monday in the month prior to June
    2. The weekday of the Nth week of the month. The week is Sunday=1, Monday=2, ... , Saturday=7
    3. The number from 1 to 12 representing the month. See note under column 1 regarding "Last X of Y"
    4. The description of the holiday

ncalm

/*
FILLTOTALS

A proposed solution to Alan Murray's challenge video https://www.youtube.com/watch?v=5ZL_DygRDm4

Inputs:
  - data - a 3-column array of data as shown in the YT link above
  
Returns:
  An array the same shape as data with sub-totals and a grand total calculated

ncalm

/*
CONVERTRECIPE

Converts a recipe ingredient from one unit of measure to another, bearing in mind the following assumptions. In an example of "300ml vegetable oil":

1) The recipe ingredient (e.g. "300ml vegetable oil") begins with the volume of the ingredient (300) and is immediately followed by an optional unit of measure ("ml")
2) There is a non-breaking space (CODE=160) between the measurement ("300ml") and the description of the ingredient ("vegetable oil")

Inputs:
- ingredient - a text string meeting the requirements above

ncalm

AreWeThereYet = LAMBDA(date,
    LET(
        n, NOW(),
        days, date - n,
        countdown, INT(days) & " days, " &
            TEXT(days, "h"" hours ""m"" minutes ""s"" seconds"""),
        working_days, NETWORKDAYS(n, date),
        working_hours, working_days * 8,
        hours, days * 24,
        minutes, days * 24 * 60,

ncalm

/*
RANDSEQUENCE

Inputs:
  - numbers: the length of the sequence to be produced
  - max_between: the maximum difference between consecutive integers
  
Note: the minimum difference between one row and the next is assumed to be 1.
*/

ncalm

/*
KNN

Returns the classification of an unknown observation using known observations in a training set. 

Inputs:
- x - an observation (row) in need of classification. This is an array of numerical measurements about an observation which you want to classify. This is one row and one or more columns. 

- trn - an array of training data which is already classified. This array will have COLUMNS(x) + 1 columns. The additional column is because the training set includes a column on the right for the classification of each row. In the example below, the species of the flower. 

ncalm

/*
FREQ.SIMPLE

Calculates a simple frequency table of the values in a column

Inputs:
1. data - a single column of data
*/

FREQ.SIMPLE = LAMBDA(data,