As I mentioned in another post, Solver in my system has considerably slowed down after having made modifications in my worksheet - as I cannot go back to the previous versions, I was wondering whether there is a way to locate particularly heavy processing - as an alternative, considering the size of the worksheet (15MB) I was considering moving to an external worksheet all parts unnecessary for the Solver computation, e.g. All graphics (or is there a way to stop updating certain parts of the worksheet until requested? Thanks for any hint Renato PS I'm running Excel for Mac 16.16.1 on MacOS 10.12.6. Hi Renato, To better understand the situation, could you share with us the following info? We want to identify if the issue is related to Excel version, installation or the file itself. Is the issue mainly/only related to this specific 15MB file?
Excel's Solver is a numerical optimization add-in (an additional file that extends the. To do this, select Tools from the menu bar and choose the 'Add-Ins.. If you cannot find the Solver Add-In, try using the Mac's Find File or Find in. Neither Solver nor the Dummy Dependent Variable Analysis add-in will be available. Initial installation of Excel. If ‘Solver’ does not appear on the ‘Tools’ menu in Excel, then you need to enable it as follows: ¾ Select the ‘Tools’ menu in Excel, and then choose ‘Add-ins’. Check the box titled ‘Solver Add-ins’ and then click ‘OK’. ‘Solver’ should then appear as an item on the Tools menu.
Or it occurs even in a simple test workbook? Does the Solver function work well with the former 16.15 version of Excel for Mac? What do you mean by 'cannot go back to the previous versions'?
Do you mean revert back to former version of Office suite or the workbook? What 'modifications' do you make to that workbook? Do the former version of Excel work well with these similar modifications?
Best regards, Tim. Hi Tim, thanks for the prompt answer 1) I assumed that it happens only with my big worksheet, but I'll try to run an old version of the sheet and will report what happens (however having automatic updating of Excel I do not know what Excel version was in use when the old sheet run ok) 2) I cannot go back in practice on my previous sheet version simply because it would mean throwing a lot of work out of the window. 3) I'm not able now to detail the modifications, but I was aware that I was doing CPU sensitive changes (e.g. Hi Renato, Please check if my short summary for the current situation is correct: In the former version of the workbook, Solver could finish calculating in one minute.
But in the problematic version of workbook, it needs several minutes to finish similar calculation. The difference for the former version and current version is that you have added some charts related to variable to the workbook. You could not just return to use the former version as many data or contents are not in it. To move forward, could you record a video for using solver in former version of the workbook? You could use mobile phone. Upload the video to a cloud storage and then share with us a guest/download link of the video in my. Meanwhile, please also attach the former version of workbook and the current problematic one in the private message.
We want to check them from our side. Disclaimer: Microsoft provides no assurances and/or warranties, implied or otherwise, and is not responsible for the information you receive from the third-party linked sites or any support related to technology. Thanks for your time.
HI TIM, 'In the former version of the workbook, Solver could finish calculating in one minute. RIGHT But in the problematic version of workbook, it needs several minutes to finish similar calculation. CORRECT The difference for the former version and current version is that you have added some charts related to variable to the workbook. ' NOT ONLY, ALSO A NUMBER OF NUMERICAL CHANGES - AS A MATTER OF FACT, I HAVE NOW SEPARATED IN TO AN EXTERNAL OPTIONAL WORKBOOK THOSE CHARTS, AND NOTHING HAS CHANGED W.R.T.
THE PROCESSING TIME - HENCE THE PROBLEM IS IN THE ALGORITMIC PARTS (MOST CALCULATIONS ARE DONE 30 TIMES EVERY CYCLE, SO A CLUMSY STATEMENT CAN HAVE BIG CONSEQUENCES.) You could not just return to use the former version as many data or contents are not in it. DATA I CAN EASILY RECOVER, CONTENTS I HAVE TO SEE: I HAVE DECIDED TO TAKE THE OLD 'FAST' VERSION AND TRY AND SEE WHAT HAS BEEN ADDED SINCE - REDOING LITTLE BY LITTLE THE CHANGES I SHOULD BE ABLE TO FIND THE CULPRIT. CONCERNING THE SOLVER ACTIVATION, I CAN ASSURE YOU IT IS DONE EXACTLY IN THE SAME WAY AND SET UP IN THE 2 CASES, FURTHERMORE I BELIEVE THE WORKSHEET IS VERY MUCH COMPLICATED AND I DO NOT THINK IT IS POSSIBLE IN A REASONABLE TIME TO IDENTIFY THE RELEVANT CHANGES - SO I'LL TRY FIRST TO RESURRECT THE OLD WORKSHEET AND I'LL UPDATE ASAP THANKS FOR NOW RENATO.
Hi Renato, OK. Just feel free to share with some updates when you have time. Meanwhile, given your description, the issue seems to be related to some specific 'changes' in this complex workbook. If you find which change will cause the problem and it will even cause problem for some simple workbooks.
You could click the smile button at right top of Excel window, directly submit the feedback to our related team. Moreover, as the issue is related to this specific complex workbook, you could post a new thread in our for further discussion. Check if there are some good suggestion to improve the calculation of Solver with your workbook. Thanks for your understanding. After on change it takes several minutes to complete the calculations That to me would be a clue that your model is not set up correctly.
You should be able to clearly define your model in about 200 rows, by 9-10 columns. One 1 sheet. ' after on change it takes several'. Do you mean an 'On Change' event to make your calculations? Solver takes over a lot of the behind the scene stuff of Excel.
It may not work. I'm running Excel for Mac 16.16.1 I have 16.17 Solver for Mac has had a bug for a long time on equality constraints.
Do you have any? A very quick-n-dirty test seems to show that this version works ok with Equality constraints. You say you 'had' a solution. With your model so complex, how are you sure the solution was correct. If a model runs a long time, most likely it is not the optimal solution. One should be able to post the model on one worksheet for others to look at. If it's too complicated to do that, then the model is probably too complex for Excel's solver.
If one needs data from another workbook, usually one copies the data needed into the Solver worksheet. Then, Solver references that data from it's own sheet.
Subscribe Now: Watch More: Making a chart on Excel with more than one variable will require you to use a scatter chart. Make a chart on Excel with more than one variable with help from a Microsoft Certified Applications Specialist in this free video clip. Expert: Jesica Garrou Filmmaker: Patrick Russell Series Description: Microsoft Excel is one of the best tools around for all of your spreadsheet creation needs. Get tips on Microsoft Excel with help from a Microsoft Certified Applications Specialist in this free video series.