It is time that so-called "Office suites" got beyond the spreadsheet.
Spread sheets are a wonderful and powerful idea. I have put them to good use several times. But they are tied to an idea that is no longer relevant - the idea from which they got their name.
A spread sheet is basically an infinitely (or atleast indefinately) large two dimensional table of cells. Each cell can do calculations based on values in other cells. This is great for doing calculations on columns or rows or whole tables. This works really well. There is just one problem.
I've never wanted to have an indefinately large table.
When I'm working with some data in a spreadsheet I might have a few numbers, or some rows or a whole table or two. But each of these have a well defined size, they aren't indefinate. It is true that I might want to add rows, and occasionally even columns, to a table, but when I do, I want to actually add them. I don't really want them to already be there, as they might well be in the wrong place.
But a spread sheet doesn't give you a few cell, rows, and the odd table. It gives you a virtually infinite sheet that you can put stuff in. And that is what people (well, myself at least) tend to do. There is room for a couple of tables side by side, a few random values and calculations and whatever. But the point is that it is all random. There is no right place to put things, so things go anywhere. An then if you want to add a row into the middle of a table, you end up inserting it in the middle of all the side-by-side tables that you might have. It was when this first happened to me that I realised something was wrong.
Many spread sheets today have multiple sheets open at once, with easy linking between them. This removes my immediate problem if not being able to insert a row in just one table - you have a separate sheet for each table. But still I feel something is wrong.
As I understand it, the name "spread sheet" can from a pencil-and-paper tool of the same name that modern spread sheets are based on. A large sheet of paper with a pre-ruled grid would be spread out, and various tables of data would be written onto it, presumably well spaced out. Then various calculations on this data would be performed and the sheet provided place to put the answers where they could easily be kept track of. As it is not possible to simply add columns on a sheet of paper, having lots of columns and rows pre-ruled help out a lot.
But that simply doesn't apply with a computer based spread sheet. It is trivial can very common to add rows and columns to a pre-existing table. So there is absolutely no need to have thousands of rows and columns pre-existing (even if they are only virtually existing).
In my mind, the way forward for spread sheets is to discard them as separate entities and simply add a "cell" concept to a standard word processor.
Word processors already have tables which can be created and extended at will, and which have well defined column headers and such. If one can create a table and embed a spread-sheet-like cell in every cell of the table, then you have the calculating power of a spread sheet right there with the formatting power of a word processor.
Ofcourse calculation cells don't have to appear in tables. They can appear anywhere in the document that a calculation is performed. Instead of creating a link to a separate spreadsheet when the text says that the mean of some set of values is "X", the calculation can be right there in the document.
Once we are liberated from the "infinite table" model of calculation there are other changes that become obvious. One this about spreadsheets that I don't like is that when you have a summary column - say column X is the sum of columns B through G, then you have to place that calculation in every single cell, rather than just once for the whole column. There are almost always very simply tools for duplication the cell suitably and it isn't hard. But it feels untidy that you have to.
The reason that we have to is obvious - normally you don't want the whole infinite column to have the same calculation. You probably want a separate heading in the first cell of the column, and you possibly want some other sort of column-summary just beyond the last cell of the column.
With a word processor (atleast a real word processor like TeX - I'm not sure exactly how WYSIWYG word processors work, their tables are almost as ad hoc as a spread sheet) There is often a default style for all cells which can be over-ridden for headers and footers. So the default style for a column could include a cell calculation which automatically then appears in every row that is created where the default is not explicitly over-ridden.
I would like to then take this model one step further and incorporate some simple database concepts into it. A table that has data explicitly entered into it should be treated much like a simple database. Columns in a table that are raw data are stored in the database. Columns in a table that are calculations are evaulated on the fly.
Then you could create a separate table in the word processor which has columns selected fromn the database, and instead of explicitly listing rows, it could be given a row-rule - a way to extract rows from the database such as "Column X is less than 42". This would make it possible to create reports based on subsets of "spread sheet" data without selectively hiding column and rows - another pet peeve of mine.
Doing this would smooth the current rather artificial distinction between a spread sheet and a database, and the distintion between a word processor and a report generator.
Ultimately I would like to have just one tool that could do arbitrary formatting, arbitrary calculation, and can access tabular data either in a simple file or behind some sort of SQL interface.
Then you just need to add an obnoxios background and use a large font, and the same tool becomes your presentation manager.
Comments...
Re: Beyond Spreadsheets (13 June 2007, 07:46 UTC)
A World Beyond Spreadsheets. See www.atebit.co.uk for a program that blows standard spreadsheets out of the water, this is how spreadsheets should of been developed.
I will be happy to answer any questions.
The European Spreadsheet Risk Interest Group has a collection on numerous research papers on errors in, and critical problems with spreadsheets. It is fact that spreadsheets have changed very little over the last twenty-five years, apart from becoming more of a presentation tool and this is where Atebion comes into the picture in a world of its own.
We claim that Atebion is the first universal numerical processor, where word processors play with words we play with numbers. This is how spreadsheets should have been developed.
Atebion fills the gap between spreadsheets and professional mathematical programs, getting over some of the main weaknesses of both. Atebion really comes into its own where standard spreadsheets start to run out of steam: at the point, where if you described a task to a programmer, he or she would suggest using Excel with Visual Basic as the answer But, that does not mean you can’t use Atebion for simple task.
A number of fully developed standard Atebion models already exist where the user may never see a single equation. These models are controlled by the use of a macro menu which minimizes errors at all stages of data input. The standard costing, resource optimization and contract monitoring model is one such model and should do most of your modeling for each department.
An optional full bureau service is available to assist with design, building and maintenance of models. We can also act as “Model Administrator” (along the lines of Database Administrators) to regulate Atebion models within your organization.
Atebion is an alternative and complimentary to standard spreadsheets:
Atebion has been designed to replace mission critical spreadsheets, making it easier for the user to build, alter and validate models, giving more confidence to the managers.
Atebion is a numerical processing package for solving modeling problems and has the ability to bring information together from standard spreadsheets, different financial systems and different databases in order to see variances and investigate “What ifs”, all under full control with the minimum of errors.
With Atebion information entry is in many ways similar to that of a standard spreadsheet but unlike a spreadsheet you only need to have a clear understanding of your problem to create or alter models. Atebion is special in its ability to switch inputs and outputs, exploiting the potential of modern computers for advanced “what ifs”.
Atebion works on the principle that:
¨ We identify entities ('Variables' in Atebion) which can be measured in terms of numbers. Examples: In finance Income, Expenditure, Profit, Cost, Quality, Outcome. In Science and Engineering Density, Mass, Volume.
¨ We establish rules ('Equations' in Atebion) which relate the Variables. (Example: Profit = Income - Expenditure).
¨ These Equations represent our world (or the part of it that we have decided to model).
¨ We then decide which Variables have Known values. (Example: Profit = 42. Income = 1212).
¨ Within the normal algebraic rules of solubility, Atebion will calculate the Unknowns. (Example: Expenditure = 1170). We do not need to know how to re-arrange an Equation.
Unlike a spreadsheet, we do not have to decide at the outset which Variables are to be Known (i.e. inputs) or Unknown (outputs). We have a general model, not a purpose made method of calculating particular answers.
The data and the Logical Rules (expressed in the form of algebraic equations) are clearly separated. The data presentation does not get in the way of the data analysis.
Follow the logic of a model a year on
Most programmers know that when you come back in two weeks you will have forgotten how you did the calculations. In Atebion a Variable is identified by its own descriptive name. Equations appear in standard language such as:
[totalFixed Cost Prod A] + [totalVariable Cost Prod A] =
[unitCost Prod A] * [totalWork Load Prod A]
This avoids unintelligible Cell references and in addition any object can have additional comments attached and freely visible.
The full name of each variable will be shown as follows:
[folderName] [columnName] [royName]
Note: Most users will never see a single equation.
Structure thousands of sheets for easy navigation
Tens, hundreds or even thousands of sheets can be structured in an alterable tree like structure (similar to Windows Explorer® file manager) for easy navigation and a clear understanding of any model.
NOTE: In Atebion we force you to structure your model. Navigation is an important issue. How would people navigate without Windows Explorer® file manager?
Model the way you work and in a way you know how
By using Equations instead of one-way formula you model the way you work without thinking how to convert your understanding of the tasks to formula(s), this also makes it easier for others to follow your logic.
Goal seek without re-designing your model
After you have defined your relationships in Atebion you can reverse any inputs and outputs. This gives you the ability to goal seek without editing a single Equation. More advanced or frequent "What ifs" may be controlled by the use of macros where you can automatically swap known and unknown variable attributes and automatically switch appropriate equations on and off if needed.
{if spreadsheets add “What if …?” to standard BASIC, then Atebion adds “What about …?” to spreadsheets.] No need to buy those add-ons that don’t really work well}.
Find logical errors and inconsistencies within a model
Special and unique diagnostic tools identify logical errors and inconsistencies in Atebion. Such cases as unsolvable task or unstable (continuous) solutions are highlighted by the program and errors are easily traced in most models.
Atebion can also display a list of all Equations acting on a Variable and all Variables involved in a particular Equation. By alternately selecting Equations and Variables you can "drill down" through a system of Equations to identify how a value is derived.
Select any variable and immediately see what it depends on, or it influences, with a break down of each calculational step.
Interactive Graphs
As you highlight a variable in a sheet you can see the corresponding point on a graph or highlight a point on a graph and you will see the corresponding sheet with the corresponding cell highlighted. Allows the busy manager to immediately see where the point on the graph comes from.
Save and Add sub-projects
Different users can work on different models which can be saved as a whole or part of, then added or substituted at any time into a master model. This allows you to build more manageable, modular template solutions, which can be combined into a bigger, more complex solution.
The top report sheet of our hospital diagnostic departments costing models feeds the surgical departments costing models with a price list.
Model Feedback Processes
Many real systems involve feedback processes ‘A’ depends on ‘B’ which it self depends on ‘A’. In reality these situations involve simultaneous Equations that Atebion solves automatically. As an example this is ideal for cross charging overheads where 5% of finance budget goes to supplies and 6% of supplies budget goes to finance (no more circular argument errors. [itis always beneficial to make the tool fit your system, rather than making your system fit the tool.]
Extended Equation Syntax
Equation Syntax is just a means to assist you to write Equations in shorthand. You can still write all Equations in long hand but why walk everywhere when you have purchased a car?
Syntax can save considerable work. e.g. For example: if you have three sheets of identical size, named Budget, Actual and Variance, only one Equation is needed to calculate all the variance values of each Cell. This single equation can be extended to cover additional sheets when you insert keywords in Folder names.
We have one customer whom has over 1,600 sheets with over 1,400,000 calculated values. There are only 160 equations, one for each rule.
Test Equations
After you create a more complex equation or to assist you with understanding someone else’s equation (especially where advanced syntax is used), you can use the Test Equation button to see the results after the computer breaks down complex equations into their basic parts.
Create customized reports with automatic linked equations
Automatically create linked reports without the need to write a single equation.
Best fitting solution where no solution is found
On calculation if no perfect solution is found then you will have the option to load the best fitting solution found, even if it does not match all your limitations. You can then decide if these values are acceptable without any further work.
Shorter Model Building times
The time it will take to build a real working business model in Atebion is much less than it would take in spreadsheets. The larger the model, the more time saved, and then you have the added advantages of other trained developers being able to follow your logic and edit your model.
User Defined Functions
The developer has the ability to write their own user defined mathematical functions.
User Defined Macro facilities
By using Atebion’s Macro facilities the user can greatly reduce the time and errors when updating a standard model.
User Version of the Atebion Software
To minimize errors we have a User Version of the software to edit standard models that have been created in the Professional Developers Version. The user won’t be able to carry out commands except by the use of macros.
User Defined Functions
The developer has the ability to write their own user defined mathematical functions.
Access and User Model Protection
The software program can only be leased and comes in three versions:
· Professional Developers Version: This is restricted to fully trained approved model developers. This version is the same as the Standard Developers Version but with the additional feature to create macro controlled user models which simplify end user training and minimizes errors during the day to day editing and reporting (see the costing model example). Models created in this version can not be edited by the Standard Developers Version which gives extra protection to what we call Core Models.
· Standard Developers Version: Full developer’s version excluding the Macro Editor. Users can create and edit models, except those last saved in the Professional Developers Version.
· User Version: This version can load Standard Developers Models but with restricted commands available for the day to day user in order to minimize errors. Standard Core Models which require access to more advanced commands which are not available in the User Version may use the Macros created in the Professional Developers Version (see the costing model example).
Ensuring that errors are kept to the minimum and users can not do illegal actions, the Professional Developers version can simplify complex models like the Standard Costing Model so that day to day users with minimum training can only update allowed figures and create allowed reports using the User Version,
You may like to see our Atebion software in Scientific Computing:
http://www.scientific-computing.com/scwdecjan06review.html
Kind regards
Barry Phillips
Director
AtebIT Limited
Tel: +44(0)1248 602 519
Mobile: + 44 (0)7876 387 453
www.atebit.co.uk
Re: Beyond Spreadsheets (13 June 2007, 07:45 UTC)
A World Beyond Spreadsheets. See www.atebit.co.uk for a program that blows standard spreadsheets out of the water, this is how spreadsheets should of been developed.
I will be happy to answer any questions.
The European Spreadsheet Risk Interest Group has a collection on numerous research papers on errors in, and critical problems with spreadsheets. It is fact that spreadsheets have changed very little over the last twenty-five years, apart from becoming more of a presentation tool and this is where Atebion comes into the picture in a world of its own.
We claim that Atebion is the first universal numerical processor, where word processors play with words we play with numbers. This is how spreadsheets should have been developed.
Atebion fills the gap between spreadsheets and professional mathematical programs, getting over some of the main weaknesses of both. Atebion really comes into its own where standard spreadsheets start to run out of steam: at the point, where if you described a task to a programmer, he or she would suggest using Excel with Visual Basic as the answer But, that does not mean you can’t use Atebion for simple task.
A number of fully developed standard Atebion models already exist where the user may never see a single equation. These models are controlled by the use of a macro menu which minimizes errors at all stages of data input. The standard costing, resource optimization and contract monitoring model is one such model and should do most of your modeling for each department.
An optional full bureau service is available to assist with design, building and maintenance of models. We can also act as “Model Administrator” (along the lines of Database Administrators) to regulate Atebion models within your organization.
Atebion is an alternative and complimentary to standard spreadsheets:
Atebion has been designed to replace mission critical spreadsheets, making it easier for the user to build, alter and validate models, giving more confidence to the managers.
Atebion is a numerical processing package for solving modeling problems and has the ability to bring information together from standard spreadsheets, different financial systems and different databases in order to see variances and investigate “What ifs”, all under full control with the minimum of errors.
With Atebion information entry is in many ways similar to that of a standard spreadsheet but unlike a spreadsheet you only need to have a clear understanding of your problem to create or alter models. Atebion is special in its ability to switch inputs and outputs, exploiting the potential of modern computers for advanced “what ifs”.
Atebion works on the principle that:
¨ We identify entities ('Variables' in Atebion) which can be measured in terms of numbers. Examples: In finance Income, Expenditure, Profit, Cost, Quality, Outcome. In Science and Engineering Density, Mass, Volume.
¨ We establish rules ('Equations' in Atebion) which relate the Variables. (Example: Profit = Income - Expenditure).
¨ These Equations represent our world (or the part of it that we have decided to model).
¨ We then decide which Variables have Known values. (Example: Profit = 42. Income = 1212).
¨ Within the normal algebraic rules of solubility, Atebion will calculate the Unknowns. (Example: Expenditure = 1170). We do not need to know how to re-arrange an Equation.
Unlike a spreadsheet, we do not have to decide at the outset which Variables are to be Known (i.e. inputs) or Unknown (outputs). We have a general model, not a purpose made method of calculating particular answers.
The data and the Logical Rules (expressed in the form of algebraic equations) are clearly separated. The data presentation does not get in the way of the data analysis.
Follow the logic of a model a year on
Most programmers know that when you come back in two weeks you will have forgotten how you did the calculations. In Atebion a Variable is identified by its own descriptive name. Equations appear in standard language such as:
[totalFixed Cost Prod A] + [totalVariable Cost Prod A] =
[unitCost Prod A] * [totalWork Load Prod A]
This avoids unintelligible Cell references and in addition any object can have additional comments attached and freely visible.
The full name of each variable will be shown as follows:
[folderName] [columnName] [royName]
Note: Most users will never see a single equation.
Structure thousands of sheets for easy navigation
Tens, hundreds or even thousands of sheets can be structured in an alterable tree like structure (similar to Windows Explorer® file manager) for easy navigation and a clear understanding of any model.
NOTE: In Atebion we force you to structure your model. Navigation is an important issue. How would people navigate without Windows Explorer® file manager?
Model the way you work and in a way you know how
By using Equations instead of one-way formula you model the way you work without thinking how to convert your understanding of the tasks to formula(s), this also makes it easier for others to follow your logic.
Goal seek without re-designing your model
After you have defined your relationships in Atebion you can reverse any inputs and outputs. This gives you the ability to goal seek without editing a single Equation. More advanced or frequent "What ifs" may be controlled by the use of macros where you can automatically swap known and unknown variable attributes and automatically switch appropriate equations on and off if needed.
{if spreadsheets add “What if …?” to standard BASIC, then Atebion adds “What about …?” to spreadsheets.] No need to buy those add-ons that don’t really work well}.
Find logical errors and inconsistencies within a model
Special and unique diagnostic tools identify logical errors and inconsistencies in Atebion. Such cases as unsolvable task or unstable (continuous) solutions are highlighted by the program and errors are easily traced in most models.
Atebion can also display a list of all Equations acting on a Variable and all Variables involved in a particular Equation. By alternately selecting Equations and Variables you can "drill down" through a system of Equations to identify how a value is derived.
Select any variable and immediately see what it depends on, or it influences, with a break down of each calculational step.
Interactive Graphs
As you highlight a variable in a sheet you can see the corresponding point on a graph or highlight a point on a graph and you will see the corresponding sheet with the corresponding cell highlighted. Allows the busy manager to immediately see where the point on the graph comes from.
Save and Add sub-projects
Different users can work on different models which can be saved as a whole or part of, then added or substituted at any time into a master model. This allows you to build more manageable, modular template solutions, which can be combined into a bigger, more complex solution.
The top report sheet of our hospital diagnostic departments costing models feeds the surgical departments costing models with a price list.
Model Feedback Processes
Many real systems involve feedback processes ‘A’ depends on ‘B’ which it self depends on ‘A’. In reality these situations involve simultaneous Equations that Atebion solves automatically. As an example this is ideal for cross charging overheads where 5% of finance budget goes to supplies and 6% of supplies budget goes to finance (no more circular argument errors. [itis always beneficial to make the tool fit your system, rather than making your system fit the tool.]
Extended Equation Syntax
Equation Syntax is just a means to assist you to write Equations in shorthand. You can still write all Equations in long hand but why walk everywhere when you have purchased a car?
Syntax can save considerable work. e.g. For example: if you have three sheets of identical size, named Budget, Actual and Variance, only one Equation is needed to calculate all the variance values of each Cell. This single equation can be extended to cover additional sheets when you insert keywords in Folder names.
We have one customer whom has over 1,600 sheets with over 1,400,000 calculated values. There are only 160 equations, one for each rule.
Test Equations
After you create a more complex equation or to assist you with understanding someone else’s equation (especially where advanced syntax is used), you can use the Test Equation button to see the results after the computer breaks down complex equations into their basic parts.
Create customized reports with automatic linked equations
Automatically create linked reports without the need to write a single equation.
Best fitting solution where no solution is found
On calculation if no perfect solution is found then you will have the option to load the best fitting solution found, even if it does not match all your limitations. You can then decide if these values are acceptable without any further work.
Shorter Model Building times
The time it will take to build a real working business model in Atebion is much less than it would take in spreadsheets. The larger the model, the more time saved, and then you have the added advantages of other trained developers being able to follow your logic and edit your model.
User Defined Functions
The developer has the ability to write their own user defined mathematical functions.
User Defined Macro facilities
By using Atebion’s Macro facilities the user can greatly reduce the time and errors when updating a standard model.
User Version of the Atebion Software
To minimize errors we have a User Version of the software to edit standard models that have been created in the Professional Developers Version. The user won’t be able to carry out commands except by the use of macros.
User Defined Functions
The developer has the ability to write their own user defined mathematical functions.
Access and User Model Protection
The software program can only be leased and comes in three versions:
· Professional Developers Version: This is restricted to fully trained approved model developers. This version is the same as the Standard Developers Version but with the additional feature to create macro controlled user models which simplify end user training and minimizes errors during the day to day editing and reporting (see the costing model example). Models created in this version can not be edited by the Standard Developers Version which gives extra protection to what we call Core Models.
· Standard Developers Version: Full developer’s version excluding the Macro Editor. Users can create and edit models, except those last saved in the Professional Developers Version.
· User Version: This version can load Standard Developers Models but with restricted commands available for the day to day user in order to minimize errors. Standard Core Models which require access to more advanced commands which are not available in the User Version may use the Macros created in the Professional Developers Version (see the costing model example).
Ensuring that errors are kept to the minimum and users can not do illegal actions, the Professional Developers version can simplify complex models like the Standard Costing Model so that day to day users with minimum training can only update allowed figures and create allowed reports using the User Version,
You may like to see our Atebion software in Scientific Computing:
http://www.scientific-computing.com/scwdecjan06review.html
Kind regards Barry Phillips Director AtebIT Limited Tel: +44(0)1248 602 519 Mobile: + 44 (0)7876 387 453 www.atebit.co.uk
[permalink][hide]