Help using If and Vlookup

[heastlund]

I am trying to calculate rates based on ages, # of children AND if a certain cell is blank there would be no calculation. I have attached my spreadsheet and hope someone can help me. The formula's start at AG.

[gebobs]

Let's look at the equations for Medica...

=IF(OR($Q68="Waived",$F68=" "),0,VLOOKUP($F68,AN$3:AO$54,2))

F68 is never going to equal " " (i.e. a single space) or even "" (blank) unless you modify the cell content (which I don't advise).

Also, it is stated that "waived" is to be indicated with a "w", so the equation should reflect that.

I don't understand what you are trying to do there. Do you intend to only calculate ages for those that are no longer going to waive coverage?

The equation for dependent children doesn't have to be a lookup since all the plans charge a certain cost per each up to three. Thus:

VLOOKUP($P68,AN$4:AO$54,2) can become MIN($P68,3)*AO$4

Other than that...I would definitely use tabs to separate your tables and get rid of the white space to the top left.

[heastlund]

I took the original formula which I did not create and modified last year. It worked until I changed the calculation for the age. We found that our old calculation created a problem because it was not calculating correctly if the age was within the first 14 days of the month of the dated census. We actually hide the all of the rows so the blank space on the left isn't visible however if the formula would work better using tabs I will do that. For the "waive", correct if there is no $ amount in the current cell there should not be a calculation under BCBS, Medica, HealthPartners or PreferredOne. Thank you so much for your help!

[gebobs]

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.