Home |
Search |
Today's Posts |
#1
|
|||
|
|||
IF problem using Solver
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!! |
#2
|
|||
|
|||
for the overage amount try something like
=max(0,use-allowance) " wrote: 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!! |
#3
|
|||
|
|||
According to Excel, when using optimiztion models, the functions to
avoid a - ABS - MIN - MAX - INT - ROUND - IF - COUNT |
#4
|
|||
|
|||
According to Excel, when using optimiztion models, the functions to
avoid a - ABS - MIN - MAX - INT - ROUND - IF - COUNT |
#5
|
|||
|
|||
You are right, but as a question why use the solver for a simple calc?
" wrote: According to Excel, when using optimiztion models, the functions to avoid a - ABS - MIN - MAX - INT - ROUND - IF - COUNT |
#6
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Solver problem | Excel Discussion (Misc queries) | |||
Solver Problems | Excel Worksheet Functions | |||
How can the Travelling Salesman Problem be solved using Solver Ad. | Excel Discussion (Misc queries) | |||
Freeze Pane problem in shared workbooks | Excel Discussion (Misc queries) | |||
Row Autofit problem Excel 2003 | Excel Discussion (Misc queries) |