Apache OpenOffice (AOO) Bugzilla – Issue 30470
Calc needs ability to specify fixed decimal.
Last modified: 2016-03-22 22:28:39 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.
Hi Bettina, one for you. Frank
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.
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).
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.
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.
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.
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.
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 "." .
To grep the issues easier via "requirements" I put the issues currently lying on my owner to the owner "requirements".
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.
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: 123 1 4567 1.2358 2. HIGHLIGHT the values you just entered. 3. Open FIND and REPLACE 4. Click on MORE OPTIONS 5. Put checkmarks in CURRENT SELECTION ONLY and REGULAR EXPRESSIONS 6. In the SEARCH FOR box copy and paste this: ^[:digit:]*[^\.-\.][:digit:]*$ 7. In the REPLACE WITH box enter =&*.01 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: 1.23 .01 45.67 1.2358 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.
PLEASE, PLEASE Someone help CALC with this ability of fixed decimal for cents. I've been waiting since I first heard of OO.org 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.
*** Issue 125837 has been marked as a duplicate of this issue. ***
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.