Circular reference error

Question

I am trying to create a formula to calculate the average of a column on numbers. After the average is calculated, I want to apply the average less 1 to any blanks in the original column of numbers

For example, I have 32 member in a group and each one that attends gets some performance points. I take an average of the members that attended and would like to assign the absent members the average points of the members that attended less a penalty for not attending.

I run into a circular reference problem as soon as I try to use a formula to assign points to the absent member.

For example with 32 members with 2 absent, I calculate the average of the 30 members present. (all is good so far). but as soon as I try to create a formula to assign an amount for the absent member I get a circular error message because the absent members points are added into the column to calculate the original average.

Thanks in advance for any help you can provide.

Answer 1

Hi,

Your approach is bound to lead to a circular reference problem.  I think there are two ways out:

1. Use another column to assign scores based on the computed average; or
2. After computing the average, you copy that cell and paste special as values.  Now when you copy and paste the revised average in the blank cells, the circular reference error will not appear.

Hope this helps.

Answer 2

Larry wrote:

Column A - list of 32 names -- these names and the order do not change

Columns C:T performance scores (18 weeks)

Week 1 (Column C) points are earned for each of the 32 members that participated

If a member is not there the points are blank

I would like the calculate the average of the members that participated and based on that value, I would like to be able to give that value to the absent member.

As soon as I try to assign a value to the absent member, I get a circular reference error.

You must use an intermediate column to record the scores of participating members, with empty cells for absent members.

[EDIT] Unless you enable Iterative Calculation mode, which I do not recommend.  If you insist on avoiding intermediate columns, I would use a VBA macro to replace the missing data.

If you want to average on a week-by-week basis, put the original data into CC2:TT33, for example.  Then put a formula into C2:T33.

If you want literally what you wrote -- the value for the absent members (the same value for all) is 1 less than the average of the participating members -- put the following formula into C2 and copy down and across through T33:

=IF(CC2<>"", CC22, AVERAGE(CC$2:CC$33)-1)

Be careful with the "$":  that is CC$2, not $CC$2, for example.

But note that the value for the absent members will not be AVERAGE(C2:C33)-1.  Is that okay?

On the other hand, do you mean:  when you are done substituting a value (the same value) for the absent members, the value for the absent members will be 1 less than the average for all members (participants plus absentees)?

That is, the value of the absent members will be AVERAGE(C2:C33)-1 when you are done substituting.

If that is what you want, put the following formula into C2 and copy down and across through T33:

=IF(CC2<>"", CC2, ( SUM(CC$2:CC$33)-COUNTBLANK(CC$2:CC$33) ) / COUNT(CC$2:CC$33) - 1)

Again, be careful with the "$":  that is CC$2, not $CC$2, for example.

---

If you want to average over all weeks, not week-by-week, the formulas above become:

=IF(CC2<>"", CC22, AVERAGE($CC$2:$TT$33)-1)

and

=IF(CC2<>"", CC2, ( SUM($CC$2:$TT$33)-COUNTBLANK($CC$2:$TT$33) ) / COUNT($CC$2:$TT$33) - 1)

---

If you are interested, the derivation of the latter formula is as follows....

We want the following, where p=#participants, a=#absentees:

( Sigma(x[i], i=1,...,p) + a*(avg-1) ) / (p+a) = avg

Sigma(x[i], i=1,...,p) + a*avg - a = (p+a)*avg

Sigma(x[i], i=1,...,p) - a = (p+a)*avg - a*avg = avg*(p+a-a) = avg*p

( Sigma(x[i], i=1,...,p) - a ) / p = avg

where Sigma(...) = SUM(range), a = COUNTBLANK(range), and p = COUNT(range).