Multi-row Formulas that Reference Each Other (Circular Reference)

[henry_vandam]

Hello! I'm having issues on constructing with the multi-row formula tool a formula that is easily done in Excel files. I did search for a solution but couldn't find an answer for it. I'm trying to create two new columns on my data that reference each other (circular reference).

Example:

*Take F G H as already existing numeric columns in the database.

New Column A=

if F=1,

then (if G>H, then H, else G)

else (if Row-1:New Column B>H, then H, else Row-1:New Column B)

endif

New Column B=

if F=1,

then G-New Column A

else Row-1:New Column B-New Column A

endif

Does anyone have a clue of what I could do? Thanks for anything!

[mceleavey]

Hi @henry_vandam ,

could you please provide a sample dataset so we can understand what you're trying to achieve?

Thanks,

M.

[JagdeeshN]

@henry_vandam  have you tired  pivoting the data using a Transpose, use the Multi-row, and then pivot back with the cross-tab. In the multi-row you'd use a group by on the Name field.

This would enable you to apply a single multirow formula to multiple columns.

Also in the two formula's you have shared, 'New Column A' is using 'New Column B' and the vie versa. Considering both these fields are new, this will lead to errors in my opinion.

Have you also tried encapsulating the logic for A within the formula for B and vice versa?

Best,

jagdeesh

[henry_vandam]

@mceleavey Of course! Attached an Excel file to the main thread. Thanks for the interest.

[danilang · Accepted solution]

Hi @henry_vandam 

Since New Column A only references Row-1:New Column B, you can embed A completely within B, like @JagdeeshN suggested

If [F]=1 then 
   G-iif(F=1,iif(G>H,H,G),iif([Row-1:New Column B]>H,H,[Row-1:New Column B]))
else 
   [Row-1:New Column B]-iif(F=1,iif(G>H,H,G),iif([Row-1:New Column B]>H,H,[Row-1:New Column B]))
endif

Another tool to calculate A and then switch the columns around

Dan

[henry_vandam]

Hi @danilang! Thank you so much, that's exactly what I was looking for! (: