vba program - life expectancy calculator

Cho EunwooCheung Chin Ho

Wong Chi KiWong Chun Hong

Life Expectancy Calculator

This VBA program is designed to estimate a person’s life expectancy, taking into account a number of factors, such as his or her current age, gender, amount of weekly exercise, smoking habits, etc.

Overview

1. “Base” Life Expectancy Life table for Hong Kong population, published by the Census of

Statistics Department in 2013, is utilized to calculate “base” life expectancy.

2. Modification The “base” is then modified according to the user input

regarding his or her own specific health conditions and daily habits.

Calculation Steps

1. “Base” Life Expectancy

• There are different life tables for male and female population

• This table links gender and age with their corresponding life expectancy.

• This life expectancy serves as a “base” for further modification.

In fact, there are many factors such as blood pressure, smoking, amount of sleep, stress level, family history on diseases, etc.

However, to ensure that all measurement are extracted from reliable sources, only 8 factors were included in this project.

Some factors affect mortality rate, while the others directly affect the absolute value of life expectancy.

2. Modification

Marital status (change in mortality rate) On average, married people have a 15 percent higher life

expectancy than people who are single.

Smoking habit (direct change in life expectancy) Smoking 1 cigarette per day reduces life expectancy by

0.008219178 years

2. Modification

2. Modification

• By utilizing Userform in Excel, the program asks user to select or input his or her relevant health data.

VBA Codes

VBA Codes

VBA Codes

VBA Codes

Demonstration

Demonstration