Threshold detection by fitting segmented regression models in Microsoft Excel

We present a generally applicable method for segmented regression analysis, which is suitable for describing data that follow two distinct functions that meet at an unknown transition or break point and is particularly useful for detecting thresholds. Although segmented regression analysis is available in Matlab and R, it requires specialist knowledge beyond the expertise of many researchers. We illustrate a method for fitting experimental data with two distinct segmented linear functions using SOLVER, freely available with Microsoft Excel. A spreadsheet template is created for input of experimental data and the fit between the model and the data optimised using SOLVER's iterative least squares fitting routine to estimate the transition point. We then demonstrate how the method can be expanded to incorporate combinations of linear and non-linear functions. The method is ideal for rapid processing of data and sufficiently flexible to allow for modifications to functions when required. •Experimental data that follow a model comprising two distinct functions that meet at an unknown transition point is amenable to segmented regression analysis. •We describe a method that uses SOLVER, an add-in that is freely available with Microsoft Excel, to carry out this analysis. •The method does not require any specialist programming knowledge.