Multiple If or Condition statements in excel

[wordperfectconvert]

I have an excel spreadsheet where the person enters sex (male or female) in B2, the date of birth in B3, and B4 calculates the age at last birthday. I want b5 to say somethine like "If B2 is Male and age is 47, then B5 (life expectancy) is 28.45". I could do this with an If formula, but only for 7 options.

I am trying to write VBA code for the cell because I will have 160 options (Male age 1-80, and Female 1-80). I am open to any suggestions. Thanks.

 

[ettienne]

Have a table somewhere in your spreadsheet with your values, then use a Lookup (VLOOKUP or HLOOPKUP)formula in B5.

 

[SkipVought]

Hi,

This is not a VBA question. It is a basic Excel question, best handled in Forum68.

Skip,

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

 

[wordperfectconvert]

SkipVough, I beg to differ. Two reasons, the variables end up with 160 options and that is too many for excel to handle. Second, I intend to publish the spreadsheet or calculator on my web page in html format and have been advised that for this cell I should use VBA code for the formula. I admit I am a complete novice with VBA, but am really looking for help.

 

[ettienne]

In VBA:

If B2="Male" then
   Select Case B4
     Case 1
     Case 2
     ..
     Case 80
   End Select
End if

If B2="Female" then
   Select Case B4
     Case 1
     Case 2
     ..
     Case 80
   End Select
End if

This get pretty long winded.

You could save the life expectancy in an array to simplify things.

 

[SkipVought]

I'd wager that the whole thing could be defined in a table and done with lookups.

Skip,

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

 

[mintjulep]

Is there an underlying formula for the life expectancy?

Why go through all the trouble of a lookup if you could simply calculate it.

 

[wordperfectconvert]

There is no underlying formula as it comes from an actuarial table. I asked the publishers of the table. (I agree there should be a formula, but they say it is not a constant)

 

[SkipVought]

Hmmmmm. "an actuarial table"

???

Skip,

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

 

[mintjulep]

...have been advised that for this cell I should use VBA code for the formula

By whom? What were their reasons?

You have 3 people here advising you to use a simple lookup table.

Do you have a rule that you only take advice from the first one to offer it?

 

[mintjulep]

Since you seem to be dead set on code to do this:

Load your table into a 2 x 80 array.

 

[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.