Forum Discussion
Help with formula for data analysis
Hi everyone, Been trying to work on this formula for a while and cant quite get it, probably as I dont use excel often. Column A is current age. Column B is the date of an event. Column C is ...
jfig9
Use this array formula on C1
=IF(B1<>"", IF(A1<>"", A1, LOOKUP(2,1/(A$1:A1<>""),A$1:A1)) - (YEAR(TODAY()) - YEAR(B1)), "")
(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.