Forum Discussion
How to prevent Excel from rounding up 2-digit numbers?
Hello! I am attempting to use Excel (on Mac) to calculate the ages of people who have died. I have their exact dates of birth, and their exact dates of death. However, for some of them, their day/month of birth happen to be more than 180 days away from their day/month of death, meaning that Excel automatically rounds their age up to the nearest integer and creates an inaccurate result.
For example, in one case, a person was born in August 1958 and died in June 1998. This would make them 39 years old at the time of their death. However, Excel always rounds this value up to 40 (because the formula I have used shows that they were 39.82 years old). I cannot include decimals for their ages; that age would need to display as the whole number of 39, but rounded down. I have no idea how to prevent the rounding-up, or if there is a way to prevent this at all. Anyone have any tips?
If it is helpful, the formula I am using is basic; it just subtracts the cell of their date of death by the cell of their date of birth, and then divides the result by 365.
=(L2-E2)/365
Thank you!
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.
6 Replies
There are at least these two ways.
=DATEDIF(BirthDate,DeathDate,"y")
=INT(DeathDate-Birthdate)/365.25)
The DATEDIF function is a carryover from Lotus 1-2-3 so Excel doesn't prompt you for the syntax, like it does for true Excel functions. But it does work.
The INT function simply returns the integer value of the very subtraction and division operation you were getting. No rounding, per se, in either direction. It just clips off the decimal, whatever it is. I used 365.25 because that is a more accurate number as the years roll on; it accounts for leap year's additional day every four years.
- Hi - thank you so much for the reply! Unfortunately, though, the first formula [=DATEDIF(BirthDate,DeathDate,"y")] did not work for me at all.
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.
