Skip to main content
P
ptc-66300901-Visitor
1-Visitor
September 14, 2014
Solved

Creating a FOR loop including recalculate and export?

  • September 14, 2014
  • 1 reply
  • 6438 views

Hi folks!

I'm having a following problem with finding an effective way to export calculation results from Mathcad:

There's two things I would like to do. Firstly, I'd want to create mechanism that calculates the worksheet several times in a row and exports results from file output box to a new file (or new row) in between the calculation rounds to save the results from each round. My calculations include random variables so the results vary between rounds even without changing the start values. Manually this is easy to do by ctrl+F9 (calculate worksheet), and then opening the excel file attached to file output box and copying the results into a new row of another excel table (or save as to save into individual file). Also this would be possible to do with a for loop in Mcad for simple cases. The problem is that my calculations are too complicated to be put in a default for loop, so I guess I have to create my own with VB.

When the for loop works for the whole calculation, the second thing would be to change some variables between rounds to see the effect of variation in results. I'm just a rookie in programming but the idea is so basic I'm optimistic for finding the solution here. Basicly what I need is following:

For i = 1...10:

Variable1 := i

Calculate Worksheet

Export to results.xls row i (or results_i.xls)

I hope you got the point and can help me with this since I don't seem to solve this by myself. One calculation round takes about 5 mins so I would rather run multiple rounds in a row than manually save the results every 5 mins for about 100 rounds.

Best answer by LucMeekes

Maybe the attached helps.

Store all three files in the same directory, and open CalcLoop.mcd

Calculate the worksheet and enjoy.

Success!

Luc

1 reply

W
Werner_E25-Diamond I
25-Diamond I
September 14, 2014

Its not really clear to me what you are going for without a concrete example in form of an attached worksheet, but it looks to me that it would be a good idea to create functions which do your calculations as otherwise you can't "loop a worksheet". Then probably its better not to export your intermediate data to excel after every loop but collect the data in a Mathcad matrix and export all the collected data after the loop has finished.

P
ptc-66300901-VisitorAuthor
1-Visitor
September 15, 2014

Thanks for your answer Werner! What do you mean by creating functions for the calculations? To program calculations inside VB code? I have about 40 pages complicated calcultions including Monte Carlo simulations with LHS so this is too much of a work if even possible.

The problem with storing the results in matrix is that how can I prevent the replacing of results from previous round? I should be able to restore the running number i somewhere so that Mcad could recognize the round number and store results into correct row of the result matrix. This would be no problem with the normal for loop in Mcad. It's just that I cannot use it with all these calculations. I need to create one that can recalculate whole worksheet i times and remember the running number i between rounds and use it to store results without replacing the previous.

I'll consider posting a worksheet for example. It's just that all I need is a for loop that can handle all the calculations in my worksheet.

W
Werner_E25-Diamond I
25-Diamond I
September 15, 2014

ptc-6630090 wrote:

Thanks for your answer Werner! What do you mean by creating functions for the calculations? To program calculations inside VB code? I have about 40 pages complicated calcultions including Monte Carlo simulations with LHS so this is too much of a work if even possible.

I hadn't VB code in mind.But if you want to iterate a caclulation or simply do it multiple times in one sheet, you have to design it beforehand by using Mathcad functions.

Example:

a:=4

b:=9

A:=a*b

x:=A^0.5

x=6

If you want to do this calculation multiple time with different values of a and b and you do not like the idea of simply manually replacing the values for a and b at the top of the sheet, you would have to turn the calculation of x into a function:

A(a,b):=a*b

x(a,b):=A(a,b)^0.5

x(5,6)=6

x(4,16)=8

etc.

But redesigning a 40 page calculation sheet can be quite cumbersome, to say the least.

On the other side Mathcad does not offer a way to turn a worksheet into a function and loop through it multiple times. The sheet has to be designed differently as shown if you intend to call the calculation multiple times.

I have no experience with it as I don't use Excel for technical calcs but you may use the Mathcad plugin for Excel and call the Mathcad Sheet from within Excel. Not sure but I guess that the looping you are after could be done that way - Letting Excel control the loop and storage of results.

The problem with storing the results in matrix is that how can I prevent the replacing of results from previous round?

No problem at all in Mathcad. You can save the results in different rows or different columns, whatever seems appropriate. You may also create a vector whose elements are the matrices you get for each run. It depends on how you would like to use the results later and what kind of matrix the result of a single run is (scalar, vector, matrix).

    Terms & ConditionsAccessibility statement