Solver question

RedBirdin96

New Member
Joined
May 12, 2006
Messages
19
I want to use solver to find when the two solutions for when the following if statements are equal:

IF($K2>=B$4,IF(B$4+($K2-B$4)*B$5>=B$6,B$6+12*$B$3,12*B$3+B$4+($K2-B$4)*B$5),12*B$3+$K2)

=IF($K2>=C$4,IF(C$4+($K2-C$4)*C$5>=C$6,C$6+12*$C$3,12*C$3+C$4+($K2-C$4)*C$5),12*C$3+$K2)

by varying the contents of K2

here is the rest of my inputs (sorry I did not figure out how to post it as an image/chart):

Plan A Plan B
Premium: 400 150
Deductible: 0 3500
Co-pay: 10% 20%
Out of Pocket Max: 5000 5000
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Solver doesn't do well with functions with discontinuities in value or slope.

Why not just list values of K2, and calculate the value for each expression, and see where they cross?
 
Upvote 0
I have that done, and I plotted it to see it graphically where they cross, but I want an exact value when they cross and depending how I vary my inputs the different formulas from the IF statements determine where they cross, so i was trying to find the exact point where the two IF statements are equal to 0 (in some instances they may never cross, so then I need to output text, but first I need to solve for the exact intersection points)
 
Upvote 0
At a glance, it looks like your equations generate a piecewise linear result. 'Tis true, and if you choose values for the domain that mark each breakpoint, then there's a workbook at https://app.box.com/s/hkivovq1895ngjeo6r1g that will return the point of intersection for two polylines.
 
Upvote 0
At a glance, it looks like your equations generate a piecewise linear result. 'Tis true, and if you choose values for the domain that mark each breakpoint, then there's a workbook at https://app.box.com/s/hkivovq1895ngjeo6r1g that will return the point of intersection for two polylines.

Got the workbook, but how do I make my Excel know what the PolylineIsect function is? Right now I get a "INVALID NAME" error.
 
Upvote 0
You can put your data in my workbook, or copy the user-defined function from my workbook to yours.

To do the latter, with both workbooks open, do

o Alt+F11 to open the VBE

o Ctrl+R to see the Project Explorer window if it's not already open

o Click the VBA project for my workbook, and beneath that, click Modules

o Drag Module1 to the VBA project for your workbook.
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top