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!
Solved! Go to Solution.
Hi @henry_vandam ,
could you please provide a sample dataset so we can understand what you're trying to achieve?
Thanks,
M.
@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
@mceleavey Of course! Attached an Excel file to the main thread. Thanks for the interest.
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
Hi @danilang! Thank you so much, that's exactly what I was looking for! (: