Help with Annoying Stuff on Excel

  • Thread starter kyphysics
  • Start date
  • Tags
    Excel
In summary,The user has run into some problems with Excel spreadsheets, specifically with the way the program rounds numbers and with how it interacts with dates. The user has tried to format the cells in the spreadsheet to what they want them to do, but this has not always worked. The user has also tried to set data types for the cells, but this has not always worked either.
  • #1
kyphysics
676
436
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 o0) ...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.
 
Computer science news on Phys.org
  • #2
kyphysics said:
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.
 
Last edited:
  • #3
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:
Przechwytywanie1.PNG

The first two will simply change the number of decimals displayed, the rightmost one will allow you to select e.g. 'date' data format.
 
  • #4
Bandersnatch said:
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.
 
Last edited:
  • #5
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!
 
  • #6
Bandersnatch - didn't know Google Docs spreadsheet wasn't related to Excel. Thanks for that correction!

Good to learn new things everyday!
 

1. How do I remove duplicate data in Excel?

To remove duplicate data in Excel, go to the "Data" tab and click on "Remove Duplicates". Then, select the column or columns that you want to check for duplicates and click "OK". This will remove any duplicate data in the selected columns.

2. How can I freeze rows and columns in Excel?

To freeze rows and columns in Excel, go to the "View" tab and click on "Freeze Panes". Then, select whether you want to freeze the top row, first column, or both. This will keep those rows or columns visible while you scroll through the rest of the spreadsheet.

3. How do I sort data in Excel?

To sort data in Excel, select the column or columns that you want to sort. Then, go to the "Data" tab and click on "Sort". Choose the criteria you want to sort by and whether you want the data to be sorted in ascending or descending order. Click "OK" to sort the data.

4. How can I make charts and graphs in Excel?

To make charts and graphs in Excel, select the data that you want to include in the chart. Then, go to the "Insert" tab and click on the type of chart or graph you want to create. You can customize the chart by adding labels, changing the color scheme, and more.

5. How do I protect a worksheet in Excel?

To protect a worksheet in Excel, go to the "Review" tab and click on "Protect Sheet". You can choose to password protect the sheet, which will prevent anyone from making changes without the password. You can also select which actions, such as inserting or deleting rows, are allowed on the protected sheet.

Similar threads

  • Computing and Technology
Replies
1
Views
928
Replies
1
Views
2K
  • Engineering and Comp Sci Homework Help
Replies
2
Views
10K
  • Engineering and Comp Sci Homework Help
Replies
2
Views
2K
  • Programming and Computer Science
Replies
18
Views
2K
  • Computing and Technology
Replies
3
Views
3K
  • Engineering and Comp Sci Homework Help
Replies
4
Views
5K
  • Engineering and Comp Sci Homework Help
Replies
1
Views
5K
  • Programming and Computer Science
Replies
4
Views
1K
  • MATLAB, Maple, Mathematica, LaTeX
Replies
1
Views
6K
Back
Top