Multi row formula

Question

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

test.xlsx

JosephSerpis · Accepted Answer

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 Answer

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