I'm trying to nest multiple IF statements, along with IF(AND(OR) statements within one cell. The "Premium Table" I have to use is below, along with a sample of the the worksheet. The question is: What IF statement do I enter in cell D2 to obtain the correct premium?
Premium Table
Age Range - Coverage Type - Premium Amount
<tbody>
</tbody>
<tbody>
</tbody>
Premium Table
Age Range - Coverage Type - Premium Amount
<colgroup><col><col><col></colgroup><tbody> </tbody> | Employee Only Employee + Spouse Employee + Child(ren) Employee + Family | $76.05 $366.19 $366.19 $366.19 | |
Ages 40-49 | Employee Only Employee + Spouse Employee + Child(ren) Employee + Family | $111.20 $418.94 $418.94 $418.94 | |
Ages 50-59 | Employee Only Employee + Spouse Employee + Child(ren) Employee + Family | $234.32 $586.02 $586.02 $586.02 | |
Ages 60+ | Employee Only Employee + Spouse Employee + Child(ren) Employee + Family | $329.20 $821.56 $821.56 $821.56 |
<tbody>
</tbody>
A | B | C | D | |
1 | Employee Name | Age | Coverage | Premium |
2 | John Doe | 35 | Employee + Family | |
3 | Jane Smith | 41 | Employee Only | |
4 | Bob Johnson | 60 | Employee + Spouse |
<tbody>
</tbody>