Overview

Spreadsheets often contain long formulas, especially for science and engineering, and often the intermediate calculations require range checking, for example to check for a divide by zero.

Multi formula spreadsheet cells simplify and reduce the size of a spreadsheet by enabling the user to enter multiple formulas in one cell and using the result of earlier formulas in subsequent formulas, rather than having to use multiple cells.

Using multiple cells obviously clutters the spreadsheet and if the formula needs to be evaluated in multiple cells it is tedious to update all the occurrences, and error prone. Repeating the same formula in the same cell reduces the clutter of multiple cells but doesn't reduce the risk of not all the occurrences being updated, plus deeply nested spreadsheet formulas are difficult to write and maintain. For both options if the cell or cells, is or are copied and pasted then multiple cell references may need to be updated. This can be very tricky for cells where the formula is repeated.

Debugging a cell with a large formula and or repeated formula is very time consuming. To find the error in the formula or the data often requires the cell to be copied and pasted into multiple cells so that it can be broken down and then each of the copies has to be edited so that it evaluates a small part of the original formula. Often the pasting will generate errors due to changes in the references.

In a multi formula cell, rather than have one long formula, may be with repeated formulas, the user can split the formula in to functional groups based on function or the need to reuse and each formula in the cell is evaluated and the user is able to see the result, enabling easier debugging of complex formulas.

Features and Functionality

Simplification of cell formulas and spreadsheets

The primary motivation for multi formula spreadsheet cells is to simplify cell formulas and spreadsheets. Many equations in engineering and science are large. Engineering equations often split these into groups to simplify the equation, for example, the value for the inside heat transfer coefficient of a tube, (when there is no phase change) can be found from,

The example below shows how the formula could be evaluated by calculating Re, Pr and Nu in individual cells, cells B10, B11, B12 and in a single cell, C14. Evaluating the formula using separate cells increases the size of the spreadsheet making it harder to identify the result. Cell C14 shows how complex the equation is if it is evaluated within a single cell.

Below is how the formula could be calculated in a spreadsheet that has multi formula cells and shows how both the spreadsheet and the cell formula are simplified and easier to read.

• A cell is specified as a multi formula cell by right mouse clicking the cell and selecting edit multi-formula cell or similar, or from the application toolbar, menu bar etc. This displays an editing box, for example a pop-up dialog, for editing the cell formulas to be displayed, as shown
  • the graphical presentation of the editing box can be of many different forms depending on the application and platform, (hardware and Operating System). The examples show a pop-up dialog (border-less, no buttons, etc.), but a side bar, either permanent or floating out when required, could be a option for applications both on computers and smart phone and tablet applications, while Internet browser based spreadsheets could use a second browser window, etc.
• The first column of the editing dialog shows the result of the formula for the row
• The user enters the variable name for the formula in the second column of the editing dialog. Leaving the variable name blank, in this example the last line, assigns the result to the cell's value, that is the value that will be displayed in the cell and used by any formulas that reference the cell
• The user enters the formula for each line in the third column
• The example doesn't show reference colouring, but this is supported
• The example shows the user editing the last formula. Formulas and variable names are edited as if they were a spreadsheet cell
• The last row of the editing box is blank, when a formula or variable is entered in the row, a new blank row be created below it. The editing box will support a right mouse pop-up menu to insert, append, copy, paste, rows as expected
• The user can of course configure the formulas as suits their needs. In the example the value of (Nu * B7)/B4 could have been directly assigned the cell's value, but in the example it was decided to create the variable hi and then assign hi to the cell's value, to make the purpose of the cell's formula self documenting
• A potential problem with variable names is if the user uses names that are the same as cell references, e.g. A1, B2, C3 etc.
  • Spreadsheet programs generally use capital letters for cell references in formulas, so permitting lower case letters could be a way to differentiate between cell references and variables, i.e. A3, B2, C1 etc. are cell references and a3, b2, c1 etc. are variables, but as spreadsheet programs typically automatically capitalise cell references entered in cell formulas this would conflict with existing functionality
  • It would be simplest to not permit variable names that match cell references. Even if the initial configuration of cell references and variable names isn't ambiguous there is no guarantee this won't occur if the formula isn't copied and pasted to another cell. Functionality to warn when variable names are ambiguous with cell references could be included but it will just complicate the editing box.

Simplifying formula debugging

The ability to assign references and formulas to variables makes the purpose of the cell's calculation much easier to understand, for example it is much easier to work out the intent of the following formula

When working with a long and complex formula it is very easy to make a mistake when entering it. One way to debug the formula is to copy it and paste it into multiple cells and edit each cell so it performs a component of the complete equation. This can be especially tedious if there are no empty cells nearby, and once the problem has been found, then the original formula has to be edited to correct it and there is no guarantee that the edit will be correct.

