Complicated formula needed

[mhawkins73]

I am hoping someone out there has an answer for this one. I am trying to get a field to automatically populate from one of three tables that is dependent upon possible values in two to three fields. Some context here is that I am tracking employee benefit enrollments. Each employee has three plan options to choose from that all have different prices that are dependent of their age. So if employee #1 chooses plan #2 and is age 45 then they have X price, it is that price I am looking to get populated from one of the three tables.

Additionally, this is further complicated that their are separate prices for each dependent and of course based off their age. I have collected the name, age, and plan choice for each employee and dependent they enrolled.

Is this something that is possible? I just have not been able to figure out how to incorporate the multiple factors that drive the result. Thank you in advance.

 

[NickRed18]

You could have in-cell formulas with embedded IF statements and maybe a couple CHOOSE's, or you could write your own functions in vba it's up to you.
Post a small example and i'll see what I can come up with.

Is this something similar to what you're working with?
Plan 1 ($/mo)
Age--> 20 30 40 50 60 70
0 120 130 140 150 160 170
1 132 143 154 165 176 187
2 144 156 168 180 192 204
3 156 169 182 195 208 221
4 168 182 196 210 224 238
5 180 195 210 225 240 255
^# Dependents


Excuse the terrible formatting... I can never get it pretty

 

[mhawkins73]

Something like that yes. I have tried to pull relevant pieces of my worksheet that should show what I am trying to do.

Excerpt from worksheet;

Age	Plan Name	Coverage Level	EE Monthly Rate	Dep Monthly Rate	EE Deduction	Dep Deduction
66	Kaiser	EE Only	?	?	?	?

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

This is an excerpt from one of the 3 tables. All three are same format just different data which is dependent off the selection made on the Plan Name;

		Employee Only	Per Dependent
		Bi-Weekly Premium	Bi-Weekly Premium
Age	Mo. Premiums
0	$171.32	$35	$79.07
1	$171.32	$35	$79.07
2	$171.32	$35	$79.07
3	$171.32	$35	$79.07
4	$171.32	$35	$79.07

<colgroup><col span="2"><col><col></colgroup><tbody>
</tbody>

So as you see the rates I have are by plan, by age and per person. While in the example I provided the rates are the same it does differ as the ages increase. I have every age from 0-100 in the tables. By selecting the plan name it dictates which table we are to use then the age of each person dictates the rate and if there are 3 dependents it is the sum of each of the three individual rates.

I hope this helps paint a better picture. Thank you for helping out with this.

 

[NickRed18]

I see now!

Assumptions (Change as needed): "Age" is in column A, your first entry is in Row 2, the "Kaiser" plan is in the range I2:O102 (Column O indicating the max dependents is 5), and the number of dependents is inbetween Plan Name and Coverage Level (Column C)

So for the employee only rate in Column E:
=IF(B2="Kaiser", VLOOKUP(A2, H2:O102, 2, FALSE), If B2 = "Plan2", VLOOKUP(A2, [Range of Plan 2], 2, FALSE), If B2="Plan3",VLOOKUP(A2, [Range of Plan 3], 2, FALSE)))

Dep Monthly Rate in Column F:
=IF(B2="Kaiser", CHOOSE(C2+1, 0, K2, L2, M2, N2, O2), IF(B2="Plan2", CHOOSE(C2+1, 0, [Plan2 - 1 Dep.], [Plan2 - 2 Dep.], [Plan2 - 3 Dep.], [Plan2 - 4 Dep.], [Plan2 - 5 Dep.]), IF(B2="Plan3", CHOOSE(C2+1, 0, [Plan3 - 1 Dep.], [Plan3 - 2 Dep.], [Plan3 - 3 Dep.], [Plan3 - 4 Dep.], [Plan3 - 5 Dep.])))

 

[mhawkins73]

So far with the Employee Only rate I am getting an error that states I have entered too many arguments in the function. Did I miss something?

 

[inactiveuserps07]

@mhawkins73, NickRed18's formula has a typo. It's missing some parentheses. I don't know if the formula will work or not, but it will have correct syntax if you just replace it with this:

=IF(B2="Kaiser", VLOOKUP(A2, H2:O102, 2, FALSE), IF (B2 = "Plan2", VLOOKUP(A2, [Range of Plan 2], 2, FALSE),IF (B2="Plan3",VLOOKUP(A2, [Range of Plan 3], 2, FALSE))))

 

[mhawkins73]

I have no yet tried the dependent one yet but from the looks of it we may be missing something here. I think we are on point with the plan but with the dependent I am not sure I follow. Let's say I have a wife and three kids ages 38, 21, 9, 3 then the rates would be $131.70, $113.63, $79.07, and $79.07 giving me a total of $403.47 which is the value I would be looking for.

 

[mhawkins73]

It worked!!!! Thank you both! Now on to the more complicated one.

@mhawkins73, NickRed18's formula has a typo. It's missing some parentheses. I don't know if the formula will work or not, but it will have correct syntax if you just replace it with this:

=IF(B2="Kaiser", VLOOKUP(A2, H2:O102, 2, FALSE), IF (B2 = "Plan2", VLOOKUP(A2, [Range of Plan 2], 2, FALSE),IF (B2="Plan3",VLOOKUP(A2, [Range of Plan 3], 2, FALSE))))

 

[NickRed18]

Thanks ErikTyler for catching that!

My mistake mhawkins, I misunderstood the dependent aspect of your question. I thought the rates applied to the age of the employee, and then the number of dependents were a set rate no matter what age they were.

 

[mhawkins73]

No worries. So basically once we determine the plan name then we would want to look to see if the dependent age fields are populated and if so then we would coordinate that with the age on the price sheet to give us the rate then it is all summed up. If no age listed this 0. I hope that makes sense.