SOLVED

New Contributor

Bypassing a circular reference

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.

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

Re: Bypassing a circular reference

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.