It did for me - with the function to optimize being "3 * A3", and limits set to 3 and 8, the solution was 7.99999.
When cell A3 is either val_1 or val_2, you will multiply your expression by zero and when A3 is between llim and ulim, the expression inside the FLOOR.PRECISE() function will evaluate to something smaller than 1 - so the FLOOR will be zero.Įnter that expression in a cell, and make your constraint that this cell must be zero. that are all valid, and a range llim and ulim (lower and upper limit), then the following equation will evaluate to zero if your conditions are met: =(A3 - val_1) * (A3 - val_2) * FLOOR.PRECISE(ABS((A3 - (llim + ulim) / 2) / ((ulim - llim) / 2))) For example, if you have (in named ranges) a number of individual values val_1, val_2 etc. If you like this example, you may also like Excel Scenario Manager.This is a little bit of a hack, but you could create a function that is zero when your constraints are met, and nonzero when they are not.
#EXCEL SOLVER DOWNLOAD#
You can download this Excel Solver example from the link below and try it with different parameters yourself. Even though it is a bit expensive to drive because of fuel, it will still cost us less by the time we change cars again. Since we have a tight budget, it is more viable to buy first car. Only then the second car will become the more economical alternative.
Our table’s final form:Īccording to our calculation, two cars expenses will be equal after 13 years of usage. You can check your table and if you are satisfied with the result, you can hit OK to keep it or Cancel to try again with different settings. After doing its calculations, solver changes target cell accordingly and present us its solution. Since this is a basic linear problem, we choose Simplex LP as solving method. We are telling solver to change cell C10 (Duration) incrementally until cell C14 (Difference) equals 0 (zero). We are going to use Solver Add-in to solve this model. You can see the formulas used in below picture. When difference equals 0, we can compare required duration to our planned usage time and decide which car to buy.
In this table, we are trying to make difference equal to 0 (zero) by incrementing duration. You can see the constructed table that calculates total expense of the cars at the end of first year below. Assuming we will use this car for 5-6 years, we want to know if second car will be more economic before that time.įirst thing we need to do is to construct a model for this problem. But we also like the expensive alternative better but it means that we need to exceed our budget.ĭue to lower operating expenses, second car is bound to become more economic in future. We have a limited budget, so buying the cheaper alternative seems viable in short term.Second car has a more expensive sales price and annual text but less fuel consumption with a less expensive fuel.First car has a cheaper sales price and annual tax but bigger fuel consumption with more expensive fuel.Both cars has different attributes regarding price, tax, fuel consumption, etc. For this example, we are going to decide between two cars.
0 kommentar(er)
