IF problem using Solver

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

Thanks for any help!!

 

[bj]

for the overage amount try something like
=max(0,use-allowance)

 

[jdthomas_gmac]

According to Excel, when using optimiztion models, the functions to
avoid are:
- ABS
- MIN
- MAX
- INT
- ROUND
- IF
- COUNT

 

[jdthomas_gmac]

According to Excel, when using optimiztion models, the functions to
avoid are:
- ABS
- MIN
- MAX
- INT
- ROUND
- IF
- COUNT

 

[bj]

You are right, but as a question why use the solver for a simple calc?

 

[jdthomas_gmac]

I will be using solver to minimize costs for 30 users. I will then be
able to enter the mins for each of the 30 users and have a recommended
plan for each in one run as opposed to reviewing 30 individual bills