Enhance Number Recognition

Calc OpenOffice.org 2.0

Document - ID	Specification Owner	Last Change	Status
	Falko Tesch	19/01/04 by Falko Tesch	Final
Conforms to	StarOffice Specification Workflow v0.1, Aug 2003
Applies to	Calc
Task ID(s)	22345
Category	Feature

Abstract

Many users are confused by inputting data to their spreadsheet when Number Recognition steps in and re-format their input to something unexpected.

This new feature will change the alter Number Recognition behaviour so that it will be easier and more understandable to use by long-time Calc users, novice users as well as users migrating from Excel or other spreadsheet applications.

i-Team Members (The specification Owner is part of the i-Team)

	Name	E-mail Address
User Experience	Falko Tesch	Falko@sun.com
Development	Niklas Nebel	Niklas.Nebel@sun.com
Quality Assurance	Oliver Crämer	Oliver.Craemer@sun.com
Documentation	Uwe Fischer	Uwe.Fischer@sun.com

Approved for Implementation

	Approved by	Date
User Experience	Falko Tesch	27.11.2003
Development	Niklas Nebel	01/15/04
Quality Assurance	Oliver Crämer	18.12.2003
Documentation	Uwe Fischer	27.11.2003
String Review		

Document Change History

Rev. Level	Change	Initials	Date
1.0	Initial Version	FT	19.11.2003
1.1	Version based on feedback for i-Team members	FT	17.11.2003
1.2	Changes and correction based on QA input	FT	12/18/03
1.3	Final changes made by Niklas	NN	01/15/04

Contents

Glossary	1

1	Motivation	1

2	User Scenarios	1

2.1	Scenario 1	1

2.2	Scenario 2	1

2.3	Scenario 3	2

2.4	Scenario 4	2

3	Goals	3

4	Requirements and Dependencies	3

4.1	Requirements	3

4.2	Technical Dependencies	3

5	Competitive Analyses	4

5.1	Calc 1.1/7	4

5.2	MS Excel 2002:	4

5.3	Corel QuattroPro 11	6

5.4	KSpread 1.2.1	6

6	Detailed Specification	7

6.1	Initial Number Recognition	7

6.2	New Number Recognition Behaviour on Cells	8

6.3	Number Recognition behaviour on Formatted Cells	8

6.4	Ease-of-use for Calc Novices Measures	11

6.5	Changing/Adding Category “Number - Format: Default”	11

6.6	Error Conditions	13

7	Future Tasks	13

8	Notes	13

9	References	13

Glossary

Term	Description
Number Recognition	A function that re-formats input based on a set of rules.
Number Format	A feature that allows the user to set a fixed interpretation of his input. It is used to to ease input (Example: Input 1,1 in a currency number formatted cell results in € 1,10)

Motivation

Many users complain about Number Recognition changing their data input in Calc. Currently the Number Recognition feature appears to many users confusing and not foreseeable.

It also creates a lot of re-work on pre-formatted cells and sheets for many users.

This specification will make Number Recognition easier to understand and will iron out the non-foreseeable events in case of already input data. It will also take in account the Number Recognition behaviour of other spreadsheet applications to ease migration issues for users of those applications.

User Scenarios

Note: The user in this chapter runs Calc in a German locale environment (where ',' is used as a decimal separator).

Scenario 1

A user opens an empty spreadsheet and starts to input his data. Since the default setting for number format of cells is “Standard” Number Recognition is on per default and is applied to any input made.

Inputting data like 1.1 or 1:1 results in auto-detecting a date (1.1) and a time (1:1) and in re-formatting the input to 01.01.2003 resp. 01:01:01.

Even that this is compliant to what other spreadsheet applications do this behaviour often is not understood.

Scenario 2

A user formats a number of cells as “Currency” (1,00 €). Now he inputs data. Accidentally he inputs 1.03 instead of 1,03. Calc will now interpret 1.03 as a “Date” format and will re-format the input to 01.03.2003 instead of the desired 1,03 €.

Even if this is a user input error and there is not much the program can do about Calc now not only displays the input data as a date it also changes silently the underlying number format of the cell. If the user corrects his error by typing in 1,03 the data will not change to 1,03 € but will stay as a plain 1,03 since Calc changed the number format of this cell to “Number”.

