Efficiently create and test complex formulas in RagTime
06/09/2016
How can one create and efficiently test complex formulas in RagTime, without losing track? A simple solution would be to split one complex formula into smaller chunks. In doing so, the formula, which is now separated into several functional units, will be easier to understand. Another advantage comes into play when debugging it: at the end of each functional unit there will be an interim result.
The training task
We shall illustrate this technique with a training task. In an input line, a date and a comment are to be entered, which should be transferred into the first empty row of a table, at the click of the button. The order of the entries is not necessarily chronological. In a second table, a list of all dates should be created, which lie between the oldest and the newest date in the first table, but are not part of it. The accompanying sample RagTime spreadsheet shows the completed solution, and shall serve as a reference for the various steps described below.
The problem with complex formulas
RagTime allows defining a formula which is linked with a button, and executed at the click of that button. However, the formula field in the button info is so limited in space that a formula, like the one that is required for our purpose, can hardly be displayed in its full length. Complex and nested formulas are anyways difficult to create, and even more difficult to modify and test. Instead of writing a complex formula, the operation can be split into single steps, whereby the button only triggers the first step, and thereafter each step triggers the next, and so on. By this means all steps can be strung together, allowing to test each newly appended step for proper and intended function.
Separate spreadsheet for formulas and auxiliary data
In this proposed approach all formulas and auxiliary data lie in a separate spreadsheet. This prevents unintentional data loss; when deleting data, no formulas get deleted or references become invalid.
The formulas are stored in column A of the formula spreadsheet. Each formula executes exactly one step on the “road to the solution”. Triggering the next step is achieved by the fact that the control cell (in our example cell D2) contains the current value that represents the number of the formula to be executed next in the sequence. This number is stored in column B. Column C provides a description of the formula. In column D you will find auxiliary data, which are used by the formulas. Corresponding descriptions can be found in column E. Column F contains such data as well, with an explanatory title in the column header. All cells used for formulas and auxiliary data are stored in the spreadsheet with the (non-printing) fill style sheet »Formel«.
In order to be able to easily move or copy formulas up or down, each formula contains two parts which control the process sequence:
- Generally, each formula is embedded in an “if” function, which checks if the number of the formula, i.e. the value Row-1, is in the control cell, so that the rest of the formula is only executed if this condition is fulfilled.
If($D$2=Row-1;…
- At the end of the formula the control cell value is raised by 1, however, this only happens if the next row also contains a formula, the one that is next executed. Otherwise, the control cell must be deleted, to finalize the formula sequence.
…SetCell(If(Index(B:B;Row+1);Row;"");$D$2)
Between these two parts of the formula lies the main function of this step. Both parts of the formula could have been written more easily. However, the used notation avoids problems when changing the sequence and facilitates testing only parts of the sequence.
Solution steps
Now we shall clarify the steps into which the desired sequence can be split. To yield perfect clarity, each formula should only process one single step in the solution chain. If that step generates values that are needed in other formulas as well, store them as auxiliary data in the spreadsheet. Or, for the sake of clarity, it may make sense to split that into a separate step.
We will not show each single solution step here, merely describe the basic idea for the solution of the fictitious task. The whole solution should be pursued by means of the individual formula descriptions in the formula spreadsheet.
The difference (in days) from the newest date to the oldest is calculated for all the dates in column A of the spreadsheet »R Tab«, and stored in column F of the »R Formeln« spreadsheet. In the final step, each date between the oldest and the newest date in »R Tab« is checked, to see if its difference to the oldest date in »R Tab« appears in column F of the spreadsheet »R Formeln«. Only those days that have no offset there are written to the target table.
Test, especially after modifications
If the formulas are strung together step-by-step, like described above, the result can easily be verified. Each formula can be modified until the result is as desired, before proceeding with the next step.
Changes are a little bit more tricky. Perhaps another auxiliary value is necessary which would require an additional row. If those parts of the formula that control the sequence were created as described above new rows can be added with no further ado, or cells be cut and added in another position of the sequence.
After such changes, it can become necessary to test the start of the formula sequence only up to a certain position. For this, just delete the number of the step, before which the test should be stopped, from column B. After testing successfully, the cell can be restored by doing copy/paste from another row in the formula. Also, testing the formula sequence from a certain position is quite easy: instead of triggering the sequence with the button, insert the number of the formula, with which the test should start, in the sequence control cell (in our example D2).
Unlike complex and nested formula constructions, a formula sequence that is arranged and documented in this way is comprehensible and transparent, even after years.
© 2016 Thomas Kaegi, ragtime-consult.ch GmbH
The purpose of these explanations is to help all RagTime users create complex documents. The use of the proposed method is therefore permitted and without any adaptations. However, passing on this instruction under other names is not allowed.
