I have format my cell to 3 digits with 2 decimal places. If I entered 9.999, it automatically rounds off to 10.00 on display. I cannot convert it to text for I need to put data validation on it ( valid range from 0 to 999.99 ) How will I turn off the rounding off the cell?

  • Do you still need it as 9.999, or can it be changed to 9.990? – LDC3 Jan 9 '15 at 2:03
  • You'll need two separate cells. Use @CallumDS33's formula in the "display" cell; use your desired value in the cell for calculation. – Ron Rosenfeld Jan 10 '15 at 1:28

The TRUNC() function truncates numbers - i.e. cuts them off without rounding. Therefore:

=TRUNC(9.999,2) = 9.99

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.