To get back to the initially desired mode (“Currency”) the user must re-format the cell's number format back to “Currency”.

Scenario 3

A company creates a set of templates for their sales division. Those templates are already formatted for easy data input and calculation for their sales-force.

A salesman of this company now is at a potential customer. The customer is already willing to order a set of items and the salesman is inputting data to the spreadsheet template to speed up the order process. Since he uses a notebook with a limited keyboard layout he sometimes make erroneous typos mixing '.' and ','.

Calc Number Recognition now a) inputs his typos as completely unusable data and b) re-formats the number format of those cells not allowing the salesman to correct his typos by plain re-typing.

The salesman now must re-format parts of the spreadsheet template which is not desired whether for a template nor in front of a customer.

Scenario 4

A user migrating from Corel Quattro Pro 11 or MS Excel 2002 opens a spread sheet in Calc.

Now that he is used to find his cell number format “General” as the top category as it is located in his former spread sheet application he will have problems to find its equivalent in Calc. Why?

Because in Calc this is not an own category but a format type of the category “Number”.

Since this format not only affects numbers but also text the user might not find it under category “Number” anyway or only by luck.

Below are screen shots from Corel Quattro Pro (see Figure 1) and MS Excel (see Figure 2) that show the dialogues in question.

Figure 1 Cell Number format dialogue of Corel QuattroPro 11

Figure 2 Cell Number format dialogue of MS Excel 2002

Goals

Make an in-depth competitive analysis with other spreadsheet applications and allow these findings to influence the 3 points stated below to ease migration issues for users coming from those applications.

List, review and correct (if necessary) the current Number Recognition when inputting data in an empty, unaltered cell/sheet

Revise and correct (if necessary) the current Number Recognition when inputting various data formats to already number-formatted cells.
Note: This issue goes for user-formatted cells as well as for cells formatted formerly by Number Recognition .

Rethink and correct the current behaviour of Calc to reset user-defined number formatting of cells in case of invalid data input (example: user input of 1.23 in a German currency-formatted cells that expects 1,23 instead)

Requirements and Dependencies

Requirements

None

Technical Dependencies

None

Competitive Analyses

Note: All competitive analysis is done within a German locale environment. This is important since Number Recognition is dependent on the surrounding locales. Example: Germans use ',' for decimal separation while US Americans use '.'. Thus inputting 1.1 in a cells will have different effects under different locales (in Germany 1.1 is interpreted as a date - 01.01.2003 - while in the US it will be the plain number value 1.1 )

Calc 1.1/7

Calc supports 11 different number format categories:

User-defined

Number - Format: standard

Number - all other formats

Percent

Currency

Date

Time

Scientific

Fraction

Boolean Value

Text

All bold, green categories are affected by Calc's Number Recognition.

While “Number - Format: Standard” is the default setting of an empty cell/sheet all others must be explicitly set by the user or the Number Recognition.

Input of data in an empty, unaltered cells/sheet

Figure 3 Bla

In Figure 3 the following was done:

Column A reflects the data the user enters.

Row 2 describes the number formatting categories that are applied to columns B to I (where “General” conforms to “Number - Format: Standard”)

The data in matrix B3 to I10 displays what happened to the initial input (A3 to A10).

As one can see inputting '1/1' and '1.1' which is both considered to be a date format is treated different in these 8 categories. Also only '1' is the only figure that is always altered to the appropriate format.

MS Excel 2002:

MS Excel supports 12 different number format categories:

General

Number

Currency

Accounting

Date

Time

Percentage

Fraction

Scientific

Text

Special

Custom

All bold, green categories are affected by Excel's Number Recognition.

While “General” is the default setting of an empty cell/sheet all others must be explicitly set by the user or the Number Recognition.

Input of data in an empty, unaltered cells/sheet

Figure 4 Number Recognition in Excel 2002 (running in German locale)

Please refer to chapter Calc 1.1/7 to read the explanation on Figure 4.

As one can see in Figure 4 MS Excel when the user inputs data like in column A how the different number formatting will change the input.

Interesting to see is that Excel is not consistent in most input variations.

