I am trying to calculate a formula where the current cell is a function of the cell in the previous row. Once that cell is calculated, the cell in the next row can then be calculated. This is in the form of an iterative formula.
ID Value Formula
1 50000 If first row = 50000
2 50002 = [VALUE:Row-1]+ ID
3 50005 = [VALUE:Row-1]+ ID
This can’t be achieved by a self join because the current cell is dependent on the next cell. It could probably be done using a loop but i was wondering if there was an action for this.
In EasyMorph use a cumulative calculation to iteratively sum up values of previous rows.
See example below for your case:
cumulative-sum.morph (2.1 KB)
This technique is used frequently. See also this topic: Dynamic/virtual subtraction of values
Thanks for your reply, i think my example was too simple! Or maybe the answer is simple but I’m just lacking the creativity.
For my project I am dealing with multiple examples of multi-row formulas. I think at the core of it I need to solve some variation of the below:
a^x + b*y + c+z
Where a,b, and c are in the current row
and x, y, and z are in the previous row
and none of these variables are in the same column (or you could possible solve using a self join)
Example data in file attached. Could you show me how to achieve this in EasyMorph?
Book1.xlsx (9.3 KB)
This can be achieved by iterating and passing the data from previous row through a temporary file which is read and updated on every iteration.
Iterations are described in our tutorial article here: https://easymorph.com/learn/iterations.html
See below your example implemented in EasyMorph. In this example:
- The source data is read from Book1.xlsx
- The first row is used as the initial state of x, y, z for iterations.
- For each row starting from 2nd, a subproject
row-calculation.morph is executed and the results are appended into one table
- The subproject reads previous row values from a .dset file, calculates new row values and updates the .dset file for use in the next iteration step.
recursive-calculation.morph (6.1 KB)
row-calculation.morph (4.8 KB)
Book1.xlsx (9.3 KB)