[Solved] Stop rounding up in age within a formula
[Solved] Stop rounding up in age within a formula
I have a horse racing spreadsheet and I have the age of the horse, for any particular race, calculated by its DOB and the race date, in single years. I also have a 2nd column next to it showing the age of the horse to 2 decimal points.
Both cells have this formula =(A7-Q7)/365.25). (A is the race date and Q is the DOB).
What I see is that when the age of the horse goes beyond half a year in the 2nd column (so after .49), the age is rounded up in the column of single years. I don't want this to happen, so how do I stop the rounding up?
Both cells have this formula =(A7-Q7)/365.25). (A is the race date and Q is the DOB).
What I see is that when the age of the horse goes beyond half a year in the 2nd column (so after .49), the age is rounded up in the column of single years. I don't want this to happen, so how do I stop the rounding up?
Last edited by Skompy on Fri May 10, 2019 3:55 pm, edited 3 times in total.
Apache OpenOffice 4.1.6 AOO416m1(Build:9790) - Rev. 1844436
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
Re: Stop rounding up in age within a formula
Either of these should work
or
Code: Select all
=QUOTIENT(A7 - Q7;365.25)
Code: Select all
=FLOOR((A7-Q7)/365.25;1)
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: Stop rounding up in age within a formula
Use =INT(<your formula>) to return only the integer part.
Re: Stop rounding up in age within a formula
Thanks!FJCC wrote:Either of these should workorCode: Select all
=QUOTIENT(A7 - Q7;365.25)
Code: Select all
=FLOOR((A7-Q7)/365.25;1)
Apache OpenOffice 4.1.6 AOO416m1(Build:9790) - Rev. 1844436
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
Re: [Solved] Stop rounding up in age within a formula
Just a suggestion for an alternative formula.
Code: Select all
=years(q7;A7;1)
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
Re: Stop rounding up in age within a formula
Skompy wrote:Thanks!FJCC wrote:Either of these should workorCode: Select all
=QUOTIENT(A7 - Q7;365.25)
Code: Select all
=FLOOR((A7-Q7)/365.25;1)
I have just found some examples when the DOB and the race date are on the same day, the above formula does not show the right age in years. Once the age goes onto the next day (e.g. DOB = 8 March and race date = 9 March), the age in years shows correctly.
Do you know why that might be?
Apache OpenOffice 4.1.6 AOO416m1(Build:9790) - Rev. 1844436
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
Re: [Solved] Stop rounding up in age within a formula
Because 1 year isn't 365.25 days exactly.Skompy wrote: =QUOTIENT(A7 - Q7;365.25)
or
=FLOOR((A7-Q7)/365.25;1)
I have just found some examples when the DOB and the race date are on the same day, the above formula does not show the right age in years. Once the age goes onto the next day (e.g. DOB = 8 March and race date = 9 March), the age in years shows correctly.
Do you know why that might be?
Use YEARS function as Russel suggest.
RusselB wrote:Just a suggestion for an alternative formula.Code: Select all
=years(q7;A7;1)
LibreOffice 6.4.5 on Windows 10
Re: Stop rounding up in age within a formula
I thought all horses were deemed to have the same birthday - 01 January in Northern Hemisphere, 01 August in Southern Hemisphere?
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
Re: [Solved] Stop rounding up in age within a formula
The YEARS formula works for the issue I had, but now those horses where the dates are less than a whole year but close to a full year (.72 to .99), roundup to the next year in age. It is only when the age is .71 or less of a year that the years round to the previous age, which is what I want.gerard24 wrote:Because 1 year isn't 365.25 days exactly.Skompy wrote: =QUOTIENT(A7 - Q7;365.25)
or
=FLOOR((A7-Q7)/365.25;1)
I have just found some examples when the DOB and the race date are on the same day, the above formula does not show the right age in years. Once the age goes onto the next day (e.g. DOB = 8 March and race date = 9 March), the age in years shows correctly.
Do you know why that might be?
Use YEARS function as Russel suggest.RusselB wrote:Just a suggestion for an alternative formula.Code: Select all
=years(q7;A7;1)
Is this a problem I won't able to solve and that I have to just accept one or the other result?
Apache OpenOffice 4.1.6 AOO416m1(Build:9790) - Rev. 1844436
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
Re: Stop rounding up in age within a formula
Well, horses are born at different times of the year and so no, they don't have the same actual birthday.RoryOF wrote:I thought all horses were deemed to have the same birthday - 01 January in Northern Hemisphere, 01 August in Southern Hemisphere?
Like many animals, there is a breeding season and this is different for northern hemisphere and southern hemisphere born horses.
The statement you have made is one I have read about before and it seems that it was "decided" on by the industry many years ago, for what looks like human convenience rather than any factual or biological reason.
I am not sure if the exact age will make any difference to what I am analyzing but I have it in my s/s, just in case it does come to be a factor.
Apache OpenOffice 4.1.6 AOO416m1(Build:9790) - Rev. 1844436
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
Re: Stop rounding up in age within a formula
In recent LibO there is (for compatibility reasons) a function DATEDIF() for the purpose. (Yes the name has this ridiculous single "F".)
The age in years for the racing horse is computed then asHaving the dates in Q7 and A7 respectively again, we can also use the rather long but logically clear formula
The age in years for the racing horse is computed then as
Code: Select all
=DATEDIF(DateOfRace; DateOfBirth; "y")
Code: Select all
=YEAR(A7)-YEAR(Q7)-(MONTH(A7)<MONTH(Q7))-(MONTH(A7)=MONTH(Q7))*(DAY(A7)<DAY(Q7))
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München
Re: Stop rounding up in age within a formula
I did put up a post about DATEDIF recently but it transpires that it is not available in OO Calc.Lupp wrote:In recent LibO there is (for compatibility reasons) a function DATEDIF() for the purpose. (Yes the name has this ridiculous single "F".)
The age in years for the racing horse is computed then asHaving the dates in Q7 and A7 respectively again, we can also use the rather long but logically clear formulaCode: Select all
=DATEDIF(DateOfRace; DateOfBirth; "y")
Code: Select all
=YEAR(A7)-YEAR(Q7)-(MONTH(A7)<MONTH(Q7))-(MONTH(A7)=MONTH(Q7))*(DAY(A7)<DAY(Q7))
The second formula you have provided works on all but a small number of cells, which are dates that are within 1-2 days of each other.
But I think I can live with that small difference among a few entries, although I will have to think of something for those dates that apply to female horses, when going from a filly to a mare, at age 5.
Perhaps when I get this s/s into a database, I might have more options or if DATEDIF or some other option becomes available in OO Calc.
Apache OpenOffice 4.1.6 AOO416m1(Build:9790) - Rev. 1844436
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
Re: Stop rounding up in age within a formula
I am very grateful for everyone who has taken time to help me with this fiendish subject.
Apache OpenOffice 4.1.6 AOO416m1(Build:9790) - Rev. 1844436
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
Re: Stop rounding up in age within a formula
I would be much interested in examples resulting in errors.Skompy wrote:The second formula you have provided works on all but a small number of cells, which are dates that are within 1-2 days of each other.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München
Re: Stop rounding up in age within a formula
Race Date ** DOB ******** Age in YearsLupp wrote:I would be much interested in examples resulting in errors.Skompy wrote:The second formula you have provided works on all but a small number of cells, which are dates that are within 1-2 days of each other.
30 Apr 13 ** 30 Apr 08 (shows as 5, with true age as 4.999)
04 May 14 ** 04 May 09 (shows as 5, with true age as 4.999)
23 Apr 14 ** 23 Apr 08 (shows as 6, with true age as 5.999)
1 May 14 ** 01 May 08 (shows as 6, with true age as 5.999)
02 May 15 ** 02 May 09 (shows as 6, with true age as 5.999)
16 May 15 ** 16 May 09 (shows as 6, with true age as 5.999)
11 Jan 14 ** 11 Jan 05 (shows as 9, with true age as 8.999)
06 Apr 15 ** 06 Apr 06 (shows as 9, with true age as 8.999)
24 May 14 ** 24 May 04 (shows as 10, with true age as 9.999)
25 Apr 14 ** 25 Apr 09 (shows as 5, with true age as 4.999)
11 Apr 10 ** 11 Apr 15 (shows as 5, with true age as 4.999)
09 Mar 14 ** 09 Mar 08 (shows as 6, with true age as 5.999)
True age is calculated with this formula =(a7-q7)/365.25 and set to 3 decimal places
Apache OpenOffice 4.1.6 AOO416m1(Build:9790) - Rev. 1844436
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
Re: [Solved] Stop rounding up in age within a formula
Leap years. There are 1826 days between 30 Apr 08 and 30 Apr 13. Your true age formula does not take leap years correctly into account.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
Re: [Solved] Stop rounding up in age within a formula
I believe you need the YEARS() function, but with the last (third) parameter given as zero, to correctly return actual age in full years from birth to race date. Using 1 there, as previously suggested, will return the number of new-year (Dec-Jan) transitions passed between the two dates.
So, to show the age in number of years (integer), replace your formula withThis will correctly take leap years into account, instead of spreading the leap day evenly across 4 years. (Also, the rule is that century transitions must be divisible by 400 to give a leap year, so the exact factor is slightly smaller: 365.2425)
If you need "true age" with 3 decimals, use "new-year count" instead (third parameter 1 as previously suggested), adjust with the the distance in days between birthday and race date (using current year for both dates), and round to 3 decimals. This will also keep the leap days in place, avoiding the "spread error".Distance between race and birth date may be positive (race after birthday) or negative (race before birthday).
So, to show the age in number of years (integer), replace your formula with
Code: Select all
=YEARS(Q7;A7;0)
If you need "true age" with 3 decimals, use "new-year count" instead (third parameter 1 as previously suggested), adjust with the the distance in days between birthday and race date (using current year for both dates), and round to 3 decimals. This will also keep the leap days in place, avoiding the "spread error".
Code: Select all
=ROUND(YEARS(Q7;A7;1) + DAYS(DATE(YEAR(A7);MONTH(A7);DAY(A7));DATE(YEAR(A7);MONTH(Q7);DAY(Q7)))/DAYSINYEAR(A7);3)
Re: [Solved] Stop rounding up in age within a formula
keme wrote:I believe you need the YEARS() function, but with the last (third) parameter given as zero, to correctly return actual age in full years from birth to race date. Using 1 there, as previously suggested, will return the number of new-year (Dec-Jan) transitions passed between the two dates.
So, to show the age in number of years (integer), replace your formula withThis will correctly take leap years into account, instead of spreading the leap day evenly across 4 years. (Also, the rule is that century transitions must be divisible by 400 to give a leap year, so the exact factor is slightly smaller: 365.2425)Code: Select all
=YEARS(Q7;A7;0)
If you need "true age" with 3 decimals, use "new-year count" instead (third parameter 1 as previously suggested), adjust with the the distance in days between birthday and race date (using current year for both dates), and round to 3 decimals. This will also keep the leap days in place, avoiding the "spread error".Distance between race and birth date may be positive (race after birthday) or negative (race before birthday).Code: Select all
=ROUND(YEARS(Q7;A7;1) + DAYS(DATE(YEAR(A7);MONTH(A7);DAY(A7));DATE(YEAR(A7);MONTH(Q7);DAY(Q7)))/DAYSINYEAR(A7);3)
So do I enter your ROUND formula and your DAYS formula all in one cell (as I have done) and which seems to have worked and resolved all the above issues?
I also have retained this formula that Lupp provided, to show age in only years in the cell next to the one showing true age.
=YEAR(A7)-YEAR(Q7)-(MONTH(A7)<MONTH(Q7))-(MONTH(A7)=MONTH(Q7))*(DAY(A7)<DAY(Q7))
The years formula seems unsuitable for my s/s.
Apache OpenOffice 4.1.6 AOO416m1(Build:9790) - Rev. 1844436
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
Re: [Solved] Stop rounding up in age within a formula
That is what I meant, yes. The spacing and stepwise explanation I gave was just to clarify the formula's operations, not to indicate that you should split it to multiple cells.Skompy wrote:[...]
So do I enter your ROUND formula and your DAYS formula all in one cell (as I have done) and which seems to have worked and resolved all the above issues?
[...]
Yes. If I'm not mistaken, that should return the same as =YEARS( Q7 ; A7 ; 0 )Skompy wrote:[...]
I also have retained this formula that Lupp provided, to show age in only years in the cell next to the one showing true age.
=YEAR(A7)-YEAR(Q7)-(MONTH(A7)<MONTH(Q7))-(MONTH(A7)=MONTH(Q7))*(DAY(A7)<DAY(Q7))
[...]
Re: [Solved] Stop rounding up in age within a formula
What's the idea of using a "year" defined in an arbitrary way as the unit for something then called "true age"?
The common year is inappropriate as a unit. It is a calendaric conception. If you want to compare ages at a higher resolution than ordinary years allow for, you should use days (as spreadsheets implicitly do). If you try to get even beyond that you get lost with any conception based on calendaric terms. You need to run a stopwatch then. "Days" and "hours" as used in everyday life are ruled by legal norms little interested in technical consistency. Under the rule of haphazard regulations ("daylight saving time" e.g.) days differ in length and hours may be skipped or doubled... And if a parliament decides to apply to a different time zone your horse may get younger.
The common year is inappropriate as a unit. It is a calendaric conception. If you want to compare ages at a higher resolution than ordinary years allow for, you should use days (as spreadsheets implicitly do). If you try to get even beyond that you get lost with any conception based on calendaric terms. You need to run a stopwatch then. "Days" and "hours" as used in everyday life are ruled by legal norms little interested in technical consistency. Under the rule of haphazard regulations ("daylight saving time" e.g.) days differ in length and hours may be skipped or doubled... And if a parliament decides to apply to a different time zone your horse may get younger.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München
Re: Stop rounding up in age within a formula
My reference to true age should, I think, more properly be called actual age, since an age in years is a very rough figure and can only ever be correctly stated in years, on the birthday, each year (notwithstanding the point you make about hours and minutes, DST).Lupp wrote:What's the idea of using a "year" defined in an arbitrary way as the unit for something then called "true age"?
The common year is inappropriate as a unit. It is a calendaric conception. If you want to compare ages at a higher resolution than ordinary years allow for, you should use days (as spreadsheets implicitly do). If you try to get even beyond that you get lost with any conception based on calendaric terms. You need to run a stopwatch then. "Days" and "hours" as used in everyday life are ruled by legal norms little interested in technical consistency. Under the rule of haphazard regulations ("daylight saving time" e.g.) days differ in length and hours may be skipped or doubled... And if a parliament decides to apply to a different time zone your horse may get younger.
I need both the rough age and actual age for my s/s.
With the ROUND + DAYS formula provided by keme and the YEAR formula that you provided, what I have now seem to provide what I need.
How would you do things differently to give me what I need?
Apache OpenOffice 4.1.6 AOO416m1(Build:9790) - Rev. 1844436
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
Re: Stop rounding up in age within a formula
It's close to what you did anyway:Skompy wrote:How would you do things differently to give me what I need?
Code: Select all
=A7-Q7
In addition you treat horses a bit like stars as you use the Julian year like astronomers do instead of either the Gregorian year or a year defined astronomically in one or another way.
Strange: To find a significant number (around 10) of cases where the difference gets striking you need to go through about 11000 pairs of random dates as statistics tells me. Did you?
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München
Re: Stop rounding up in age within a formula
I can only understand a little of what you wrote, so I can't comment on it, except for your question.Lupp wrote:It's close to what you did anyway:Skompy wrote:How would you do things differently to give me what I need?That's the age in days disregarding the TOD of birth and of the race as well. Its the division by 365.25 what screws things up (a bit). If you compare these results to the "full-year-age" in cases where this is exact, you get unavoidably some irritating/unplausible results for the reason I already mentioned: The year of some odd days+fraction is not a unit. You may use it like a unit (defining the LY - https://en.wikipedia.org/wiki/Light-year e.g.) as long as you keep it decoupled from the calendar then. That's not an option for you, however, since you have to handle calendaric dates for DOB, DOR.Code: Select all
=A7-Q7
In addition you treat horses a bit like stars as you use the Julian year like astronomers do instead of either the Gregorian year or a year defined astronomically in one or another way.
Strange: To find a significant number (around 10) of cases where the difference gets striking you need to go through about 11000 pairs of random dates as statistics tells me. Did you?
Yes, my s/s has a lot of dates, and dates are a very critical element to what I am working on.
I have more than 15000 rows of data, with the corresponding number of the date pairs shown in this thread.
Apache OpenOffice 4.1.6 AOO416m1(Build:9790) - Rev. 1844436
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
Re: [Solved] Stop rounding up in age within a formula
The "days count" provides a perfectly linear time scale. The calendar approach where you use "year" as the unit, makes a slightly "bumpy line". The leap year bumps and monthly dents are so small that they make little difference for our everyday tasks. The calendar is the time scale we share, worldwide, and for many tasks it is the only viable choice.
When you do statistical work on a population of this magnitude, however, those tiny bumps and dents in time may become significant. In this case you may need to decide whether it is the exact age (day count) or relation to calendar/seasonal variations - e.g. using "years + days", or "day_count/365.25" - which is most important.
"Day_count/365.25" is also linear, but will not always return integer results when you need it to (which is the reason why you started this thread, it seems). From this you get the "bumps" you first mentioned, in the classification of age groups.
When you do statistical work on a population of this magnitude, however, those tiny bumps and dents in time may become significant. In this case you may need to decide whether it is the exact age (day count) or relation to calendar/seasonal variations - e.g. using "years + days", or "day_count/365.25" - which is most important.
"Day_count/365.25" is also linear, but will not always return integer results when you need it to (which is the reason why you started this thread, it seems). From this you get the "bumps" you first mentioned, in the classification of age groups.
Re: [Solved] Stop rounding up in age within a formula
I want the correct information, so whatever achieves that is what I will go with.keme wrote:The "days count" provides a perfectly linear time scale. The calendar approach where you use "year" as the unit, makes a slightly "bumpy line". The leap year bumps and monthly dents are so small that they make little difference for our everyday tasks. The calendar is the time scale we share, worldwide, and for many tasks it is the only viable choice.
When you do statistical work on a population of this magnitude, however, those tiny bumps and dents in time may become significant. In this case you may need to decide whether it is the exact age (day count) or relation to calendar/seasonal variations - e.g. using "years + days", or "day_count/365.25" - which is most important.
"Day_count/365.25" is also linear, but will not always return integer results when you need it to (which is the reason why you started this thread, it seems). From this you get the "bumps" you first mentioned, in the classification of age groups.
Just to clarify, I need one cell with the age in years only and a second cell with the actual age calculated in years, months & days.
Regarding the latter, RusselB gave me a formula on another thread of mine (about DATEDIF) which attempted to show the difference between 2 dates as x years, x months and x days, all in one cell. It mostly works but there is a problem with the formula and he said he will work in it. If he is able to correct the problem, then I can use that too for the actual age
For the showing of the age as only years, is it possible to calculate the age using DAYS, but then format it to show the days as whole years?
Apache OpenOffice 4.1.6 AOO416m1(Build:9790) - Rev. 1844436
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.