While inputting a simple number (1) will always lead to the expected format inputting the €-symbol as well the %-symbol lead to quite different formatting interpretations.

Also apart from categories “General”, “Time”, “Currency” and “Number”all others are not consistent with their formatting.

MS Excel actually interprets data values to display them in other format. A good example is the input of 1.1 and 1:1 which are automatically considered to be “Date” and “Time” values. When entered in a “Number” or “Currency” formatted cells Excel first does a conversion from “date/Time” to a decimal number and than applies the appropriate number-format.

In case of “Date” Excel count all days from the 00.01.1900 until the interpreted data value of the input data (in case of 1.1 this is 01.01.2003) and uses this calculated value (37622) to be formatted.

In case of “Time” Excel calculates the percentage of time that is already elapsed relative to midnight (0:00 or 12:00AM). In the case above 1:1 results in 01:01(AM) which is 4,2361% (rounded 4% or 0,04)
Note: While this value could also be used for category “Percentage” it is not. Instead 1:1 results in 1:1%..

Input of data in an already formatted cell

Excel has two ways of applying data formats to cells

The user himself uses the “Format Cells” dialogue to assign a desired number format.

Excel does it itself. This happens automatically with every cells that receives data from one of the green categories. Once Excel determines a category/number format it re-formats the input as well as assigning this number format to the cell.

Figure 5Formatting in Excel 2002 (running in German locale)

Figure 5 displays what happens if the user inputs data (in this case a simple 1) to the fields that have been formerly formatted by Excel (see Figure 4 for this).

While the categories “Number”, “Currency” and “Time” display the value 1 (in their way of formatting of course) the other categories alter the input considerable.

Corel QuattroPro 11

Corel QuattroPro 11 uses a very dominant number recognition. As shown in Figure 6 inputting two number separated by a '.' will always result in a date format. It will also overwrite any existing number format applied to the underlying cell.

Interestingly QuattroPro seems not to recognise '€' as a currency (symbol) but assumes a string and assigns the Text number format while using 'DM' succeeding a number is interpreted as 'Currency'.

The automatic number recognition even ignores if a cell is formatted as “Text” thus making it really hard to input strings that consists out of numbers.

In general QuattroPro behaves very much the way Calc does nowadays and therefore is does not make much sense to further investigate for possible changes to-be-made in Calc.

Figure 6 Corel QuattroPro 11

KSpread 1.2.1

KSpread, the spreadsheet application of KOffice 1.21, uses only partly Number Recognition. As one can see in Figure 7 neither '1/1' nor '€1' are interpreted as numbers but as strings.

On the other hand most interestingly KSpread (in German locale) replaces a '1.1' by '1,1'! This seems to be logical since in Germany 1.1 has no real meaning but is rather a result of a typo ('.' instead of desired ','). Unfortunately this behaviour corrupts entries like '1.234' which is equals '1234' in German (the '.' is considered a thousands-separator) . KSpread interprets this as '1,234'.

This behaviour get even more inconsistent once one types in '1.234,56'. This will be interpreted as a String!

All in all one can say that Number Recognition in KSpread is very rudimentary and fails as soon as more complex combinations ('1.234,56') are entered.

The only advantage for many users will be the automatic replacement of the '.' and the interpretation of a '/' (like 1/1) as a string.

Figure 7 Number input in KSpread 1.2.1

Detailed Specification

This chapter will be split up to 5 different issues:

Initial Number Recognition

New Number Recognition Behaviour on Cells

Number Recognition behaviour on Formatted Cells

Ease-of-use for Calc Novices Measures

Changing/Adding Category “Number - Format: Default”

Initial Number Recognition

As described in User Scenario 1 users are often confused with the Number Recognition on “empty” cells.

It was discussed to allow disabling Number Recognition as an option for Calc.

This option was rejected for the following reason. If Number Recognition would be turned off all input had to be interpreted as strings. So even a simple 1 would automatically become a string no-one could calculate with. So the benefit of helping first-time users with Calc would lead to an even bigger problem for them:
Because they would see a number (which is a string) but they are not able to calculate with this “number”.

Furthermore extensive discussions about implementing some sort of automatism that could “foresee” what kind of input was meant (example instead of 1.1 1,1 ) was rejected based on too many variables that must be taken into consideration.

