Lesson#52: Let’s check your lifeline with Excel

Just a different exercise of excel. I have downloaded a listed country-wise average life expectancy by WHO. Let’s check where are we in our lifeline by this sheet.

Let’s take an input of the date of birth from the user. I have made different boxes for Year, Month, and Day. From that it will make the date of birth by using this formula =IF(OR($B$3=0,$B$4=0,$B$5=0),””,DATE($B$5,$B$4,$B$3))
This formula won’t show any date of birth until any of the three box left blank.

Here input of day and month is taken from the user will be selected from lists by using Data Validation just like I have shown in pictures.

For taking input of Year I have used Data Validation>Whole Number
I have given a minimum value of 1900 because excel doesn’t take the value of the Year before 1900 in date format. And the maximum value is =YEAR(TODAY()) the year of the present date.

For taking input of SEX here I have used a Data Validation>List which allows you to choose from a drop-down list.

For taking input of COUNTRY here I have used a Data Validation>List which allows you to choose from a drop-down list.

Your Age is being calculated with this formula =IF(A6=””,””,ROUND(YEARFRAC(A6,TODAY()),1))

=IFERROR(IF($D$3=”MALE”, VLOOKUP($C$6,$A$14:$I$196,7,FALSE), IF($D$3=”FEMALE”, VLOOKUP($C$6,$A$14:$I$196,5,FALSE),IF($D$3=”TRANSGENDER”, VLOOKUP($C$6,$A$14:$I$196,3,FALSE)))),””)


This formula lookup the value of Your Average Life Expectancy from the input of Sex and Country.

Now I have selected both Your Age and Your Average Life Expectancy and right-clicked on them. Selected Format Cells>Number>Custom and added suffix “Year”.

This one deliver the message =IF(OR($B$3=0,$B$4=0,$B$5=0,$D$3=0,$C$6=0),””,”You have lived “&ROUND(($A$8/$C$8)*100,0)&”% of your expected life”)

Another one shows “Congratulation! Stay fit and healthy” if Your age = Your Average Life Expectancy,
“Still more to go” if Your age < Your Average Life Expectancy,
“God bless you” if Your age > Your Average Life Expectancy

by this formula =IF(OR($B$3=0,$B$4=0,$B$5=0,$D$3=0,$C$6=0),””,IF($A$8=$C$8,”Congratulation! Stay fit and healthy”, IF($A$8<$C$8,”Still more to go”, “God bless you”)))


Download the file from here and check your lifeline.