Page 1 of 1

[Solved] Stop rounding up in age within a formula

Posted: Fri May 03, 2019 4:11 pm
by Skompy
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?

Re: Stop rounding up in age within a formula

Posted: Fri May 03, 2019 4:30 pm
by FJCC
Either of these should work

Code: Select all

=QUOTIENT(A7 - Q7;365.25)
or

Code: Select all

=FLOOR((A7-Q7)/365.25;1)

Re: Stop rounding up in age within a formula

Posted: Fri May 03, 2019 4:33 pm
by keme
Use =INT(<your formula>) to return only the integer part.

Re: Stop rounding up in age within a formula

Posted: Fri May 03, 2019 4:37 pm
by Skompy
FJCC wrote:Either of these should work

Code: Select all

=QUOTIENT(A7 - Q7;365.25)
or

Code: Select all

=FLOOR((A7-Q7)/365.25;1)
Thanks!

Re: [Solved] Stop rounding up in age within a formula

Posted: Fri May 03, 2019 8:04 pm
by RusselB
Just a suggestion for an alternative formula.

Code: Select all

=years(q7;A7;1)

Re: Stop rounding up in age within a formula

Posted: Thu May 09, 2019 5:31 pm
by Skompy
Skompy wrote:
FJCC wrote:Either of these should work

Code: Select all

=QUOTIENT(A7 - Q7;365.25)
or

Code: Select all

=FLOOR((A7-Q7)/365.25;1)
Thanks!

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?

Re: [Solved] Stop rounding up in age within a formula

Posted: Thu May 09, 2019 5:40 pm
by gerard24
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?
Because 1 year isn't 365.25 days exactly.

Use YEARS function as Russel suggest.
RusselB wrote:Just a suggestion for an alternative formula.

Code: Select all

=years(q7;A7;1)

Re: Stop rounding up in age within a formula

Posted: Thu May 09, 2019 5:44 pm
by RoryOF
I thought all horses were deemed to have the same birthday - 01 January in Northern Hemisphere, 01 August in Southern Hemisphere?

Re: [Solved] Stop rounding up in age within a formula

Posted: Thu May 09, 2019 5:50 pm
by Skompy
gerard24 wrote:
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?
Because 1 year isn't 365.25 days exactly.

Use YEARS function as Russel suggest.
RusselB wrote:Just a suggestion for an alternative formula.

Code: Select all

=years(q7;A7;1)
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.

Is this a problem I won't able to solve and that I have to just accept one or the other result?

Re: Stop rounding up in age within a formula

Posted: Thu May 09, 2019 5:59 pm
by Skompy
RoryOF wrote:I thought all horses were deemed to have the same birthday - 01 January in Northern Hemisphere, 01 August in Southern Hemisphere?
Well, horses are born at different times of the year and so no, they don't have the same actual birthday.
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.

Re: Stop rounding up in age within a formula

Posted: Thu May 09, 2019 6:42 pm
by Lupp
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 as

Code: Select all

=DATEDIF(DateOfRace; DateOfBirth; "y")
Having the dates in Q7 and A7 respectively again, we can also use the rather long but logically clear formula

Code: Select all

=YEAR(A7)-YEAR(Q7)-(MONTH(A7)<MONTH(Q7))-(MONTH(A7)=MONTH(Q7))*(DAY(A7)<DAY(Q7))

Re: Stop rounding up in age within a formula

Posted: Thu May 09, 2019 7:51 pm
by Skompy
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 as

Code: Select all

=DATEDIF(DateOfRace; DateOfBirth; "y")
Having the dates in Q7 and A7 respectively again, we can also use the rather long but logically clear formula

Code: Select all

=YEAR(A7)-YEAR(Q7)-(MONTH(A7)<MONTH(Q7))-(MONTH(A7)=MONTH(Q7))*(DAY(A7)<DAY(Q7))
I did put up a post about DATEDIF recently but it transpires that it is not available in OO Calc.

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.

Re: Stop rounding up in age within a formula

Posted: Thu May 09, 2019 7:54 pm
by Skompy
I am very grateful for everyone who has taken time to help me with this fiendish subject. :)

Re: Stop rounding up in age within a formula

Posted: Thu May 09, 2019 8:57 pm
by Lupp
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.
I would be much interested in examples resulting in errors.

Re: Stop rounding up in age within a formula

Posted: Thu May 09, 2019 10:01 pm
by Skompy
Lupp wrote:
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.
I would be much interested in examples resulting in errors.
Race Date ** DOB ******** Age in Years
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

Re: [Solved] Stop rounding up in age within a formula

Posted: Thu May 09, 2019 10:11 pm
by RoryOF
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.

Re: [Solved] Stop rounding up in age within a formula

Posted: Fri May 10, 2019 11:49 am
by keme
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 with

Code: Select all

=YEARS(Q7;A7;0)
This 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".

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)
Distance between race and birth date may be positive (race after birthday) or negative (race before birthday).

Re: [Solved] Stop rounding up in age within a formula

Posted: Fri May 10, 2019 1:27 pm
by Skompy
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 with

Code: Select all

=YEARS(Q7;A7;0)
This 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".

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)
Distance between race and birth date may be positive (race after birthday) or negative (race before birthday).

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.

Re: [Solved] Stop rounding up in age within a formula

Posted: Fri May 10, 2019 2:32 pm
by keme
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?
[...]
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:[...]
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))
[...]
Yes. If I'm not mistaken, that should return the same as =YEARS( Q7 ; A7 ; 0 )

Re: [Solved] Stop rounding up in age within a formula

Posted: Fri May 10, 2019 3:12 pm
by Lupp
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.

Re: Stop rounding up in age within a formula

Posted: Fri May 10, 2019 3:55 pm
by Skompy
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.
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).
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?

Re: Stop rounding up in age within a formula

Posted: Fri May 10, 2019 7:48 pm
by Lupp
Skompy wrote:How would you do things differently to give me what I need?
It's close to what you did anyway:

Code: Select all

=A7-Q7
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.

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?

Re: Stop rounding up in age within a formula

Posted: Fri May 10, 2019 8:05 pm
by Skompy
Lupp wrote:
Skompy wrote:How would you do things differently to give me what I need?
It's close to what you did anyway:

Code: Select all

=A7-Q7
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.

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?
I can only understand a little of what you wrote, so I can't comment on it, except for your question.
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.

Re: [Solved] Stop rounding up in age within a formula

Posted: Mon May 13, 2019 8:31 am
by keme
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.

Re: [Solved] Stop rounding up in age within a formula

Posted: Mon May 13, 2019 2:29 pm
by Skompy
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.
I want the correct information, so whatever achieves that is what I will go with.
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?