New Number Recognition Behaviour on Cells

As described in User Scenario 2 users don't want their assigned formatting being overwritten as a result of a typo.

Currently Calc uses its Number Recognition not only for “empty” cells but also for any cell.

This will change.

In case of a another number format (other than none or “General”) applied to cells Calc will apply the recognised number format to the cell only if its category is incompatible with that of the cell's existing number format (this condition existed before), and the cell's existing number format was one of "General", the default date format, or the default time format for its locale or the one of those defined in this Matrix (this condition is new).

For the latter case some more specification must be done. This is covered in Number Recognition behaviour on Formatted Cells

Number Recognition behaviour on Formatted Cells

This chapter specifies how Calc will behave in case an alien number format will be input to an already formatted cell.

Calc will only apply a new format type to an already formatted cell in case the recognised input value's format type is incompatible with the existing format type and the existing format type is one of the default formats of category “Number”, “Date”, “Time” or “Boolean”.

An exception in this case is if the recognised input value is “Boolean”. In this case any existing number format will always be overwritten

Example: Cell A1 carries the number format “Currency” (running on a German locale). The input in this cell is '1.1'. Normally '1.1' will be interpreted as a “Date” (01.01.2003).

Since Calc will not overwrite the existing cell number format (unless it is one of the category “Number”, “Date”, Time” or “Boolean”, which it is not in this case) Calc will interpret the input as a different way to input a valid value and will keep the “Currency” format and will output 37622,00 €.

Another change in behaviour must be taken into account:

Like mentioned in Initial Number Recognition all input will get a number format assigned to. While currently Calc do allow overwriting number formats the user can still change the cells number format by using a specific input. This will not be possible anymore for any category apart from “Number”, “Date”, “Time” or “Boolean”.

This new behaviour must be well documented and should find also its way to a possible “What's New” documentation.

Insertion of... ↓	Cell is formatted as standard... (in German locale) Number (not General) Percent Currency Date Time Scientific Fraction Boolean Text General
1 or 1,1 (Number)	1,00 or 1,10	1,00% or 1,10%	1,00 € or 1,10 €	1 or 1,1	1 or 1,1	1,00E+001 or 1,10E+001	1 _/_	1 or 1,1	'1	1 or 1,1
1% (Percent)	0,01	1.00%	0,01 €	1%	1%	1,00E-002	0 _/_	1%	'1%	1%
€1 (Currency)	1,00	100,00%	1,00 €	1,00 €	1,00 €	1,00E+001	1 _/_	1,00 €	'€1	1,00 €
1.1 or 1/1 (Date)	37622,00	1.1% (as string)	37622,00 €	01.01.2003	01.01.2003	3,76E+004	37622 _/_	01.01.2003	'1.1 or '1/1	01.01.2003
1:1 (Time)	0,04	1:1% (as string)	0,04 €	01:01:01	01:01:00	4,00E-002	0 _/_	01:01:00	'1:1	01:01:00
1e1 (Scientific)	10,00	1e1% (as string)	10,00 €	1,00E+001	1,00E+001	1,00E+001	10 _/_	1,00E+001	'1e1	1,00E+001
1 1/2 (Fraction)	1,50	1 1/2% (as string)	1,50 €	1 1/2	1 1/2	1,50E+000	1 1/2	1 1/2	'1 1/2	1 1/2
WAHR (Boolean)	WAHR (eq. value 1)	WAHR (eq. value 1)	WAHR (eq. value 1)	WAHR (eq. value 1)	WAHR (eq. value 1)	WAHR (eq. value 1)	WAHR (eq. value 1)	WAHR (eq.value 1)	'WAHR	WAHR (eq.value 1)
@ (Text)	@	@	@	@	@	@	@	@	'@	@

Legend:

Column A (“Insertion of... ↓”) lists all kinds of input with recognised number formats.

Row 1 (“Cell is formatted as standard...[German locale]”) accomodates the list of all possible number formats in Calc (apart from “User defined”) .

To see which pre-formatted cell will change their formatting (or not) based on the recognised input read the Matrix first from left (to select the recongised input format) and than choose a cell number format from the first row and travel down to the junction cell.

