4 ms·

The 'Solver' (http://www.solver.com/ http://www.solver.com/) tool in Excel is widely used for many business optimization problems. If you have x & y quantities

by chime 17y ago

The 'Solver' (http://www.solver.com/ http://www.solver.com/) tool in Excel is widely used for many business optimization problems. If you have x & y quantities of two raw materials and products A and B consume different proportions of each raw material, then you can use the solver to determine the most optimal production strategy that maximizes your profit (or minimizes your labor requirements etc.)

eru 17y ago
The joys of linear optimization. Do you know if it also supports integer optimization? (Doing that fast enough would be a feat in pure Excell.)
- ShabbyDoo 17y ago
  I believe it does. And, IIRC, it's a native code library that uses Excel for data input and results presentation.
- 3pt14159 17y ago
  There are multiple versions of Solver. To get the free one to do this requires a hack (using some sort of Fourier Transform into a cell or something, I think there are multiple ways). There are paid versions of Solver that are far more powerful, with fully developed APIs. Also, Iron Python can be used with Excel, which makes the whole thing really intense because many Python Libs can be used. Excel truly is Microsoft's shiniest, best piece of software.
  - rbanffy 17y ago
    > Excel truly is Microsoft's shiniest, best piece of software. I would grant that honour to SQL Server. In fact, I regard SQL Server as Microsoft's third best product, lagging behind both the Natural Keyboard series (a must) and their mice ("honest value", reliable and comfortable). Never thought that, but Excel could be the fourth product in this list.
  - jodrellblank 17y ago
    Excel is scriptable by COM and therefore VBSript, Python+PyWin32, and any other COM scripting language. Presumably you mean IronPython integration in a different sense due to it being a .Net library? Got any links with examples of how that looks / what it can do?
    - 3pt14159 17y ago
      You are totally right, it had been a while since I had looked into this. I was confusing (blending together?) pyinex and IronPython. You are right, IronPython (and tons of other languages) all have access to the .Net libs, but pyinex is what I was thinking of. Apparently, though, it is not fully developed yet. http://code.google.com/p/pyinex/ http://code.google.com/p/pyinex/
      - jodrellblank 17y ago
        I was thinking you were going to refer to .Net linking with Office, which seems to be something different from the COM automation, and more complicated than I've ever been interested in. That, however, looks pretty darn cool. On the odd occasion I've used Python/COM/Excel together, I've really wished for a Python shell inside Excel to do things like: for cell in Range('a1:a10'): cell.Value = cell.Value.split('-')[0] or whatever. I don't know if Pyinex will quite do that, but thanks for the link!
        gilest 17y ago
        You can do almost exactly that in Resolver One (http://www.resolversystems.com/ http://www.resolversystems.com/), which has got a Python console as well as allowing Python functions and Python syntax in the grid. It's a separate spreadsheet program, though, not an Excel plug-in. Alternatively, there's PyXLL (http://www.pyxll.com/ http://www.pyxll.com/), which lets you write Excel user-defined functions in Python, but doesn't have a console. (Full disclosure: I work for Resolver Systems.)
- Rod 17y ago
  Give Solver Foundation a try: http://www.solverfoundation.com http://www.solverfoundation.com
simonsquiff 17y ago
I've seen Solver used by a number of accountants to say 'I need my profit to be this number, what figures do I need to change to hit that?'. Solver is a life saver for those looking to cook the books (sadly far too many people).

by chime 17y ago

eru 17y ago

The joys of linear optimization. Do you know if it also supports integer optimization? (Doing that fast enough would be a feat in pure Excell.)

ShabbyDoo 17y ago
I believe it does. And, IIRC, it's a native code library that uses Excel for data input and results presentation.
3pt14159 17y ago
There are multiple versions of Solver. To get the free one to do this requires a hack (using some sort of Fourier Transform into a cell or something, I think there are multiple ways). There are paid versions of Solver that are far more powerful, with fully developed APIs. Also, Iron Python can be used with Excel, which makes the whole thing really intense because many Python Libs can be used. Excel truly is Microsoft's shiniest, best piece of software.
- rbanffy 17y ago
  > Excel truly is Microsoft's shiniest, best piece of software. I would grant that honour to SQL Server. In fact, I regard SQL Server as Microsoft's third best product, lagging behind both the Natural Keyboard series (a must) and their mice ("honest value", reliable and comfortable). Never thought that, but Excel could be the fourth product in this list.
- jodrellblank 17y ago
  Excel is scriptable by COM and therefore VBSript, Python+PyWin32, and any other COM scripting language. Presumably you mean IronPython integration in a different sense due to it being a .Net library? Got any links with examples of how that looks / what it can do?
  - 3pt14159 17y ago
    You are totally right, it had been a while since I had looked into this. I was confusing (blending together?) pyinex and IronPython. You are right, IronPython (and tons of other languages) all have access to the .Net libs, but pyinex is what I was thinking of. Apparently, though, it is not fully developed yet. http://code.google.com/p/pyinex/ http://code.google.com/p/pyinex/
    - jodrellblank 17y ago
      I was thinking you were going to refer to .Net linking with Office, which seems to be something different from the COM automation, and more complicated than I've ever been interested in. That, however, looks pretty darn cool. On the odd occasion I've used Python/COM/Excel together, I've really wished for a Python shell inside Excel to do things like: for cell in Range('a1:a10'): cell.Value = cell.Value.split('-')[0] or whatever. I don't know if Pyinex will quite do that, but thanks for the link!
      - gilest 17y ago
        You can do almost exactly that in Resolver One (http://www.resolversystems.com/ http://www.resolversystems.com/), which has got a Python console as well as allowing Python functions and Python syntax in the grid. It's a separate spreadsheet program, though, not an Excel plug-in. Alternatively, there's PyXLL (http://www.pyxll.com/ http://www.pyxll.com/), which lets you write Excel user-defined functions in Python, but doesn't have a console. (Full disclosure: I work for Resolver Systems.)
Rod 17y ago
Give Solver Foundation a try: http://www.solverfoundation.com http://www.solverfoundation.com

simonsquiff 17y ago

I've seen Solver used by a number of accountants to say 'I need my profit to be this number, what figures do I need to change to hit that?'. Solver is a life saver for those looking to cook the books (sadly far too many people).