How to setup for automatic decimal

My accounting software and my accounting calculator both do a really handy thing: they insert the decimal for me. If, for example, I type in 123456, it will be automatically converted to $1234.56. If I type 1200 it will become 12.00 unless I override by typing in the decimal where I want it. So if I want 1200.00 I can either type 1200. or 120000. Both my calculator and my numeric keypad have a 00 key to make data entry faster when you have lots of zeros.

SO, is there any way to do this in calc? Is it a setting somewhere, or a number format that I can’t find or what?


I bet the replacement is something a macro could do, but probably only after a keypress, so it would only save you some key presses.

There would be no point to that. If I have to push an extra key I’ll push the dot. The real point is that when you enter a LOT of monetary amounts on an accounting calculator or into accounting software, you get into a rhythm which allows you very fast and accurate data entry. Having to constantly remember to put in the decimal - when the computer should be able to do that for you - and having to backspace every few entries when you forget the decimal, or having your data full of errors because you didn’t notice that you forgot the decimal really messes things up. Without the automatic decimal, this just isn’t professional level accounting or bookkeeping software. I suppose it’s great as a general purpose spreadsheet, making presentations or for people who don’t routinely do a lot of bookkeeping, but that’s about it.

According to the help page on Number Format Codes under Decimal Places and Significant Digits it does not appear possible to format a cell such that it assumes a set number of decimal places in the data being entered. I also cannot locate any option that would alter the interpretation of data entry in the manner indicated.

There does not appear to be an open enhancement request specifically related to this issue. Please file an enhancement bug and provide as much information about this new feature as possible. Don’t forget to mark your bug as an ‘enhancement’. The QA team will be happy to help you triage your feature request in the bugtracker. Please post a link to any bugs you file in a comment below using the format “fdo#123456”. Thanks.

I tried submitting a problem report once. I don’t remember exactly what it was anymore, but I sure learned to stay away from that bugtracker. I wasted a lot of time. As I remember it, I stated the problem and then was asked to re-state it. I re-stated it and someone reproduced it. A couple of years later I got a notice that it had been cancelled because I had not provided additional information. Hah! Fool me once, shame on you, fool me twice, shame on me.

I think LibreOffice and OpenOffice are very good software designed by hobbyists who don’t always understand the “markets” that would like to use their software. The developers work on what they want to work on and prioritize their work based on their interests, not on the needs of their customers. I think this is very reasonable and I don’t blame them a bit. I do not like people telling me how to pursue my hobbies, either. But software development isn’t my hobby. I can’t fix this problem myself and I doubt that any of the developers have ever used this function on their calculator even if they have it. So the same thing will probably happen as happened the last time. Time will be wasted, time will pass, and the request will go nowhere.

Anyway, my desktop accounting calculator has this very basic functionality. Any software that cannot do it isn’t suitable for keeping extensive financial records on. Little onsy-twosy spreadsheets, fine, but not large ones with itemized expenses for larger projects spread over different asset and expense categories. So I’ll look around at the other spreadsheet out there and see if any of them can do it.

It seems manipulating formatting is easier for you than recording macros. So perhaps try this (admittedly inelegant) workaround:

  1. Format the empty cells as percentage (%)
  2. Enter the values in 100’s. You will notice that per your example, ‘1200’ entered becomes ‘1200%’.
  3. After you have finished, reformat the cell to Currency. ‘1200%’ should become ‘$12.00’.

This works because percentages are stored as floats. e.g. ‘50%’ is actually ‘0.5’.

Hmmm, I’ll experiment with it, but it sounds too fussy. And if I miss a cell, then I have a serious mistake that can cost me money in an audit. Pretty much the same problem as if I forget to type in a decimal point with the current situation.

Manipulating formatting only happens a few times even on a huge spreadsheet because I just copy/paste rows and the formatting and formulae follow automatically and everything is always formatted correctly.

I wonder if a format can be stored as a sequence of events or formulae. That would really modernize the spreadsheet - these things haven’t changed all that much since the SuperCalc that came with my Osborne I computer back when dinosaurs roamed the earth. One problem they all seem to have is that a cell can’t seem to change its own contents. What I really want is to be able to type 1200 into a cell and have it automatically divided by 100, but with the result remaining within the cell. It’s easy to divide it by 100 and have the result go to a different cell, but that isn’t how really accounting calculators and software work.