Intensional circular reference

[eherron]

I have intensionally written circular reference formulas (see attached file). What I want to do is calculate the limits (rows 2 and 3) excluding any data that are outside those limits. Column B does not have the circular reference and works fine. I tried setting up 10 iterations with 0 to 1 maximum changes. I tried adding IF cell="#VALUE!" in the array. Nothing seems to work. Does anyone know how to do a circular reference calculation?

[shg]

Can you explain what you're trying to do?

[eherron]

I am try to automatically exclude data that is not within 3 standard deviations of the average from the calculation of the average and the standard deviation. This will lower the standard deviation and may then excude some additional data. This should remove any outliers from the calculation of the limits.

[shg]

How about an approach without iteration?

[djapigo]

Hi eherron,

I haven't looked at the circular reference deeply, but the first mistake I see is on rows 43 and 44... #VALUE! is not a text... therefore you can't have C2="#VALUE!"... you should change that to ISERROR(C2)...

Fix this first...

[eherron]

Hi, I used shg's idea to advoid the circular calculation by adding rows instead of columns, which works (see book3, sheet2), but is a very inelegant solution. djapigo was correct about changing to ISERROR() in the formula (sheet1), but this solution requires a manual calculate now after adding a new row of data (row 40, lot 5F01) even when set to automatic calculation. I would prefer to use the solution on sheet1, but not if the person entering the data needs to remember to do a calculate now action. Is there away around this (preferable without a macro enabled file).

[djapigo]

Hi eherron,

Again, I just can't seem to follow the circular reference, so I'm just going to avoid it... I'm sure you know that pressing F9 will calculate the whole worksheet... so instead of a macro, just use F9 to put a note to press F9 to calculate whenever someone is done...