A multi formula cell allows the formula to be split without having to find cells to use. And once the formula is debugged the debugged formula is used, the location of the error in the original formula doesn't have to be located and the correct change applied.

Simplifying formulas with Conditional Functions

If you want to use the same equation in both the evaluation statement of a conditional function and the result, with current spreadsheets you have to repeat the formula every time it is needed, which makes the overall function longer and harder to read and understand and much more difficult to correctly update if the formula needs to be corrected and, or modified.

The example below is from a spreadsheet cell that indicates if the expected date has changed since the previous month. In a current spreadsheet the formula is,

In a spreadsheet with multi formula cells the above formula can be written as

Simplifying copying and pasting formulas

In the formula in the first example, the cells B4, B5 and B7 are used multiple times, if the equation is copied and pasted then both occurrences will need to be updated, even if the cell references are updated graphically by dragging the reference rather than editing the formula box.

The example below shows that assigning a cell reference to a variable not only makes the formula easier to understand, but means that if the formula is pasted and the locations of the data are different, then each reference only needs to be updated once.

Access to variables calculated within the cell

It is good programming practice to only calculate a value in a single location within a program so that if the formula is updated it only has to be done so once.

With multi formula spreadsheet cells providing the ability to calculate a number of variables, but with only one formula being the cell's value, i.e. the value that will be displayed in the cell and used by any formulas that reference the cell; the spreadsheet provides a "dot variable" functionality that enables the user to access variables calculated in the cell, by specifying the cell address, immediately followed by a dot and the variable name. For example, from the examples above, B10.Re, B10.Pr etc. or cell.area (from the example formula for calculating the volume of a number of tubes).

Cells accessing the variables within a multi formula cell wouldn't need to be multi formula cells themselves. Any cell type that references a variable that is not created in a multi formula cell would generate a reference error, e.g. #Ref! in Excel and LibreOffice Calc.

Revision history

When using a spreadsheet to perform calculations, for example sizing equipment, estimating the cost of a project, etc. using a standard spreadsheet enables continuous improvement of the spreadsheet by adding functionality and fixing errors. Frustration occurs when after making a number of improvements to the latest version of the spreadsheet a custom formula or value is needed for the new version.

Multi formula cells allow the user to keep the normal formula in the cell while providing the ability to use a custom formula and a means to document what is happening.

There are many ways that the user can add a custom formula;

• just enter the custom formula after the normal one, the normal formula will be evaluated, but then overwritten by the next formula
  =HLOOKUP(A1,B1:C10,2,FALSE)
  =200
• use variable names to indicate history of the formula, assigning the normal formula to a variable that indicates it is the normal, original etc. and then assign the custom value straight to the cell value or via an identifying variable name
  normal =HLOOKUP(A1,B1:C10,2,FALSE)
  custom =200
         =custom
• use comments, multi formula cells would support line comment characters such as C/C++'s "//"
  //=HLOOKUP(A1,B1:C10,2,FALSE)
  //temporarily comment out the above for this project
  200

Similarly, if we want to maintain a revision history of the cell's formula we can use same techniques to comment on the formula's revision history and or use variable names to identify the revision number of each formula in the cell.

Dynamic formula evaluation

Although not intrinsic to the implementation of multi formula spreadsheet cells, the multi formula cell editing dialog box makes it possible to display the evaluated formulas and the values of referenced cells contained in each formula. The formula evaluation would be displayed in an additional column to the right of the formula as shown following;

• For clarity the example shows a cell with only a single formula, as the functionality is independent of the number of formulas in the cell. This functionality can also be implemented in existing spreadsheets
• For multi formula cells the formula to view is selected by clicking the right arrow, or other indicating icon or similar graphical device, at the right of the entered formula. For existing spreadsheets, the pop-up window would be displayed by right mouse clicking the cell and selecting view dynamic evaluation or similar, or from the application toolbar, menu bar etc.
• The top line of the dynamic evaluation view shows the entered formula with all cell referenced replaced with the referenced values
• As necessary, subsequent lines show the evaluation of each level of parentheses
  • In the example above, each group of parentheses are evaluated independently, that is the third level (1300/500) and the first level (50+273.15) are evaluated in the iteration. It may be easier to implement so that all parentheses of the same level are evaluated at the same iteration; the above example would appear as,
    =(((1300/500)^0.28443649)-1)/0.71*(50+273.15)
    =((2.6^0.28443649)-1)/0.71*(50+273.15)
    =(1.31230132-1)/0.71*(50+273.15)
    =0.31230132/0.71*323.15