The Latest Evolution in Spreadsheets

	$25 - immediate electronic delivery
	Free - 15 day trial version (1.6 MB)

"If you're familiar with spreadsheet formula construction, you should find
this concept fascinating."   - ZDNet

The Spheresoft Modeler is protected by U. S. Patent 6,199,078
"Analytic network engine and spreadsheet interface system".

The Spheresoft Modeler allows users to enter a value and multiple formulas into each cell of Microsoft Excel, and it permits circular references, making for spreadsheet models that are much more compact and easier to use.

The patented Modeler represents a major advance in the power and flexibility of spreadsheet technology. A standard spreadsheet limits the models you can express to unidirectional flows from input cells (with one value) to output cells (with one formula). Since the Spheresoft Modeler allows you to enter a value plus multiple formulas into each spreadsheet cell, with it you can create spreadsheets that specify all the relationships between all the cells in your model – end users enter values for the cells they know and the Modeler calculates values for the cells left blank. There is no more strict distinction between input and output cells. This provides a more intuitive interface paradigm and it allows users to work forward and backward in a problem very easily.

The Modeler also introduces a variety of other innovative features, such as separate formatting for user-supplied and calculated values, default values, and integrated back-solving that makes creating very flexible spreadsheets a snap.  It also features the ability to completely hide the calculation layers of your spreadsheet model, so that end users can neither change nor even see the underlying structure of the spreadsheet - they just type in values and get back answers.

Benefits

• Spreadsheets that are much more compact and manageable:  since multiple formulas can be entered into each cell, spreadsheets that have reasonably complex relationships between cells become much smaller with no redundant cells or formulas.

• Spreadsheets that are much easier for end-users to understand and use:  end-users simply enter the values they know and the cells left blank will be calculated.  Default values help users get a quick start and cut down on data entry.  And independent formatting for input values, calculated values, and defaults makes it clear which values the users entered and which were calculated by the Modeler.

• Faster spreadsheet development:  fully-integrated back-solving makes it extremely easy to fill out all the relationships between cells in a spreadsheet model. The formula editor, with drag-and-drop capability, makes it simple and quick to organize the calculations in a spreadsheet.  The Modeler also handles circular references elegantly.

• Easier to use and stronger model security:  rather than forcing spreadsheet authors to indicate which cells should be locked, the Modeler can lock the calculation layers of all cells, hiding the underlying calculations from view and modification.

• Built on the industry-standard Excel™ spreadsheet application: the environment is completely familiar to most users, and all of Excel's functions, macros, and access to VB or external functions are available.

A Simple Example

The simplest example of a spreadsheet model that demonstrates the power of the Modeler is a temperature conversion calculator that calculates equivalent temperatures in Celsius and Fahrenheit.

If you wanted to calculate the Fahrenheit equivalent of a Celsius temperature and the Celsius equivalent of a Fahrenheit temperature using a traditional spreadsheet, you would have to create two distinct models, one for each direction of conversion, F to C and C to F.  Using the Spheresoft Modeler you can create a single model, using just two cells, that represents the relationship between Fahrenheit and Celsius temperatures in both directions.

Let's say that in your model you wish the Celsius temperature to appear in cell C1 and the Fahrenheit temperature to appear in cell F1.  Then in cell C1, you would enter the formula "=(F1-32) * 5 / 9" which is the standard formula that converts a Fahrenheit temperature into a Celsius one.  And in cell F1 you would enter the formula "=(C1 * 9 / 5) + 32".  That's it.  Please note that you have just entered a circular reference.  If you did that in Excel (without the Modeler) you would get an error message, but for the Modeler it is no problem.

Now, if you know the Celsius temperature and want to find out the Fahrenheit one, enter a value in C1, for example "100", and a value will automatically be calculated for cell F1, in this case "212".  The formula you had previously entered in C1 will not be overwritten by the value 100 that you just entered, as it would be when using a traditional spreadsheet.  Now, if you know a Fahrenheit temperature and want to find its Celsius equivalent, just enter the value in F1, for example "32", and the 100 that was previously in C1 will be replaced by the calculated value "0", which is of course the Celsius equivalent of the given Fahrenheit value of 32.  The 100 has not been erased, just suppressed, so if you decide you want to back up, you can erase the new value (32) in F1 and the 100 will return to cell C1 and the 212 will be recalculated for F1.

Basic Concepts

First, let’s define a few terms.  An “input value” is a value that has been typed in directly by a user.  A “calculator” is a formula that is used to calculate the value in one cell based on the values in one or more other cells.  A “model” is the set of calculators and input values specified by the spreadsheet author.

