Forum Discussion
Calculate Payout using Individual and Family Deductibles
bryson8420 OK didn't catch that. So is that maximum any individual can count toward the family deductible = to the individual deductible or does the 10% they pay on services over that amount also count? assuming the former I think this will work:
=CompPerc*MAX( MIN(SUMIFS($C$1:$C2,$A$1:$A2,$A2,$B$1:$B2,$B2)-Individual,$C2), MIN(MAX(REDUCE(0,UNIQUE(FILTER($B$1:$B1,$A$1:$A1=$A2,0)), LAMBDA(p,q,p+MIN(1000,SUMIFS($C$1:$C1,$A$1:$A1,$A2,$B$1:$B1,q)))) +$C2-Family,0),$C2), 0)EDIT: I got to wondering how I would have done this 'old school' before LAMBDA and figured I would probably add a couple helper columns. These helper columns actually help see what is happening so you might consider this option. I created a column for amount of $ going towards Individual deductible, amount of $ going towards Family deductible, and then resulting payout based on that.
$ to IND = IF(C2,MIN(Individual-SUMIFS($F$1:$F1,$A$1:$A1,$A2,$B$1:$B1,$B2),$C2),"") $ to FAM = IF(C2,MAX(MIN(Family-SUMIFS($F$1:$F1,$A$1:$A1,$A2),$F2),0),"") Payout = IF(C2,(C2-MIN(F2:G2))*CompPerc,"")See attached.
mtarler Thanks for your reply! Your solutions works as long as only one person in a family has more than the deductible. In the file you shared, the family circled is returning the wrong payout:
D16 is correct, but D17 and D18 are not correct.
Essentially, D17 should be $3600 ($5,000 - invidual deductible of $1,000 * .9), and then because both person 1 (row 16) and person 2 (row 17) have each met individual deductibles of $1,000 which equal the $2,000 family deductible, the payout for all following members of this family should be 90% of their medical bills, so D18 should be $4,500.
bryson8420 OK didn't catch that. So is that maximum any individual can count toward the family deductible = to the individual deductible or does the 10% they pay on services over that amount also count? assuming the former I think this will work:
=CompPerc*MAX(
MIN(SUMIFS($C$1:$C2,$A$1:$A2,$A2,$B$1:$B2,$B2)-Individual,$C2),
MIN(MAX(REDUCE(0,UNIQUE(FILTER($B$1:$B1,$A$1:$A1=$A2,0)),
LAMBDA(p,q,p+MIN(1000,SUMIFS($C$1:$C1,$A$1:$A1,$A2,$B$1:$B1,q))))
+$C2-Family,0),$C2),
0)EDIT: I got to wondering how I would have done this 'old school' before LAMBDA and figured I would probably add a couple helper columns. These helper columns actually help see what is happening so you might consider this option. I created a column for amount of $ going towards Individual deductible, amount of $ going towards Family deductible, and then resulting payout based on that.
$ to IND = IF(C2,MIN(Individual-SUMIFS($F$1:$F1,$A$1:$A1,$A2,$B$1:$B1,$B2),$C2),"")
$ to FAM = IF(C2,MAX(MIN(Family-SUMIFS($F$1:$F1,$A$1:$A1,$A2),$F2),0),"")
Payout = IF(C2,(C2-MIN(F2:G2))*CompPerc,"")See attached.
bryson8420 I'm glad it is working for you. The best way to learn the syntax is to break it down and try a part of the formula and see what it spits out. Here is a little help to get you started. The 3 column solution:
$ to IND = IF(C2,MIN(Individual-SUMIFS($F$1:$F1,$A$1:$A1,$A2,$B$1:$B1,$B2),$C2),"") $ to FAM = IF(C2,MAX(MIN(Family-SUMIFS($F$1:$F1,$A$1:$A1,$A2),$F2),0),"") Payout = IF(C2,(C2-MIN(F2:G2))*CompPerc,"")The 1st column (F) is the $ to IND
a) finds how much Individual deductible is left based on Individual - SUMIFS(...) where that sumifs is adding up everything in column F up to the row above it only when both the family id and individual id match this row
b) it does a MIN( amount left, this row amount) which takes the lesser of the 2 values
The 2nd column (G) then does almost the same thing but only checks the family id for the sum and uses the Family deductible instead of the Individual. In this case I added the MAX( ... , 0) because the contributions toward the individual deductibles of a family could exceed the family deductible so that prevents a negative number.
The payout is then just based on the lesser of F or G (which will only be less than this amount if one or both deductibles are hit and then you want the lesser because that deductible was hit first.
mtarler I am awestruck at your wisdom and understanding of what I needed. This is working perfectly. Thank you SO much!!
Now I just have to figure out exactly what the entire syntax is doing so I can learn from this. It's quite complicated to me!! 😉I greatly appreciate your time and willingness to help me out!!
mtarler For what it's worth, I tried something with Power Query, focusing on family totals or individual totals where a family only contains one person.
