Hi
I am trying to get the spreadsheet to give a sum of zero in the cell if the formula sums to a negative number or keep the positive number if it is greater than 0.
I am solving for how much the insurance company will pay.
Scenario-
If the Amt billed to insurance is <Deductible then the patient will pay
If the Amt billed to insurance is >Deductible then Insurance will pay the amount over the deductible-coinsurance.
After the deductible is paid by the patient, then they will owe the coinsurance % for the amount over the deductible and the insurance will pay the balance.
Example
$1357 billed to insurance
$500 Deductible 20% coinsurance
$1357-500 (pt amount of Ded)
$857 * 20% coinsurance =$171.40
Patient would pay $500 ded and $171.40 coinsurance for total of $671.40.
Insurance would pay $685.60
If the Deductible was $5000 then $1357 would be billed and it would all be patient deductible.
I cannot figure out how to make my spreadsheet work!
| Insurance Covered Services | Units | | Cost | Total Billed Amount | CODE | Ins Units | | AETNA | Total Contractual Amount |
| Re-exams | 2 | | $65.00 | $130.00 | 99212 | | | $36.61 | $73.22 |
| Manipulations 1-2 reg | 0 | | $55.00 | $0.00 | 98940 | | | $24.56 | $0.00 |
| Manipulations 3-4 reg | 18 | | $65.00 | $1,170.00 | 98941 | | | $34.28 | $617.04 |
| Extra Spinal | 0 | | $38.00 | $0.00 | 98943 | | | $23.06 | $0.00 |
| Estim | 22 | | $30.00 | $0.00 | 97014 | | | $13.28 | $0.00 |
| Traction | 18 | | $33.00 | $594.00 | 97012 | | | $13.82 | $248.76 |
| Rehab | 16 | | $30.00 | $480.00 | 97110 | | | $26.14 | $418.24 |
| | | | | $2,374.00 | | | | | $1,357.26 |
| Total Insurance Billed Amount | | | | | | | | | $1,357.26 |
| Insurance Contractual | | | | | | | | | $1,357.26 |
| Estimated Insurance Adjustment | | | | | | | | | $1,016.74 |
| Noncovered Services | | | | | | | | | |
| Cash Manipulations | 3 | | $60.00 | $180.00 | | | | | |
| Cash Re-exam | 3 | | $50.00 | $150.00 | | | | | |
| Cash Rehab | 0 | | $20.00 | $0.00 | | | | | |
| Cash Acupuncture/Dry Needling | 0 | | $60.00 | $0.00 | | | | | |
| Cash Decompression | 0 | | $60.00 | $0.00 | | | | | |
| SoftWave | 0 | | $ - | $0.00 | | | | | |
| Total Noncovered Services | | | | $330.00 | | | | | |
| Total Insurance and Noncovered Services | | | | | $1,687.26 |
| Deductible to be met: | | | | | | | | | $500.00 |
| Services after deductible | | | | | | | | | $0.00 |
| Coinsurance | | | 20.00% | | | | | | $171.45 |
| Copays | | | $0.00 | | | | | | $0.00 |
| Estimated insurance payment | | | | | | | | | $685.81 |
| Estimated Patient Balance | | | | | | | | | $1,001.45 |
