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.
Yes, it is possible to calculate the payout based on individual and family deductibles using the described logic. Here's an example formula (without having opened your file, for your own security reasons) that you can use in Excel to achieve this calculation:
=IF(COUNTIFS(A:A,A2)>1,IF(SUM(B:B)>family_deductible,SUM(B:B)-family_deductible,0),IF(B2>individual_deductible,B2-individual_deductible,0))*payout_percentage
Here's how you can use this formula:
- Assume that column A contains the "family" number and column B contains the medical bills for each person.
- Replace family_deductible with the value of the family deductible (e.g., $2,000).
- Replace individual_deductible with the value of the individual deductible (e.g., $1,000).
- Replace payout_percentage with the percentage of payout (e.g., 90%).
- Enter the formula in the desired cell (e.g., in column C next to each person's medical bills).
- Copy the formula down for all the individuals in the family.
The formula will check the number of occurrences of the "family" number in column A.
If there is more than one occurrence, it will calculate the payout based on the family deductible.
If the total medical bills for the family are greater than the family deductible, it will subtract the deductible from the total.
If there is only one occurrence of the "family" number, it will calculate the payout based on the individual deductible.
If the individual's medical bills are greater than the individual deductible, it will subtract the deductible from the individual's medical bills.
The calculated payout amount will be displayed in column C for each individual.
Remember to adjust the cell references and values according to your specific data and requirements.
I hope this helps!
NikolinoDE I copied your formula, and I think I replaced all the cell/value references correctly, and it is not quite right yet:
The families of only one person (row 11 and 20) are correct, but if there are >1 member in the family, it is calculating the entire B column less 2,000 times .9.