Aging of a whale population

[FanCou]

Hello,

I have been looking at many forums and examples online but cannot manage to find what I am looking for.

I have a database with the population of whales of a particular area. I have the year of birth and death (for the dead ones) for each individual of this population.

My research focuses on 1970-today. For each year, I am trying to get the number of individuals in the population depending on how many births and deaths occur per year (managed to do that with the COUNTIF formula).

Now my second part is where I am stuck. For each year (so from 1970 to today again), I am also trying to divide my population between young (less than 12 years old) and adult (more than 12 years old), but I cannot manage to do that.

Any suggestion?

Thank you for your help !

[Pete_UK]

You can probably use a COUNTIFS for that, but the actual formula will depend on how your data is laid out.

It would help if you attached a sample Excel workbook.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Don't try to use the Paperclip icon, as it doesn't work on this forum.

Hope this helps.

Pete

Pete

[FlameRetired]

Hello FanCou. Welcome to the forum.

It is usually more helpful if you upload a representative Excel file. It lends context and presents the layout of the data (that's often important) as well as the layout of the summaries you want.

If you do not know how to do this:



To attach a file to your post: (Please no pics or screenshots ... saves retyping data.),

be sure to desensitize the data

• click “Go Advanced” (next to Post Quick Reply – bottom right),
• scroll down until you see “Manage Attachments”, click that,
• click “Browse”.
• select your file(s)
• click “Upload”
• click “Close window”
• click “Submit Reply”

The file name will appear at the bottom of your reply.

[FanCou]

Hello again,

Thank you for your reply. I attached a fake simplified version of my database now.
To calculate the number of individuals in the population each year, I used the formula (number of alive-number of dead):

COUNTIF($D$3:$D$86, "<="&H2)-COUNTIF($E$3:$E$86, "<="&H2) (cf. excel sheet)

Now I am trying to know how many young (<12 yrs old) and adult (>12 yrs old) are in my population each year (from 1970 to today).

Thank you very much for your help,

Fann

[FlameRetired]

I am not sure I understand properly.

In the attached find column D where the ages are calculated. Where column C is blank I've used 2019. This returns an age of 70 for row 4. Does this sound reasonable?

That formula is

Please Login or Register  to view this content.

In I1:J1 are the young and adult boundaries.

In column H are the years 1970 to 2019.

In I2 filled down and across.

Please Login or Register  to view this content.

[FanCou]

Hello again,

I don't think this is correct to take 2019 as a reference. In 1970, I have 20 youngs and 26 adults which is way more than my total population...

I am gonna try to explain a bit better...

If I look at row 20 (individual T): Born in 1971, this was a young until 1983 (<12 years old) and then was an adult from 1983 until its death in 1989.

I am basically trying to ask excel to subtract each year (1970 onwards) with the birth year of each individual, and to tell me how many of those results are higher or lower than 12.

Back to the example of individual T, let's say for the year 1980. The function should do something like 1980-1971(birth year)=9 years (<12 years, so count +1 young).

Does it make more sense?

Thank you very much for your help !

Fann

[FlameRetired]

I am basically trying to ask excel to subtract each year (1970 onwards) with the birth year of each individual, and to tell me how many of those results are higher or lower than 12.

I thought that's what my upload does.

Back to the example of individual T, let's say for the year 1980. The function should do something like 1980-1971(birth year)=9 years (<12 years, so count +1 young).

Filtering the upload shows no rows with that combination so I can't cross check against what I have.


What am I missing?

[Norie]

Fann

I'm almost 100% sure there's a better way to do this but see the attached.

[FlameRetired]

@ Norie

Ahh! Now I see.

Thank you.

[FanCou]

Thank you so much FlameRetired and Norie for your help !
It's a good thing it's a small population !!

[FlameRetired]

You are welcome. Thank you for the feedback.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.