Forum Discussion
Solved
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 instea...
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.
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.