take cell amount from another cell till no remainder then changing functions for rest

[bwildman]

Hello all,

I am stuck on a project for work and am hoping to get some help on here. I'll try to explain this the best I can and to me that is giving more of a back story to why I am trying to figure this out. I work in a doctors office that deals with insurance. I am trying to make an excel sheet to figure out a patients total cost. I have the basics down but am stuck on how to do the total thanks to deductibles and co-insurances. I am trying to write a formula that will take the cost of each service off the difference in the deductible until there is no more deductible, then take the co-insurance off of the remaining items. So basically I have a cell for what's remaining and a cell for the deductible. Then I have another cell for the % of co-insurance. I am using this idea for if the deductible has been met: (The current formula is much larger than this and doesn't end in zero but to save the long post this is just a taste)

=IF(AND(T11="No",(D7=F7)),(A11-(A11*K7)),0)
Key:
D7 is whats been met on the deductible ($20)
F7 is the deductible amount ($100)
K7 is the co-insurance (80%)
T11 is just a question regarding the type of insurance
A11 was the total for one of the services (which we have 7 different items that come off the deductible- this is just an example)

Which this is working perfectly for when the deductible has been met. What I need help on is trying to figure out the formula for when the deductible hasn't been met. so when D7 does not equal F7. When that is the case, I need to take the difference from each service until the deductible is met, and any service after that, I need to take away the co-insurance. Which is where my "just started using excel this week" skills find their limit.

So, if D7=20 and F7= 100, that leaves me with $80.
A11= $20
A12= $40
A13= $20
A14= $10

the $80 would satisfy A11-A13 so I would then need it to stop subtracting from F7 and take K7 (80%) off of the remaining cells.So whichever cell it satisfies the deductible on, it automatically starts with the co-insurance for the remainder remaining services that are added into the total? (Which I have figured out would be to add A14-(A14*K7) into the over all total.

For a bonus: in that previous scenario.... say A13=$30 so that leaves a remainder of $10. Is there a way to take the 80% off of that remainder? So whichever cell it satisfies the deductible on, it automatically starts with the co-insurance for the remainder of that service/cell plus the following cells?

I tried looking through other posts and wasn't sure if I could get any of them to work for what I needed. However, if something is related I apologize in advance for reposting a similar question and would appreciate the link to that

[FDibbins]

Unless Im missing something, arent you over complicating this?

You have the charge
You have the primary ins
sub-total1 = charge - primary (the ins will never cover the full amount, correct?)

Then is there is a 2nd ins...
sub-total2 = subtotal1 - 2nd ins (again, 2nd ins will never cover remaining balance, will it?)

Isnt that how it wold work?

[bwildman]

Unless Im missing something, arent you over complicating this?

You have the charge
You have the primary ins
sub-total1 = charge - primary (the ins will never cover the full amount, correct?)

Then is there is a 2nd ins...
sub-total2 = subtotal1 - 2nd ins (again, 2nd ins will never cover remaining balance, will it?)

Isnt that how it wold work?

So i understand that and I have that for when there isn’t a deductible. The issue is that I need to satisfy the deductible difference amount before taking the co-insurance.

So if I have seven cells that add up to equal the total (their out of pocket total), and they haven’t met the deductible yet then some or maybe all of the cells will go towards their out of pocket cost and never have the need to factor in the 80% for insurance. I’m trying to get it so that the cells that are going towards the deductible will add into the total themselves until that deductible is met (or in this case f7=d7) once that happens I need it to stop adding the value in the cells to the total and start adding the remaining cells at 80%. If that makes since.