Populating a year chart with a persons age

[Ocean525]

I would like help to create a column of of dates (month and year) that I can also minimize to just year. Then I want to put a persons age in the next collum and have is populate down showing the persons age relatice to the date, EG: 2024/56
2025/57
2026/58
I would need a another column to add another person's age to populate the same
2024/56/51

I must be honest I don't know where to begin but once I read the code I'm sure it will all make sense.

 

[Gerald Higgins]

Hi, welcome to the board.
I've read and re-read this and I'm confused about what you want to do.
When you say you want to be able to "minimise to just year", what do you mean ?

If you have consecutive years in one column, and you just want to show a person's age increasing by one year, each year, this is very easy.
Let's say you have someone who is 30 this year, and you have years, starting with 2011, in cell A1, and their age, 30, in B1, then in B2 just put

=B1+1

and copy down as far as required.

But I'm guessing you maybe want something more complicated than this. Can you explain please ?

 

[Ocean525]

Thanks Gerald, sorry for the double post. I created it that way and founf that if the origional cell age was blank I had a lot of "0" populating the chart which I did not want. I tried to write a "IF" to calculate it leaving a blank sell if the age sell was blank and it worked on the first cell and all the reat were #value!. I want it to look clean, put in the age and let it populate. Actually want I really want is to put be able to put in the cell above the DATE column (To Age 90) and then when I put in the persons age in the cell to the right both coulmns populate down with the date and age so it the person is 50 there will be 50 cell beolw with the years in one row and the age, 50, 51,52 etc in the next column.

 

[Gerald Higgins]

Sorry, I just don't understand what you mean.

Can you give us a small example of what you have, and what you want the results to be ?

 

[Ocean525]

WOW I just read my reply and I am embarrassed for the poor spelling and grammar. I was in a rush and was being asked to do something for which I was late in doing and did not proof the content before clicking reply. Sorry

I originally did what you suggested. The cell that you put the age in (I will call the “age cell”) that all the other cells below it calculate adding plus one works great. The problem is that if there isn’t a number in that “age cell” the cells below change to “0” and it looks terrible.

So I tried to write a "WHAT IF" ( =+IF(B2>0.5,B2+1,"") ) to do the calculation hoping that I could eliminate the “0” in all the cells below. It worked in the first cell below the Age Cell but all the cells below that had “#VALUE!”, which looks worse than the “0” when using the simple calculation you proposed.

Want I really want is for it to look clean like a blank sheet. I would like to have a “date cell” 2A where I put in the year, lets say 2012. Then in cell 2B I put in the current age, lets say 40. In cell 1A I put in the years I want it to populate the cells below. Lets say to age 90. Since the person is 40 that would be 40 years. Since the date is 2012 that would mean that cell 2A would start with 2012 and populate all the cells below 2A ending with 2072. Cell range 2B would start with age 40 and end at 90. If I changed the number in cell 1A to 100 then the date range would populate down to 2082 and age would be 100 in the corresponding cell to the right cell. If I chance the 2A cell to 2020 and put in the age of the person in 2020 and put in cell 1A- 30 years and 2A -30 years old, the cells below would populate showing 2050 and 60 years old. Whatever the formula used in the b column for age I can copy and arrange for column line “C” to add another person’s age range.

Hopefully this time I explained it better. Thanks for your help

 

[Gerald Higgins]

OK I think I understand now.

First, to sort this part out

So I tried to write a "WHAT IF" ( =+IF(B2>0.5,B2+1,"") )

try this instead

=if(or(b2=0,b2=""),"",b2+1)

For the rest of it, I think it's straightforward to do that, but I'm a rush to go out now so I'll look back over the weekend. Maybe someone else can fix it in the meantime, if not I'll try and do it on Sunday evening (UK time).

 

[Gerald Higgins]

OK. Let's say, as in post #5, that you are inputting the current year in cell A2 and the current age in B2, and the final Target age in A1.

In A3...

=if(A2="","",if((A2-A$2)>=(A$1-B$2),"",A2+1))

In B3

=if(A3="","",B2+1)

Copy A3 and B3 down as far as required.

 

[Ocean525]

Thanks that works great. Do you have an explanation of what the formular means so I and use it again or take parts of it to use in other formulars?

That really is sweet! Really thanks alot.

John

 

[Ocean525]

I just noticed that if I have several person age columns (the whole family) that if I dont put in a age in a column that the column still popluates below to match the age duration set in cell A1. Is ther a way so that is no information is put in the cell C2,D2... etc that the cells below will be blank?

thanks

John

 

[Gerald Higgins]

Try in A3

=IF(OR(A2="",B2=""),"",IF((A2-A$2)>=(A$1-B$2),"",A2+1))