Hi. This is my first post in this group, so I hope I'm adhering to proper protocol.

Because our rather absurd healthcare system is such a labryinth of premiums, deductibles, out-of-pocket maximums, primary doc visit fees, specialist visit fees, lab fees, Rx fees, copayment vs. coinsurance etc., I built a worksheet to help me run "what-if" scenarios for each of the nearly 30 plans from which I can choose for 2018. The worksheet uses a combination of nested conditionals and VLOOKUPs to create a sum that represents what I would spend on a given plan in 2018 given certain values for type of doc visit and frequency as well as same for prescriptions.

My problem is this: My formula yields a result, but it's not correct against a known, manual calculation of same.When I examine Formula Builder for each of the conditional statements and VLOOKUPs, the returned values are accurate but it fails the manual check. I've checked and rechecked spelling of names and syntax of the conditional statements. Something's up.

The named ranges/values are as indicated he

PREMIUM = Dollar amount of monthly premium
PRIMARY_VISIT = Either of the dollar amount OR the coinsurance percentage per primary care MD visit
SPECIALIST= Either of the dollar amount OR the coinsurance percentage per specialist visit
VISIT_COSTS = the lookup table for visit type (text value), cost (dollars), and quantity/year (integer)
LAB = Either of the the dollar amount OR the coinsurance percentage per lab visit
TIER_2_MED = Either of the the dollar amount OR the coinsurance per prescription
DRUG_DEDUCTIBLE = the dollar amount of any annual Rx deductible

This is the formula for all rows:

=(PREMIUM*12)+(DENTAL*12)+IF(PRIMARY_VISIT=1,VLOO KUP("DOC VISIT",VISIT_COSTS,3)*PRIMARY_VISIT,PRIMARY_VISIT* VLOOKUP("DOC VISIT",VISIT_COSTS,2)*VLOOKUP("DOC VISIT",VISIT_COSTS,3)+IF(SPECIALIST=1,VLOOKUP("SP ECIALIST VISIT",VISIT_COSTS,3)*SPECIALIST,SPECIALIST*VLOOKU P("SPECIALIST VISIT",VISIT_COSTS,2)*VLOOKUP("SPECIALIST VISIT",VISIT_COSTS,3)+IF(LAB=1,VLOOKUP("LAB VISIT",VISIT_COSTS,3)*LAB,LAB*VLOOKUP("LAB VISIT",VISIT_COSTS,2)*VLOOKUP("LAB VISIT",VISIT_COSTS,3))+IF(TIER_2_MED<1,VLOOKUP("RX ",VISIT_COSTS,2)*VLOOKUP("RX",VISIT_COSTS,3)*TIER_ 2_MED,IF(TIER_2_MED=0,DRUG_DEDUCT*0.5,TIER_2_MED*V LOOKUP("RX",VISIT_COSTS,3)))))

In Row 1 of the sheet, the values are as follows:

PREMIUM = $491
DENTAL = $29
PRIMARY_VISIT = $75
SPECIALIST = $105
LAB = $40
TIER_2_MED = $0
DRUG_DEDUCT = $500

Value types:

PREMIUM: Dollar amount
DENTAL: Dollar amount
PRIMARY_VISIT: Dollar amount OR percentage
SPECIALIST: Dollar amount OR percentage
LAB: Dollar amount OR percentage
TIER_2_MED: Dollar amount OR percentage
DRUG_DEDUCT: Dollar amount

My manual calculation is $6,940, but the formula consistently returns $6,465 regardless of what I do.

Additional note: This model is admittedly not perfect€”particularly with Rx calculations, but I didn't get so granular as to include logic for bumping up against the Drug Deductible because I doubt I'd hit it. However, it does return a reasonable approximation of what I can expect to spend given the variables I enter.

Any help is REALLY appreciated. :)

Thanks.

On Wednesday, December 13, 2017 at 10:30:12 AM UTC-8, SanDiegoGuy wrote:
Hi. This is my first post in this group, so I hope I'm adhering to proper protocol.

