OASIS Mailing List ArchivesView the OASIS mailing list archive below
or browse/search using MarkMail.


Help: OASIS Mailing Lists Help | MarkMail Help

office-comment message

[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]

Subject: RE: [office-comment] Requirement - reinvent the spreadsheet

Another element which would help in quality assurance of spreadsheets

Joe Raftery


+CATEGORY (select one or more from below)

+SCOPE (select one or more from below)


One way to quickly identify loose thinking in calculations is to check
the units match up on both sides of the equation.

If the user is multiplying Area by kW and getting an answer in degrees
Celsius then 
there might be a problem.

Checking that the units match up is an application issue but for the
applications to 
do this it needs to be made a lot easier to specify what units the
number in any 
cell or column is measured in.

Each cell has a number type (floating point, integer, logical, text
and a display type (%, fixed numer of decimals,  etc.). 

Extend this to allow any cell to be assigned any arbitrary unit type 
(Kelvin, kW, billions of dollars per day, micro parsecs per 
fortnight, football pitches).

Allow users to include a definition for any new unit they add, defining
relationship with other units and how it should be displayed (what
to use? at the front or at the back?).

Currency would become a unit type rather than a display format.

Interestingly Gregorian and Julian dates become display formats (with 
the underlying number in Days before or after 1/jan/1900).

Applications must display the units as specified.

If they want to applications may use units in a number of ways: 
* apply QA tests to require units to be specified for all numbers, 
* check units correspond in formulas,
* automatically change formulas to add a conversion factor if the units
are changed.


-----Original Message-----
From: Joe Raftery [mailto:JoeRaftery@hoarelea.com] 
Sent: 11 March 2009 15:40
To: office-comment@lists.oasis-open.org
Subject: [office-comment] Requirement - reinvent the spreadsheet

Joe Raftery


+CATEGORY (select one or more from below)

+SCOPE (select one or more from below)

We use spreadsheets for presentation and they are not good at that.

We create a little table - columns for each data type. Column for 
calculated results on the right, calculated based on the data columns on
the left.
We add a row at the top for column names.
We add a row at the bottom for totals.
We add some more rows at the top for a heading so it looks better.
We add a parameter we might want to change and put it off to the side.
We add some more calculations on the bottom to process the totals and
complain that
the column width doesn't work with this bit.
We come back a month later and change a number and get the wrong answer 
and it takes a week to find where we made the mistake.

Let's rethink the spreadsheet. 
What if a spreadsheet is reduced to a simple table embedded in an ODF
page that
contains lots of these tables.
The column names are not just letters they can be the actual column
Other parameters that don't belong in the table are elsewhere on the
They have names that we can reference in formulas. 

This is not just an implementation issue. There are a number of aspects
can be helped with markup.
Column and row names and can be specified as any arbitrary value -
"Sales" or 
"Profit" or "Hours" or "January" so a cell can be "January.Sales".

Formulas can be applied to an entire column rather than using formulae
wildcards and copying down. E.g. "Profit"="Sales"-"Costs" where
"Sales" and "Costs" are column names.

Summary rows with subtotals can have a special status so they are not
part of the table and the 'column' formulae don't apply to them. These
would be 
linked to a 'group of rows', a concept that you already have in ODF.

Miscellaneous other parameters can be placed outside the table but
referenced and used in calculations. E.g. "Cost inc Tax" = "Cost" * (1 +
"Tax Rate")
where "Cost inc Tax" and "Cost" are column names and "Tax Rate" is a
given elsewhere in the document but not in the table.

Standalone parameters could also be used for the sort of post processing
we put 
below the table in our spreadsheets.

I know this is starting to look more like a database but maybe that
isn't necessarily 
a bad thing. It will make importing from and exporting to databases more

Does this make sense?.

This email (and any attachment) is intended only for the attention of
the addressee. It is STRICTLY CONFIDENTIAL and may be legally
privileged. If you have received it in error any disclosure distribution
or copying of all or any part of the contents is prohibited. While virus
checked by Hoare Lea, the recipient remains responsible for the
consequences of any virus received.

This publicly archived list offers a means to provide input to the
OASIS Open Document Format for Office Applications (OpenDocument) TC.

In order to verify user consent to the Feedback License terms and
to minimize spam in the list archive, subscription is required
before posting.

Subscribe: office-comment-subscribe@lists.oasis-open.org
Unsubscribe: office-comment-unsubscribe@lists.oasis-open.org
List help: office-comment-help@lists.oasis-open.org
List archive: http://lists.oasis-open.org/archives/office-comment/
Feedback License: http://www.oasis-open.org/who/ipr/feedback_license.pdf
List Guidelines: http://www.oasis-open.org/maillists/guidelines.php

[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]