Forum Discussion
How to prevent Excel from rounding up 2-digit numbers?
Look at this image.
I'm not sure what you're doing either, but cell A1 displays 39 when what I've entered into that cell is =INT(39.792)
Or look at this: when 39.999 has been entered.
I don't know if there's a default set somewhere in your system that is leading to "rounding up" (I can find no setting in Preferences to do that, for what it's worth).
In any event, there is a function called ROUNDDOWN and it yields this:
By the way, have you noticed that the function names highlighted in blue, both in the paragraph above and in the prior message, are hyperlinks taking you to a really good resource called ExcelJet? I commend that to you for your own research. The link to ROUNDDOWN will also take you to links to many other similar functions within the ExcelJet website.
I also tried that second formula [=INT(DeathDate-Birthdate)/365.25)], and it still returned a value of 40 when I need it to read 39. I'm not sure if I'm doing anything wrong. For "DeathDate," I set it to the cell corresponding to that value (in my case, L2), and "BirthDate" was set to the cell corresponding to its value (in my case, E2), and I divided both by 365.25. Now the value of the cell is 39.792, but it still rounds up to 40.
Is there a way to get smaller numbers like 39.792 to round down to 39, even when they logically should be rounded up?
And thank you again for the initial response! I really appreciate the help!
Look at this image.
I'm not sure what you're doing either, but cell A1 displays 39 when what I've entered into that cell is =INT(39.792)
Or look at this: when 39.999 has been entered.
I don't know if there's a default set somewhere in your system that is leading to "rounding up" (I can find no setting in Preferences to do that, for what it's worth).
In any event, there is a function called ROUNDDOWN and it yields this:
By the way, have you noticed that the function names highlighted in blue, both in the paragraph above and in the prior message, are hyperlinks taking you to a really good resource called ExcelJet? I commend that to you for your own research. The link to ROUNDDOWN will also take you to links to many other similar functions within the ExcelJet website.
mathetes- Thank you for the follow-up! Your second reply helped me to resolve the issue completely!
I realized my error -- I did not have an extra set of parentheses around the text following =INT. When I wrote the following, I consistently got a value rounded up to 40 when it should have read 39:
=INT(L2-E2)/365.25
However, when I put parentheses around it like this, I consistently get a value properly rounded down to 39:
=INT((L2-E2)/365.25)
I feel silly for not realizing my error earlier, but this was the solution I was looking for. Thank you so much for your help!!
- The correct sequence of operations makes a BIG difference. Placing the right set of parentheses in the right places is a part of that.
Glad you got it resolved. I hope you have also taken time along the way to look at the ExcelJet resources that I linked to. It's been clear all along that you have not been aware of the several different functions that can be used to achieve effects comparable to rounding down.
By the way, I'm also using Excel on a Mac, which I notice you are using. I have a subscription to Microsoft 365 and Excel version 16.84.
None of that should make any difference, but it strikes me as odd that you said DATEDIF didn't work at all. (Of course, the whole sequence is odd, since INT should have produced the lower number as well!!)