Hey there!
I have 3 rows of data.
First one is time - let´s say I am interested in a 5 years time frame.
Second and third are variables - X and Y
=> =>=> Data looks like =>=>=>
Period X Y
1 0.9 1.1
2 0.5 1
3 2 1.3
4 4 0.3
5 2.2 2.1
Calculation for the year one is prety simple. Ijust need to summarize values X*Y for all periods (1-5) =>(0,9*1.1)+(0.5*1)+(2*1.3)+(4*0,3)+(2.2*21).
Here comes the tricky part.
In period 2 I want to skip value of X in period 1 and value of Y for period 5. Calculation should looks like => (0,5*1,1)+(2*1)+(4*1,3)+(2,2*0,3)
In period 3 I want to skip values of X for periods 1 and 2 and values of Y for periods 4 and 5. Calculation should looks like => (2*1.1)+(4*1)+(2*1,3)
Thanks and cheers!
Solved! Go to Solution.
Hi @sspi1153
Use Multi-Row formula Tool and you'll be fine.
[X] * [Row-1:Y] - Period 2
[X] * [Row-2:Y] - Period 3
Then Summarize Tool will do the rest.
Cheers,
Hi @sspi1153
Here's a workflow that lays out the concept that @Thableaus was getting at. It relies on the fact that rows that don't exist, i.e. any rows before 1 are treated as having a value of 0, so multiplication by these gives you a 0 result.
The window with the Blue title bar shows this concept. Period2 and Period3 have 0 values according to your requirements. The other window shows the result of the summation.
This method is not at all dynamic and is a maintenance headache if you need to add more periods.
Dan
Thanks, I got this one.
But what would be the solution if I would like to make it dynamic? Where should I start looking for the solution?
Thanks!
It all depends on what you mean by dynamic. If it's just a questions of the number of periods increasing then the workflow will work. If the formulas need to be dynamic as well, i.e. period n looks n-x rows back or some other definable rule, then you'll probably be looking at a macro solution with a dynamic multi row formula or perhaps one that joins the X and Y columns with a dynamic offset.
Dan
Hi @sspi1153
Attached is a workflow with an iterative macro. It will update to however many rows your data has.
Please let me know if this works.
Under the hood:
With each pass, the X value from the row below is moved up. When the tool has run through all of the rows, it's finished.
<<NOTE ON UPDATED POST: workflow no longer needs the filter (the macro still does). After I posted the picture, I realized that the > should be a >=. Whoops!!>>
Cheers!
Esther
Thanks!