Excel and decimals

[Mike Blakesley]

I know there are a few people on here who are very good at Excel.

I have my overall program settings configured to insert two decimal places after entering a number in a cell, since a lot of my entries are dollar amounts.

However, there are plenty of times where I'm NOT entering dollars and would like to configure those particular cells to just take whole numbers. But, even if I go into the format dialog and request "zero" decimal places, it still insists on putting two decimal places in. So if I type 105, it will change it to 1.05, even though I've specified zero decimal places on the format dialog. I've tried formatting individual cells and also columns, but neither has worked.

Is there any way to fix this? Google has not helped.

[Ed Gordon]

Mike, is this what you are trying to do:

image.png
Column A (Format Type) is what format Column B is using. Column B (Number used) signifies that "1.05" is what was manually entered in B2 through B5. B2 through B5 were formatted manually as noted in Column A.

In the same worksheet if I use "105" instead of "1.05" it gives me this:

image.png

I suspect your problem is "I have my overall program settings configured to insert two decimal places after entering a number in a cell". Change this to default format for that column to be Currency and over-ride that format to decimal as needed.

[Ryan Gallagher]

I was more familiar with google sheets methods, so I had to look up a guide. but what you are after is cell "Data Types". You can per cell or selection of cells (rows and columns too) set the data type expected and formatting/display. It even lets you choose the number of decimal places to show (it will store more decimal places, you are only altering the view of the data).

Your overall defaults are only really useful for your most common data type, beyond that it may try to do some smart logic and change them on the fly based on what information you enter, but when it's not behaving as expected better to be specific and set the data types for the cells.

All that is hidden in the "Format Cells" popup.

A tutorial here:
https://www.datacamp.com/tutorial/data-types-in-excel

EDIT: apologies, I rushed in, your OP indicates you already had been manipulating cell formats this way. I suspect as Ed points out that it's the difference in behavior between "Currency" data type and "Decimal" data types.

[Randy Stankey]

Are there certain cells that are always formatted for currency and other cells that are always in normal numeric format?

For instance, if you have an item name in the first column, an item quantity in the second column and an item price in the third. The first column should be formatted for text/alphanumeric data. The second should be numeric and the third would be currency.

Now, let's say that the fourth column is an extended price (price x quantity) and that column would also be currency. In fact, the format of the fourth column should automatically flow from the third because it is the result of an operation with currency. However, if you wanted to ensure that the fourth column always contained a dollar amount, you could also format it as currency.

The thing you should not do is set the application preferences or the document prefs to universally format as currency (or any other format) unless the majority of your document needs to be formatted that way. It's better to leave the entire document as generic (or "auto-format") then set only the columns to special formats as you need them.

It is usually best to create the entire spreadsheet, first, with rows and columns laid out the way you want them, THEN go back and set the formats for each row, column or cell.

You can usually select in a row or column header, a cell or a group of cells then go to the sidebar, button bar or pull-down menu to set those things to the format you need.

If there is a cell that could be either numeric or currency format then you should define a custom format for that cell or group of cells. In that case, you'd define a custom format such that:

IF [first character] = "$" THEN Format = "Currency" ELSE Format = "Numeric."

That way, you can just type any number into that cell and the program will assume that you meant generic numeric format but, if you typed a dollar sign as the fist character, the program will make it be currency.

The bottom line is that, when you are first designing a spreadsheet, the user needs to spend a few minutes thinking about how the document needs to be laid out and what you want it to tell you.

To often, people (myself included) will just start typing into an empty document with only a vague idea of what they ultimately want out of their spreadsheet. Then, they end up with a mish-mash of data that has to be completely redone, later on.

I often have to remind myself of the old saying, "Proper planning prevents piss poor performance."

[Mike Blakesley]

Yeah, I am guilty of the not-planning-ahead thing too.

This particular spreadsheet is one I use for my daily end-of-day at our store and it's an ever-evolving sheet -- I keep thinking of more things to make it do and it has basically morphed over the years. Almost all of the numbers in it are currency, but there is one column I need to be "whole" numbers only and I can't get that to work. I've tried re-programming each cell separately or the column as a whole, no luck. I go to "format cells" and "number," and select "zero" decimal places, but it still automatically inserts two places like it was currency.

[Mike Blakesley]

Okay, I think I figured it out, sort of. Or at least now I understand what it is doing.

When I select "0 decimal places," it's only applying that to the number displayed after I press Enter, not what I actually typed. For example, if I type in 99, (hoping to get a quantity of 99), it will apply two decimal places and change it to 1. If I type 149, it'll also change THAT to 1. It's interpreting those numbers as .99 and 1.49 and just rounding off the decimal places.

How do I get it to just give me exactly what I typed in without any freakin' decimals? If I type 12, I want 12, not 0.12. The only way I can get 12 is if I put a period after it. (which is not a huge deal, but at this point it is becoming a quest.)

[Frank Cox]

I don't use Excel (or Microsoft Windows, for that matter) but maybe you can set up a custom number format and tell it that the number is an integer?

[Ryan Gallagher]

Okay, I think I figured it out, sort of. Or at least now I understand what it is doing.

When I select "0 decimal places," it's only applying that to the number displayed after I press Enter, not what I actually typed. For example, if I type in 99, (hoping to get a quantity of 99), it will apply two decimal places and change it to 1. If I type 149, it'll also change THAT to 1. It's interpreting those numbers as .99 and 1.49 and just rounding off the decimal places.

How do I get it to just give me exactly what I typed in without any freakin' decimals? If I type 12, I want 12, not 0.12. The only way I can get 12 is if I put a period after it. (which is not a huge deal, but at this point it is becoming a quest.)

That almost sounds like you have some "efficiency" feature enabled that does not require you to type decimals themselves, and it just assumes the last two numbers are intended as the decimals. That would be pretty non-standard behavior... but maybe there is a separate place to look for that and/or rounding preferences?

Perhaps a couple articles that encroach the subject:
https://answers.microsoft.com/en-us/...5-a707dc21a95f

https://www.indeed.com/career-advice...-from-rounding

When you go to re-edit one of those cells, does it still have your original input stored, or is it actually rounding before it stores?

[Ryan Gallagher]

Actually, there is a feature for "automatic decimal point insertion" (to avoid having to type them). I could see that combining with a 0 decimal places formatting on a cell causing this behavior?

It's under the advanced preferences apparently.

https://www.tutorialspoint.com/how-t...umber-in-excel

[Ryan Gallagher]

In fact yes, I was able to reproduce your behavior on a blank sheet if I did these two things:

1. Set the global preferences, advanced tab, automatic decimal insertion to checked (with 2 decimal places).

2. But then formatted a block of cells to a number with 0 decimal places visible.

My input: .99
Displays 1

My input 99
Displays 1

My input: 2079
Displays 21

My input 20.79
Displays 21

Note I believe automatic decimal insertion applies at the time you edit a cell (a data entry streamline thing). Highlighting those cells revels the decimalized versions, even if that is not what the cell is rounding to.
The formatting to 0 places visible can be applied at any time and undone at any time.

I think in a data entry context you would just mash two extra zeros if you wanted to keep that feature on but represent a whole number and not have it grab your last two digits as the decimals.

[Frank Cox]

I've encountered something similar to this on spreadsheets before due to the way they handle floating point math.

It shows up when you do a mathematical operation on two or more cells and use the result in another cell.

Cell A is 2, Cell B is 2, the sum in Cell C is 4. Enter 4 in cell D, compare C and D and the cells are not equal.

You can get bit this way with other things too (C compilers) but the problem can be less obvious when it's on a spreadsheet.