Formula based on previous row

1

Hi,
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.

For example:
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.

Thanks

2

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)
image

This technique is used frequently. See also this topic: Dynamic/virtual subtraction of values

3

HI Dmitry

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)

4

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:

  1. The source data is read from Book1.xlsx
  2. The first row is used as the initial state of x, y, z for iterations.
  3. For each row starting from 2nd, a subproject row-calculation.morph is executed and the results are appended into one table
  4. 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)

5

I am not sure if this helps your issue, but I just had a problem where I had to use values form the previous (or next) row in calculations. The basic steps I took were: From the original dataset derive two tables. In table 1 enumerate the rows, in table 2 trim out the first row and enumerate. Essentially, you are making row 2 of table 1 row 1 of table 2. Merge the two tables on RowNo and pick your columns to match and what info you want to merge. To reverse it (instead of trimming out the first row) just trim out the last row, enumerate, and modify the RowNo column to RowNo + 1 and do the same thing.

6

@Radical-R, did you consider using the "Shift columns" action? It might be an easier solution.