Oct 22 2021 12:45 PM
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!
Oct 22 2021 01:12 PM
SolutionThere 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.
Oct 22 2021 01:12 PM
SolutionThere 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.