Solver in Excel

mrb82
mrb82 Registered Posts: 147 Beyond epic contributor 🧙‍♂️
in Chat and off-topic discussion
Hi all,

Does anyone know how to use the Solver Add-In to find a combination of values in a column to meet a target value?

Trial and error doesn't seem to be working. I can get it to calculate the target value, but it's changing my cell value range to meet this.

Thanks

Comments

  • blobbyh
    blobbyh Registered Posts: 2,415 Beyond epic contributor 🧙‍♂️
    Never heard of Solver but this looks a good tutorial (assuming you've not already read it?):

    http://office.microsoft.com/en-gb/excel-help/introduction-to-optimization-with-the-excel-solver-tool-HA001124595.aspx

    I briefly used Excel Goal Seek a while ago... is it similar to that? (Just looked it up, it's a more advanced version of Goal Seek in that you can have multiple variable cells. Might give it a try sometime soon).
  • mrb82
    mrb82 Registered Posts: 147 Beyond epic contributor 🧙‍♂️
    Thanks Robert,

    That looks like a good tutorial, but it doesn't quite fit what I'm after. There are a lot of good guides on t'internet, but you need to understand what they're talking about to get it to work!

    I think I cracked it this morning, but the only problem I can see is that it doesn't show every scenario within the given range.

    Will keep practicing, I'm sure it can do it!
  • ExcelAnt
    ExcelAnt Registered Posts: 80 Epic contributor 🐘
    My only advice on this one is try not to have one cell do everything.

    If you need multiple scenarios create multiple sheets to cope with changing values

    Eg investment of 10k

    Invest in scenario A B or C with 3 different interest rates over three diffferent time period (3 5 7 years) quickly gives you 27 scenarios. which is the best and more likely ? It's cumbersome but calculate them all! This can be more time efficient than trying to make a clever sheet to do it all at once :)

Categories

Privacy Policy