Solver Problem with IF function

[jdthomas_gmac]

Hello All,
I am trying to determine the most cost effective cell phone rate plan
based on the amount of minutes a user has in the past month. I have
set up a table of rates (i.e. 49.99 per month, 1000 plan minutes, .35
per minute for overage usage, 59.99, 69.99, etc) and I am attempting to

set up solver to minimize the total cost of the bill by changing the
user's plan to the most cost effective plan rate. Currently cost is
calculated by adding the plan cost (49.99 per month) and adding the
overage usage (minutes over plan X .35).
The problem I have is building the calculation for the overage minutes.

I cannot figure out a way to adequately calculate the overage minutes
without using an IF function. For Example, a user with the 1000 plan
minutes as listed above used 1200 minutes last month. His plan minutes

would be 1000 minutes, and the overages would be 200(1200 used minutes
- 1000 plan minutes). However, the next month, the user uses 800
minutes, and now I have to use the IF function to specify that if the
user has less usage minutes than plan minutes, then the overage minutes

is 0; otherwise I get a negative number for overage minutes.
I know I will not always obtain an optimal solution using an IF
function, but I cannot figure another way around using the function.
According to Excel, when using optimization models, the functions to
avoid are:
- ABS
- MIN
- MAX
- INT
- ROUND
- IF
- COUNT
Thanks for any help!!

 

[Bernie Deitrick]

jd,

I don't think you need solver, since this isn't an iterative solution. Simply figure out the actual
charge based on each plan, using a formula like

=A2+IF($E$2-B2<0,0,$E$2-B2)*C2

where A2 has the base cost (49.99), B2 has the number of included minutes, C2 has the per minute for
overage, and E2 has the actual number of minutes used. Copy down to match your cost table, then use
MIN on the column of formulas. To extract the plan number, you could use a combination of INDEX and
MATCH, finding your MIN cost within your table.

HTH,
Bernie
MS Excel MVP

 

[Bernard Liengme]

In addition to Bernie's comment: you should never use a Solver model with
discontinues functions such as IF, V/HLOOKUP, CHOOSE, etc