(1) Explain your problem, don't simply post "This isn't working". What were you doing when you faced the problem? What have you tried to resolve - did you look for a solution using "Search" ? Has it happened just once or several times?
(2) It's also good to get feedback when a solution is found, return to the original post to explain how it was resolved so that more people can also use the results.
Vladimir, Rafael,
In one of my questions about MSP, Vladimir answered, that it is very easy to implement Excel-like features in Spider.
But so far I could not find how to do it.
How would I achieve the following: say I have several Project-level parameters, and I want durations of some (but not all) of the activities to be dependent on these parameters, based on certain formulas?
So, if I would do it in Excel, I would do something like this:
Regards.
Evgeny
Printer-friendly version
Send to friend
Vladimir,
Why didn't you told me before, that will do it perfectly, very handy formula. The first thing I will explore is what happens when changing WBS and changing sorting, reapplying the formula will be no issue.
Will store the formula on a reference book and a print screen of your posting, of course for future reference.
Needless to say I will use other variable code instead of A starting with prefix "RD-" as to prevent conflict with other users coding.
I see you are in the hundred thousands and row labels show in full, maybe my sample job was in the million activities, I am not sure but wanted to challenge Spider, it is possible I did go that far in my testing. It is not difficult to go that far duplicating a job many times, just insane. I stopped challenging the number of activities, no way I will ever go over that limit.
Still for printing, the formula is very useful, not to mention possible variations.
That I have my reserves about automatic formulas it does not mean I do not want a few more spreadsheet like functionality. For me it would help being able to create formulas by selecting column titles or specific cells as well.
Another thing I like about Spider Formulas is that you can apply multiple formulas to the same field no matter if a regular field or a user field. With user field you can create as many columns as you want and make it with a single formula. But what about multiple formulas on regular fields, what about automating multiple formulas on the same field? Seems like a 3d spreadsheet functionality.
Regards,
Rafael
Rafael,
If I understood you correctly you want to create a column with row numbers?
Create user field (numerical). Let's suppose that its code is A.
Formula looks like A=A[-1]+1
The result is below:
First row has zero number. Now apply A=A+1.
If the problem is different let me know. I did not meet problems with large schedules in Spider.
Best Regards,
Vladimir
RE "I meant formulas created by users that are set to be calculated automatically"
I have my reserves with circular references and recursive formulas on automatic mode.
RE "Spider is not RAM hungry and formulas add a little to RAM requirements. Recently I calculated portfolio schedule consisted of more than 700,000 activities on my notebook without any problems at all. I don't think that anybody will meet memory problems working with Spider."
I heard this before long ago and wanted to try it myself to see how it was, I tried with a test file of about 280,000 activities, resource loaded and with all kind of logic relationships. I did it by adding the same job to itself several times and then duplicating it several times to run them all on a single portfolio. It ran flawlessly the resource leveling, but could not figure out how to set the column at the left (column for row headings) to display the whole sequential number when too large. I know it was there but could not see it. I also could not figure it out on the print options how to print this column similar to Excel option to print column and row headings. Yes it runs without RAM issues, even when at the time I tried it my laptop had half the 8GB RAM it actually have.
Best Regards,
Rafael
Rafael,
I meant formulas created by users that are set to be calculated automatically.
Spider is not RAM hungry and formulas add a little to RAM requirements.
Recently I calculated portfolio schedule consisted of more than 700,000 activities on my notebook wothout any problems at all.
I don't think that anybody will meet memory problems working with Spider.
Best Regards,
Vladimir
Rafael,
RE “I am not sure if MSP formulas can be applied to all formulas or only to custom fields, if only to custom fields it is a limitation and there would be a reason for it, I do not believe it would be by accident.”
In MSP it is easy to create formula to set a value of a customer field and it will be executed automatically. Also it cannot address values outside of it’s own task.
If you want to modify other fields (e.g. task duration etc), you would have to create a Visual Basic code.
Also, with VBA I think it shall be possible to address individual tasks, but it would not be that easy as in Spider
Regards.
Vladimir,
"what problems do you see with the ability to use formulas for the regular fields? It is convenient and easy, some fields certainly depend on others like indirect costs that are frequently calculated as percent of direct costs."
*** I do not see any problems with formulas being applied to regular fields other than worksheets must be fully loaded on RAM while most databases do not, it might be an issue of available RAM and how RAM-hungry is your software. Spreadsheets and databases share some characteristics, but they involve different technologies. I am not sure if MSP formulas can be applied to all formulas or only to custom fields, if only to custom fields it is a limitation and there would be a reason for it, I do not believe it would be by accident.
"Formulas that shall be executed automatically link with cost calculation."
When you say formulas that shall be executed automatically do you refer to formulas created by the user, or predefined formulas embedded on the programming, or both?
Evgeny.
Thanks, I got it. But what if you have several formulas, then in what order? In any case flags for automatic scripts execution you would define at the script editor instead that at the formula editor. But what if you have several such flags, then in what order?
Simply create a script that will define the order of execution for all actions, can be execute several formulas in sequence, execute a command and then execute other formulas. Scripts provide for the required control without ruling out recursive formulas.
Best regards
Rafael
Vladimir,
Thanks for explanation. I didn’t look yet at scripts
Rafael, re “I do not understand what you mean by attaching formulas to Cost Calculation, please clarify with an example.”
What I mean is that if you set a flag to a formula “Execute in Activity Gantt after cost calculation”, then you can trigger execution of all of these formulas in entire project by pressing the “Cost and material calculation button”.
Regards.
Evgeny
Evgeny,
as Rafael explained a sequence of formula execution is essential and besides with recursive formulas it is easy to create a mess.
If you regularly need to execute several formulas together create a script. It is very easy to make Spider just to remember your actions. Next time instead of applying formulas apply selected script.
Formulas that shall be executed automatically link with cost calculation.
Rafael,
what problems do you see with the ability to use formulas for the regular fields? It is convenient and easy, some fields certainly depend on others like indirect costs that are frequently calculated as percent of direct costs.
Regards,
Vladimir
Thanks for your explanation. I think if we really talk in terms of Excel, then we need to differentiate between function, which is executed always, when input parameter changes and a Macro, which has to be run manually.
***In Spider Functions are executed via formulas, functions are not executed until the formula runs them.
So far what I can see is that Spider formulas are more like an Excel Macro, but there is a flexibility to make them pretty much like Excel functions, by attaching them to Cost Calculation event and running all of them, whenever you change a parameter.
***In Spider formulas mean formulas, that they are executed under user control is another thing, in any case an Excel Macro is more like a Spider Script.
***I do not understand what you mean by attaching formulas to Cost Calculation, please clarify with an example.
So far I found no other way to run several formula at the same time, except triggering them by Cost Calculation event. There is no multiselect available in Edit=>Formulae.
***You run formulas in sequence either manually or via a script. Even on a spreadsheet circular references require iteration by the computer, formulas are executed in sequence. You edit formulas one by one though you can paste the same copied text in multiple formulas.
***Excel calculates formulas in some sequence (not at the same time) based on dependencies and dependency trees and chains.. http://www.decisionmodels.com/calcsecretsc.htm
***Adding spreadsheet functionality to Spider database will be at some cost, perhaps too much for the benefit, it is better to keep the tables as regular database tables. It is an error to believe database and spreadsheet software are the same, otherwise why Microsoft Access and Microsoft Excel if they are the same thing.
I do not have nor use MSP but believe MSP formulas can only be applied to custom fields that are user defined while in Spider formulas can be applied to custom fields as well as to all other software pre-defined fields. Maybe it is just a matter of ease of use versus power and flexibility. Maybe having both is feasible but at too much cost.
Is my understanding correct, that there is no way to run several functions at the same time, one has to run them one by one?
***In spider you run several functions in sequence never at the same time, even if using a script. It is important to keep control of formula execution as a recurrent formula can create chaos. I do not think Spider will ever allow a user script to be tied to an action. Too many possibilities to create confusion, I do not think it is good practice.
Rafael,
Thanks for your explanation. I think if we really talk in terms of Excel, then we need to differentiate between function, which is executed always, when input parameter changes and a Macro, which has to be run manually.
So far what I can see is that Spider formulas are more like an Excel Macro, but there is a flixibility to make them pretty much like Excel functions, by attaching them to Cost Calculation event and running all of them, whenever you change a parameter.
Question:
So far I found no other way to run several formula at the same time, except triggering them by Cost Calculation event. There is no multiselect available in Edit=>Formulars.
Is my understanding correct, that there is no way to run several functions at the same time, one has to run them one by one?
Regards.
Evgeny
Evgeny,
Formulas are calculated manually on purpose.
This allows you to define recursive formulas and other circular references that if executed automatically would induce an endless loop. Say duration = duration x 1.2 a formula to be applied once to increase duration by 20%, it can also be applied a second time to further increase the already increased duration by another 20%.
Uncontrollable formula execution is undesirable. Say you create a formula to modify your activities remaining durations such as remaining duration = A; can also be a conditional formula, look for them they are very useful. Next time you update your project remaining durations shall be reduced [or even increased if need be, but not by automatic formula that will keep it unchanged.
At times a formula will change some values and these changed values will create changes in other values, therefore order of execution must be controlled by the user. In case of several formulas must be executed at different stages in a predefined sequence but other formulas shall not be executed then I suggest creating a Script that will execute the sequence at a single click of the mouse.
Formulas are powerful, use them wisely.
In case of unwanted formulas are applied by error there are several options such as undo last action, using the Protocol of Actions, closing the file without saving or restoring from the automatic backup among others.
I always suggest keeping a backup set independent of the automatic backup, especially if automatic backup is set to occur frequently and was already changed with errors in the changes by the user [it happens, remember Murphy], this is one of my other options. You can also save to a reference book a formula you want to avoid using by error, then delete the formula, if you need it back transfer it back from the reference book.
At times I write protect some files using the windows explorer as an extra precaution, to protect files from myself.
Best regards,
Rafael
Vladimir,
Thanks. I can see, that, unlike MSP, Spider has flexibility to reference values of individual specific activities. I was a bit concerned, that one has to run every formula manually, but the possibility to trigger formula execution by pressing Cost and Material Calculation button seems resolve this issue.
Regards.
Formulas look like
DurPlanD[Code,3]=A[Code,Project]+B[Code,Project]
DurPlanD[Code,4]=B[Code,Project]-A[Code,Project]
Where
DurPlanD is the code of remaining duration in days, 3 and 4 are activity codes in Spider, Project is project code, A and B are field codes of Parameters 1 and 2 if both are on the project level.
Email: PPadmin@planningplanet.com
Replies