Generic Function based on the above row

Mahmoudona

Hi Guys,

I am introducing a new column titled 'Delta' where each cell's value relies on the value of the cell directly above it, as illustrated below:

in row#15, I have this code: =IF((ISBLANK([ColumnA]@row)), Delta14, [ColumnA]@row - [ColumnB]@row + Delta14)

in row#16 I have this code: =IF((ISBLANK([ColumnA]@row)), Delta15, [ColumnA]@row - [ColumnB]@row + Delta15)

in row#17, I have this code: =IF((ISBLANK([ColumnA]@row)), Delta16, [ColumnA]@row - [ColumnB]@row + Delta16)

and so on... in general, I want a generic function in row#X where the code is: =IF((ISBLANK([ColumnA]@row)), DeltaX-1 , [ColumnA]@row - [ColumnB]@row + DeltaX-1)

Your assistance with this matter would be greatly appreciated :)

Mahmoudona

@Paul Newcome

@Mark Cronk

@L_123

@Leibel S

Mark Cronk

I don't think it can be done with current formulas. Is there a pattern in your data or other logic that could be used to determine what the value in the cell above should be?

Mahmoudona

@Mark Cronk Nope, currently I am using the formula inserted above, but the problem appears when I insert a new row in the middle or delete a row.

Is there any workaround to overcome this? I did review almost all previous discussions without any clue.

BraylenStanton

To create a generic function on row #X that depends on the value of the cell above it and the previous value of the Delta column, you can use the following approach. You need to simply use the DeltaX-1 expression as the value if the current cell in ColumnA is empty, otherwise calculate the difference between the current row's values in ColumnA and ColumnB and add the previous value of the Delta column. This approach will ensure that the function works correctly for any row X, using the values of the previous rows and the current row.

Mahmoudona

@ BraylenStanton I'm currently doing just that, but I'm struggling to generalize it. Instead of using terms like DeltaX-1, I find myself consistently resorting to specific numbers like Delta14, Delta15, Delta16, and so forth.