Because our rather absurd healthcare system is such a labryinth of premiums, deductibles, out-of-pocket maximums, primary doc visit fees, specialist visit fees, lab fees, Rx fees, copayment vs. coinsurance etc., I built a worksheet to help me run "what-if" scenarios for each of the nearly 30 plans from which I can choose for 2018. The worksheet uses a combination of nested conditionals and VLOOKUPs to create a sum that represents what I would spend on a given plan in 2018 given certain values for type of doc visit and frequency as well as same for prescriptions.

My problem is this: My formula yields a result, but it's not correct against a known, manual calculation of same.When I examine Formula Builder for each of the conditional statements and VLOOKUPs, the returned values are accurate but it fails the manual check. I've checked and rechecked spelling of names and syntax of the conditional statements. Something's up.

The named ranges/values are as indicated he

PREMIUM = Dollar amount of monthly premium
PRIMARY_VISIT = Either of the dollar amount OR the coinsurance percentage per primary care MD visit
SPECIALIST= Either of the dollar amount OR the coinsurance percentage per specialist visit
VISIT_COSTS = the lookup table for visit type (text value), cost (dollars), and quantity/year (integer)
LAB = Either of the the dollar amount OR the coinsurance percentage per lab visit
TIER_2_MED = Either of the the dollar amount OR the coinsurance per prescription
DRUG_DEDUCTIBLE = the dollar amount of any annual Rx deductible

This is the formula for all rows:

=(PREMIUM*12)+(DENTAL*12)+IF(PRIMARY_VISIT=1,VLOO KUP("DOC VISIT",VISIT_COSTS,3)*PRIMARY_VISIT,PRIMARY_VISIT* VLOOKUP("DOC VISIT",VISIT_COSTS,2)*VLOOKUP("DOC VISIT",VISIT_COSTS,3)+IF(SPECIALIST=1,VLOOKUP("SP ECIALIST VISIT",VISIT_COSTS,3)*SPECIALIST,SPECIALIST*VLOOKU P("SPECIALIST VISIT",VISIT_COSTS,2)*VLOOKUP("SPECIALIST VISIT",VISIT_COSTS,3)+IF(LAB=1,VLOOKUP("LAB VISIT",VISIT_COSTS,3)*LAB,LAB*VLOOKUP("LAB VISIT",VISIT_COSTS,2)*VLOOKUP("LAB VISIT",VISIT_COSTS,3))+IF(TIER_2_MED<1,VLOOKUP("RX ",VISIT_COSTS,2)*VLOOKUP("RX",VISIT_COSTS,3)*TIER_ 2_MED,IF(TIER_2_MED=0,DRUG_DEDUCT*0.5,TIER_2_MED*V LOOKUP("RX",VISIT_COSTS,3)))))

In Row 1 of the sheet, the values are as follows:

PREMIUM = $491
DENTAL = $29
PRIMARY_VISIT = $75
SPECIALIST = $105
LAB = $40
TIER_2_MED = $0
DRUG_DEDUCT = $500

Value types:

PREMIUM: Dollar amount
DENTAL: Dollar amount
PRIMARY_VISIT: Dollar amount OR percentage
SPECIALIST: Dollar amount OR percentage
LAB: Dollar amount OR percentage
TIER_2_MED: Dollar amount OR percentage
DRUG_DEDUCT: Dollar amount

My manual calculation is $6,940, but the formula consistently returns $6,465 regardless of what I do.

Additional note: This model is admittedly not perfect€”particularly with Rx calculations, but I didn't get so granular as to include logic for bumping up against the Drug Deductible because I doubt I'd hit it. However, it does return a reasonable approximation of what I can expect to spend given the variables I enter.

Any help is REALLY appreciated. :)

Thanks.

OOPS: I duplicated the value types while forgetting to include info on the lookup table. The VLOOKUP functions on a three-column table: Column 1 is the Visit type (text); column 2 is the average cost of a visit (dollar amount); column 3 is the number of visits/year.

VISIT TYPE AVG COST QTY/YEAR
Doc Visit $150 3
Rx $50 6
Specialist Visit $250 1
Lab Visit $250 3

Thanks.

Thanks for those who looked at this, but turns out it was a syntax problem. Solved!