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

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!

ExampleCircularReference.xlsx

Preparation

Accepted answers

danilang

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

Semi-CircularReferences.yxmd

All comments

mceleavey

Hi @henry_vandam ,

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

Thanks,

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.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15875

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703