There are a couple of concepts that are important for an understanding of how the Modeler works.  The first is this: in Excel, there is not a real distinction between an empty cell and zero.  They are formatted differently, but if you use an empty cell in a formula, it is treated like a zero.  This is not the case with the Modeler.  The Modeler treats empty cells as empty and does not allow formulas that depend on them to be computed.  This is how the Modeler chooses which formula to use to calculate the value for a cell that contains multiple formulas – it looks for one that has all of its input cells specified.

A second important concept is that the Modeler ensures that all the values showing on the screen at once are consistent with one another given the set of calculators it has.  A calculator will not be used if its output is not consistent with all the current input values in the model.  And if there are input values that are not consistent with one another, one or more of those input values will be suppressed.  When an input value is suppressed, a calculated value may show up in its cell instead.  A suppressed value is not erased, it is merely hidden from view and not used in any calculations.  But if the other input values conflicting with it are erased, a suppressed value will again appear and be used in calculations.  This behavior means you can “back up” in your work (which is somewhat different from Undo).

Default Values

Another new concept introduced by the Modeler is the Default Value.  A Default Value is a value that a cell will use if there is no user input and no calculator that can provide a value for that cell.  It’s the value used if the spreadsheet model doesn’t have anything else to put into a cell.  But, if the user supplies a value, either directly into the cell or via a calculator, then the default is ignored in favor of the input or calculated value.  A Default may be a constant value or it may be a formula. For instance, if you have a field called “Today”, you may want to give it a Default of “=Today()”, which is the Excel function that returns today’s date.  However, if the user types in another date into that field, the Today() function is ignored and the user input is used instead.

Separate Formatting for Inputs, Defaults, and Calculated Values

With the Modeler, you can specify that values which are input directly by the user should utilize a particular font and color, you can specify that calculated values should use another font and color, and that default values should use yet another font and color, if you wish.  This role-based formatting makes it easy for the user to keep track of what values he has supplied and which are being calculated.

Integrated Back-Solving

If you know the formulas for determining A from B and B from A, then you can enter them into the Modeler as two calculators.  However, in many cases, given a formula that calculates A from B, there may be no formula that simply calculates B from A.  Yet you may want your users to be able to determine B by entering a value for A.  That is where back-solving comes in.  Back-solving uses iterative numerical methods to guess values for B until it finds one that results in the value for A  that you typed in.  This is exactly the function played by “Goal Seek” and “Solver” within Excel and other traditional spreadsheets.  The difference between Modeler’s back-solving and that of other spreadsheets is that with the Modeler the back-solving functionality is tightly integrated;  it is not a separate mechanism that is applied to the spreadsheet.  Back-solvers in Modeler are just another form of calculator, and the Modeler will choose to use them just as it would choose to use any other given calculator formulas.   This makes it very simple to specify back-solving as part of your model, and it makes it easy for the end-user: they don’t need to know which values are calculated by formulas and which are back-solved; as always, they just enter the values they know and the Modeler calculates the ones left blank. 

Locking

With Excel, you can lock spreadsheets so that the values and formulas in certain cells cannot be changed.  Since the Modeler features multiple layers of values and formulas, you can lock the just layers supplied by the spreadsheet author and let the spreadsheet end-user add, delete, and change his own layers.  When a Modeler spreadsheet is locked, not only can the end-user not change the locked layers, he cannot even see them.  The spreadsheet is now a black-box, which he can use freely, without seeing what the “box” looks like inside, much more like a regular application than a spreadsheet model.

Easy to use

With all this new functionality, you might reasonably assume that the Modeler is complex to use and hard to learn.  This is, fortunately, not the case.  Entering values and formulas is as easy as entering them in Excel itself – you merely type them directly into the cells.  The difference is that with the Modeler, you can keep typing formulas, one after the other, into a single cell and build up your functionality.  The Modeler also features a pop-up edit window that helps you organize your work when you do have multiple formulas in a cell.  The Formula Editor window shows you all your calculators, defaults, and back-solvers in one place, allows you to reorganize them, delete them, or add new ones.  The Formula Editor can stay up while you are working and constantly show you the full contents of the cell you are currently working on.

All the other features of the Modeler are accessed via intuitive menu items on the Modeler menu, which appears on the main Excel menu bar.  A detailed Help system ensures that the answers to your questions are only a click away.

When there are multiple formulas for a particular cell, you can always see which formula calculated the current value by selecting the cell and looking at the Excel status line.  The Modeler also comes with a debugging tool that allows you to see which input values and which calculators are being used in which order in your spreadsheet overall.

Download a free trial copy of the Spheresoft Modeler

	$25 - immediate electronic delivery
	Free - 15 day trial version (1.6 MB)

Money Back Guarantee: We want you to be completely happy with our products. If you are unsatisfied with your Spheresoft product for any reason, let us know within 60 days of purchase and we will immediately refund your entire payment.

Not what you were looking for? Please send us your feedback!