Help with formula for data analysis

Question

Hi everyone,&nbsp;Been trying to work on this formula for a while and cant quite get it, probably as I dont use excel often.&nbsp;Column A is current age. Column B is the date of an event. Column C is where I would like the output of how old they were when the event occurred.&nbsp;For example:&nbsp;A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;C18&nbsp; //age this year&nbsp; &nbsp;23/11/2018&nbsp; &nbsp; &nbsp; &nbsp; 12&nbsp; &nbsp;//years old in 201817&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 17/02/2020&nbsp; &nbsp; &nbsp; &nbsp; 13&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 02/04/2020&nbsp; &nbsp; &nbsp; &nbsp; 13&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;//preferably this line is all blank16&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 09/11/2019&nbsp; &nbsp; &nbsp; &nbsp; 11&nbsp;I am trying to ignore the blank spaces like in B4 while using A2 as the placeholder for the calculations that would require a number in A3. This is small example of a larger cohort, so take that into consideration please.=IFERROR(-DATEDIF(B1,TODAY(),"Y"))+A1),""&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&lt;- this formula does not take into account the&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;blank spaces but seems to work otherwise.I have also messed around with IF(OR) and xlookup, but I am a bit short on time to learn it all at once.&nbsp;Any help is appreciated,Thanks&nbsp;

rodrigo_ · Answer

jfig9&nbsp;Use this array formula on C1&nbsp;&nbsp;=IF(B1&lt;&gt;"", IF(A1&lt;&gt;"", A1, LOOKUP(2,1/(A$1:A1&lt;&gt;""),A$1:A1)) - (YEAR(TODAY()) - YEAR(B1)), "")&nbsp;&nbsp;(use ctrl+shift+enter instead of enter key only after typing/pasting the formula)It will check first if the corresponding row has a value on column B, if so, it uses the age on column A, if not, it uses the lookup function to fine the last non-empty cell in the array (A$1:A1)then YEAR(TODAY()) - YEAR(B) calculates the number of years between the current year and the event year(B), then the formula subtracts that number from the age(A) determined in the previous step.&nbsp;&nbsp;

Forum Discussion

Help with formula for data analysis

Resources