Issue 30470 - Calc needs ability to specify fixed decimal.
Summary: Calc needs ability to specify fixed decimal.
Alias: None
Product: Calc
Classification: Application
Component: ui (show other issues)
Version: OOo 1.1.1
Hardware: All All
: P3 Trivial with 21 votes (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
Depends on:
Reported: 2004-06-19 03:13 UTC by jcdelta
Modified: 2016-03-22 22:28 UTC (History)
6 users (show)

See Also:
Latest Confirmation in: ---
Developer Difficulty: ---


Note You need to log in before you can comment on or make changes to this issue.
Description jcdelta 2004-06-19 03:13:34 UTC
In excel, when entering numbers, it is possible to set the option “fixed
decimal” to 2, so that the following entries give the following results:

entry		result
123 		1.23
1		0.01
4567		45.67
1.2358		1.2358

I've also seen this labeled as “auto decimal”, and it is very handy.  This is
quite literally the main issue holding us back from switching away from excel. 
We already use writer, and would like to use Calc as well.
Comment 1 frank 2004-06-20 21:38:16 UTC
Hi Bettina,

one for you.

Comment 2 shadthames 2006-11-03 11:29:15 UTC
Firstly, thank you to all the developers who develop OO, it is a great product.

However, as with the original poster of this issue, the lack of fixed decimals
is holding me and several colleagues back from converting to Open/StarOffice. We
all hoped it might be resolved in v2.

We find ourselves having to use MS Office mostly as a result of the additional
inconvenience of trying to chop-and-change between 2 office suites, one for WP
and presentations, the other for S-Ss.

If this issue was resolved, we'd all convert over immediately.
Comment 3 jmgraetz 2008-01-03 18:48:45 UTC
On desk calculators, this is called "add mode". It's a basic capability for
anyone keeping a running account book. I'd be happy to help develop the
function, if someone will help me with programming (I haven't written a line of
code in 15 years).
Comment 4 jcdelta 2008-01-03 21:20:18 UTC
The algorithm is not difficult. It's just cell_value * 10 ^ - magnitude_change.
 There would be only a single entry in the preferences dialog; the desired
magnitude_change, (defaulting to zero).  I just don't know my way around the
code base.  I could write the code, I just don't know where to put it.

Considering this issue's importance to anyone trying to use oo for any sort of
financial application, it's sad to see that it's languished for over three years.
Comment 5 jmgraetz 2008-01-04 00:57:37 UTC
As jcdelta points out, it is simple arithmetic; it shouldn't be hard to write a
function in VBA to do it. But I don't know the underlying language for OO, and
then there's the question of how to make it available. Excel is crude, at least
in the Office97 version I use: you have to set a global option for fixed
decimal, even if you want it only for a particular spreadsheet or range of
cells. Seems to me it should be a formatting choice, both for numbers and
(especially) for currency.
Comment 6 joryvc 2008-01-24 13:58:39 UTC
I too would like Calc to have this feature. We use Excel in the field as we
measure well casing in decimals. This feature is the last item on our list that
keeps us from converting over completely.
Comment 7 jalcradus02 2009-09-28 21:36:23 UTC
I agree with the other comments that it's a "make or break" issue for those who
enter monetary information--which is probably most of the business users. 
Entering the decimal point each time can add up to a lot of extra keystrokes.
Comment 8 baomike 2010-04-18 21:01:13 UTC
They are right, this is a use/non use issue with financial data.
Without the ability of the keypad to act as ten-key input it is just aggravation
to use Calc. I spend half my time reentering numbers because of a "3" instead 
of a "." . 

Comment 9 bettina.haberer 2010-05-21 14:42:19 UTC
To grep the issues easier via "requirements" I put the issues currently lying on
my owner to the owner "requirements". 
Comment 10 got2av8 2011-03-22 01:49:47 UTC
I gather it's been going on 7 years and not a lot of action on this issue.  I understand there are a lot of requests out there and relatively few programmers, but just in case I'll weigh in on how a lack of this particular feature really inhibits our ability to migrate away from Excel, which has had this option since 1997.  It seems that something virtually any desk calculator can do with the flip of a lever shouldn't be all that hard to offer as an option.
Comment 11 Shirley 2012-06-18 11:08:43 UTC
I was reading through the oldests bugs because my particular favorite is next to the oldest.  In doing so, I ran across this bug -er- enhancement - that is also rated trivial.  I think the powers that decide on what to work on are using the votes of the current user base (and probably only the more technical and vocal among them) and not the potential customers who are out there.  

But about this problem in particular.  This isn't a pretty solution but it works.  It would be nicer if it were in a macro but every time I try to develop a macro in OO, I fail miserably (in spite of having programmed for years and years.  Once upon a time, I even wrote a HR budgeting system using only Lotus macros).

So here is my suggestion.

1. Enter the series of numbers with and without decimal points:


2. HIGHLIGHT the values you just entered.
3. Open FIND and REPLACE
4. Click on MORE OPTIONS
5. Put checkmarks in 

6. In the SEARCH FOR box copy and paste this: 
7. In the REPLACE WITH box enter 
8. Click on FIND ALL to see which cells with be changed.  
9. Click on REPLACE ALL.

In the example, the results will appear as:

Again, this isn't an elegant solution.  You have to remember to run the FIND and REPLACE.  If you change one of the numbers after you did the FIND and REPLACE, you have to enter the decimal point in the correct position. 

If you use a comma to separate whole numbers from the fractional part, this isn't going to work but could be adapted.
Comment 12 JAD 2014-05-12 13:40:50 UTC
PLEASE, PLEASE Someone help CALC with this ability of fixed decimal for cents.  I've been waiting since I first heard of years ago.  Every time a new version is offered, I've downloaded, then been so disappointed.  I use Excel to enter all the info from checking account and have since the 90's.  There is no way that I want to complicate the job by having to manually insert the decimal into every entry. I'm pretty computer literate, 67 years old and used to work as tech support for a high school. Thank you!  signed "Desperate for a fix"  Don't want to purchase another version of MS Office just because of this.
Comment 13 oooforum (fr) 2014-11-07 13:39:56 UTC
*** Issue 125837 has been marked as a duplicate of this issue. ***
Comment 14 Kay 2014-11-09 23:42:58 UTC
This is also possible in Apache OpenOffice 4.1.1 and probably older versions as well, though I'm not certain at what point it was fixed.

 To "fix" decimal places for numbers, select a column, go to Format -> Numbers and you will see the Decimal places selection under Options.