Complicated formula needed

mhawkins73

New Member
Joined
Jun 27, 2017
Messages
6
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. :)
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

NickRed18

Board Regular
Joined
May 25, 2017
Messages
76
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
 
Last edited:

mhawkins73

New Member
Joined
Jun 27, 2017
Messages
6
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;
AgePlan NameCoverage Level EE Monthly RateDep Monthly RateEE DeductionDep Deduction
66KaiserEE 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 OnlyPer Dependent
Bi-Weekly PremiumBi-Weekly Premium
AgeMo. 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

Board Regular
Joined
May 25, 2017
Messages
76
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.])))
 
Last edited:

mhawkins73

New Member
Joined
Jun 27, 2017
Messages
6
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

Banned user
Joined
May 25, 2017
Messages
748
@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))))
 
Last edited:

mhawkins73

New Member
Joined
Jun 27, 2017
Messages
6
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

New Member
Joined
Jun 27, 2017
Messages
6
It worked!!!! Thank you both! Now on to the more complicated one. :eek:

@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

Board Regular
Joined
May 25, 2017
Messages
76
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

New Member
Joined
Jun 27, 2017
Messages
6
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,090,027
Messages
5,411,930
Members
403,405
Latest member
CoenH

This Week's Hot Topics

Top