[Solved] Stop rounding up in age within a formula

Discuss the spreadsheet application
Post Reply
Skompy
Posts: 110
Joined: Tue Feb 18, 2014 10:35 am

[Solved] Stop rounding up in age within a formula

Post 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?
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.
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Stop rounding up in age within a formula

Post 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)
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.
User avatar
keme
Volunteer
Posts: 3699
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Stop rounding up in age within a formula

Post by keme »

Use =INT(<your formula>) to return only the integer part.
Skompy
Posts: 110
Joined: Tue Feb 18, 2014 10:35 am

Re: Stop rounding up in age within a formula

Post 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!
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.
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

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

Post by RusselB »

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.
Skompy
Posts: 110
Joined: Tue Feb 18, 2014 10:35 am

Re: Stop rounding up in age within a formula

Post 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?
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.
gerard24
Volunteer
Posts: 958
Joined: Sat Oct 30, 2010 5:12 pm
Location: France

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

Post 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)
LibreOffice 6.4.5 on Windows 10
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Stop rounding up in age within a formula

Post by RoryOF »

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
Skompy
Posts: 110
Joined: Tue Feb 18, 2014 10:35 am

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

Post 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?
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.
Skompy
Posts: 110
Joined: Tue Feb 18, 2014 10:35 am

Re: Stop rounding up in age within a formula

Post 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.
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.
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Stop rounding up in age within a formula

Post 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))
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
Skompy
Posts: 110
Joined: Tue Feb 18, 2014 10:35 am

Re: Stop rounding up in age within a formula

Post 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.
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.
Skompy
Posts: 110
Joined: Tue Feb 18, 2014 10:35 am

Re: Stop rounding up in age within a formula

Post by Skompy »

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.
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Stop rounding up in age within a formula

Post 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.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
Skompy
Posts: 110
Joined: Tue Feb 18, 2014 10:35 am

Re: Stop rounding up in age within a formula

Post 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
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.
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

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

Post 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.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
User avatar
keme
Volunteer
Posts: 3699
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

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

Post 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).
Skompy
Posts: 110
Joined: Tue Feb 18, 2014 10:35 am

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

Post 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.
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.
User avatar
keme
Volunteer
Posts: 3699
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

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

Post 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 )
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

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

Post 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.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
Skompy
Posts: 110
Joined: Tue Feb 18, 2014 10:35 am

Re: Stop rounding up in age within a formula

Post 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?
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.
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Stop rounding up in age within a formula

Post 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?
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
Skompy
Posts: 110
Joined: Tue Feb 18, 2014 10:35 am

Re: Stop rounding up in age within a formula

Post 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.
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.
User avatar
keme
Volunteer
Posts: 3699
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

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

Post 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.
Skompy
Posts: 110
Joined: Tue Feb 18, 2014 10:35 am

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

Post 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?
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.
Post Reply