Issue 20496 - Enhanced formula input
Summary: Enhanced formula input
Alias: None
Product: Calc
Classification: Application
Component: ui (show other issues)
Version: OOo 1.0.0
Hardware: All All
: P3 Trivial with 20 votes (vote)
Target Milestone: ---
Assignee: frank
QA Contact: issues@sc
Keywords: oooqa
: 4849 23495 27421 30784 40307 46416 52179 58264 58968 61905 66399 (view as issue list)
Depends on:
Blocks: 87974
  Show dependency tree
Reported: 2003-09-30 17:32 UTC by niklas.nebel
Modified: 2013-08-07 15:14 UTC (History)
9 users (show)

See Also:
Issue Type: FEATURE
Latest Confirmation in: ---
Developer Difficulty: ---

Updated with er's requirement - and +/- is similar to = in case of cell selection (1.72 KB, patch)
2005-09-02 02:39 UTC, muthusuba
no flags Details | Diff
Patch(1) for 'accepting' formula input. (1.15 KB, patch)
2006-10-30 15:09 UTC, muthusuba
no flags Details | Diff
Patch(2) for 'accepting' an input. (1.19 KB, application/msword)
2006-10-30 15:10 UTC, muthusuba
no flags Details
Patch(2) for 'accepting' an input. (1.19 KB, patch)
2006-10-30 15:10 UTC, muthusuba
no flags Details | Diff
Spec. (36.16 KB, application/vnd.oasis.opendocument.text)
2006-11-25 12:48 UTC, muthusuba
no flags Details
Testdocument (9.21 KB, application/vnd.oasis.opendocument.spreadsheet)
2007-09-28 11:39 UTC, frank
no flags Details
testcasespecification (9.61 KB, text/html)
2007-09-28 11:39 UTC, frank
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description niklas.nebel 2003-09-30 17:32:48 UTC
Task tracking system for Childs



Product Requirement 
Enhanced formula input

Customer Need/Problem
In competing applications one are able to insert formulas starting with a +
instead of = This makes life much easier because there is no need to leave the
num-block for input.


Eng Effort

Eng Owner
Niklas Nebel

Product Concept
Cell input handling can be changed so formulas can be started with '+' and '-'
in addition to '='. This change would disturb users who want to enter normal
strings starting with '+' or '-', who might not expect to get the formula
reference selection when using the cursor keys. A final specification has to
come from the User Experience team.

Functional Specification
Comment 1 lutz.hoeger 2003-10-23 07:44:19 UTC
added keyword Q-PCD
Comment 2 niklas.nebel 2003-11-07 17:49:33 UTC
*** Issue 4849 has been marked as a duplicate of this issue. ***
Comment 3 Rainer Bielefeld 2003-11-14 19:04:31 UTC
Hm, today I tried an daw, that my I checked with 1.1.0 German version
WIN98SE: 645m19(Build8693) accepted an entry '+12' or '-5'
alternatively to '=+12' or '=-5'

Funny thing, I never saw that before, but I did not try for a long
time. Does that mean we can make these issues WFM?

Comment 4 daniel.rentz 2003-11-17 07:16:33 UTC
This issue is about formulas, not simple numbers :-)
That means for example, "+4+5" or "-4+5" should become formulas.
Comment 5 frank 2003-12-12 13:35:13 UTC
*** Issue 23495 has been marked as a duplicate of this issue. ***
Comment 6 jovishc 2004-05-03 10:55:41 UTC
There could be an option to let introduce formulas with a + or -, so some 
users will not be disturbed and others could improve their productivity. This 
will make it easier to migrate from Excel to OO.
Comment 7 frank 2004-06-28 09:00:04 UTC
*** Issue 30784 has been marked as a duplicate of this issue. ***
Comment 8 niklas.nebel 2004-07-28 17:03:53 UTC
nn->ft: This is marked as "for beta", but what do we want here? We can treat "+"
and "-" similar to "=" and always assume formula input, but that would take away
the possibility to input normal text starting with "+" or "-" ("hand-made"
enumerations, for example). Do we really want that?
Comment 9 ggabriel 2004-07-29 01:11:54 UTC
The real question for me is:
Do I really want to press + or - on the numeric keypad to enter a formula,
instead of having to press "Shift + 0" on the main keyboard (the
spanish-keyboard key combination to access the = sign)?
Of course YES!
(perhaps on the english keyboard the = sign is accesed easily as an unmodified
key press, but having to press Shift plus a key to access it is really intrusive
and anoying, and if there is a chance to fix this I for sure would be more than

other questions that could help to thing about this matter could be:
- what people input more on a spreadsheet program: formulas or text?
- what for the program input should be optimized: formulas or text?
I think a word processor should be optimized for text, but a spreadsheet program
should be so for numbers, fórmulas and such... is that clear!

my two cents of "peso"
Gabriel Gazzán
Montevideo - Uruguay
Comment 10 falko.tesch 2004-08-04 08:35:06 UTC
FT: This issue will not make it into 2.0 but will be shifted to OfficeLater.
This is due to time reasons.
Comment 11 falko.tesch 2004-08-04 08:35:40 UTC
Comment 12 lohmaier 2004-09-01 22:17:39 UTC
*** Issue 27421 has been marked as a duplicate of this issue. ***
Comment 13 frank 2005-01-13 10:23:45 UTC
*** Issue 40307 has been marked as a duplicate of this issue. ***
Comment 14 pedroa 2005-01-13 23:16:31 UTC
Sorry for my duplicate 40307.

Here goes my 2 cents and a vote :)

