Excel-based analytical engines have been developed frequently for specific, limited purposes. Analyzing larger data sets or a breadth of conditions can be difficult because of design limitations in the analytical engine. Sometimes altering array operations is not straightforward. More frequently, misguided application of password protection hinders simple modifications or one must use an unaltered version of a published workbook. The workbook MonthEToEngine.xlsx is an example of an analytical engine for computing ET rates from meteorological data and is password protected (Figure 1).
The EngineINTERFACE.v3.xlsm workbook automates the process of analyzing data sets larger than the input of an analytical engine. Larger data sets can be analyzed without modification by analyzing subsets of data and tabling analytical-engine results from these subsets. Automation makes this approach tractable and avoids potential errors from manual execution of this approach. Larger ranges of input data and output results are specified in an auxiliary workbook (Figure 2).
The EngineINTERFACE.v3.xlsm workbook consists of a single control on one page that launces the Specify Ranges form (Figure 3). The form allows necessary ranges to be specified interactively, activate workbooks, and specify accumulation of results along rows or columns. Usage of the Specify Ranges form is annotated and summarized on the single page with the “Process data through engine” button.
EngineINTERFACE.v3.xlsm, MonthET_Data+Results.xlsx, MonthEToEngine.xlsx, and explanatory PDF can be downloaded with the following link.
Revisions
July 17, 2021—Revisions in version 2 include the following. Explanation graphic added to workbook.
September 20, 2023—Revisions in version 3 include the following. Modified minimum auxiliary workbooks from 2 to 1. This was so that results can be accumulated in the same workbook that contains the analytical solution.