At this junction one can see if the input will actually change the number format of the cell or if it will be adjusted to the number format.

Ease-of-use for Calc Novices Measures

A lot of people that are novices to spreadsheet applications or that very seldom uses such are often confused and irritated that their input is altered without a comment.

This chapter will determine what automatism can/cannot implemented to ease or even avoid such confusion and it will specify the possible measures.

What Can and what Can't be done?

For the time being there is not AI that can foresee what the user really wants to do with his/her input so a sensible set of rules must be set up to interpret the users input.

Apart from the expectation of novice users also experienced users migrating from other spreadsheet applications must be taken into account.

Calc must interpret the input data since the only alternative to this would be that all data will be treated as strings. This is not desired since a spreadsheet application cannot calculate with strings at all.

So disabling Number Recognition is not an option (since no one will understand why Calc cannot calculate with numbers anymore).

It was discussed if disabling Number Recognition for “Date” and “Time” number format categories make sense this those two are the ones creating the most confusion (1.1 turns in 01.01.03).

Again question left is “What was it, that the users wanted to input?”.

The only answer can be given by the users himself so the only option here is to ask the user in an pop-up dialogue.

This again is opposed by the majority of users that desire a more simplified and easier-to-use UI for OpenOffice. (see also http://tools.openoffice.org/releases/q-concept.html).

What Can be done:

Technical Publishers (TechPubs) can write a comprehensive guide for “How To Use Calc” for novices. Furthermore a short but easy to read introduction in Calc help is another good point to help those users,

Currently such information is more or less scattered throughout the OpenOffice.org help.

What Can't be done:

A switch that turns off Number Recognition is useless as well as any try to partly disable Number Recognition.

In both cases the results will be more confusing and less foreseeable than helpful.

Changing/Adding Category “Number - Format: Default”

Important Note: On request of development this issue might not make it into target release of this specification due to time reasons!

In Calc the standard format of a cell is category “Number”, sub-category/format “Standard”.

This is confusing to many users since they expect a simple number behind this format. Instead this format is a placeholder for a cell with a yet undefined format. Once data is input into the cell the Number Recognition will change format (and category) according to the input data.

Note: Also Corel Quattro Pro 11 and MS Excel 2002 do use a single category for this item (see Figure 1 and Figure 2).

To reflect this circumstance in a more understandable and clear way a new category will be introduced:

General

This category will not contain any formats but an explanatory text instead (see Figure 8).

Also the category “All” will be removed completely since this category will not be able to house “General” anymore.

New category “General” will become the top category (formerly “All”).

Since moving from format “Standard” to category “Standard” also the UI has to change for the category “all” (see ). “Standard” will not be listed in the “Format” list box anymore but will appear as the category “General” just below the category “All” instead.

All setting and behaviour of category “General” will comply to the format format “Standard” in category “Number”. This goes for default settings as well as for dependencies in conjunction with the set Language.

Figure 8 New Category in Format Cell dialogue

String list

Item	English	German	Comments
Format - Cells - Numbers: Cell Format Category	General	Standard	Replaces the old “Standard” format from cell format category “Number”
Format - Cells - Numbers: Cell Format Category	General formatted cells have no specific number format.	Standard formatierte Zellen besitzen keine spezifischen Nummernformate.	Short description for what “General” is.

Tab Order

In all dialogues the current tab order will be preserved.

Key Board Shortcuts

No new key board shortcuts needed.

Error Conditions

n/a

Future Tasks

Write a comprehensive “First steps with Calc”

Disable the AutoCorrection of all fractions (1/2 becomes ½ etc.) in Calc per default.

Possibly issue 6.5 Changing/Adding Category "Number - Format: Default"

Notes

Disable 1/2 1/4 etc in Calc!!!

References

Required - Give references to related documentation, such as engineering designs, technical specs, and other sources for related units. Also include links to documents that other sections in this spec need to refer to, such as user opinions usability test reports.

Enhance Number Recognition

Page 12

http://specs.openoffice.org/calc/ease-of-use/enhance_number_recognition.sxw

Enhance Number Recognition

Falko Tesch

2003-11-11T14:00:00

Matthias Müller-Prove