SOLVED

Bypassing a circular reference

%3CLINGO-SUB%20id%3D%22lingo-sub-2875347%22%20slang%3D%22en-US%22%3EBypassing%20a%20circular%20reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2875347%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20two%20arrays%20of%20data%3A%20A%20and%20B.%20In%20the%20normal%20case%2C%20data%20on%20B%20is%20dependent%20on%20A%20(e.g.%20B1%20is%20A1%20%2B%20X).%20There%20is%20a%20catch%20though.%20If%2C%20say%2C%20A1%20is%200%20(in%20the%20picture%20below%20the%20unwanted%20value%20is%204%20instead)%2C%20then%20B1%20will%20not%20be%20X%20(X%2B0)%2C%20but%20the%20Median%20of%20all%20B's%20that%20follow%20the%20normal%20case%20(Y).%20Y%20is%20determined%20by%20the%20formula%20%3DMEDIAN(IF(A%26lt%3B%26gt%3B0%2CB)).%20So%20in%20fact%2C%20data%20on%20B%20is%20given%20by%20the%20formula%20%3DIF(A%26lt%3B%26gt%3B0%2C%20A%2BX%2C%20Y).%20The%20error%20occurs%20when%20I%20expand%20the%20formula%20%3DIF(A%26lt%3B%26gt%3B0%2C%20A%2BX%2C%20Y)%20to%20the%20cells%20on%20B%20where%20A%20is%200%20(i.e.%20if%20A1%20is%200%2C%20when%20I%20try%20to%20expand%20that%20formula%20to%20B1%20the%20circular%20reference%20error%20will%20pop-up).How%20should%20I%20go%20about%20this%3F%20Is%20there%20something%20wrong%20with%20my%20formulas%2Flogic%20or%20is%20the%20problem%20not%20my%20fault%3F%3C%2FP%3E%3CP%3EThanks%20in%20advance!%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22Untitled.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F319341i1BA88605B9F42C53%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Untitled.png%22%20alt%3D%22Simpler%20representation%20where%20X%20is%20100.%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3ESimpler%20representation%20where%20X%20is%20100.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2875347%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
New Contributor

I have two arrays of data: A and B. In the normal case, data on B is dependent on A (e.g. B1 is A1 + X). There is a catch though. If, say, A1 is 0 (in the picture below the unwanted value is 4 instead), then B1 will not be X (X+0), but the Median of all B's that follow the normal case (Y). Y is determined by the formula =MEDIAN(IF(A<>0,B)). So in fact, data on B is given by the formula =IF(A<>0, A+X, Y). The error occurs when I expand the formula =IF(A<>0, A+X, Y) to the cells on B where A is 0 (i.e. if A1 is 0, when I try to expand that formula to B1 the circular reference error will pop-up).How should I go about this? Is there something wrong with my formulas/logic or is the problem not my fault?

Thanks in advance!

Simpler representation where X is 100.Simpler representation where X is 100.

 

1 Reply
best response confirmed by EverlastingSnail (New Contributor)
Solution

@EverlastingSnail 

There is an easy workaround. The median of the B's that follow the normal case = (the median of the A's not equal to the forbidden value) + X.

The median of the A's not equal to the forbidden value is =MEDIAN(IF(A<>forbidden,A))

See the attached workbook.