Here's what I've worked up. You can either go with this or let me know where I am completely wrong and I'll work on any updates we need to do.
* I put the tables in a tab called Premiums. The leftmost table is the premiums for employee, spouse, etc by age for each of the plans. The other is the per child premium for dependents.
* Ages are only calculated if there is a birth date. Otherwise, it's blank.
* Premiums are calculated only for those individuals with ages i.e. if an employee wants to waive coverage, don't put a birth date for him/her. Likewise, their spouse or children.
* Premium for dependent children is as I had above. However, it is important to have data in each cell. I noticed you have display of zero values suppressed so if you have a blank rather than a zero for an employee with no dependent children, it will calculate or three children. If you leave it blank, MIN("",3) returns 3 rather than 0.
|