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
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,779
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
May 12, 2006
Messages
19
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
Joined
May 7, 2008
Messages
21,779
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
May 12, 2006
Messages
19
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
Joined
May 7, 2008
Messages
21,779
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,548
Messages
5,596,787
Members
414,102
Latest member
8885001133

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
Top