Thread: [Solved] Help using If and Vlookup
View Single Post
 
Old 08-04-2014, 01:34 PM
gebobs gebobs is offline Windows 7 64bit Office 2010 64bit
Expert
 
Join Date: Mar 2014
Location: Atlanta
Posts: 837
gebobs has a spectacular aura aboutgebobs has a spectacular aura about
Default

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.
Attached Files
File Type: xlsx Renewal Census Template 2014-test2.xlsx (34.3 KB, 15 views)
Reply With Quote