Random Rounding in Google Sheets

In summary, someone has introduced a bug into a Google Sheets spreadsheet where numbers are rounded UP instead of down. There may be a workaround of copying a cell with generic formats onto a 2-D range of cells, but it is unclear what other options are available.
  • #1
kyphysics
676
436
If anyone knows what on Earth could be happening and how to fix this, please let me know.

I'm using Google Sheets. I have lots of cells where I'm manually entering numbers with some having decimal places. All of a sudden, a random cell rounds a number UP for me. WTH? There's no rounding (that I can tell) in other cells.

Anyone know why this happens and how to remove it so I only get what I enter (i.e., no rounding)?

Props to anyone who knows how to fix this!
 
Computer science news on Phys.org
  • #2
Is it feasible to delete the entire row or column this cell is in? Copy the remaining cells to a new row/column before if needed and if that doesn't create too many problems elsewhere in the sheet.
 
  • #3
This is just a guess. The number may be fine, but the default formatting of that cell may be different. You can test that by changing the number format of the offending cell. Increase the number of decimal digits displayed.

Why the default may be different for different cells, I have no idea.
 
  • Like
Likes berkeman and russ_watters
  • #4
anorlunda said:
The number may be fine, but the default formatting of that cell may be different.
That was my thought as well. I'm not familiar with Google Sheets, but in Excel I sometimes run into formatting issues when adding information to a spreadsheet.

I don't know what a similar fix would be in Google Sheets, but in Excel you can copy a cell (that you know has generic formats) onto a 2-D range of cells. That's one way to clear out any leftover formats in the target cells. There may be some other Edit option that will let you clear the formats in a selected 2-D range of cells.

1568401185036.png
 
  • #5
An additional speculation is that the program a certain number of cells are created and populated with default empty cell values on startup. Later, more rows and columns can be added, and populated with empty cell default values.

It would be a bug if those two cases of default values were not the same, but it is certainly conceivable.
 

1. What is random rounding in Google Sheets?

Random rounding in Google Sheets is a feature that allows users to round numbers to the nearest whole number or a specified decimal place, while randomly rounding up or down. This means that the rounded number will be either the next highest or lowest whole number, with a 50% chance for each outcome.

2. How do I use random rounding in Google Sheets?

To use random rounding in Google Sheets, you can use the ROUND function with the addition of the RANDBETWEEN function. For example, to randomly round the number in cell A1 to the nearest whole number, you can use the formula =ROUND(A1*RANDBETWEEN(0,1),0).

3. Can I specify the decimal places for random rounding in Google Sheets?

Yes, you can specify the decimal places for random rounding in Google Sheets. You can use the ROUND function with the addition of the RANDBETWEEN function and specify the number of decimal places in the formula. For example, to randomly round the number in cell A1 to 2 decimal places, you can use the formula =ROUND(A1*RANDBETWEEN(0,1),2).

4. What is the purpose of random rounding in Google Sheets?

The purpose of random rounding in Google Sheets is to introduce an element of randomness in rounding numbers, which can be useful in certain situations. For example, in a game or simulation, random rounding can simulate the natural variability of numbers.

5. Is random rounding in Google Sheets accurate?

Random rounding in Google Sheets is not meant to be an accurate rounding method. It is designed to introduce randomness and should not be used for precise calculations. It is important to keep in mind that the results of random rounding will vary each time the spreadsheet is recalculated.

Similar threads

  • Quantum Physics
Replies
13
Views
818
  • Computing and Technology
Replies
5
Views
2K
  • Computing and Technology
Replies
3
Views
2K
  • Programming and Computer Science
Replies
10
Views
2K
Replies
4
Views
787
  • Computing and Technology
Replies
6
Views
960
  • Set Theory, Logic, Probability, Statistics
Replies
6
Views
1K
Replies
19
Views
2K
  • MATLAB, Maple, Mathematica, LaTeX
Replies
12
Views
698
  • STEM Educators and Teaching
Replies
19
Views
1K
Back
Top