Multi row formula

[manjarigoyal1975]

I am looking for the formula where If any region has a value in a column "NewField" then above that row it should be "Value" and below that number, it should be zero. Attached is the spreadsheet and the column "Final result" is what I am looking to get using formula.

Thanks

Manjari

[JosephSerpis · Accepted solution]

Hi @manjarigoyal1975 I mocked up a workflow that produces your result I created a new field called "Newfield1" so you can compare your desired output results to the results my formula produces.

[estherb47 · Accepted solution]

Hi @manjarigoyal1975 

You'll definitely want the Multi-Row Formula tool to take care of this, since you're looking to rows before. Set it up so that you're grouping by Region, and set the Values that Don't Exist to "NULL"

The formula itself is a simple IF THEN ELSEIF. If NewField has a value other than zero, then copy it to Final Value. Else, if the previous row has a NewField with a value, set Final Value to zero. Else. if the previous Final Value is zero, then set Final Value to zero. Otherwise, copy the Value column.

Let me know if that helps!

Cheers,

Esther

[manjarigoyal1975]

Thankyou!! it worked.