# Solver question

#### RedBirdin96

##### New Member
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
Deductible: 0 3500
Co-pay: 10% 20%
Out of Pocket Max: 5000 5000

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### shg

##### MrExcel MVP
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?

#### RedBirdin96

##### New Member
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)

#### shg

##### MrExcel MVP
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.

#### RedBirdin96

##### New Member
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.

#### shg

##### MrExcel MVP
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.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,311
Messages
5,836,583
Members
430,438
Latest member
David Gr

### 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.

### Which adblocker are you using?

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

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