3

In Google Sheets, I set the data validation of a cell to a number that must be greater than or equal to 100. When I enter 9999999999999999999999, I get an error that the entered value must be greater than or equal to 100. Why does this happen? I also tried to change the formats to no avail.

Screenshot 1 | Screenshot 2 | Screenshot 3

1 Answers1

2

Google Sheets (and spreadsheets in general) store numbers with 15 digits of precision. The reason why has to do with how numbers are stored in computer hardware. See https://en.wikipedia.org/wiki/IEEE_754 for an explanation.

When you enter a number like 9999999999999999999999, Google Sheets treats it like a string. If sheets tried to treat it like a number, it would actually be rounded to 1E22 (the last 8 digits are rounded off).

mary
  • 702
  • 4
  • 11
  • Ohhhhhh. That makes sense. Thank you so much! I found a way around it by using the custom formula "=REGEXMATCH(TO_TEXT(E2:E), "^[0-9]{19,}$")". –  Mar 13 '21 at 19:55