This is prioritary and I can not understand the difficulty of this. If it is 
to start a non formula cell just start with a ' (single quote), like in Excel.
Example: '+ whatever (This will give a string to the cell)
Example: +2+1 (This will send the formula =+2+1 value to the cell)

I want to see this one on 2.0, no excuses for this. Yeah easy for me to say...
Comment 15 bilbo13 2005-03-11 23:59:57 UTC
Big disappointment - this really has not made it into 2.0 Beta - so
unfortunately Calc is still unusable for me. What does it take to convince you
developer guys? As minor as this may seem, for me and many other people this is
a major usability issue. Seriously.

Sad but still hopeful

Comment 16 jmachado1972 2005-03-14 11:38:36 UTC

Is there anyone working with a non-American keyboard?

I'm from Portugal, and in my keyboard if I want to type the "=", I have to
shift+0, while if I want to type "+", w'll just type "+".

I'm not asking this, because it is a similar beahvior by MS products.
I'm asking this, because it is simplier to use.
Comment 17 utomo99 2005-03-22 08:28:56 UTC
Many people need this, as we can see many duplicates issue. also in Issue 
4849 . 
maybe it is difficult to get into 2.0 as it is Not in Q concept. 
So, if possible I request this for 2.01 please. 
I believe many user will understand about this schedule. 

Comment 18 frank 2005-04-01 10:10:55 UTC
*** Issue 46416 has been marked as a duplicate of this issue. ***
Comment 19 frank 2005-07-20 09:07:36 UTC
*** Issue 52179 has been marked as a duplicate of this issue. ***
Comment 20 mmeeks 2005-07-20 09:39:20 UTC
So - the patch to do this from Muthu is:

Formula cells should be allowed to start with '+' or '-' along with '='
This would help in ease of use. Many excel or gnumeric users find it easier to
start a formula cell by using '+' or '-' (because the number pad contains these
and its tough to press '=' every time a formula is to be entered).

