You are viewing limited content. For full access, please sign in.

Question

Question

Calculation in forms table - reference same field from previous row and use in calculation for next row.

asked on April 6, 2022 Show version history

Team,

I have a requirement where the calculation needs to be done on a field with the value from the same field but from the previous row.

From the image below, total area is the field in play

(Total area Row(n) = Total area from row (n-1) + Area covered (row n))

Area covered - L * W

I have tried few combinations with open formula but finding it difficult to reference the field value from previous row.

 

 

Any help appreciated.

Thank you.

0 0

Replies

replied on April 7, 2022

Although you'd think you could reference the row above with something like this: =INDEX(table.column,ROW()-1) it gives a circular reference error since it's referencing the same value, even though from another row.

When I've tried to get around this by doing an additional column where column 3 sums columns 1 and 2, and column 2 gets the value of column 3 from the prior row, it also gives a circular reference.

The only way I've personally found to do a running total like this was to have a 3rd column (which can be hidden) that calculates the row number (   =ROW()   ).  Then for the subtotal column, do a SUMIF that sums the value column when the 3rd column is less than or equal to the current row (   =SUMIFS(table_variable.value_column,table_variable.row_number_column,"<="&ROW())   ).  The only issue I end up having with this is that when I delete a row, it doesn't recalculate the row numbers in the 3rd column.  I get around that by disabling the ability to delete rows on the table (usually via CSS).  It's possible that issue with the row number formula not updating was fixed at some point (I'm still on version 10.2.1 in Forms).

1 0
replied on April 7, 2022

Hi Mathew,

Thank you. I had the row count (using Row()) and was finding it difficult to push forward as the circular reference was causing an issue. I will try your SUMIF <= option).

Thank you once again.

1 0
replied on April 8, 2022

I had a look at this as well and the challenge I've found with SUMIF is that I couldn't use a variable in the condition. If you find an answer for that, let me know.

1 0
replied on April 13, 2022 Show version history

Yes, finally got it working (had mailed LF support as well).

This works if we add/delete row as well. Like @matthew mentioned above, have a field with =ROW() calculation. Add another filed where the running total is calculated. In this field (running total) use the following calculation ,

=SUMIF(Table.Row, "<="&ROW(), Table.SubTotal) 

Table.Row = Field which is used to identify row count 

Table.SubTotal= Field where the total of individual row is calculated

Thank you.

3 0
replied on April 13, 2022

Really glad to hear you got it working.

0 0
replied on April 13, 2022

@Matthew - Thank you

1 0
You are not allowed to follow up in this post.

Sign in to reply to this post.