Using Sex and Age to determine Life Expectancy

[wordperfectconvert]

I was referred from VBA forum to this forum. I have a table where B1 ( named B1 "Sex") asks for sex ("male" or "female") and B2 asks for Date of Birth. B3 (named "Age") calculates the persons age. B4 has been named "LifeExpectancy". I have to use a specific mortality table for my purposes. I have ages from 1 to 80. That will give me about 160 different results. I tried If statements in a cell, but was limited by the number of If statements I can use.

The formula I used so far is a formula to calculate Age for B3, as:
Function Age1(DOB As Date)


If DOB = 0 Then
Age1 = "No Birthdate"
Else
Select Case Month(Date)
Case Is < Month(DOB)
Age1 = Year(Date) - Year(DOB) - 1
Case Is = Month(DOB)
If Day(Date) >= Day(DOB) Then
Age1 = Year(Date) - Year(DOB)
Else
Age1 = Year(Date) - Year(DOB) - 1
End If
Case Is > Month(DOB)
Age1 = Year(Date) - Year(DOB)
End Select
End If
End Function

THIS GETS ME THE AGE.

I am now trying to say something like If Sex = Male and Age = 47 then LifeExpectancy = 36.45. I truly am stumped. I have been surfing the net and trying different options for over 30 hours and am seeing double. Any help would be greatly appreciated. Thank you in advance.

 

[SkipVought]

This is based on data in a table, by your own admission in Forum68.

This should be a LOOKUP using sheet functions OR MS Query, which I would prefer.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[wordperfectconvert]

The mortality information is not contained in THIS table. I am pulling it from a 2001 standard mortality table used for this circumstance. I don't want to clutter up my calculator with the entire mortality table. I would prefer to enter a formula for each age and sex, even though it would involve a lot of code.

 

[strongm]

>I would prefer to enter a formula for each age and sex

The point is that noone who is trying to help you can understand [bold]why[/b] you prefer this. A simple lookup would be so much ... simpler

 

[SkipVought]

I'd say that a function with 160 control statements is ALOT more cluttered than a lookup table approch.

How big is your mortality table, anyhow? I do lookups in tables that have 50,000+ rows. No clutter at all: CLARITY!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[wordperfectconvert]

Thanks to all who contributed. I gave up on attempting to learn the VBA code. It is way over my head. For those looking for the answer, here is my solution.

I set up a separate worksheet named Mortality that had 3 columns and 91 rows. I then used the following formula in the cell I was using for the answer.

=IF(DOB="","",(IF(O2="Male",VLOOKUP(O4,Mortality!A1:C91,2,0),IF(O2="Female",VLOOKUP(O4,Mortality!A1:C91,3,0)))))

For those concerned about my stubbornness, I was trying to learn something new. I apologize for the frustration.

 

[SkipVought]

[tt]
=IF(DOB="","",(IF(O2="Male",VLOOKUP(O4,Mortality!A1:C91,2,0),IF(O2="Female",VLOOKUP(O4,Mortality!A1:C91,3,0)))))
[/tt]
[tt]
=IF(DOB="","",VLOOKUP(O4,Mortality!A1:C91,IF(O2="Male",2,3),0))
[/tt]
assuming that gender will be either Male or Female.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}