Maximizing an objective that is a ratio

Our take

Maximizing an objective that is a ratio, such as Value/Ore, requires a structured approach to optimization. While using Excel's Solver with the simplex method can be effective, it may not directly handle ratio objectives or dropdown selections. Instead, consider creating a binary decision matrix to represent material selections, allowing for more flexible toggling of inputs. This method can streamline your optimization process, especially as you scale to multiple factories.

https://preview.redd.it/1igfk739kexg1.png?width=558&format=png&auto=webp&s=df993656a5d85541fdde6593615c078a8bdb6122

I would like to be able to maximize the total Value/Ore, rn I just have it as being able to select the material manually and then it calculates what the Value/Ore is.

How would I go about this optimization problem? I was thinking about using simplex on the excel solver, but idk if this would work for an objective that is a ratio (Value/Ore). iirc the solver can't select from a dropdown either so I'd need to create a binary matrix.

I could brute force it too, but this is not the only set of factories I will be optimizing, there will be other tables with possibly 10+ factories, so I imagine it will get to a point where brute forcing is unreasonable.

Anyone got an idea on what the cleanest and most effective way to go about solving this would be? Note: I would like the materials that are used in the calc to be toggleable too.

submitted by /u/Draedon_Is_Life
[link] [comments]

Read on the original site

Open the publisher's page for the full experience

View original article →

Tagged with

#Excel alternatives for data analysis#Excel compatibility#Excel alternatives#rows.com#natural language processing for spreadsheets#generative AI for data analysis#financial modeling with spreadsheets#Value/Ore#optimization problem#simplex#Excel solver#objective#binary matrix#brute force#factories#toggleable materials#calculation#total value#dropdown selection#optimization tables