BESHStatNG UDF Cookbook

Copy-paste Excel formulas for statistical workflows, chart-ready outputs, and reusable workbook templates.

The BESHStatNG UDF Cookbook is designed for users who want to work directly in Excel with worksheet formulas instead of relying only on ribbon dialogs. It brings together practical formula patterns for chart-ready outputs, handle-based model workflows, and reproducible analysis templates that can be reused across projects.

This page is a practical entry point. Use it to see what the UDF workflow looks like, download a hands-on workbook, and jump into the full cookbook and tutorial tracks.

Found a UDF issue or unexpected formula result? Please report it on GitHub and include the exact formula, input ranges, expected output, actual output, and a workbook if possible.

Why use worksheet UDFs?

BESHStatNG already supports practical Excel-first workflows with downloadable workbooks and screenshot-based tutorials, and the Tutorials section is aimed at researchers, analysts, teachers, students, and biomedical users who want to run analyses directly in Excel. The UDF cookbook extends that idea by showing how to build the same kinds of workflows with formulas that stay live inside the worksheet.

With UDFs, you can:

  • build reproducible workbook templates
  • keep calculations close to the source data
  • create chart-ready spill ranges for Excel charts
  • fit models once and extract multiple outputs
  • reuse the same workbook structure across studies, datasets, or teaching examples

What kinds of formula workflows are included?

Scalar formulas

These return one result per cell, such as a statistic, p-value, AUC, or confidence limit.

Example:

=BESH.PLOT.ROC_STATS(B2:B101,A2:A101)

Result-table formulas

These return labeled spill tables that are ready to read or chart.

Example:

=BESH.PLOT.HIST_BINS(A2:A101)

Handle-based workflows

These fit a model once, store the result in a handle cell, and then let you extract summaries, scores, loadings, or other outputs from that handle.

Example:

=BESH.MULTI.PCA_FIT(A2:F101)

Plot-data workflows

These return chart-ready tables that you can connect directly to native Excel charts.

Example:

=BESH.PLOT.ROC_POINTS(B2:B101,A2:A101)

Featured cookbook examples

ROC curve from marker values and class labels

Use worksheet formulas to generate the ROC curve points needed for charting, and a separate table with AUC, confidence intervals, and related numerical output.

Example formulas:

=BESH.PLOT.ROC_POINTS(B2:B101,A2:A101)
returns threshold, sensitivity, specificity, and false-positive-rate columns for charting
=BESH.PLOT.ROC_STATS(B2:B101,A2:A101)

This is especially useful for workbook-based diagnostic workflows and teaching examples. The Tutorials page already lists ROC analysis as one of the planned additions, so this cookbook fills an important practical gap.

Histogram bins and optional normal overlay

Build chart-ready histogram tables directly in Excel.

Example formulas:

=BESH.PLOT.HIST_BINS(A2:A101)
=BESH.PLOT.HIST_NORMAL(A2:A101)

This is helpful when you want an Excel-native chart that remains fully editable.

Principal component analysis with extracted outputs

Fit PCA once, keep the handle in a dedicated cell, then extract the outputs you need.

Example formulas:

=BESH.MULTI.PCA_FIT(A2:F101)
=BESH.MULTI.PCA_SUMMARY(H2)

returns a model handle first, then extractor functions return labeled output tables

=BESH.MULTI.PCA_LOADINGS(H2)
=BESH.MULTI.PCA_SCORES(H2)

BESHStatNG already offers a full multivariate tutorial track covering principal component analysis, factor analysis, correspondence analysis, multiple correspondence analysis, k-means clustering, hierarchical clustering, and discriminant analysis. Use the cookbook as the quick formula entry point, and the multivariate tutorials for full workbook walkthroughs.

Kaplan–Meier survival curve table

Generate the data needed for an Excel survival plot directly from worksheet ranges.

Example formula:

=BESH.PLOT.KM_CURVE(A2:A101,B2:B101,C2:C101)
returns chart-ready survival step data

The Tutorials section already includes a survival-analysis track covering Kaplan–Meier plots, log-rank tests, and Cox regression, so the cookbook works best here as a formula-first companion rather than a full replacement tutorial.

Discriminant analysis from worksheet formulas

Use a handle-based workflow to fit the model once and then extract summaries, confusion tables, canonical outputs, and casewise results.

Example formulas:

=BESH.MULTI.DA_FIT(A2:D101,E2:E101)
=BESH.MULTI.DA_SUMMARY(H40)
=BESH.MULTI.DA_CONFUSION(H40)
=BESH.MULTI.DA_CASEWISE(H40)

This fits naturally with the multivariate tutorial track already available on the site.

GUI to formula mapping

Many BESHStatNG users start with the ribbon dialogs and then move to formulas when they want more reproducible workbook workflows. The cookbook helps make that transition easier.

Typical mappings include:

  • ROC Curve dialog → ROC point table + ROC statistics formulas
  • Histogram dialog → histogram-bin and normal-overlay formulas
  • PCA dialog → PCA_FIT plus summary, loadings, and scores extractors
  • Factor Analysis dialog → FA_FIT plus loadings, communalities, and factor-score extractors
  • K-means dialog → KMEANS_FIT plus centers and assignment extractors
  • Hierarchical Clustering dialog → HCLUST_FIT plus agglomeration and membership extractors
  • Discriminant Analysis dialog → DA_FIT plus summary, confusion, and casewise outputs

Download the companion workbook

The cookbook includes a companion Excel workbook with hands-on examples so you can see how the formulas are arranged in a real sheet, not just in isolated snippets.

Use the workbook to:

  • inspect the input layout
  • see the formulas in place
  • experiment with your own values
  • connect spill outputs to Excel charts
  • build your own reusable templates

Download the example workbook

Where to go next

The cookbook is meant to complement, not replace, the main tutorial tracks.

The Tutorials section currently provides full workbook-based learning tracks for: