Using OR in a formula

[DRae1005]

I need a formula to give 3 different answers based on the value of one cell in a worksheet that could change.

J19 is the variable cell in my worksheet. The value of (J9-J20) may be a positive or negative number and then I need a value in J21 based on positive or negative.  If positive, I need the sum.  If negative, I need the cell to be 0.

If J20 is zero I need the value to be the sum of another cell J23

These are the 3 formulas that will give the correct answers but I need it to be an OR to each formula to get the correct answer in cell J25

J25

=IF(J20>J9,J9,J9-J20)+J22+J23+E18 works if the deductible is LARGE

=IF(J20>J9,J9,J9-J20)+J22+J23+E18 works if the deductible is smaller than the charges

=IF(j20=0,J23)+E18 works if the deductible is zero and there is a copay

Is this even possible to solve for?

Thank you,

Donna

[#2]

@DRae1005 

Hello,

Can you attach a sample file or a screenshot without sensitive data? It would be much easier to follow your explanation.

When you say: "If positive, I need the sum". The sum of which cells?

Also, what are the other cells in your formulation? (J22, E18...) =IF(J20>J9,J9,J9-J20)+J22+J23+E18

[#3]

Hi Martin,
Thank you for your response. I am attaching the link for the file for your review.
https://1drv.ms/x/c/781d73810532bd92/EfQ3nO52MGtOiDsa1UBItfYB9CkPshFumoZcP3xd5mEKXQ?e=mGUbdU&nav=MTV...
I hope this helps. I am trying to calculate the patient responsibility after insurance is applied, with deductible that is less than the Insurance charged amount, more than and with no deductible and only a copay.
Thank you!

[#4 · Accepted solution]

@DRae1005 

Hi,

Thank you for sharing a sample document. Try using this on the estimated patient balance (J25 cell):

=IFS(J20>J9,J9+J22+J23+E18,J20<J9,J20+J22+J23+E18,J20=0,J23+E18)

Does this unique formulation calculate each scenario correctly independently on the case?

Martin

[#5]

AWESOME!!! Thank you so very much for your assistance!!

[#6]

@DRae1005 

So glad it worked as desired!

Martin