Proposed patch: 
--- sc/source/ui/view/viewfunc.cxx	2005-07-17 21:30:12.000000000 +0530
+++ sc/source/ui/view/viewfunc.cxx	2005-07-17 21:36:50.786937728 +0530
@@ -451,7 +451,8 @@ void ScViewFunc::EnterData( SCCOL nCol, 
 		BOOL bNumFmtChanged = FALSE;
 		// einzelnes '=' ist String (wird fuer Spezialfilter so gebraucht)
-		if ( rString.GetChar(0) == '=' && rString.Len() > 1 )
+		if ( ( rString.GetChar(0) == '=' || rString.GetChar(0) == '+' ||
rString.GetChar(0) == '-' )
+			   && rString.Len() > 1 )
 		{	// Formel, compile mit AutoCorrection
 			for (i=0; i<nTabCount; i++)
 				if (rMark.GetTableSelect(i))

This will of course ship in the ooo-build version of 2.0 ;-) we don't need a UI
team to tell us it's a good idea ;->
Comment 21 jodygoldberg 2005-07-20 18:29:07 UTC
This is the right idea, but the proposed implementation could use a bit more
depth.  We can't just check for a leading '+' (and '-') that will produce some
false positives.
are _not_ formulas.  They need to marked as constants, not expressions that are
Comment 22 muthusuba 2005-08-03 17:54:38 UTC
Updated Patch:

--- sc/source/ui/view/viewfunc.cxx	2005-07-17 21:30:12.000000000 +0530
+++ sc/source/ui/view/viewfunc.cxx	2005-08-02 22:20:08.923484024 +0530
@@ -450,8 +450,13 @@ void ScViewFunc::EnterData( SCCOL nCol, 
 		BOOL bNumFmtChanged = FALSE;
+		ULONG nIndex = 0;
+		double fVal;
 		// einzelnes '=' ist String (wird fuer Spezialfilter so gebraucht)
-		if ( rString.GetChar(0) == '=' && rString.Len() > 1 )
+		if ( rString.Len() > 1 && 
+		   ( rString.GetChar(0) == '=' || rString.GetChar(0) == '+' ||
rString.GetChar(0) == '-' ) &&
+			!rFormatter.IsNumberFormat(rString, nIndex, fVal) )
 		{	// Formel, compile mit AutoCorrection
 			for (i=0; i<nTabCount; i++)
 				if (rMark.GetTableSelect(i))
Comment 23 ooo 2005-08-29 18:05:25 UTC
We'd really need some specification of what should happen with the cell
reference selection during formula input. Is it to be completely ignored with
+/- (as the patch currently does), or should it be identical to '=', but then
interfere with the ability to move the cursor in a -123 number input, or should
it be available but deactivated first, and be switched on with F2?
Comment 24 ooo 2005-08-29 18:11:42 UTC
Just a further comment already made on IRC:

ULONG nIndex = 0;

isn't correct, itshould be the format index of the current input cell instead.
But then it should only be obtained if needed, i.e. if the input starts with
=/+/-, to avoid unnecessary calls.

Comment 25 muthusuba 2005-09-02 02:39:32 UTC
Created attachment 29259 [details]
Updated with er's requirement - and +/- is similar to = in case of cell selection
Comment 26 falko.tesch 2005-10-20 20:51:11 UTC
FT: I'm leaving therefore I need to re-assign this issue to you. 
Comment 27 rubindj 2005-11-01 00:51:42 UTC
FYI... How every spreadsheet has worked for the last 25 years when VisiCalc
started the PC revolution.

A +/-/= are all the same, they are equivalent in terms of cell input.

-123, is treated as -123, meaning that entering '-123' on a keyboard is the same
as =-123, and '+123' is treated the same as '=+123'.

This is a huge usability function -- more important than just about anything for
business oriented users.
Comment 28 jodygoldberg 2005-11-01 15:45:41 UTC
rubinj : not quite.

-123 is not the same as =-123
It should be considered a constant rather than an expression whose value is
constant.  -123-12 would be the same a =-123-12 because (ignoring const folding)
it requires some operations.

That is why the proposed patch does the numbermatch test.
Comment 29 frank 2005-11-24 13:31:47 UTC
*** Issue 58264 has been marked as a duplicate of this issue. ***
Comment 30 giuliano1969 2005-12-01 13:18:35 UTC
The idea on how to distinguish between formula and constant in the cell, should
come from the parsing of the input string; in the string

the first char "+" is  the positive sign, 
the second char "+" is the sum operator.

so this MUST be saved as a formula, because an operator is invoked.

Jus to give an example of REAL office work, .... (silly but real)
let's consider to manually sum some invoices in a specific cell;
then you discover a mistake and get back on that cell to find which invoce you
have mistyped;
I the value has been saved as a constant you will have to retype all the list of
numbers; if the FORMULA has been inserted, you will easily find the error and
easily correct it.

Pleas, start considering strings with "-" or "-" char at position 0 as formulas !!


Comment 31 muthusuba 2005-12-01 13:31:42 UTC
please see this: 
------- Additional comments from muthusuba Thu Sep 1 17:39:32 -0800 2005 -------

with that patch you can start a formula with +, - or =, you'll have to wait
until nn (or somebody else) puts it into the build.

+123 and -123 are examples of constants while +123+1 and -123+1 are formulae.
Comment 32 giuliano1969 2005-12-02 15:37:36 UTC
One more observation about using +/- as starting a formula:

If we may think possible to be "+anynumber" a constant and not a formula
(because it is missing operator) consider that "+table1.A1" shouldn't be a
constant, (because it vary depending on the value of the referenced cell).

So it appears that if an operator is missing, the string inserted could be
anyway a formula !!!


how does the patch deal in this situation ?
Comment 33 jodygoldberg 2005-12-02 16:04:01 UTC
giuliano1969 : The patch does not need to handle the case of '+table.A1' because
it is written to look only for the very specific case of [+-]{formated number}.
 table.A1 is not a number nor is .A1.   The closest we would come to a problem
would be with some of the work I'm doing for R1C1 style addresses.  That change
also adds support for MS Excel style A1 addresses.  Which include full row
references of the form '2:2'.  Which means that a user could conceiveable enter
+2:2.  However, even that would not cause a problem because
   1) 2:2 is not a valid number.  We would match only on the leading 2 then fail
   2) Even if there is a custom number format that could somehow produce 2:2
    (something like #:0).  It makes absolutely no sense as an expression.
    Anyone that goes that far out of their way deserves to have '+2:2' get
    entered as the constant 22.
Comment 34 lars 2005-12-06 19:19:29 UTC
*** Issue 58968 has been marked as a duplicate of this issue. ***
Comment 35 rubindj 2005-12-10 16:36:46 UTC
The below is why it needs to be considered a formula.  I should be able to enter
+R1C15 as easily as +123... etc.  I've used just about every spreadsheet since
Visicalc, and all of them treated +/-/= as formula initiators.

People like me who have used spreadsheets for 20+ years in every concievable
version cannot understand why this is such an issue.  I was really excited about
the OO 2.0 release because I like Linux, and I like python for scripting a lot
more than VB.  Unfortunately, Calc is still mostly useless to me and most
powerusers I know.

------- Additional comments from jodygoldberg Fri Dec 2 08:04:01 -0800 2005 -------

   2) Even if there is a custom number format that could somehow produce 2:2
    (something like #:0).  It makes absolutely no sense as an expression.
    Anyone that goes that far out of their way deserves to have '+2:2' get
    entered as the constant 22.
Comment 36 jodygoldberg 2005-12-19 14:53:36 UTC
You will be able to enter +A1 or (with my R1C1 patch) +R1C1 and have that
interpreted as a formula.  The focus of this discussion is a specialization of
the patch to avoid making it create a formula when it is not needed.
Comment 37 muthusuba 2006-01-23 08:13:31 UTC

    Could you please check the patch and tell me if you need any changes?

Comment 38 niklas.nebel 2006-01-23 10:22:26 UTC
I know this is unpopular, but what we need is (to avoid the evil s-word) an
agreed-upon definition what the desired behavior is. Should "+" start reference
input mode? Should it still be possible to input something like "---" as a text?
Should "+" and "-" work also in plain-text import, or only in direct input?

How is anyone supposed to tell if the code does the right thing, without knowing
what the right thing is?
Comment 39 lars 2006-02-10 19:07:57 UTC
*** Issue 61905 has been marked as a duplicate of this issue. ***
Comment 40 pedroa 2006-05-04 10:53:45 UTC
Hi all,

First of all thanks to the team for OOo2, you are doing a great work.

About our issue here, I see that there are patches already referenced here but I
don't know where is such a binary available out there, because it is not working
on OOo 202.

About the --- possibility not being parsed as =---  for example Excel lets you
bypass the formula parsing by user inserting an initial ' to the cell, so if you
type '--- it renders the desired ---

About the constants problem, well if you start by + or - it should allways
render a formula by appending a = to start of the cell.
In fact only negative constants are a problem since when a user needs to enter a
positive constant he normally does not need to enter a + because unsigned
constants are natural positives. So when a user starts with a + he is most
probably willing to really create a formula.

So I believe the users out there (me included) could live happily if our
negative constants got a = added to the start of the cell.

Again thank you very much,

Pedro A.
Comment 41 frank 2006-06-14 08:02:55 UTC
*** Issue 66399 has been marked as a duplicate of this issue. ***
Comment 42 muthusuba 2006-06-14 08:48:30 UTC
My opinion:
1. This issue is most annoying when we are using the keyboard to input values.
2. When we import something like +10+20 we need not change it to =+10+20
(probably the user wanted it that way i.e. as +10+20). I have also noticed that
spreedsheet applications like Excel save it as =+10+20. 
3. ---- could be converted to =---- and reported as error OR even better we
could popup the auto-correction dialog to correct it to '----

I guess this would be acceptable to most of the users.
Comment 43 pedroa 2006-06-14 16:42:14 UTC
That's all fine by me just as long as something gets out on a binary form :)
So we can wait for it on a future dev release?

Thanks a lot,

Pedro A.
Comment 44 kendy 2006-08-02 10:44:31 UTC
This is a patch - set it as such.
Comment 45 ooo 2006-08-02 11:41:41 UTC
Please, Kendy, at least read the issues you're about to change. Unfortunately
interspered hot vapor comments don't make it easier to follow.. The patch wasn't
accepted as still no one bothered to clearly define how the behavior should
exactly be. Resetting to 'feature'.
Comment 46 muthusuba 2006-08-02 11:52:40 UTC
  Who should tell us what the behaviour should be? User experience team?
I have given my side of comments, thou.

I guess we are giving too much of importance to 'other' issues rather than the
main usability issue. I have seen many (most?) advanced users of spreadsheet
application(s) using the number pad 'alone' for inputting values. If we don't
support it - it will sure be a major usability issue for them.


Comment 47 andreschnabel 2006-08-02 16:01:31 UTC
just for all who support this issue:
we just had a user complaining about unexpected behaviour in OOo novell edition.
He is used to enter a lot of values in Calc (positive / negitve). To decide,
what the next cell for input should be, he is using Cursor Keys instead of Enter.
But this is not possible with the patch in place: he cannot leave the cell, as
he now is in formula input mode.

If we look to our main competitor: +/- are *not* cosidered to start a formula,
as long as there is no other criteria for formulas (e.g. cell reference, second

Comment 48 discoleo 2006-08-10 00:39:25 UTC
If +/- are implemented as introducing a formula, I would like to have an option
to disable this.

Indeed, for some it may be an useful feature, but for others it is

My suggestion:
 - option to DISABLE this !!
 - consider formula only if:    ('+' equivalent to '-')
   - '+''number' followed by another operator
   - '+' followed by a cell
   - '+' followed by a function
 - definitely, do NOT consider formula IF
   - '+' followed by only one number
   - '+' followed by a string NOT evaluated as a function

Alternatively, offer possibility that users replace '=' by '+'(i.e. any '+' is
evaluated as formula), but users must activate this feature.
Comment 49 tgn 2006-10-19 10:53:26 UTC
It is very important and useful issue when we use Calc.
When we use Excel, we can input a simple expression on ten-key block only.
But Calc need to push = key, with shift key.
It is very troublesome for many people, I guess.
Comment 50 tgn 2006-10-19 10:54:51 UTC
It is very important and useful issue when we use Calc.
When we use Excel, we can input a simple expression on ten-key block only.
But Calc need to push = key, with shift key.
It is very troublesome for many people, I guess.
Comment 51 muthusuba 2006-10-19 10:58:55 UTC
working on adding an option to enable or disable this.
Comment 52 muthusuba 2006-10-19 11:10:19 UTC

 where do you think this option can go (in tools->options?)?

Comment 53 niklas.nebel 2006-10-19 11:19:28 UTC
If we need an option, the logical place seems to be "Input settings" on the
Calc/General options page. But don't make UI changes without involving User
Experience. May I even mention the unpopular word "specification" again?
Comment 54 muthusuba 2006-10-19 11:36:16 UTC
  what we can see here is that - we need an option to disable/enable this option
(from the above comments), so we need to change UI.

i am ready to help on the "specification" part but i am new to this (on OOo).

Comment 55 mmeeks 2006-10-19 11:50:07 UTC
An option is a nightmare; we just need to fix the simple bugs in the
implementation - I would argue it is -totally- clear what people expect here,
and there should be no need for discussion or detailed competitive analysis. To
consolidate the requirements:

Test Text      Behavior
1.   +1        cell contains numeric 1
2.   -1        cell contains numeric -1
3.   +1+1      cell contains formula =1+1
4.   ---1      cell contains formula =---1
5.   +A1       cell contains formula =A1

Now some corner cases: andreschnabel:
6.   +A1<down arrow>  - this should not result in eg. +A1B6 but finish the edit
                      - this is an (unrelated) bug in the formula entry mode
                      - we should be in formula entry mode
7.   =A1<down arrow>  - this should not result in eg. =A1A4 - as it does 

8.   NN: text import: - should behave identically.

So - we can argue about these for another year of course, it's only 3 years this
bug has been filed ;-) or we can just commit the patch, and fix the andre's
problem in the right place [ helping all formula editing ].
Comment 56 andreschnabel 2006-10-19 12:03:55 UTC
mmeeks: Thanks for this minimal specification :-)

The problem with the current patch is, that it does not conform to your points 1
an 2.

Text -1 results in formula =-1 (not numeric -1)
Text +1 results in formula =1 (not numeric 1)

that would mean, you argued against the patch?
Comment 57 mmeeks 2006-10-19 12:16:11 UTC
andre: sure - then lets fix the patch; that is at least do-able in linear time;
but lets not argue about what it -should- be doing, that is totally pointless.
Comment 58 mmeeks 2006-10-19 12:17:52 UTC
andre: having said that, my Novell OO.o behaves as described for tests 1 -> 5,
so I'm not sure what patch you're using / discussing, but it's not what we ship.
Comment 59 Rainer Bielefeld 2006-10-19 12:18:30 UTC
pls. mention  Issue 66735 (spin off of issue 24271)
Additionally to your list 'Additional comments from mmeeks Thu Oct 19 03:50:07
-0700 2006' 

I recommend:
11.   -4^2      input pane changes to -(4^2), cell contains numeric -16 
                (or similar)
Comment 60 muthusuba 2006-10-19 13:26:51 UTC
  -4^2 should become =-4^2 thats all - this way there will be no confusion.

  8.   text import: - should behave identically.
  i feel we need not consider this. (eg. +4+2 will be exported as =+4+2 - so
when we read from a file and we find +4+2 we should have it as-is (and not
append '=') probably the user wanted it that way!)

   i see 6. 7. out of scope of this issue (that's a problem with the formula
input mode we'll fix that separately)


pls. correct me if i am wrong!
Comment 61 niklas.nebel 2006-10-19 14:18:35 UTC
We still need to involve User Experience.
mmp, what do you think of mmeeks' list above?
Comment 62 discoleo 2006-10-19 16:54:01 UTC
I posted previosly:
 - option to DISABLE this !!

However, the current comment is about something else (as I belive that some
people really need this feature).

I think that specifications of 'should +1 become a formula' miss totally the point.

IF I have '+1' or '=+1' it is actually the same. Both cells are equivalent and
the difference is minimal. (It is only a diference IF I need the string '+1',
which is probably a rare event.)

The problem arises for NON-NUMERIC entries, like +A1, +'NAME OF FUNCTION',
+'STRING'. Here the differences *ARE BIG*, if it is only string or formula.

Therefore, I believe THESE situations should be detailed and a SPECIFICATION
should be made available.

As I pointed in my previous post:
 - IF +'string' evaluates to a cell or valid function, then this should be formula
 - ELSE, this should be text string
 - I use often '--' to describe a result that is not-available (but e.g. the
test was performed, yet did not yielded something interpretable), therefore I
would like that '--' be entered as a string ('--' has indeed a different format
from the templates discussed in the first IF and would be string by default
using my recommendations)
Comment 63 discoleo 2006-10-19 17:21:14 UTC
I had a new brainstorming session and would like to expand on some issues/

What__________| How
is____________| should it be
entered_______| interpreted
* Number (e.g. 4)      => as plain number
* negative number (-3) => as negative plain number
* '+' + 'Number'       => as STRING !!!
  -- this is currently interpreted as a number, too, but in categorical data you
may have often categories of, e.g. '+4' which is different from '4' (think of
grades in histology and so on);

NOTE: when we have only one number [regardless of the +/-/(or nothing) in front
of that number], the formula does *NOT bring* any *additional benefit*, so I do
NOT believe that a formula is needed in these situations!!!)

* 'plain number'+'any operator' => string
* '+' + 'plain number' + 'any operator' => formula
* '-' + 'plain number' + 'any operator' => string (formula?)
* ('+' or '-') + '-' + 'plain number' + 'any op' => formula; strip first +/-
  -- advanced: IF "'any operator'..." does evaluate to *ERROR*, consider string
instead of formula

* any string => string
* ('+' OR '-') + STRING
  -- IF string evaluates to valid function => formula
  -- IF NOT => just a string
Comment 64 muthusuba 2006-10-20 06:23:10 UTC
  have you tried the patch attached in this issue? it will be easier to know
what is 'left out' from the patch.

This is what the patch does:
<this is very similar to excel> (you can interchange '+' and '-' for the below

+1       => +1
+1+2     => =+1+2
+exe     => =+exe
+<arrow> => starts the formula input mode (i.e. become +A1 etc..)
++a      => =++a
-4^2     => =-4^2
+A1+A2   => =+A1+A2
'+abcd   => string +abcd
+abcd    => if it generates an error then it is shown as an error (similar to
what excel does) - probably the user made a typo! (consider this, =inf("system") 
generates an error then why shouldn't +inf("system") generate an error?)

(import is not considered in the patch so importing a .csv file with +4+2 will
give +4+2 in the cell - this is because exporting would have been =+4+2 (in case
the user typed +4+2 it would have converted it to =+4+2))

And yes, as far as i can see from the above comments, we are considering adding
an option to disable this feature (mmeeks, nn, er would be able to comment
better here).

[Waiting for comments from user experience team - in the mean while i will work
on adding an option to disable this - more the features better it is! :)]
Comment 65 discoleo 2006-10-20 09:06:52 UTC
> +abcd    => if it generates an error then it is shown as an error (similar to
> what excel does) - probably the user made a typo! (consider this, =inf("system") 
> generates an error then why shouldn't +inf("system") generate an error?)

This is exactly what I hate at Excel. I am working very much with strings and it
is annoying to generate formulas on the fly.

 - '+abcd' is definetely NOT intended to be a formula
 - it should be a string
 - IF there is a mistyping, THEN
   -- the user HAS to come back to correct the error
   -- so having it as a string is the same as having it as a formula
   -- because Calc would NOT have changed the string
   -- (i.e. it is different from 36373+34737, where you wouldn't be able to
correct one of the numbers, because Calc would already added them up)
 - WHEN correcting a 'should be formula', you are again in the particular cell
and the input routine would(/could) change it back to formula mode (there is
still the '+' in front of the string), so there is NO need to make an erroneous

Just my thaughts.

There was another issue (applies even for the official OOo 2.04).
 - '+4':
   -- currently it is transformed to the plain number '4'
   -- we do NOT need a formula for simple numbers
   -- however, when working with categorical data, there might exist categories
of '+4', '+3', which are different from '4', '3'
   -- I would have liked that this be converted to string

So, I think, this issue should be viewed as a more general approach to format
the input in Calc (and NOT just the simple formula enter mode)
Comment 66 muthusuba 2006-10-30 15:09:35 UTC
Created attachment 40164 [details]
Patch(1) for 'accepting' formula input.
Comment 67 muthusuba 2006-10-30 15:10:20 UTC
Created attachment 40165 [details]
Patch(2) for 'accepting' an input.
Comment 68 muthusuba 2006-10-30 15:10:28 UTC
Created attachment 40166 [details]
Patch(2) for 'accepting' an input.
Comment 69 muthusuba 2006-10-30 15:18:37 UTC
I have attached 2 patches. (Patch(1)... and Patch(2) ...).

Used for: (input can start with +/-/=)
Input                Output
=4<arrow>            Accept input and move to <arrow> cell.
=4+<arrow>           Formula input mode
=4+5<arrow>          Accept input and move to <arrow> cell.
=4+func(<arrow>      Formula input mode

  Can you review these, please?
  The 1st one is better - in the sense - it does what it is supposed to do (but
has hard coded values like '+', '-' ...)
  The 2nd one creates a temporary 'cell' to execute the formula compiler.

what is the best method to solve this?

Comment 70 muthusuba 2006-11-25 12:48:29 UTC
Created attachment 40911 [details]
Comment 71 muthusuba 2006-11-25 12:53:42 UTC
nn / others,

I have attached a specification document (an attempt).
Can you please review it?

Comment 72 dramenbejs 2007-07-16 16:50:08 UTC
Current implementation of this feature makes impossible to enter a text
"-101,-102,-103" from a keyboard, corrupting it for "=-101-102-103" and showing
"-306". The same with other comma delimited lists of numbers

At least, if the input text IS NOT A VALID FORMULA, it should not be corrupted
this way but let alone as a text.

Second thing -- the input from clipboard behaves well and does not corrupt.

Please!!! Make user-visible possibility to evade this behaviour, if you think
programs should behave this way. And do it undoable.
Comment 73 dramenbejs 2007-07-16 17:38:29 UTC
Pardon for my mistakes, I will use the apostrophe (') for inputting this kind of
text now.
Comment 74 frank.loehmann 2007-07-20 12:44:40 UTC
Set target.
Comment 75 frank.loehmann 2007-08-02 17:13:56 UTC
Please find the spec. here:
Comment 76 muthusuba 2007-08-03 09:01:37 UTC

the specification misses a lot of points like:
1. when should the <arrow> key act like 'reference input' and 'accept input'
2. Function inputing.

a correction in spec: (preferred).
1. +number+number => =+number+number
(note the '+' after '=')

please correct me if i am wrong.

Comment 77 ooo 2007-08-03 11:28:18 UTC

Please note that the spec is not final but work in progress. Since discussing
things in issues is quite cumbersome I suggest we move technical discussion of
the spec to the dev@sc mailing list. Btw, it's 'fl' now, not 'ft' anymore ;-)

@fl: Frank, please subscribe to the dev@sc list if you didn't already.

Comment 78 thomas.benisch 2007-08-21 14:09:43 UTC
Due to some input from the mailing list
I updated the specification at
Comment 79 thomas.benisch 2007-08-21 14:10:34 UTC
reassigned to TBE
Comment 80 thomas.benisch 2007-08-21 14:11:27 UTC
Comment 81 thomas.benisch 2007-08-21 14:51:27 UTC

I had a look at your patches sc-startformulawplus-updated.diff
and sc-formula-input2.diff and found a few problems:

- In ScViewFunc::EnterData() the exceptions as specified in rule 4
  of section 1.1 in the specification
  are not converted to text but to a formula, e.g. '++1', '+-+1', '+',
  '-', '++', '--', etc.

  In addition invalid non-auto-corrected formulas are converted to a
  formula instead of a text.

- In ScTabViewShell::ExecuteInputDirect() you make use of ScFormulaCell
  for checking if reference input should be active. One problem I found
  is that there are several cases, where your comparison with the
  error codes failes and reference input mode is not active, e.g.
  when you enter '+max('. In this case you get an error errIllegalArgument.
  In general I think the algorithm relying on error codes as
  errUnknownToken is rather fragile.

  Another problem with the usage of ScFormulaCell is performance.
  For complex formulas one runs into serious performance problems,
  because the formula is interpreted before switching to reference input

Nevertheless thanks for your patch. I hope you don't mind that I fixed
the remaining issues in CWS calcformula.
Comment 82 thomas.benisch 2007-08-21 14:52:49 UTC
fixed on CWS calcformula
Comment 83 thomas.benisch 2007-08-21 15:05:21 UTC
The following files are affected:

Comment 84 muthusuba 2007-08-21 16:40:35 UTC
Thanks tbe!
Comment 85 thomas.benisch 2007-08-22 09:05:25 UTC
TBE->FST: Please verify in CWS calcformula.
Comment 86 frank 2007-09-28 11:39:06 UTC
Created attachment 48576 [details]
Comment 87 frank 2007-09-28 11:39:47 UTC
Created attachment 48577 [details]
Comment 88 frank 2007-09-28 11:48:07 UTC
found fixed on cws calcformula using Solaris, Windows and Linux build
Comment 89 streik 2007-10-04 22:42:09 UTC
is very importatne for the adapted use of formulas of this spreadsheet. It was 
already made this way in multiplan, Lotus 1-2-3, and excel of course.
is necessary to modify the following thing:

+<arrow> => starts the formula input mode (i.e. become +A1 etc..)

+<arrow> => =<arrow>

+<arrow> => =+<arrow>

-<arrow> => =-<arrow>

is very importatne to modify this system of entrance of formulas, is the error 
number one, agility in the work. I use excel by this.
Comment 90 frank 2007-12-11 14:52:20 UTC
found integrated on master m239 using Linux, Solaris and Windows build
Comment 91 jallowas 2008-01-02 09:56:37 UTC
Thank you guys for the resolution, this will be very very helpful !
Comment 92 jarkod 2008-03-13 20:10:35 UTC
I've tried this new feature in RC4. What, in my opinion, is unacceptable from 
the standard user point of view is that a different ways of inserting formula 
result in different cell content, eg.:
1. =5+6 => =5+6
2. +5+6 => =+5+6
It looks as if these were two different formulas or I've forgotten to enter 
some part of the formula in point 2. It really misleading and annoying. One 
formula has to always look the same regardless of the input method. 
What's more, while +5 results in simply 5 in a cell, why +5+6 doesn't omit the 
leading +. It's inconsistent.
Comment 93 muthusuba 2008-03-16 10:09:39 UTC
  It would be easy if you could explain why =+5+6 will cause a problem for you -
as in annoying.
mathematically, 5+6 is same as +5+6. 
Replacing the first '+' with '=' might not be a problem (like excel does ++5+6
=> =+5+6) - but why is it needed?

Comment 94 tab 2008-05-22 03:03:27 UTC
	Re: Issue 20496
	I a column of 'Expenses'I have cells that, normally, receives only constants,
such as '264.58'.
Sometimes, however, I have to add an item, ie, change the constant to a formula:
'264.58+86.3'. Easy: I hit f2 (to enter the edit mode) and, since the cursor is
at the end of '264.58', enter '+86.3'. Problem: that's not a formula without a
leading =.  To enter that = sign, I have to hit Home to put the cursor at the
formula beginning, and hit =. (If entering the initial = sign is a problem to
begin with --Issue 20496; eg, ggabriel Thu Jul 29 00:11:54 +0000 2004; Issue
46416--, it becomes worse in this case, when it has to be added in front of an
existing number.)
	I read all the discussion around Issue 20496, and the suggestions to put a
leading + or - to identify a formula, but the requirement to put ANYthing in
front of the first datum ('264.58') will add at least two keystrokes --too much,
when repeated often-- and the risk to alter that datum by hitting the wrong key.
	It would be a lot easier (not only to me, but to anyone trying to enter a
formula beginning with a number) if Calc recognized the obvious: that
'264.58+86.3' is a formula. Or, that any string beginning with a numeral is a
formula, eg: '20+D5'; '6*A44-F12+64.9'; '.5*sum(E8:F22)'. If the user wants this
to be text, let her precede it with an apostrophe; because real text seldom
begin with a numeral, this requirement will be much lighter than requiring a
leading = in a formula.
	The leading = sign (or +, or -) would remain useful in such formulae as
'=sum(B4:C10)' or '=C3^2.