SPREADSHEET COMPOSER: AN OVERVIEW

INTRODUCTION

Spreadsheet Composer is an Excel add-in, which provides facilities to automatically construct, verify, catalogue, manage and implement complex worksheet formulae.

The core capability of Spreadsheet Composer is the 'compose' function.  This enables the user to define a spreadsheet calculation as a series of small interlinking steps and to combine those steps into a single complex formula at the click of a button.  Further explanation is given in the 'examples' section below.

Once a formula has been constructed, it can be verified.  Different values can be written into the input cells and it can be confirmed that the output from the new formula is always identical to the output from the original sequence of calculations. It can also be confirmed that the formula provides the expected output for a range of different input values. 

The verified formula can be named, commented and stored in a database.  The formula, together with its explanatory notes, can be retrieved from the database as required. 

A formula may be implemented in a working spreadsheet by means of the 'copy' function.  This enables the user to select a destination cell for the formula and to select the input cells for the formula.  The addresses of the input cells within the formula may be automatically selected to be in relative or absolute address format.

Once a formula has been written into a spreadsheet, its complexity may look somewhat intimidating.  Fortunately, Spreadsheet Composer provides the user with the ability to automatically match a formula in a spreadsheet to one in the database. In this way, the stored notes for a formula can be automatically retrieved, when required.

Spreadsheet Composer provides an 'export' function, which allows a completed spreadsheet to be written out into a new workbook.  This enables a finished spreadsheet to be used in a standalone environment that is independent of Spreadsheet Composer.


EXAMPLES

This is a very simple example to demonstrate the 'compose' function.

In a sale, prices are reduced by 20% and rounded to the nearest dollar.  A spreadsheet to compute the price has the following entries.

Cell C10 = Pre-sale input value

Cell D10= '+C10*80/100'

Cell E10 = '+ROUND(D10,0)'

If cell 'E10' is selected and the 'Compose' button is clicked then the following formula is automatically generated.  

Composed formula = '+ROUND(C10*80/100,0)'

This is a simple example of a composed formula. 

The following is an example of a complex formula, compiled using Spreadsheet Composer.

Composed formula = IF(OR(ISNUMBER(D8), ISBLANK(D8)), IF(OR(D8<0, NOT(ROUND(D8, 0)=D8)), "invalid data", IF(ISBLANK(D8), "", D8*IF(D8>=10, G8, IF(D8>=5, F8,E8)))), "invalid data")

This formula computes a total price for the number of items specified in cell 'D8', using a different price per item depending on whether the number of items is 4 or less, 5 or more, or 10 or more.  Additionally, the formula outputs a blank if the entry for the number of items cell is blank and otherwise outputs the message "invalid data" if the number of items is not a positive whole number. 

