Generic Function based on the above row

edited 04/03/24 in Formulas and Functions

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


  • Mark Cronk
    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?

    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • 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
    edited 04/07/24

    @ 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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!