Help with Annoying Stuff on Excel

[kyphysics]

I currently use the free online (web-based) version of MS Excel through Google Docs.

I recently made a spreadsheet to input medical billing stuff and have come across some annoying problems that I'm wondering if anyone has experience with and could help me resolve:

1.) I have one column labeled "bill total," where I input what I paid for various prescription drugs, doctor co-pays, and other medical services. However, a random thing happens sometimes where I'll input a total and it will automatically round that number upwards.

So, for example, I had a $6.xx (six dollar something - I'm too lazy to pull it up right now to find the exact amount) and the spreadsheet just turns it into $7. Yet, oddly, it didn't do this for other totals under the same column. I also know it's not because those totals were below the rounding point. Many totals are XX.9X in them, where I know for sure it should be rounded if the entire column were somehow formatted to be rounded. I never tried to format any rows or columns to be rounded however (I don't actually know how to even do that), yet some cells are mysteriously rounded and I cannot input an exact total.

2.) Similar to the rounding problem, some when I type in $0.00 in many cells, it becomes $0 sometimes. Thus, I have $0 and $0.00 in various cells with no discernible pattern as to why some automatically turn into the $0 amount. Yes, I know both are the SAME amount, but I just like visual consistency! lol ...Also, I'm just curious why it would do that to begin with.

3.) Lastly, when I input dates, I usually do the following format:

XX/XX/XX

Again, this is a consistency problem, where if I try to enter the following:

01/08/17, it could come out as 01/08/17 sometimes and at other times 01/8/17 (missing a 0 in front of the 8). Again, I know they are the same thing, but I'm just genuinely curious why it does this and also if it can be fixed for consistency sake!

For any Excel experts out there, thanks so much for your time, help and input! Greatly appreciate it guys.

 

[UsableThought]

I never tried to format any rows or columns to be rounded however (I don't actually know how to even do that), yet some cells are mysteriously rounded and I cannot input an exact total.

I don't use cloud versions of Excel & have only occasionally used Google Docs; I prefer the older versions of Excel found in MS Office for Mac 2011 and MS Office for Windows 2010. But regardless of the specific spreadsheet program, I would never, ever think of building a spreadsheet without formatting cells in columns to do what I want them to do, as opposed to what they might do by default. Even for small one-off sheets I take the time & format. One thing that can happen is that very small differences in how you type in the data - things you don't notice because to you as a human they aren't important - can have a big effect what the program thinks you want.

So dig into the help for the application & learn how to format cells for the data types you're using. If you format the cells the way you want them, and still have a problem, then it may be that you're still throwing off the program w/ how you type the data; again, the help would tell you about this. But right now, my guess is, the software is doing its best, but is guessing & sometimes getting it wrong. Tell it what you need & it won't have to guess.

 

[Bandersnatch]

What I think is going on, is without specifying any formatting Google Sheets (nothing to do with MS Excell, by the way) interprets inputs according to some algorithm that doesn't always guess the same data type. If you were to set the data type manually, that should solve the issue as the program will always 'know' what you're expecting to see. To do so, select a whole column/row, or a range of cells, and click on one of the icons indicated below:

The first two will simply change the number of decimals displayed, the rightmost one will allow you to select e.g. 'date' data format.

 

[UsableThought]

If you were to set the data type manually, that should solve the issue as the program will always 'know' what you're expecting to see.

Exactly.

To set data types in Google Docs, I would explore what is on offer via the menu - "Format > Number >" opens up a selection of dates, currencies, numbers, etc.; and down at the bottom "More Formats >" gives you access to further variations. But the buttons may work pretty well also, I haven't tried them.

 

[kyphysics]

Appreciate the responses everyone.

I'm going to play with the formatting later tonight and report back what happens when I figure out what's going on!

 

[kyphysics]

Bandersnatch - didn't know Google Docs spreadsheet wasn't related to Excel. Thanks for that correction!

Good to learn new things everyday!