Excel Calibration Curve

calibration.txt

// 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),
        intercept, index(lin, 1, 2),
        slope * xs + intercept
    )
);

// Residuals
sumsq_residuals = lambda(xs, ys,
    let(
        y_hats, y_hats(xs, ys),
        sum((ys - y_hats)^2)
    )
);

// Standard error of regression
se_regression = lambda(xs, ys,
    let(
        n_, COUNT(ys),
        SQRT(sumsq_residuals(xs, ys) / (n_ - 2))
    )
);

// Standard error of slope
se_slope = lambda(xs, ys,
    let(
        x_bar, average(xs),
        sumsq_x, sum((xs - x_bar)^2),
        se_regression(xs,ys) / sqrt(sumsq_x)
    )
);

// standard error of intercept
se_intercept = lambda(xs, ys,
    let(
        sumxsq, sum(xs^2),
        n, count(xs),
        x_bar, average(xs),
        se_regression(xs,ys) * sqrt(sumxsq / (n * sum((xs - x_bar)^2)))
    )
);

SS_tot = lambda(ys,
    let(
        y_bar, average(ys),
        sum((ys - y_bar)^2)
    )
);

SS_reg = lambda(xs, ys,
    SS_tot(ys) - sumsq_residuals(xs, ys)
);

R_squared = lambda(xs, ys,
    1 - (SS_reg(xs, ys)/SS_tot(ys))
);

// standard error of the analyte back calculated from a calibration
// curve of analytes (x) vs instrument signal (y).
//
// Fitted to a first order polynomial = slope.X + intercept.
//
// This assumes that all the error lies in the response (y).
// 
// Inputs:
//     xs: the analytes in the calibration curve
//     ys: the instrument response in the calibration curve
//     samples: the instrument response from a given sample. This may be a 
//         single measurement or a set of repeats
// Return:
//     The standard error of the analyte determined from the calibration curve
SEanalyte = lambda(xs, ys, samples,
    let(
        m_, COUNT(samples),
        n_, COUNT(xs),
        fit_, LINEST(ys, xs, TRUE, TRUE),
        b1_, INDEX(fit_, 1, 1),
        sr_, jp.se_regression(xs, ys),
        dsample_, (AVERAGE(samples) - AVERAGE(ys))^2,
        dcalibs_, b1_^2 * SUM((xs - AVERAGE(xs))^2),
        (sr_/b1_) * SQRT(1/m_ + 1/n_ + dsample_/dcalibs_)
    )
);

AnalyteLevel = LAMBDA(xs, ys, response,
    let(
        fit, LINEST(ys, xs),
        b1_, index(fit, 1),
        b0_, index(fit, 2),
        (response - b0_)/b1_
     )
);