minimize rounded amounts

[stevenpaul]

Hello folks,

I am trying to minimize the amount I will be rounding off of numbers in an array. I have numbers listed in column K, the same numbers rounded off in column L, and the difference between column K and L, stated absolutely, in column M (see attachment). The numbers in column K were derived from calculations elsewhere in the sheet. By changing the number in F3, the numbers in K through M will vary. My goal is to minimize the amount I have rounded off from the values in the K column. That is, I would like to create a low value in the M cells. Specifically, I would like each cell in M to be less than 0.1.

Is there a way to solve for F3, such that each cell in column M is less than 0.1? F3 is the only cell I am free to change. Lastly, F3 must be 3 or greater.

Any help would be most appreciated. I look forward to learning more from you all.

Steven

[outofthehat]

Try installing the Solver Add-In (2007: Excel Options > Add-ins ; 2003: Tools > Add-ins). This installs an optimisation module.
Once installed, create a target cell to optimise - this is a key step - what are you trying to optimise? You will get different results if you're trying to minimise the highest value than if you minimise the sum. You could even write a formula that calculates some measure of variance above 0.1.
I used M6 with the formula =Max(M2:M5) or =sum(M2:M5)
(Neither get all values below 0.1)

2003: Tools > Solver
2007: Data tab > Analysis group > Solver

Set target as M6
Equal to: Min (radio button selection) - this means you are trying to minimise the result in M6
By changing: $F$3
Subject to constraint $F$3 >= 3

Run the solver, and you get as close as Excel can to your answer. You can then save each as a scenario direct from the Solver, enabling you to compare the different outcomes with the Summary feature in Scenario Manager.

[stevenpaul]

Try installing the Solver Add-In (2007: Excel Options > Add-ins ; 2003: Tools > Add-ins). This installs an optimisation module.
Once installed, create a target cell to optimise - this is a key step - what are you trying to optimise? You will get different results if you're trying to minimise the highest value than if you minimise the sum. You could even write a formula that calculates some measure of variance above 0.1.
I used M6 with the formula =Max(M2:M5) or =sum(M2:M5)
(Neither get all values below 0.1)

2003: Tools > Solver
2007: Data tab > Analysis group > Solver

Set target as M6
Equal to: Min (radio button selection) - this means you are trying to minimise the result in M6
By changing: $F$3
Subject to constraint $F$3 >= 3

Run the solver, and you get as close as Excel can to your answer. You can then save each as a scenario direct from the Solver, enabling you to compare the different outcomes with the Summary feature in Scenario Manager.

Great Solution, outofheat!

I had never thought of using the Solver add-in, but it's just what was needed to accomplish this task. Good thinking!

Steven